Sunday, August 27, 2017

Why would anyone use a global temporary table?



SQL Azure has added something called database scoped global temporary tables.

Azure SQL Database supports global temporary tables that are also stored in tempdb and scoped to the database level. This means that global temporary tables are shared for all users’ sessions within the same Azure SQL database. User sessions from other Azure SQL databases cannot access global temporary tables.

They way you add these is by using a double pound sign

They way you create a database scoped global temporary table is like this

CREATE TABLE ##test ( a int, b int);

You are probably thinking...wait a minute, how is this different from a global temporary table? It is pretty much the same but on SQL Azure, it is scoped to the database level, while on prem it is scoped to the instance level


I was listening to the latest SQL Server Radio podcast and the hosts were talking about why anyone would ever need a global temporary table. Why not use a real table instead

I can come up with one answer....

Let's take a look

First create a database and then add a user who has read and write permissions


CREATE DATABASE Test
GO

CREATE LOGIN DenisTest   
    WITH PASSWORD = 'DenisTest', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; 

USE Test
GO

CREATE USER DenisTest FOR LOGIN DenisTest;  
GO   

ALTER ROLE [db_datareader] ADD MEMBER DenisTest
GO
ALTER ROLE [db_datawriter] ADD MEMBER DenisTest
GO

Now, let say we want to run a query several times and store the results



USE Test
GO

SELECT name,type_desc,state_desc,size
FROM sys.database_files
WHERE name ='Test'

So I need a table to store the results right?
Let's try creating one......

CREATE TABLE SomeTable (name sysname,
type_desc nvarchar(120),
state_desc nvarchar(120),
size int)

Msg 262, Level 14, State 1, Line 8
CREATE TABLE permission denied in database 'Test'.

That is right, we don't have permissions

Now let's add a double pound sign in front of that table

CREATE TABLE ##SomeTable (name sysname,
type_desc nvarchar(120),
state_desc nvarchar(120),
size int)

And this works

Now we can insert into the table we just created

INSERT ##SomeTable
SELECT name,type_desc,state_desc,size
FROM sys.database_files
WHERE name ='Test'

Also, we can insert and select from other sessions.query windows as well

Of course if the server restarts, this table will be gone. However if you want to capture some query output and look at it, you don't have to wait for someone to give you ddl_admin permission, create the table for you or make you db_owner.


Now why everyone would need a temporary stored procedure...that is another story and I can't really think of a reason









Thursday, August 24, 2017

PASS tv has added a boatload of videos



While I was away on vacation in San Francisco,  I noticed that PASS tv has added dozens of videos to their YouTube channel. These videos were added 2 days ago. There should be something there for everyone interested in working with SQL Server as well as interested in presenting

There are videos by Kalen Delaney, Bob Ward, Buck Woody, Kendra Little and many more


You can find all the videos here: https://www.youtube.com/user/SQLPASSTV/videos


Sunday, August 06, 2017

I don't always use a RIGHT JOIN, but when I do, it's because I am lazy



Do you use RIGHT JOINs? I myself rarely use a RIGHT JOIN, I think in the last 17 years or so I have only used a RIGHT JOIN once or twice. I think that RIGHT JOINs confuse people who are new to databases, everything that you can do with a RIGHT JOIN, you can also do with a LEFT JOIN, you just have to flip the query around

So why did I use a RIGHT JOIN then?

Well the answer is in the image below



That is right..it was pure laziness, if you ever met a programmer, you will find out pretty quickly that programmers are pretty lazy. I guess lazy is not the right word to use here, it is better to call programmers efficient or people who don't want to reinvent the wheel.

So what actually happened? I was checking some data in a table, I already had written the following query

SELECT number 
FROM SomeTableWithIDs c

Now I just needed to see how many numbers were not used

So what did I do, I just continued below the query I already had, I ended up with this

SELECT number 
FROM SomeTableWithIDs c
RIGHT JOIN(SELECT number 
FROM master..spt_values 
WHERE type = 'p' 
AND number BETWEEN 0 and 255) x on c.SomeID = x.number
WHERE c.SomeID is null


Let's take a look at this again with some DDL and DML you can run

First create the following table, it has only one column, this column has a tinyint data type (don't ask why it is tinyint, this is the reason I had to take a look at how many unused IDs we had to begin with)

CREATE TABLE SomeTableWithIDs (SomeID tinyint not null )

ALTER TABLE SomeTableWithIDs
ADD CONSTRAINT PK_SomeTableWithIDs PRIMARY KEY CLUSTERED (SomeID)


Now that we have the table created, let's populate it with 250 rows

INSERT SomeTableWithIDs
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY s.ID)
FROM sys.sysobjects s
CROSS JOIN sys.sysobjects s2

Let's delete a couple of rows

DELETE SomeTableWithIDs WHERE SomeID IN (2,4,6,11)


Running the RIGHT JOIN query will give us the missing rows

SELECT number 
FROM SomeTableWithIDs c
RIGHT JOIN(SELECT number 
FROM master..spt_values 
WHERE type = 'p' 
AND number BETWEEN 0 and 255) x on c.SomeID = x.number
WHERE c.SomeID is null

Here is the output

0
2
4
6
11
251
252
253
254
255



Now you have to realize that this was just an ad-hoc query, if I were to put this in a proc or script, I would have done it like this

SELECT number 
FROM master..spt_values s
WHERE type = 'p' 
AND number BETWEEN 0 and 255
AND NOT EXISTS (SELECT SomeID 
   FROM SomeTableWithIDs c 
   WHERE c.SomeID = s.number)


That query is less confusing for beginners than a RIGHT JOIN