Interview With Louis Davidson Author of Pro SQL Server 2008 Relational Database Design and Implementation

I interviewed Louis Davidson in 2005 about the 2005 edition of his book. I did another one today about Pro SQL Server 2008 Relational Database Design and Implementation. You can check out the interview at the link below, you will enjoy it


Interview With Louis Davidson Author of Pro SQL Server 2008 Relational Database Design and Implementation

Interesting Bug/Feature In SQL Server 2008 RTM

Someone had a problem with 8 year old procs which started to fail after moving to SQL Server 2008
Of course he should have used ints, but let's see what happens

Run this code on SQL Server 2005 and 2000

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;
SELECT @num_Passed


IF (@num_Passed = 0)
PRINT 'True';



No problem right?

Run just this part on SQL 2008


DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;

SELECT @num_Passed


No problem either
Now run this whole thing

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;
SELECT @num_Passed


IF (@num_Passed = 0)
PRINT 'True';




Oops, this is what we get
Server: Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type tinyint.


Change the -1 to 1

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = 1;
SELECT @num_Passed



IF (@num_Passed = 0)
PRINT 'True';




No problem either.

Run this

IF (convert(Numeric(2, 0),-1) = 0)
PRINT 'True';

That fails
Let's make it numeric(3,0)

IF (convert(Numeric(3, 0),-1) = 0)
PRINT 'True';



No problem, that runs fine. So is this a bug because of implicit conversion to tinyint which can't hold negative values?

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

Take a look at this query.

SELECT * FROM

(

SELECT customer_id, ‘MTD’ AS record_type, COUNT(*), SUM(…), AVG(…)

FROM payment_table

WHERE YEAR(payment_dt) = YEAR(GETDATE())

and MONTH(payment_dt) = MONTH(GETDATE())

GROUP BY customer_id) MTD_payments

UNION ALL

(

SELECT customer_id, ‘YTD’ AS record_type, COUNT(*), SUM(…), AVG(…)

FROM payment_table

WHERE

WHERE YEAR(payment_dt) = YEAR(GETDATE())

GROUP BY customer_id) YTD_payments

UNION ALL

(

SELECT customer_id, ‘LTD’ AS record_type, COUNT(*), SUM(…), AVG(…)

FROM payment_table) LTD_payments

) payments_report

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

where year(payment_dt) = year(getDate())
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?
where year(payment_dt) = year(getDate())
and month(payment_dt) = month(getDate())

You would change it to this:
where payment_dt >= dateadd(mm, datediff(mm, 0, getdate())+0, 0)
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

AND DATEDIFF(d, '08/10/2008', DateCreated) >= 0

AND DATEDIFF(d, DateCreated, '08/15/2008') >= 0

I told him to change it to this

AND DateCreated >= '08/10/2008'
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?

SQL Tip, Compiling Your SQL Without Running It to See If It Would Run

Let's say you have a big SQL script with a ton of code and you want to make sure it runs but you don't want to execute it because it updates tables, deletes data etc etc.
Take this simple example

SELECT GETDATE()
GO
SELECT 1/asasasas
GO


You can probably guess that the second statement is not valid, when you have a lot of code it is more difficult to spot these things.
Execeute the code above and you will get this


(1 row(s) affected)

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'asasasas'.

SQL server has the SET NOEXEC statement. From BOL:

When SET NOEXEC is ON, Microsoft® SQL Server™ compiles each batch of
Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all
batches are executed after compilation.

The execution of statements in
SQL Server consists of two phases: compilation and execution. This setting is
useful for having SQL Server validate the syntax and object names in
Transact-SQL code when executing. It is also useful for debugging statements
that would usually be part of a larger batch of statements.

The setting
of SET NOEXEC is set at execute or run time and not at parse time.

So execute the code below

SET NOEXEC ON
GO

SELECT GETDATE()
GO
SELECT 1/asasasas
GO
SET NOEXEC OFF
GO



As you can see the output is the following:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'asasasas'.


You never see the getdate. Parsing that code will not throw an error and because of deferred name resolution you can fat-finger table names and it will parse without a problem.

Microsoft Visual Studio 2008 Service Pack 1 Available For Download

Get it here: http://www.microsoft.com/downloads/thankyou.aspx?familyId=fbee1648-7106-44a7-9649-6d9f6d58056e&displayLang=en

Here is the link to the 831MB ISO
http://www.microsoft.com/downloads/details.aspx?FamilyId=27673C47-B3B5-4C67-BD99-84E525B5CE61&displaylang=en


