Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Your profile

Search




Main Categories

Categories

Web Developer

Architecture, Design & Strategy

Data Management

Desktop Developer

Enterprise Developer

System Admins

IT Professionals

IT Students & Researchers

November 2008
Mon Tue Wed Thu Fri Sat Sun
 << <   > >>
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

Tools

XML Feeds

What is RSS?

Tag cloud

.net .net 3.5 asp.net azure book browser bug c# chrome cloud computing database design patterns flash free functions generate google hotfix howto indexing interview kilimanjaro linq linux nhibernate nunit performance problem programming resharper review rhino mocks ria silverlight sql sql server sql server 2005 sql server 2008 sqlserver structuremap t-sql tip toread trick unit testing vb.net video windows forms windows presentation foundation wpf

Tags: t-sql

All Blogs

All the LessThanDot Journals

FIX for A MERGE statement may not enforce a foreign key constraint when the statement updates a unique key column that is not part of a clustering key bug

by SQLDenis


Permalink 16 Sep 2008 07:07 , Categories: Data Modelling & Design Tags: bug, hotfix, sql server 2008, t-sql

FIX: A MERGE statement may not enforce a foreign key constraint when the statement updates a unique key column that is not part of a clustering key and there is a single row as the update source in SQL Server 2008

In Microsoft SQL Server 2008, a foreign key constraint may not be enforced when the following conditions are true:
• A MERGE statement is issued.
• The target column of the update has a nonclustered unique index.

Microsoft has created the first Hotfix for SQL Server 2008 which will fix this issue.

You can read more about this bug including how you can obtain the fix here: http://support.microsoft.com/kb/956718

Leave a comment » • Send a trackback » 223 views

What is deferred name resolution and why do you need to care?

by SQLDenis


Permalink 08 Sep 2008 08:23 , Categories: Data Modelling & Design Tags: gotcha, howto, sql server, t-sql, tip, trick

So I posted a teaser in the puzzles forum. Without running this, try to guess what will happen?

tsqlLine number On/Off | Show/Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. IF (@x = 0)
  6. BEGIN
  7.     SELECT 1 AS VALUE INTO #temptable
  8. END
  9. ELSE
  10. BEGIN
  11.    SELECT 2 AS VALUE INTO #temptable
  12. END
  13.  
  14. SELECT * FROM #temptable –what does this return

This is the error you get
Server: Msg 2714, Level 16, State 1, Line 12
There is already an object named ‘#temptable’ in the database.

You can do something like this to get around the issue with the temp table

tsqlLine number On/Off | Show/Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. CREATE TABLE #temptable (VALUE INT)
  6. IF (@x = 0)
  7. BEGIN
  8.     INSERT #temptable
  9.     SELECT 1
  10. END
  11. ELSE
  12. BEGIN
  13.     INSERT #temptable
  14.     SELECT 2
  15. END
  16.  
  17. SELECT * FROM #temptable –what does this return

So what is thing called Deferred Name Resolution? Here is what is explained in Books On Line

When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the syscomments system table.

When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the syscomments system table of the procedure and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

In the resolution stage, Microsoft SQL Server 2000 also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, the stored procedure stops executing when it gets to the statement that references the missing object. In this case, or if other errors are found in the resolution stage, an error is returned.

So what is happening is that beginning with SQL server 7 deferred name resolution was enabled for real tables but not for temporary tables. If you change the code to use a real table instead of a temporary table you won’t have any problem
Run this to see what I mean

tsqlLine number On/Off | Show/Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. IF (@x = 0)
  6. BEGIN
  7.     SELECT 1 AS VALUE INTO temptable
  8. END
  9. ELSE
  10. BEGIN
  11.    SELECT 2 AS VALUE INTO temptable
  12. END
  13.  
  14. SELECT * FROM temptable –what does this return

What about variables? Let’s try it out, run this

tsqlLine number On/Off | Show/Hide | Select all
  1. DECLARE @x INT
  2.  
  3. SET @x = 1
  4.  
  5. IF (@x = 0)
  6. BEGIN
  7.     DECLARE @i INT
  8.     SELECT @i = 5
  9. END
  10. ELSE
  11. BEGIN
  12.    DECLARE @i INT
  13.    SELECT @i = 6
  14. END
  15.  
  16. SELECT @i

And you get the follwing error
Server: Msg 134, Level 15, State 1, Line 13
The variable name ‘@i’ has already been declared. Variable names must be unique within a query batch or stored procedure.

Now why do you need to care about deferred name resolution? Let’s take another example from a blogpost I made a while back: Do you depend on sp_depends (no pun intended)

First create this proc

tsqlLine number On/Off | Show/Hide | Select all
  1. CREATE PROC SomeTestProc
  2. AS
  3. SELECT dbo.somefuction(1)
  4. GO

now create this function

tsqlLine number On/Off | Show/Hide | Select all
  1. CREATE FUNCTION somefuction(@id INT)
  2. RETURNS INT
  3. AS
  4. BEGIN
  5. SELECT @id = 1
  6. RETURN @id
  7. END
  8. Go

