SQL Server Code,Tips and Tricks, Performance Tuning

Thursday, November 09, 2006

SQL Server 2005 Service Pack 2 CTP (November 2006) Download Links

The other day I wrote that SQL Server 2005 Service Pack 2 CTP would be available; here are the download links:

SQL Server 2005 Service Pack 2 CTP (November 2006)

SQL Server 2005 Express Edition and Tools Service Pack 2 CTP (November 2006)

SQL Server 2005 Books Online Service Pack 2 CTP (November 2006)

Feature Pack for SQL Server 2005 Service Pack 2 CTP (November 2006)

Additional Sources of SQL Server 2005 Information

Enjoy

Wednesday, November 08, 2006

Windows Vista RTM

Vista has been released to manufacturing!

Windows Vista Blog
http://windowsvistablog.com/blogs/windowsvista/archive/2006/11/08/it-s-time.aspx

Media Alert: Microsoft Announces Conference Call With Jim Allchin
http://www.microsoft.com/presspass/press/2006/nov06/11-08AllchinMA.mspx

Q&A: Microsoft Windows Vista Released to Manufacturing
http://www.microsoft.com/presspass/features/2006/nov06/11-08VistaRTM.mspx

Channel 9 also has a video on their site: Jim Allchin: It's time - Windows Vista RTM

Tuesday, November 07, 2006

SQL Server 2005 Service Pack 2 CTP

Microsoft is releasing the SQL Server 2005 Service Pack 2 Community Technology Preview Tuesday, November 7th.

Here are some of the changes:

Database Engine

Maintenance plans are now supported by the SQL Server Database Services installation. Before SP2, you were required to install SQL Server 2005 Integration Services (SSIS) to run maintenance plans on a server-only installation.
Maintenance plans now support multiserver environments, logging to remote servers, and multiple schedules. For more information, see How to: Create Multiserver Maintenance Plans How to: Create a Maintenance Plan and How to: Add or Modify Maintenance Plan Subplan Schedules.
Added new functionality in the SQL Server 2005 Enterprise Edition to provide an alternate storage format that can be used to minimize the disk space needed to store existing decimal and numeric data types. No application changes area are required to use its benefits. This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values do not require it, you can potentially save the disk space needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.
Added logon triggers and a common criteria compliance enabled Option for sp_configure to support common criteria in the SQL Server 2005 Enterprise Edition.
The sqllogship application is now supported. This application performs a backup, copy, or restore operation and associated clean-up tasks for a log shipping configuration.
Plan cache improvements that provide improved system performance, better use of the available physical memory for database pages, and the ability to return text XML query plans that contain an XML nesting level greater than or equal to 128 by using the new sys.dm_exec_text_query_plan table-valued function.
SMO for Relational Engine Features
- Table.CheckIdentityValue() correctly generates the schema name for the fully qualified object name.
- Column.AddDefaultConstraint() works against table columns for SQL Server 2000 database instances.

To see all the changes go here

SQL Server 2005 Script Repository

Saturday, November 04, 2006

How Are Dates Stored In SQL Server?

Internally dates are stored as 2 integers. The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second.

So if we run the following code for the base date (1900/01/01)

DECLARE @d DATETIME
SELECT @d = '1900-01-01 00:00:00.000'

SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
Go

The results are

DateInt DateBinary
----------- ----------
0 0x00000000

TimeInt TimeBinary
----------- ----------
0 0x00000000

If we use the max date 9999/12/31

DECLARE @d DATETIME
SELECT @d = '9999-12-31 23:59:59.997'

SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
Go

we get the following result

DateInt DateBinary
----------- ----------
2958463 0x002D247F

TimeInt TimeBinary
----------- ----------
25919999 0x018B81FF

If you take binary values and convert to datetime you get the following results

SELECT CONVERT(DATETIME,0x0000000000000001) --1 Tick 1/300 of a second
------------------------------------------------------
--1900-01-01 00:00:00.003

SELECT CONVERT(DATETIME,0x000000000000012C) -- 1 minute = 300 ticks
------------------------------------------------------
--1900-01-01 00:00:01.000

SELECT CONVERT(INT,0x12C) --= 300
SELECT CONVERT(VARBINARY(3),300) --= 0x00012C

SELECT CONVERT(DATETIME,0x0000000100000000) --add 1 day
------------------------------------------------------
--1900-01-02 00:00:00.000

For smalldatetime the time is stored as the number of minutes after midnight

Now here is some fun stuff