Finally, Now I can also install SQL Server 2008 :-)

SQL Server 2008 is RTM

that is right and you can already download it from MSDN

Microsoft Visual Studio Team System 2008 Database Edition GDR July CTP Released

Microsoft® Visual Studio Team System 2008 Database Edition GDR July CTP is available for download

In addition to providing support for SQL Server 2008 database projects, this release incorporates many previously released Power Tools as well as several new features. The new features include distinct Build and Deploy phases, Static Code Analysis and improved integration with SQL CLR projects.

Database Edition no longer requires a Design Database. Therefore, it is no longer necessary to install an instance of SQL Express or SQL Server prior to using Database Edition.

Download it here: http://www.microsoft.com/downloads/deta ... laylang=en

sp_indexinfo an enhanced index information procedure

Tibor Karaszi has created a very useful index information stored procedure for SQL Server 2005 and up.
This stored procedure will tell you the following"

What indexes exists for a or each table(s)
Clustered, non-clustered or heap
Columns in the index
Included columns in the index
Unique or nonunique
Number rows in the table
Space usage
How frequently the indexes has been used

Check it out here: http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

Awesome Collection Of ASP.NET Hacks, Tips and Tricks

We added a bunch of ASP.NET Hacks, Tips and Tricks

we have the following categories
1 Applications
2 Caching
3 Controls
4 Database
5 Dates
6 Debugging
7 Email
8 Encryption
9 Files
10 Images
11 Javascript
12 Objects and Classes
13 Pages
14 Sessions
15 Strings
16 Validation
17 Visual Studio
18 Web

The URL to these hacks is here: http://wiki.lessthandot.com/index.php/ASP.NET_Hacks
Bookmark that URL because we will be adding more hacks, tips and tricks

Interview With SSIS Guru Jamie Thomson

As promised yesterday, here is the link to the interview I did with jamie Thomson: Interview With SSIS Guru Jamie Thomson

I tried to keep it a secret but it is all over the internet: I am a SQL Server MVP

So all over the internet is exaggerated,I became a SQL Server MVP today and was not going to do a blog post about it. However some other people had other ideas

Congratulations, Denis!
SQL Server MVP - Denis Gobo

and even an announcement on lessthandot

My profile is here: https://mvp.support.microsoft.com/profile=BCCF7416-DA4E-4D73-83E2-65FD61BAB16D

Stay tuned, I will have an interview with Jamie Thomson tomorrow

Working On SQL Admin Hacks, Tips and Tricks

I haven't posted for a while because I have been working on SQL Admin Hacks, Tips and Tricks lately. it is still a work in progress but below is what is on the wiki currently. It is not yet categorized but we will do that once we get more of these hacks done. To see what it will look like when it is done take a look at the SQL Server Programming Hacks

Can you think of any admin stuff you would like to see? This is what we have right now

Find Primary Keys and Columns Used in SQL Server
Get The Domain Name Of Your SQL Server Machine With T-SQL
Grant Execute/SELECT Permissions For All User Defined Functions To A User
Grant Execute Permissions For All Stored Procedures To A User
Kill All Active Connections To A Database
SQL Server 2008: When Was The Server Last Started?
Check If Auto Update Statistics Is Enabled By Using DATABASEPROPERTY
Three Way To List All Databases On Your Server
Generate A List Of Object Types By Using OBJECTPROPERTY
How to find all the tables and views in a database
Find Out Server Roles For a SQL Server Login
Which Service Pack Is Installed On My SQL Server
Test SQL Server Login Permissions With SETUSER
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2000
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Compare Tables With Tablediff
Find All Tables Without Triggers In SQL Server
Find All Tables With Triggers In SQL Server
Create Stored Procedures That Run At SQL Server Startup
Cycle The SQL Server Error Log
How to read sql server error messages
Use OBJECT_DEFINITION To Track SQL Server Stored Procedure Changes
SQL Compare Without The Price Tag
How To Get The Database Name For The Current User Process
How To Find Out Which Columns Have Defaults And What Those Default Values Are
Fixing Cannot add, update, or delete a job that originated from an MSX Server Error after renaming a server

SQL Server 2008 Release Candidate 0 Available To The General Public For Download

SQL Server 2008 Release Candidate 0 has been made available to the general public.

There are 2 versions (and 3 flavors of each) ISO or DVD

Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=35F53843-03F7-4ED5-8142-24A4C024CA05&displaylang=en

Don't forget to also visit the SQL Server 2008 Release Candidate 0 connect site here: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

Puzzle: Friday the Thirteenths

