Constraints on Temp tables

Yesterday, I had an interesting conversation on temp tables:
All started with this question:  What is the best method to create constraints on temp tables? I never expected it to give me a chance to learn something new.
As you know Temp tables are created in tempdb. Local temp tables are visible only to the user, and it allows multiple users to have temp tables on their own. That means while user A has a temp table #MyFirstTable another user can have a table (at the same time) at the same name.
Generally all objects created in a database will have an entry in sys.objects. For the table, the entry will not be on the same name. SQL Server will add additional characters to create a unique name. The objects are dropped when the connection is dropped or reset.
But there is a difference when it comes to constraints. When a constraint is created, it created with the exact name specified and it will have an entry in sys.objects table. For example the following code is going to fail if multiple users are going to execute during the same period.

CREATE TABLE #MyFirstTable

(

MyFirstTable_PK int,

CONSTRAINT PK_#MyFirstTable PRIMARY KEY CLUSTERED (MyFirstTable_PK)

)

The reason for failure is nothing to do with syntax. When the first user executes it two entries into sys.objects: a row with system defined name for table and another row for name we specified for primary key. When the second user executes the code, it can add the row for table but it can’t add an entry for primary key. So it will fail for second user.

String "Aggregates" Solution

Even though I promised to update a solution to a problem,  I failed to do so for a long time.

Here is the problem

If you need to get the sum of salary by department we may be able to use Sum and group by clauses in a single select statement to get it done.  But if you have a situation where you need to give a comma separated string of employees for each department, how will you handle it?
Consider these tables:

Department Table

DepartmentID	DepartmentName
1	Sales
2	IT
3	Finance

Employee Table

EmployeeID	DepartmentID	EmployeeName
1	1	Sales Person 1
2	1	Sales Person 2
3	2	Developer 1
4	2	Developer 2
5	2	DBA 1

Now you want the results like this:

DepartmentName	EmployeeList
Sales	Sales Person 1, Sales Person 2
IT	Developer 1, Developer 2, DBA
Finance	NULL

Identifying Identity related functions

Almost after a week I am writing this post. The heave work load at office and some of the other commitments prevented me from writing during most of the last week.

This time I decided to write about Identity column as I see this issue coming again and again in various forums and blogs.

These are the principles:
When you create a table with the identity property, it does not have any values assigned to it.

USE tempdb;

CREATE TABLE t1 (id int IDENTITY(1,1) )

GO

  

Now we'll check each identity related command and see the results.

SELECT IDENT_CURRENT('t1'), SCOPE_IDENTITY(), @@IDENTITY

IDENT_CURRENT function will return the seed value. This will return null under one condition: The table does not have any column with identity property (or table does not exist).

As there is no statement in this batch has inserted value into this table, both SCOPE_IDENTITY and @@IDENTITY return null.

Now Let us insert a row into t1

INSERT INTO t1 DEFAULT VALUES

SELECT IDENT_CURRENT('t1'), SCOPE_IDENTITY(), @@IDENTITY

Once the first row is inserted, the current identity value will be set to 1.
As the statement in the query inserts a row to t1, scope_identity returns 1.  Also, as that is the last row to be inserted, @@Identity too will return 1


Now we'll do another experiment.
DROP TABLE t1
GO

SELECT IDENT_CURRENT('t1'), SCOPE_IDENTITY(), @@IDENTITY

Are tou receiving values different from what you expected?

1. Ident_Current gives null
2. Both Scope_Identity and @@Identity return a new value:1
   

As the table does not exist, Ident_Current returns null.
Scope_Identity returns a value even though the table does not have any values. Even if the table is not recreated, scope_identity returns the same value it returned when the table was existed.
@@Identity will return the last identity value inserted within the session. This is not tied to a table.  Currently even though we do not have any table with the identity column, @@Identity will return a value it got within the session.

These values will again reset to null when the session is closed.

I'll continue on Identity on future blogs as well.

Here is the code for yesterday's Scenario:
create table #Department
(
DepartmentID int not null primary key clustered,
DepartmentName varchar(10) not null
)

create table #Employee
(
EmployeeID int not null identity primary key clustered,
DepartmentID int,
EmployeeName varchar(100)
)


insert #Department values(1, 'Sales'), (2, 'IT'), (3, 'Finance')
insert #Employee values (1, 'Sales Person1'), (1, 'Sales Person 2'), (2, 'Developer 1'), (2, 'Developer 2'),(2, 'DBA')

How to "aggregate" on strings

If you need to get the sum of salary by department we may be able to use Sum and group by clauses in a single select statement to get it done. But if you have a situation where you need to give a comma separated string of employees for each department, how will you handle it?
Consider these tables:

Department Table

DepartmentID	DepartmentName
1	Sales
2	IT
3	Finance

Employee Table

EmployeeID	DepartmentID	EmployeeName
1	1	Sales Person 1
2	1	Sales Person 2
3	2	Developer 1
4	2	Developer 2
5	2	DBA 1

Now you want the results like this:

DepartmentName	EmployeeList
Sales	Sales Person 1, Sales Person 2
IT	Developer 1, Developer 2, DBA
Finance	NULL

Catching row count and error number

After some days I am writing this blog.
I had a couple of training programs (total of 5.5 days) and then I need to travel to US (on official visit!) so I couldn't update the blog.
So here is the update.

If you need to track error number and row count of a statement how will you handle it?
Both of these are captured using @@ERROR and @@ROWCOUNT system variables.
But there is a catch:
They reflect the value of the immediate SQL statement irrespective of whether it is a DML statement (INSERT, UPDATE, DELETE & SELECT statements) or just a control statement. (Examples are IF, SET, WHILE) That means if you add SET @Err_Variable = @@ERROR after a DML statement, you will be able to catch the error number, but it will take the statement SET @Row_Variable = @@ROWCOUNT to return 1 always
How to resolve it?