DECLARE @d DATETIME
SELECT @d = .0
SELECT @d
GO
------------------------------------------------------
--1900-01-01 00:00:00.000

DECLARE @d DATETIME
SELECT @d = .1
SELECT @d
GO
------------------------------------------------------
--1900-01-01 02:24:00.000

DECLARE @d DATETIME
SELECT @d = .12
SELECT @d
GO
------------------------------------------------------
--1900-01-01 02:52:48.000

DECLARE @d DATETIME
SELECT @d = '0'
SELECT @d
GO
Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.

DECLARE @d DATETIME
SELECT @d = 0
SELECT @d
GO
------------------------------------------------------
--1900-01-01 00:00:00.000

So there is no implicit conversion, o is fine 'o' is not

DECLARE @d DATETIME
SELECT @d = 20061030
SELECT @d
GO
Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

DECLARE @d DATETIME
SELECT @d = '20061030'
SELECT @d
GO
------------------------------------------------------
--2006-10-30 00:00:00.000

Here we have the reverse, the varchar value is fine but the int is not.
This happens because the max integer value that a datetime can take is 36523
If we run the following we are okay

DECLARE @d DATETIME
SELECT @d = 2958463
SELECT @d
GO
------------------------------------------------------
--9999-12-31 00:00:00.000

Friday, November 03, 2006

ISO-11179 Naming Conventions

Straight from the man himself comes this statement posted in the microsoft.public.sqlserver.programming forum: "You need to read ISO-11179 so you use proper data element names. You
actually had "tbl-"on the table names! Sometimes "id" id a
prefix and sometimes it is a postfix."

Of course you know who I am talking about? No? Joe Celko of course. So what is ISO-11179?

The 11179 standard is a multipart standard that includes the following parts:

11179-1
Part 1: Framework, introduces and discusses fundamental ideas of data elements, value domains, data element concepts, conceptual domains, and classification schemes essential to the understanding of this set of standards and provides the context for associating the individual parts of ISO/IEC 11179.

11179-2
Part 2: Classification, provides a conceptual model for managing classification schemes. There are many structures used to organize classification schemes and there are many subject matter areas that classification schemes describe. So, this Part also provides a two-faceted classification for classification schemes themselves.

11179-3
Part 3: Registry Metamodel and Basic Attributes, specifies a conceptual model for a metadata registry. It is limited to a set of basic attributes for data elements, data element concepts, value domains, conceptual domains, classification schemes, and other related classes, called administered items. The basic attributes specified for data elements in ISO/IEC 11179-3:1994 are provided in this revision.

11179-4
Part 4: Formulation of Data Definitions, provides guidance on how to develop unambiguous data definitions. A number of specific rules and guidelines are presented in ISO/IEC 11179-4 that specify exactly how a data definition should be formed. A precise, well-formed definition is one of the most critical requirements for shared understanding of an administered item; well-formed definitions are imperative for the exchange of information. Only if every user has a common and exact understanding of the data item can it be exchanged trouble-free.

11179-5
Part 5: Naming and Identification Principles, provides guidance for the identification of administered items. Identification is a broad term for designating, or identifying, a particular data item. Identification can be accomplished in various ways, depending upon the use of the identifier. Identification includes the assignment of numerical identifiers that have no inherent meanings to humans; icons (graphic symbols to which meaning has been assigned); and names with embedded meaning, usually for human understanding, that are associated with the data item's definition and value domain.

11179-6
Part 6: Registration, provides instruction on how a registration applicant may register a data item with a central Registration Authority and the allocation of unique identifiers for each data item. Maintenance of administered items already registered is also specified in this document.

The one that deals with naming conventions is 11179-5 The link will point to a zip file which has a pdf file in it. The TOC of this pdf file is below

Contents
Foreword
1 Scope
2 Normative references
3 Terms and definitions
4 Data Identifiers within a registry
5 Identification
6 Names
6.1 Names in a registry
6.2 Naming conventions
7 Development of naming conventions
7.1 Introduction
7.2 Scope principle
7.3 Authority principle
7.4 Semantic principle
7.5 Syntactic principle
7.6 Lexical principle
7.7 Uniqueness principle
Annex A (informative) Example naming conventions for names within an MDR registry
Annex B (informative) Example naming conventions for Asian languages

So check it out and hopefully you and your team can adapt a common naming conventions instead of having things named like employee_address, EmployeeAddress, employeeAddress and tblEmployeeAddress.

Thursday, November 02, 2006

SQL Teaser