now run this

tsqlLine number On/Off | Show/Hide | Select all
  1. SP_DEPENDS ’somefuction’

result: Object does not reference any object, and no objects reference it.

Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won’t be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

tsqlLine number On/Off | Show/Hide | Select all
  1. SELECT specific_name,*
  2. FROM information_schema.routines
  3. WHERE object_definition(OBJECT_ID(specific_name)) LIKE ‘%somefuction%’
  4. AND routine_type = ‘procedure’
4 comments » • Send a trackback » 753 views

SQL Server 2008 Express with Advanced Services And SQL Server 2008 Express With Tools Now Available For Download

by SQLDenis


Permalink 26 Aug 2008 12:25 , Categories: Data Modelling & Design Tags: free, rdmbs, sql, sql server 2008, sql server 2008 express, t-sql

There are 3 versions of SQL Server 2008 Express now available for download from the SQL Server 2008 Express site. Below are the 3 different versions

SQL Server 2008 Express

  • SQL Server database engine - create, store, update and retrieve your data

SQL Server 2008 Express with Tools

  • SQL Server database engine - create, store, update and retrieve your data
  • SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases

SQL Server 2008 Express with Advanced Services

  • SQL Server database engine - create, store, update and retrieve your data
  • SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases
  • Full-text Search - powerful, high-speed engine for searching text-intensive data
  • Reporting Services - integrated report creation and design environment to create reports

Get it all here http://www.microsoft.com/express/sql/download/

Make sure to register your copy so that you can get a free eBook and other goodies

1 comment » • Send a trackback » 515 views

How Do You Check If A Temporary Table Exists In SQL Server

by SQLDenis


Permalink 22 Aug 2008 08:57 , Categories: Data Modelling & Design Tags: howto, programming, sql server, t-sql, tip, trick

I see more and more people asking how to check if a temporary table exists. How do you check if a temp table exists?