NOT IN, NOT EXISTS and LEFT JOIN ... What To Use?

This blog is to analyze the differences between the above statements and to find what suits where better.
Lets consider this scenario: (This example is taken from AdventureWorks) Find all customers who do not have Sales. It could be written in three ways:


SET ANSI_NULLS ON
-- Method 1: Using NOT IN
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE CustomerID NOT IN
(
SELECT CustomerID
FROM Sales.SalesOrderHeader
)

-- Method 2: Using NOT EXISTS
SELECT CustomerID, AccountNumber
FROM Sales.Customer C
WHERE NOT EXISTS
(
SELECT 1
FROM Sales.SalesOrderHeader S
WHERE S.CustomerID = C.CustomerID
)

-- MEthod 3: Using LEFT JOIN
SELECT c.CustomerID, c.AccountNumber
FROM Sales.Customer c
LEFT JOIN Sales.SalesOrderHeader soh
ON soh.CustomerID = c.CustomerID
WHERE soh.CustomerID IS NULL


All the above queries give the same results, show the same execution plan, take the same amount of resources in IO and executed almost at the same time. However, this example is quite simple. The column CustomerID is not having any null values. When it comes with null values, the results may vary:

Update on Getting Table Row Count(For Replicated Data)

After seeing my last post, I got a few requests:

• Can't we resolve the schema issue with sp_SpaceUsed?
• Can we modify the query to handle multiple susbcriptions bit more efficiently?

Let me explain the second issue first. the query in the post calculates the row count each time for each subscription.  It could be written efficiently for multiple subscriptions.

Getting The Table Usage

Recently I had an issue with replication. Suddenly due to some reason, some of the replications have failed but no error is reported in replciation monitor. We found out only when developers complained that the data they entered has not reflected properly in all places. We identified that the issue is with replication and quickly checked the tables he mentioned. There was a difference in number of rows between the source table and the destination table. We immediately wrote some scripts to transfer the remaining data and dropped the subscription and created it again.
When replication started working, (Okay it didn’t start working until we find out that there was a security issue and correct it.) I wanted to check what are the other tables were affected.

As I know the publication database and subscription database, we can get the data from sysarticles table from the publication database.

There were three methods before me to get row count.

1. Selecting data by using SELECT COUNT(1) FROM <table> method. This result is accurate, but it will consume a lot of memory and time. This will result reading the entire clustered index (a full clustered index scan) or a table scan.
2. Reading the rows FROM sysindexes for the clustered index or heap. This is the fastest method but there is a possibility of inaccuracy. (Even when I ran the test I got some slightly inaccurate results.) This method is fairly okay if you want to know the approximate row count, but in case you need to get the exact row count, this method is not recommended.
3. Using sp_SpaceUsed system procedure. This method also does not give accurate numbers; additionally as this is a stored procedure, it does not gives the flexibility to add additional columns or remove unnecessary columns. Additionally, even though this stored procedure accepts schema_name.table_name format as input, on the output only table name is mentioned. This may give some issues if your database having multiple tables with the same name (in different schemas).

I went with the first method to get the row count and completed the issues as I got the weekend to resolve it. (Remember last weekend was a long weekend.) as I already had a script to work with and I added a few things to complete it. I have added the script with some modifications for you all to use it.

SELECT '
        SELECT ''source'' AS Table_Location,
               '''+ OBJECT_SCHEMA_NAME(a.objid)+''' AS SchemaName,
               '''+ OBJECT_NAME(a.objid) +''' AS TableName,
               COUNT(*) as row_count
        FROM '+ OBJECT_SCHEMA_NAME(a.objid)+'.'+ OBJECT_NAME(a.objid)+'
        UNION ALL
        SELECT ''destination'' AS Table_Location,
               '''+ a.dest_owner +''' AS SchemaName,
               '''+ a.dest_table +''' AS TableName,
               COUNT(*) as row_count
         FROM '+ s.dest_db +'.'+ a.dest_owner +'.'+ a.dest_table
FROM dbo.sysarticles a
INNER JOIN dbo.syssubscriptions s
ON a.artid = s.artid

Is this usefull?

Average Function

Have you ever tried something like this:

SELECT AVG(NUM)
FROM
(
    SELECT 1 AS NUM UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 0

) AS A

Are you expecting an answerr of 0.75? You will be surprised to see the return value is only 0.
The reason behind this is how average function works.

If the input values are of integer data type, the average function too will return an average value.
So if you have a column which has integer data type, you need to convert it to decimal before calculating the average value

SELECT AVG(NUM*1.0)
FROM
(
    SELECT 1 AS NUM UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 0

) AS A


Are you happy now?

Have Fun with SQL and Prime Numbers

• Want to have some fun with SQL Server?
• Interested in math puzzles?
• Like to win a $100 Amazon Voucher?

Try this out! Celko's Summer SQL Stumpers: Prime Numbers You have a better chance of having fun the the $100 voucher!

Storing Hierarchical Data - An Early Solution

SQL Server 2008 can with a new data type to handle hierarchical data – hierarchyid.

How to handle hierarchical data in the previous editions?

Even though it is not supported natively, there are different methods we could employ to handle hierarchical data. Some of them are rely cool and perform as fast as SQL Server 2008.

I have written an article on this:
Storing Hierarchical Data - An Early Solution
 
Even though this site requires paid subscription to read this article, you could go with online trial. You can cancel it if you don't like the site