Try to guess what the output will be of the following SQL Select statement (don't worry, you can't get it right ;-)

First guess then run this

SELECT 123.654, 123d6, 123e4, '123'e4

Surprise!!!!!!

I lifted/stole/borrowed the select statement from Mladen Prajdic's blog

Wednesday, November 01, 2006

Tuesday, October 31, 2006

Download A FREE Trial Of Spotlight On SQL Server And Win A Xbox 360

I just received an email from Quest Software. Quest is giving away 15 Xbox 360™ game systems in 15 days! Download a free trial of Spotlight® on SQL Server Enterprise and you'll be automatically entered into the Quest "Xbox-a-Day Giveaway" drawing.

Now you have a chance to win the Xbox 360™ and detect, diagnose and resolve database performance issues with ease—just by trying Spotlight on SQL Server Enterprise.

What is Spotlight on SQL Server Enterprise?
Spotlight on SQL Server Enterprise is an award-winning database diagnostics tool that can help you ensure data availability and prevent problems before they occur.

Discover, diagnose and resolve SQL Server performance issues

Spotlight on SQL Server Enterprise provides an agent-less easy-to-use database issue discovery solution that enables you to identify and resolve SQL Server performance problems within your SQL Server environment. This powerful tool pinpoints the underlying SQL server contention issues and processes for fast and efficient database administration.

With Spotlight, DBAs can drill down to locate in-depth information about the source of thousands of SQL Server performance problems such as: a specific user, a resource-intensive SQL transaction, an I/O bottleneck, a lock or wait. Spotlight for SQL Server Enterprise sets a baseline for normal activity for each instance, and can set thresholds, notify users and display alerts when it detects performance bottlenecks of any kind.

Spotlight not only monitors the SQL Server Environment, but the underlying operating system on which it resides. From an overview screen, DBAs can view the most active SQL Server sessions, SQL statements, Replication information , blocks, deadlocks, waits, and disk activityto pinpoint and alleviate problems before they occur before and seriously impact end users.

Through one single interface, Spotlight on SQL Server Enterprise answers many questions related to performance:

How is each SQL Server instance performing?
How is the SQL Server Environment as a whole performing?
Where and when are the performance bottlenecks occurring?
How do I catch the problems before they arise?
Who or what is causing the problem and how do I resolve it?

Go HERE for more details

Monday, October 30, 2006

Working Four Days A week Until Next Year

I will be working four days a week only until next year. I did not take a vacation this year since my wife was pregnant with twins. We couldn't go anywhere because the due date was the first week of August; twins usually arrive a couple of weeks earlier. Since we didn't know the exact due date and had to go for monitoring every week we decided not to go anywhere. the end result is that I have 8 (out of 10) vacation days left. So I will be taking wednesdays off until 2007.

I used to live in the Netherlands where everyone has five weeks vacation. So when I came to the United States I was surprised that there was not a law like that here. Besides the five weeks of vacation in Holland you also get 8% of your salary as vacation money. So when you go on vacation you can use that as spending money.

I saved the 'best' for last: if you are unemployed in Holland then you have the same rights. that's right five weeks vacation, 8% vacation money. In Holland you can be unemplyed for years and years, in the US you get 6 months unemployment and the cap is $405 a week. After 6 months you are on your own. I think they should do exactly the same in Holland (no wonder the top tax bracket was 72% in the late nineties; it is I believe 52% now). I knew a lot of people who would be unemployed and work off the books...........

Friday, October 27, 2006

Halloween SQL Teaser

Try to guess without running the code which of the following 3 queries will return a value for maxvalue that is not null
Is it A, B or C?

--A
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT @maxvalue = MAX(crdate)
FROM sysobjects WHERE ID = -99
SELECT 'A' ,@maxvalue maxValue
GO

--B
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT TOP 1 @maxValue = crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'B' ,@maxvalue maxValue
GO