You can use IF OBJECT_ID(’tempdb..#temp’) IS NOT NULL Let’s see how it works

tsqlLine number On/Off | Show/Hide | Select all
  1. –Create table
  2. USE Norhtwind
  3. GO
  4.  
  5. CREATE TABLE #temp(id INT)
  6.  
  7. –Check if it exists
  8. IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
  9. BEGIN
  10. PRINT ‘#temp exists!’
  11. END
  12. ELSE
  13. BEGIN
  14. PRINT ‘#temp does not exist!’
  15. END
  16.  
  17. –Another way to check with an undocumented optional second parameter
  18. IF OBJECT_ID(‘tempdb..#temp’,‘u’) IS NOT NULL
  19. BEGIN
  20. PRINT ‘#temp exists!’
  21. END
  22. ELSE
  23. BEGIN
  24. PRINT ‘#temp does not exist!’
  25. END
  26.  
  27.  
  28.  
  29. –Don’t do this because this checks the local DB and will return does not exist
  30. IF OBJECT_ID(‘tempdb..#temp’,‘local’) IS NOT NULL
  31. BEGIN
  32. PRINT ‘#temp exists!’
  33. END
  34. ELSE
  35. BEGIN
  36. PRINT ‘#temp does not exist!’
  37. END
  38.  
  39.  
  40. –unless you do something like this
  41. USE tempdb
  42. GO
  43.  
  44. –Now it exists again
  45. IF OBJECT_ID(‘tempdb..#temp’,‘local’) IS NOT NULL
  46. BEGIN
  47. PRINT ‘#temp exists!’
  48. END
  49. ELSE
  50. BEGIN
  51. PRINT ‘#temp does not exist!’
  52. END
  53.  
  54. –let’s go back to Norhtwind again
  55. USE Norhtwind
  56. GO
  57.  
  58.  
  59. –Check if it exists
  60. IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
  61. BEGIN
  62. PRINT ‘#temp exists!’
  63. END
  64. ELSE
  65. BEGIN
  66. PRINT ‘#temp does not exist!’
  67. END

now open a new window from Query Analyzer (CTRL + N) and run this code again

tsqlLine number On/Off | Show/Hide | Select all
  1. –Check if it exists
  2. IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
  3. BEGIN
  4. PRINT ‘#temp exists!’
  5. END
  6. ELSE
  7. BEGIN
  8. PRINT ‘#temp does not exist!’
  9. END

It doesn’t exist and that is correct since it’s a local temp table not a global temp table

Well let’s test that statement

tsqlLine number On/Off | Show/Hide | Select all
  1. –create a global temp table
  2. CREATE TABLE ##temp(id INT) –Notice the 2 pound signs, that’s how you create a global variable
  3.  
  4. –Check if it exists
  5. IF OBJECT_ID(‘tempdb..##temp’) IS NOT NULL
  6. BEGIN
  7. PRINT ‘##temp exists!’
  8. END
  9. ELSE
  10. BEGIN
  11. PRINT ‘##temp does not exist!’
  12. END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

tsqlLine number On/Off | Show/Hide | Select all
  1. –Check if it exists
  2. IF OBJECT_ID(‘tempdb..##temp’) IS NOT NULL
  3. BEGIN
  4. PRINT ‘##temp exists!’
  5. END
  6. ELSE
  7. BEGIN
  8. PRINT ‘##temp does not exist!’
  9. END

And yes this time it does exist since it’s a global table

I have also added this to our wiki, read it here: Check If Temporary Table Exists

Leave a comment » • Send a trackback » 1249 views

Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

by SQLDenis


Permalink 17 Aug 2008 07:55 , Categories: Data Modelling & Design Tags: database, dates, indexing, performance tuning, rdbms, sql, t-sql, temporal data

Take a look at this query.

tsqlLine number On/Off | Show/Hide | Select all
  1. SELECT * FROM
  2.  
  3. (
  4.  
  5. SELECT customer_id, ‘MTD’ AS record_type, COUNT(*), SUM(…), AVG(…)
  6.  
  7. FROM payment_table
  8.  
  9.  
  10. WHERE YEAR(payment_dt) = YEAR(GETDATE())
  11.  
  12. and MONTH(payment_dt) = MONTH(GETDATE())
  13.  
  14. GROUP BY customer_id) MTD_payments
  15.  
  16. UNION ALL
  17.  
  18. (
  19.  
  20. SELECT customer_id, ‘YTD’ AS record_type, COUNT(*), SUM(…), AVG(…)
  21.  
  22. FROM payment_table
  23.  
  24. WHERE
  25.  
  26. WHERE YEAR(payment_dt) = YEAR(GETDATE())
  27.  
  28. GROUP BY customer_id) YTD_payments
  29.  
  30. UNION ALL
  31.  
  32. (
  33.  
  34. SELECT customer_id, ‘LTD’ AS record_type, COUNT(*), SUM(…), AVG(…)
  35.  
  36. FROM payment_table) LTD_payments
  37.  
  38. ) payments_report
  39.  
  40. ORDER BY customer_id, record_type

Can you see the problem?
A person had this query, it would run for over 24 hours. Wow, that is pretty bad, I don’t think I had ever written something that ran over an hour, and the ones I did were mostly defragmentation and update statistics jobs.

The problem is that the following piece of code

tsqlLine number On/Off | Show/Hide | Select all
  1. WHERE YEAR(payment_dt) = YEAR(GETDATE())
  2. and MONTH(payment_dt) = MONTH(GETDATE())

is not sargable. First what does it mean to be sargable? A query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.

This query is not sargable because there is a function on the column, whenever you use a function on the column you will not get an index seek but an index scan. The difference between an index seek and an index scan can be explained like this: when searching for something in a book, you go to the index in the back find the page number and go to the page, that is an index seek. When looking for something in a book you go from page one until the last page, read all the words on all the ages and get what you need, that was an index scan. Do you see how much more expensive in terms of performance that was?

Let’s get back to the query, what can we do to make this piece of code use an index seek?

tsqlLine number On/Off | Show/Hide | Select all
  1. WHERE YEAR(payment_dt) = YEAR(GETDATE())
  2. and MONTH(payment_dt) = MONTH(GETDATE())

You would change it to this:

tsqlLine number On/Off | Show/Hide | Select all
  1. WHERE payment_dt >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+0, 0)
  2. and payment_dt < DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0)

You can see the complete question on the MSDN forum site here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3746751&SiteID=1

The Person said that his query went from over 24 hours to 36 seconds. Wow!! That is very significant. hardware cannot help you out if you have bad queries like that.

The same exact day I answered a very similar question, take a look here: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3752248&SiteID=1

The person had this

tsqlLine number On/Off | Show/Hide | Select all
  1. AND DATEDIFF(d, ‘08/10/2008′, DateCreated) >= 0
  2. AND DATEDIFF(d, DateCreated, ‘08/15/2008′) >= 0

I told him to change it to this

tsqlLine number On/Off | Show/Hide | Select all
  1. AND DateCreated >= ‘08/10/2008′
  2. and DateCreated <= ‘08/15/2008′

And that solved that query. If you are interested in some more performance, I have written some Query Optimization items on the LessThanDot Wiki. Below are some direct links

Case Sensitive Search
No Functions on Left Side of Operator
Query Optimizations With Dates
Optimization: Set Nocount On
No Math In Where Clause
Don't Use (select *), but List Columns

If you are interested in some blogposts about dates, take a look at these two which I wrote earlier
How Are Dates Stored In SQL Server?
Do You Know How Between Works With Dates?

16 comments » • 1 trackback » 5852 views

Tools


RssFeeds

Blogs


Become a blogger

Statistics


Top 10 rated users for the last 7 days

User Ratings
Emtucifor16
Remou13
AlexCuse10
ZmrAbdulla10
j460610
damber6
SQLDenis4
ptheriault3
Thirster421
onpnt1

Links



With Thanks To...


Powered by B2Evolution
Valid XHTML | Valid CSS