There is a puzzle on LessThanDot

The goal is to identify all friday the thirteenths for a given timeframe. We'll use a relatively small number of years, like 10. This should make it a little easier in procedural languages.

I posted my SQL solution, what about you?

Go here: Friday the Thirteenths and show me what you got. Make sure to use the [hide][/hide] tags so you don't spoil it for other people

Less Than Dot A New community Site Has Been Launched

Myself and a bunch of friends have been working on Less Than Dot for a while now. The site has a forum, blogs and a wiki. More info why we started Less Than Dot and who we are can be found here: http://www.lessthandot.com/aboutus.php

Since I am mostly a SQL guy, I wrote a collection of SQL Server hacks. This collection of SQL hacks is available on the Wiki, right now we have 8 sections and between 70 and 80 hacks. Ideally we will have more hacks and we will also have a SQL admin hacks page in the future.

SQL Server Hacks Sections
* 1 NULLS
* 2 Dates
* 3 Sorting, Limiting Ranking, Transposing and Pivoting
* 4 Handy tricks
* 5 Pitfalls
* 6 Query Optimization
* 7 Undocumented but handy
* 8 Usefull Admin stuff For The Developer


Below are some direct links to a couple hacks, you can also get a list of all the hacks on the wiki itself here: SQL Server Programming Hacks

Trouble With ISDATE And Converting To SMALLDATETIME
Row To Column (PIVOT)
Column To Row (UNPIVOT)
Five ways to return all rows from one table which are not in another table
Order IP Addresses
Use XACT_STATE() To Check For Doomed Transactions
Three differences between COALESCE and ISNULL
Case sensitive search
Use the sys.dm_db_index_usage_stats dmv to check if indexes are being used

Hopefully you will like the site and find some good content, if you have a question then don't hesitate to ask it in a forum.

SQL Teaser uniqueidentifier

Create this table

CREATE TABLE #bla (SomeVal uniqueidentifier)
INSERT #bla VALUES('D903D52D-DBFA-4904-9D95-F265152A391F')

what do you think this will return?


SELECT * FROM #bla
WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F12345678910'
UNION ALL
SELECT * FROM #bla
WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F1'

Surprised?

What about this?

SELECT * FROM #bla
WHERE SomeVal = CONVERT(uniqueidentifier,'D903D52D-DBFA-4904-9D95-F265152A391F12345678910')

Interview With Erland Sommarskog About SQL Server and Transact SQL

I have interviewed Erland Sommarskog, you can find that interview here: Interview With Erland Sommarskog About SQL Server and Transact SQL

Enjoy

Interview With Craig Freedman About Indexing, Query Plans And Performance

I interviewed Craig Freedman about Indexing, Query Plans And Performance and more. You can find that interview here: Interview With Craig Freedman About Indexing, Query Plans And Performance

Enjoy

I have a question for you on DB development blunders!

Last week Kalen Delaney wrote Did You Know? I have a question for you on DBA Blunders! I though the comments were very interesting, S we did the DBA part but what about developers? What are some of the worst blunders you have seen?

Here are a couple of things

Starting a begin tran, then some insert/update command, never commiting but minimizing the window.


Here is my all time favorite, can you reduce this by 90%?


declare @Token int
select @Token = 51234567

declare @val int


if left(@Token,1) = 1
select @val = 1
else if left(@Token,1) = 2
select @val = 2
else if left(@Token,1) = 3
select @val = 3
else if left(@Token,1) = 4
select @val = 4
else if left(@Token,1) = 5
select @val = 5
else if left(@Token,1) = 6
select @val = 6
else if left(@Token,1) = 7
select @val = 7
else if left(@Token,1) = 8
select @val = 8
else if left(@Token,1) = 9
select @val = 9
else if left(@Token,1) = 0
select @val = 0


select @val


Actually we put that on the whiteboard after we found it in our code and every time the developer wanted something we teased him about it...Oh you mean like that (pointing to the whiteboard)


What about changing the datatype from smallint to int in the table but keeping the params smallint in the proc.....mmmm why do I get a conversion in the execution plan?





So let's here some of what you have seen others write, we all know we couldn't write stuff like that ourselves right? :-)

Spring Cleaning, Getting Rid Of Some Books

I did some spring cleaning yesterday, I got rid of some old or obsolete books. I asked some people at work if they want any of these books and I believe all the books except for the JCL book are about to be given away.

I got rid of these books because:
I got a new version
I don't use the technology anymore
The book is obsolete


Click on the image and you can see a bigger version on my flickr page.