--C
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SET @maxvalue = (SELECT TOP 1 crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'C' ,@maxvalue maxValue
GO

Let me know if you were correct. I must admit I saw this code on Simon Sabin's blog
To understand why this happens read this Tony Rogerson article: T-SQL Value assignment SET vs SELECT

So what does this have to do with Halloween? Nothing really.

One Of The Reasons I Participate In Forums And Newsgroups

This is one of the reasons I participate in SQL Server newsgroups and forums

A person had the following update statement


Update Object_data
set External_Claim_Number = BWCClaimNum
From yson_Claims_eDocs_10_27_2006
where LastName = INJ_lname
and FirstName = INJ_fname
and SSN = inj_ssn
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
and convert(varchar,pkclaim) = internal_claim_Number
and BWCClaimNum is not null
and BWCClaimNum <> External_Claim_Number

On the table that got updated (Object_data) the following trigger was created


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER   TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS

declare @rows int
declare @object_Data_ID int
set @rows = @@ROWCOUNT
if @rows = 0
    return
if @rows = 1
    begin
    select @object_Data_Id = object_data_id
    from inserted
    goto singlerecord
    end
declare curObject insensitive cursor for
    select object_data_id
    from inserted
open     curObject
fetch next from curObject into @object_data_id
while (@@fetch_status <> -1)
    begin
    singlerecord:
    insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
        INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
        DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
        YEAR_NUM,DateChanged,ChangedBy,Change
        ,PROV_FAX_NUM)   
        select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
            CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
            DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
            getdate(),user_name(),'U'
            ,PROV_FAX_NUM   
        from    inserted where object_data_id = @object_data_id
    if @rows = 1
        return
   
    fetch next from curObject into @object_data_id
    end
close curObject
deallocate curObject

I suggested that the person did not need the cursor since he is inserting everything anyway into the object_datahist table
I decided to modify the trigger as follows


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER   TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS
IF @@ROWCOUNT =0
RETURN

    insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
        INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
        DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
        YEAR_NUM,DateChanged,ChangedBy,Change
        ,PROV_FAX_NUM)   
        select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
            CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
            DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
            getdate(),user_name(),'U'
            ,PROV_FAX_NUM   
        from    inserted

I also suggested modifying the following line from the update query
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
to
and injurydate = claim_injury_date
since the dates are the same anyway

This is the response I got

"I tested removing the cursor driven trigger from the Object_Data table in development and replaced it with the set based up date you provided. Here is what happened:

20,784 records in the test.

1. Using the current trigger I canceled the update after an hour and eleven minutes of running. It had updated 10,218 rows.
2. Using the set based trigger it ran in eleven seconds and generated the correct amount of history.

That's really something to me! Thanks again!"

You see, this is one of the reasons I participate in forums/newsgroups because it feels good to help other people. Another reason is that you can look at the responses from the SQL MVP's and see how they would handle certain situations. I remember running into problems once and I also remembered that I saw this same problem in a newsgroup. after that it's a quick Google and you find your answer

What Are You Watching On Halloween?

I have difficulty picking a movie to watch on Halloween.
Did any of you (my readers) see any of these movies below? Which movie would you pick?

Omen (2006 Edition)
The Gingerdead Man
Saw I
Saw II
Exorcist (Begining)
Seed of Chucky
Ringu
Texas Chainsaw
Silent hill
Hills have eyes
Underworld (evolutions)

Leave me a comment or send me an email by using the Contact Me link.

Thursday, October 26, 2006

SQL Server experienced revenue growth of over 30%

Microsoft Corp. today announced first quarter revenue of $10.81 billion for the period ended September 30, 2006, an 11% increase over the same quarter of the prior year. Operating income for the quarter was $4.47 billion, an 11% increase compared with $4.05 billion in the prior year period. Net income and diluted earnings per share for the first quarter were $3.48 billion and $0.35 per share. For the same quarter of the previous year, net income and diluted earnings per share were $3.14 billion and $0.29, including a $0.02 per share charge for certain legal charges.

Server & Tools revenue increased 17% over the comparable quarter in the prior year, reflecting healthy performance for offerings such as SQL Server™ 2005, Windows Server®, Visual Studio® 2005 and BizTalk® Server. SQL Server experienced revenue growth of over 30%, as customers are increasingly deploying SQL Server for their mission critical, transaction-oriented databases.

Good news if you are a SQL Server developer

Wednesday, October 25, 2006

Enable xp_cmdshell In SQL Server 2005 With The Surface Configuration Tool Or With A Script

This question popped up in a newsgroup today. How do I enable xp_cmdshell in SQL Server 2005? Well you can do it two ways; one is with a script and the other with the Surface Configuration Tool
Let's start with the Surface Configuration Tool

Surface Configuration Tool
You have to navigate to the tool from the start button, the path is below
Programs-->Microsoft SQL server 2005-->Configuration Tools-->Surface Configuration Tool

Then select Surface Area Configuration for Features (bottom one)
Expand Database Engine go all the way down to xp_cmdshell and click enable xp_cmdshell and hit apply

SQL Script
To do it with a script use the one below

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

http://sqlservercode.blogspot.com In Top 100000 On Technorati

This blog is currently ranked 90,981 on Technorati
Rank: 90,981 (65 links from 30 blogs)

Not bad I guess, I wonder how long it will take to reach 50000?
The rank was 150000 just a couple of months ago so it's going towards the top (but will never reach it ;-( )

You can check the ranking here http://www.technorati.com/search/sqlservercode.blogspot.com and see who is linking here

Microsoft Changing Live Search Back To MSN Search

Just saw The Island the other day and I must say I liked it a lot. I don't know why the movie didn't do as well as expected. The stars of the movie are Scarlett Johansson (Lost in Translation, Girl with a Pearl Earring) and Ewan McGregor ( Star Wars: Episode III - Revenge of the Sith , Trainspotting , Moulin Rouge!) other prominent roles are for Sean Bean, Steve Buscemi and Michael Clarke Duncan.. The movie is directed by Michael Bay who did Pearl Harbor and Armageddon. Michael Bay is currently working on The Hitcher and Transformers The links are all to IMDB in case you want more details about any of the movies.

The best part of the movie: the car chase.
The car chase is probably the best ever; it even beats the one from the second Matrix movie.

The movie takes place in the year 2050 and people have clones made as insurance; if their liver gives up then they just take the liver from the clone. These clones live isolated underground and have no idea what's going on; they think that they survived some contamination. Every now and then a lottery takes place and the winner goes to the Island. In reality the winner goes to the operating table. I won't give away the whole story you watch that yourself

There is some major product placement in this movie. The Xbox is featured in the movie alongside MSN Search; that's right not Windows Live Search. I guess they can always release a director's cut and 'fix' that. Cadillac has some top of the line car that costs half a million dollars; How much is half a million in the year 2050? To me it seems that a Porsche Carrera would cost about $500000 in about 50 years. Anyway the 'real' Ewan in the movie, who is very rich complains when his 'very' expensive car gets trashed. Would he really get that upset over the car; his clone is more expensive and he tries to kill the clone.

If you can catch the movie on HBO/On Demand then watch this movie. If you don’t want to watch the whole movie then at least watch the car scene; you won’t regret it

Monday, October 23, 2006

Do You Know How Between Works With Dates?

Do you use between to return data that has dates? Do you know that between will get everything since midnight from the first criteria and up to midnight exactly from the second criteria. If you do BETWEEN 2006-10-01 AND 2006-10-02 then all the values that are greater or equal than 2006-10-01 and less or equal to 2006-10-02 will be returned. So no values after 2006-10-02 midnight will be returned.

Let's test this out, first let's create this table

CREATE TABLE SomeDates (DateColumn DATETIME)

Insert 2 values
INSERT INTO SomeDates VALUES('2006-10-02 00:00:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:00:00.000')

Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
This works without a problem

Let's add some more dates including the time portion
INSERT INTO SomeDates VALUES('2006-10-02 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:59.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:01.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:12:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 23:00:00.000')

Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
Here is where it goes wrong; for 2006-10-02 only the midnight value is returned the other ones are ignored

Now if we change 2006-10-02 to 2006-10-03 we get what we want
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn

Now insert a value for 2006-10-03 (midnight)

INSERT INTO SomeDates VALUES('2006-10-03 00:00:00.000')

Run the query again
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn

We get back 2006-10-03 00:00:00.000; between will return the date if it is exactly midnight

If you use >= and < then you get exactly what you need
SELECT *
FROM SomeDates
WHERE DateColumn >= '20061001' AND DateColumn < '20061003'
ORDER BY DateColumn

--Clean up
DROP TABLE SomeDates

So be careful when using between because you might get back rows that you did not expect to get back and it might mess up your reporting if you do counts or sums

Sunday, October 22, 2006

SQL Server 2005 Practical Troubleshooting: The Database Engine Sample Chapter Available

Ken Henderson has posted a sample chapter from the forthcoming book SQL Server 2005 Practical Troubleshooting: The Database Engine on his blog. The sample chapter is: Chapter 4 Procedure Cache Issues. This is a must read for anyone who is troubleshooting SQL Server queries and procedures. Some of the things covered are: Cached Object Leaks, Cursor Leaks, Parameter Sniffing, Excessive Compilation, Poor Plan Reuse and How Cache Lookups Work. I think that this is the first time I have seen parameter sniffing described in a book. Check it out and let me know what you think.