I don't know if I should laugh or cry after looking at this code. This could not have been written by a human right?
Anyway here it is, consider it a teaser. If you want to torture yourself and look at the whole query then go here: http://www.tek-tips.com/viewthread.cfm?qid=1421304&page=1
HAVING (((1 = 1)
AND (PP.Created >= ISNULL(NULL,'1/1/1900')
AND PP.Created < 1 =" 2)">= ISNULL(NULL,'1/1/1900')
AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
WHERE
(
(NULL IS NOT NULL
AND pv.DoctorId IN (NULL))
OR(NULL IS NULL)
)
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END
Someone will red flag the post, here is the whole query.
/* New Patient Analysis */
SET NOCOUNT ON
CREATE TABLE #PatientTemp
(
[PatientProfileId] [int] NOT NULL ,
[Prefix] [varchar] (10) NULL ,
[First] [varchar] (30) NULL ,
[Middle] [varchar] (30) NULL ,
[Last] [varchar] (30) NULL ,
[Suffix] [varchar] (20) NULL ,
[Created] [datetime] NOT NULL
)
Insert #PatientTemp
SELECT PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix,
CASE
WHEN 1 = 1 THEN PP.Created
WHEN 1 = 2 THEN Min(PV.Visit)
ELSE NULL
END As Created
FROM PatientVisit PV
INNER JOIN PatientProfile PP ON PP.PatientProfileId = PV.PatientProfileId
GROUP BY PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix, PP.Created
HAVING (((1 = 1)AND
(PP.Created >= ISNULL(NULL,'1/1/1900') AND PP.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(PV.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
Order By PP.First, PP.Last
SELECT dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix) AS Name,
CASE
WHEN 1 = 1 THEN #PatientTemp.Created
WHEN 1 = 2 THEN Min(pv.Visit)
ELSE NULL
END As Created,
COUNT(*) AS [Number Of Visits],
SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
SUM(pva.InsPayment + pva.PatPayment) AS Payments,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULLA
END As Grouping
FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT JOIN DoctorFacility df1 ON pv.ReferringDoctorId = df1.DoctorFacilityId
LEFT JOIN DoctorFacility df2 ON pv.FacilityId = df2.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId
WHERE
(
(NULL IS NOT NULL AND pv.DoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.ReferringDoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
GROUP BY #PatientTemp.Created,dbo.FormatName(#PatientTemp.Prefix, #PatientTemp .First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix),
pva.PatientProfileId,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END
HAVING (((1 = 1)AND
(#PatientTemp.Created >= ISNULL(NULL,'1/1/1900') AND #PatientTemp.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(pv.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(pv.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
ORDER BY dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp .Suffix), #PatientTemp.Created
Drop table #PatientTemp
Showing posts with label SQL Server 2000. Show all posts
Showing posts with label SQL Server 2000. Show all posts
Friday, October 26, 2007
Wednesday, October 10, 2007
SQL Teaser: Guess the output
What do you think will be the output?
DECLARE @d datetime
SET @d = '20071010'
SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)
After that run this to see how to get first and last days for years, quarters, months and weeks(be careful of ISO weeks!)
DECLARE @d datetime
SET @d = '20071010'
SELECT DATEADD(yy, DATEDIFF(yy, 0, @d), 0) as FirstDayOfYear,
DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) as LastDayOfYear,
DATEADD(qq, DATEDIFF(qq, 0, @d), 0) as FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) as LastDayOfQuarter,
DATEADD(mm, DATEDIFF(mm, 0, @d), 0) as FirstDayOfMonth,
DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) as LastDayOfMonth,
DATEADD(wk, DATEDIFF(wk, 0, @d), 0) as FirstDayOfWeek,
DATEADD(wk, DATEDIFF(wk, 0, @d)+1, -1) as LastDayOfWeek
DECLARE @d datetime
SET @d = '20071010'
SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)
After that run this to see how to get first and last days for years, quarters, months and weeks(be careful of ISO weeks!)
DECLARE @d datetime
SET @d = '20071010'
SELECT DATEADD(yy, DATEDIFF(yy, 0, @d), 0) as FirstDayOfYear,
DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) as LastDayOfYear,
DATEADD(qq, DATEDIFF(qq, 0, @d), 0) as FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) as LastDayOfQuarter,
DATEADD(mm, DATEDIFF(mm, 0, @d), 0) as FirstDayOfMonth,
DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) as LastDayOfMonth,
DATEADD(wk, DATEDIFF(wk, 0, @d), 0) as FirstDayOfWeek,
DATEADD(wk, DATEDIFF(wk, 0, @d)+1, -1) as LastDayOfWeek
Tuesday, October 02, 2007
How to find out the recovery model for all databases on SQL Server 2000, 2005 and 2008
How do you find out the recovery model for all the databases on your SQL Server box?
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)
--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases
--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)
--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases
--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases
Monday, October 01, 2007
SQL Teaser: Guess the output
Here is a small teaser, can you guess the output?
SELECT d.c-d.b/d.a
FROM(SELECT 1,2,5)d(a,b,c)
SELECT d.c-d.b/d.a
FROM(SELECT 1,2,5)d(a,b,c)
Friday, September 21, 2007
SQL Teaser PASS Special: Table Size
What will be the outcome of this script?
First we create a table with a total of 6000 bytes
Next we increase col2 from 1000 to 2000 bytes, this will give us a total of 7000 bytes
Finally we add col3 which has 1000 bytes, this will give us a total of 8000 bytes
First run these two statements
--Total size = 6000
CREATE TABLE TestSize (Col1 char(5000),col2 char(1000))
GO
--total size = 7000
ALTER TABLE TestSize
ALTER COLUMN col2 char(2000)
GO
Now what do you think will happen when you run this?
--total size should be 8000 bytes (5000 + 2000 + 1000)
ALTER TABLE TestSize
ADD Col3 char(1000)
GO
Now for bonus points. What book have I been reading.
Hint: the author is at PASS
Monday, September 10, 2007
SQL Gotcha: Do you know what data type is used when running ad-hoc queries?
This is for SQL Server 2000 only, SQL Server 2005 is a lot smarter which is another reason to upgrade.
When running the following query you probably already know that 2 is converted to an int datatype
SELECT *
FROM Table
WHERE ID =2
What about the value 2222222222? Do you think since it can't fit into an int that it will be a bigint? Let's test that out.
First create this table.
CREATE TABLE TestAdHoc (id bigint primary key)
INSERT INTO TestAdHoc
SELECT 1 UNION
SELECT 2433253453453466666 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
Now let's run these 2 queries which return the same data
SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666
SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)
Now run the following SET statement and run the 2 queries again
SET SHOWPLAN_TEXT ON
SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666
SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)
And what do we see?
First Query
--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))
--Compute Scalar(DEFINE:([Expr1002]=Convert([@1])-1,
[Expr1003]=Convert([@1])+1, [Expr1004]=If (Convert([@1])-1=NULL)
then 0 else 6If (Convert([@1])+1=NULL) then 0 else 10))
--Constant Scan
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id] > [Expr1002] AND [TestAdHoc].[id] < [Expr1003]), WHERE:(Convert([TestAdHoc].[id])=[@1]) ORDERED FORWARD)
Second Query
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id]=2433253453453466666) ORDERED FORWARD)
The first query has a much different execution plan than the second query. The first execution plan has a lot more than the second execution plan and will be a little slower.
So how do you know what dataype the value is converted to? Here is a simple SQL query which I first saw on Louis Davidson's blog. Just run this query.
SELECT CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'BaseType') AS varchar(20)) + '(' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Precision') AS varchar(10)) + ',' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Scale') AS varchar(10)) + ')'
So the output is this numeric(19,0). So instead of a bigint SQL Server converts the value to a numeric data type.
Here is another query which demonstrates the different datatypes used.
SELECT CAST(SQL_VARIANT_PROPERTY(2,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(222222222,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(2222222222,'BaseType') AS varchar(20))
So when running ad-hoc queries it is always a good practice to use parameters or inline convert statements.
When running the following query you probably already know that 2 is converted to an int datatype
SELECT *
FROM Table
WHERE ID =2
What about the value 2222222222? Do you think since it can't fit into an int that it will be a bigint? Let's test that out.
First create this table.
CREATE TABLE TestAdHoc (id bigint primary key)
INSERT INTO TestAdHoc
SELECT 1 UNION
SELECT 2433253453453466666 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
Now let's run these 2 queries which return the same data
SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666
SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)
Now run the following SET statement and run the 2 queries again
SET SHOWPLAN_TEXT ON
SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666
SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)
And what do we see?
First Query
--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))
--Compute Scalar(DEFINE:([Expr1002]=Convert([@1])-1,
[Expr1003]=Convert([@1])+1, [Expr1004]=If (Convert([@1])-1=NULL)
then 0 else 6If (Convert([@1])+1=NULL) then 0 else 10))
--Constant Scan
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id] > [Expr1002] AND [TestAdHoc].[id] < [Expr1003]), WHERE:(Convert([TestAdHoc].[id])=[@1]) ORDERED FORWARD)
Second Query
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id]=2433253453453466666) ORDERED FORWARD)
The first query has a much different execution plan than the second query. The first execution plan has a lot more than the second execution plan and will be a little slower.
So how do you know what dataype the value is converted to? Here is a simple SQL query which I first saw on Louis Davidson's blog. Just run this query.
SELECT CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'BaseType') AS varchar(20)) + '(' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Precision') AS varchar(10)) + ',' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Scale') AS varchar(10)) + ')'
So the output is this numeric(19,0). So instead of a bigint SQL Server converts the value to a numeric data type.
Here is another query which demonstrates the different datatypes used.
SELECT CAST(SQL_VARIANT_PROPERTY(2,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(222222222,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(2222222222,'BaseType') AS varchar(20))
So when running ad-hoc queries it is always a good practice to use parameters or inline convert statements.
Monday, August 20, 2007
Do you know how NULLIF and non-deterministic functions work?
Run this first
CREATE TABLE #j (n varchar(15))
DECLARE @a int
SET @a = 1
WHILE @a <= 1000 BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Go
After that is done run this query
SELECT * FROM #j WHERE n = ' '
You will get back between 200 and 300 rows. What just happened? In our insert we use this NULLIF(REPLICATE('1', RAND()*2) , ' ')
What this does is the following: if REPLICATE('1', RAND()*2) equals ' ' then it will insert a NULL, so where do the blanks come from? Well let's find out.
Run this
SET SHOWPLAN_TEXT ON
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
and we see the following
--Compute Scalar(DEFINE:([Expr1000]=If (replicate('1', Convert(rand(NULL)*2))=' ') then NULL else replicate('1', Convert(rand(NULL)*2))))
This can also be written like this
SELECT CASE WHEN REPLICATE('1', RAND()*2) =' '
THEN NULL ELSE REPLICATE('1', RAND()*2) END
See what happens? First SQL evaluates if REPLICATE('1', RAND()*2) is ' ' if that is not ' ' then the same code gets executed again and thus could be a blank
If you use a variable this can never happen.
Here is an example, keep hitting F5 and you will see it will never be blank
DECLARE @val float
SET @val = RAND()
SELECT NULLIF(REPLICATE('1', CONVERT(int, @val*2)) , ' ')
CREATE TABLE #j (n varchar(15))
DECLARE @a int
SET @a = 1
WHILE @a <= 1000 BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Go
After that is done run this query
SELECT * FROM #j WHERE n = ' '
You will get back between 200 and 300 rows. What just happened? In our insert we use this NULLIF(REPLICATE('1', RAND()*2) , ' ')
What this does is the following: if REPLICATE('1', RAND()*2) equals ' ' then it will insert a NULL, so where do the blanks come from? Well let's find out.
Run this
SET SHOWPLAN_TEXT ON
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
and we see the following
--Compute Scalar(DEFINE:([Expr1000]=If (replicate('1', Convert(rand(NULL)*2))=' ') then NULL else replicate('1', Convert(rand(NULL)*2))))
This can also be written like this
SELECT CASE WHEN REPLICATE('1', RAND()*2) =' '
THEN NULL ELSE REPLICATE('1', RAND()*2) END
See what happens? First SQL evaluates if REPLICATE('1', RAND()*2) is ' ' if that is not ' ' then the same code gets executed again and thus could be a blank
If you use a variable this can never happen.
Here is an example, keep hitting F5 and you will see it will never be blank
DECLARE @val float
SET @val = RAND()
SELECT NULLIF(REPLICATE('1', CONVERT(int, @val*2)) , ' ')
Article: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)
LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes. You can use LINQ expressions to query the database with them, as well as update/insert/delete data.
This is the 6th article that Scott Guthrie has written about LINQ. The first 5 can be found below.
Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
What is covered in the 6th article?
To SPROC or not to SPROC? That is the question....
The Steps to Map and Call a SPROC using LINQ to SQL
How to Map a SPROC to a LINQ to SQL DataContext
How to Call our Newly Mapped SPROC
Mapping the Return Type of SPROC Methods to Data Model Classes
Handling SPROC Output Parameters
Handling Multiple Result Shapes from SPROCs
Supporting User Defined Functions (UDFs)
Summary
Read the article here: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
This is the 6th article that Scott Guthrie has written about LINQ. The first 5 can be found below.
Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
What is covered in the 6th article?
To SPROC or not to SPROC? That is the question....
The Steps to Map and Call a SPROC using LINQ to SQL
How to Map a SPROC to a LINQ to SQL DataContext
How to Call our Newly Mapped SPROC
Mapping the Return Type of SPROC Methods to Data Model Classes
Handling SPROC Output Parameters
Handling Multiple Result Shapes from SPROCs
Supporting User Defined Functions (UDFs)
Summary
Read the article here: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
Thursday, August 09, 2007
SQL Server Monitor Gadget for Windows Vista Sidebar
Conchango are making available version 1 of our SQL Server Monitor Gadget for Windows Vista SidebarThe gadget does exactly what it says on the tin; it monitors a specified SQL Server database for various metrics and displays them on the Windows Vista Sidebar. It is provided for use by SQL Server DBAs that require a nice'n'easy way of monitoring important metrics on their SQL Server installations. In version 1 the gadget provides the following information:
Data file size - total file size of the database data files
Data file used - amount of used space on those data files
Data file used % - The percentage of the total data size that has been used
Log file size - total file size of the database log files
Log file used - amount of used space on those log files
Log file used % - The percentage of the total log size that has been used
Watch the demo, read more and download the gadget here: http://blogs.conchango.com/jamiethomson/archive/2007/08/09/Announcing-SQL-Server-Monitor-Gadget-for-Windows-Vista-Sidebar.aspx
Wednesday, July 04, 2007
SQL Server 2005 Best Practices Analyzer Released, End Of Support For SQL Server 2000 SP3a In 6 Days
End of Support for SQL Server 2000 Service Pack 3a
Support for SQL Server 2000 Service Pack 3a (SP3a) will end on July 10, 2007.
Microsoft will end technical support on this date, which also includes security updates for this Service Pack. Microsoft is ending support for this product as part of our Service Pack support policy, found http://support.microsoft.com/lifecycle.
Customers running SQL Server 2000 Service Pack 3a are encouraged to migrate to SQL Server 2000 Service Pack 4 or SQL Server 2005. Remaining current on your service pack installation ensures that your products remain supported per the Support Lifecycle policy. Additionally, your software benefits from the many enhancements, fixes, and security updates provided through the latest service pack.
Read more here: http://blogs.msdn.com/sqlreleaseservices/archive/2007/07/02/end-of-support-for-sql-server-2000-service-pack-3a.aspx
SQL Server 2005 Best Practices Analyzer (July 2007) Realeased
It does not say CTP anywhere on this page so I assume that this is a 'production' version.
Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=da0531e4-e94c-4991-82fa-f0e3fbd05e63&DisplayLang=en
Support for SQL Server 2000 Service Pack 3a (SP3a) will end on July 10, 2007.
Microsoft will end technical support on this date, which also includes security updates for this Service Pack. Microsoft is ending support for this product as part of our Service Pack support policy, found http://support.microsoft.com/lifecycle.
Customers running SQL Server 2000 Service Pack 3a are encouraged to migrate to SQL Server 2000 Service Pack 4 or SQL Server 2005. Remaining current on your service pack installation ensures that your products remain supported per the Support Lifecycle policy. Additionally, your software benefits from the many enhancements, fixes, and security updates provided through the latest service pack.
Read more here: http://blogs.msdn.com/sqlreleaseservices/archive/2007/07/02/end-of-support-for-sql-server-2000-service-pack-3a.aspx
SQL Server 2005 Best Practices Analyzer (July 2007) Realeased
It does not say CTP anywhere on this page so I assume that this is a 'production' version.
Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=da0531e4-e94c-4991-82fa-f0e3fbd05e63&DisplayLang=en
Thursday, June 14, 2007
C# IsNullOrEmpty Function In SQL Server
Mladen Prajdic has created a SQL equivalent of the C# IsNotNullOrEmpty
I looked at it and thought that there was way too much code
Here is my version which I have modified, you pass an additional parameter in to indicate whether you want blanks only to count or not
CREATE FUNCTION dbo.IsNotNullOrEmpty(@text NVARCHAR(4000),@BlanksIsEmpty bit)
RETURNS BIT
AS
BEGIN
DECLARE @ReturnValue bit
IF @BlanksIsEmpty = 0
BEGIN
SELECT @ReturnValue= SIGN(COALESCE(DATALENGTH(@text),0))
END
ELSE
BEGIN
SELECT @ReturnValue= SIGN(COALESCE(DATALENGTH(RTRIM(@text)),0))
END
RETURN @ReturnValue
END
Go
Here are some calls where we want blanks to return as empty or null
The function returns = if it is empty and 1 if it is not empty
SELECT dbo.IsNotNullOrEmpty(null,1),dbo.IsNotNullOrEmpty('azas',1),
dbo.IsNotNullOrEmpty(' ',1),dbo.IsNotNullOrEmpty('',1)
Here are some calls where we don't want blanks to return as empty or null
SELECT dbo.IsNotNullOrEmpty(null,0),dbo.IsNotNullOrEmpty('azas',0),
dbo.IsNotNullOrEmpty(' ',0),dbo.IsNotNullOrEmpty('',0)
My function is the opposite of Mladen's I check for is NOT null or empty instead of IS null or empty (easier to code it with the SIGN function)
I looked at it and thought that there was way too much code
Here is my version which I have modified, you pass an additional parameter in to indicate whether you want blanks only to count or not
CREATE FUNCTION dbo.IsNotNullOrEmpty(@text NVARCHAR(4000),@BlanksIsEmpty bit)
RETURNS BIT
AS
BEGIN
DECLARE @ReturnValue bit
IF @BlanksIsEmpty = 0
BEGIN
SELECT @ReturnValue= SIGN(COALESCE(DATALENGTH(@text),0))
END
ELSE
BEGIN
SELECT @ReturnValue= SIGN(COALESCE(DATALENGTH(RTRIM(@text)),0))
END
RETURN @ReturnValue
END
Go
Here are some calls where we want blanks to return as empty or null
The function returns = if it is empty and 1 if it is not empty
SELECT dbo.IsNotNullOrEmpty(null,1),dbo.IsNotNullOrEmpty('azas',1),
dbo.IsNotNullOrEmpty(' ',1),dbo.IsNotNullOrEmpty('',1)
Here are some calls where we don't want blanks to return as empty or null
SELECT dbo.IsNotNullOrEmpty(null,0),dbo.IsNotNullOrEmpty('azas',0),
dbo.IsNotNullOrEmpty(' ',0),dbo.IsNotNullOrEmpty('',0)
My function is the opposite of Mladen's I check for is NOT null or empty instead of IS null or empty (easier to code it with the SIGN function)
Wednesday, June 13, 2007
SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged
I am still amazed at how many people still think that TRUNCATE TABLE is not logged. There is some logging going on but it is minimal, here is what Books On Line says:
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
Let’s prove that we can rollback a truncate
Create this table and do the select
CREATE TABLE dbo.Enfarkulator (ID int IDENTITY PRIMARY KEY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator VALUES(1)
SELECT * FROM dbo.Enfarkulator
ID SomeOtherCol
1 1
2 1
Now run this part
BEGIN TRAN
TRUNCATE TABLE dbo.Enfarkulator
SELECT * FROM dbo.Enfarkulator
ROLLBACK TRAN
ID SomeOtherCol
(0 row(s) affected)
As you can see the table was truncated, now select from the table again
SELECT * FROM dbo.Enfarkulator
ID SomeOtherCol
1 1
2 1
Yep, the data is there, proving that you can rollback a truncate and all the data will be there. There are two other major difference between truncate and delete which I will explain below.
Truncate doesn’t preserve the identity value but delete does
This is another difference between truncate and delete, truncate will reset the identity value but delete does not. Run the following code to see how that works
CREATE TABLE dbo.Enfarkulator2 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator2 VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)
SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator
DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator
INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)
SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator
The Enfarkulator id was reset and the Enfarkulator2 id was not. In order to do the same with delete you will need to run a dbcc checkident reseed command. Here is the code for that.
DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator
DBCC CHECKIDENT (Enfarkulator2, RESEED, 0)
Now insert again and you will see that the values are the same.
INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)
SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator
You can’t truncate tables that are referenced by a foreign key constraint.
If you have a table which is referenced by another table with a foreign key constraint then you cannot truncate that table. Here is the code for that
CREATE TABLE dbo.Enfarkulator3 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator3 VALUES(1)
Now let’s add the foreign key
ALTER TABLE dbo.Enfarkulator3 ADD CONSTRAINT [FK_Fark3_Fark]
FOREIGN KEY ([ID]) REFERENCES [dbo].[Enfarkulator] ([ID])
Now try to truncate.
TRUNCATE TABLE Enfarkulator
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Enfarkulator' because it is being referenced by a FOREIGN KEY constraint.
See? You cannot do that
--Clean up time ;-)
DROP TABLE dbo.Enfarkulator3,dbo.Enfarkulator2,dbo.Enfarkulator
Cross-posted from SQLBlog! - http://www.sqlblog.com/
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
Let’s prove that we can rollback a truncate
Create this table and do the select
CREATE TABLE dbo.Enfarkulator (ID int IDENTITY PRIMARY KEY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator VALUES(1)
SELECT * FROM dbo.Enfarkulator
ID SomeOtherCol
1 1
2 1
Now run this part
BEGIN TRAN
TRUNCATE TABLE dbo.Enfarkulator
SELECT * FROM dbo.Enfarkulator
ROLLBACK TRAN
ID SomeOtherCol
(0 row(s) affected)
As you can see the table was truncated, now select from the table again
SELECT * FROM dbo.Enfarkulator
ID SomeOtherCol
1 1
2 1
Yep, the data is there, proving that you can rollback a truncate and all the data will be there. There are two other major difference between truncate and delete which I will explain below.
Truncate doesn’t preserve the identity value but delete does
This is another difference between truncate and delete, truncate will reset the identity value but delete does not. Run the following code to see how that works
CREATE TABLE dbo.Enfarkulator2 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator2 VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)
SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator
DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator
INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)
SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator
The Enfarkulator id was reset and the Enfarkulator2 id was not. In order to do the same with delete you will need to run a dbcc checkident reseed command. Here is the code for that.
DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator
DBCC CHECKIDENT (Enfarkulator2, RESEED, 0)
Now insert again and you will see that the values are the same.
INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)
SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator
You can’t truncate tables that are referenced by a foreign key constraint.
If you have a table which is referenced by another table with a foreign key constraint then you cannot truncate that table. Here is the code for that
CREATE TABLE dbo.Enfarkulator3 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator3 VALUES(1)
Now let’s add the foreign key
ALTER TABLE dbo.Enfarkulator3 ADD CONSTRAINT [FK_Fark3_Fark]
FOREIGN KEY ([ID]) REFERENCES [dbo].[Enfarkulator] ([ID])
Now try to truncate.
TRUNCATE TABLE Enfarkulator
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Enfarkulator' because it is being referenced by a FOREIGN KEY constraint.
See? You cannot do that
--Clean up time ;-)
DROP TABLE dbo.Enfarkulator3,dbo.Enfarkulator2,dbo.Enfarkulator
Cross-posted from SQLBlog! - http://www.sqlblog.com/
Wednesday, June 06, 2007
How To Protect Yourself From Fat-Finger Sally, Crazy Bosses and Other SQL Villains
You all have been through this at least once in your life. In your shop there is this one person who likes to use Enterprise Manager as their Rapid Data Entry Application. We all know how these people operate; they delete rows, drop tables and all kinds of other funky stuff. SQL Server 2005 has DDL triggers to help you protect against these scoundrels. What about if you are still running that piece of software from the late Triassic period known as SQL Server 2000, what can help you in that case? Don’t worry I will show you a way but first I will tell you a story. About 6 years ago I worked in New York City as a consultant on a project for a nonprofit organization. I looked in the database and found this table which was named YesNoTable. I was curious I opened the table and noticed it had only 2 rows. Here is what was stored in the table.
0 no
1 yes
I dropped it immediately. 5 minutes went by and suddenly the CRM application was broken. They ran the debugger and found out a table was missing. Luckily for me it was very easy to recreate this table. And yes, we did get rid of it soon after. Now had the table be used by a view which had been created with schemabinding I would not be able to drop the table without dropping the view first. You see even I became a SQL villain one time.
What the code below does is it will loop through all the user created tables then union them all, I created a where 1 =0 WHERE clause just in case someone decides to open the view. Since a union can only have 250 selects or so, I have created the code so that you can specify how many tables per view you would like, you do that with the @UnionCount variable.
The code does print statements it does not create the views
If you run the code in the msdb database and you specify 5 as the @UnionCount your output will be this
-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_1 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[log_shipping_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_monitor]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_history]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plans]
WHERE 1=0
GO
-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_2 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[RTblClassDefs]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDatabaseVersion]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBMProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBXProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDTMProps]
WHERE 1=0
GO
The code is not very complex if there are more tables in the DB than you specify in the @UnionCount variable then it will do them in chunks of whatever you specified, if there are less then it will do all of them in 1 view.
Below is the code, if you have any questions then feel free to leave a comment.
USE msdb
SET NOCOUNT ON
DECLARE @UnionCount int
SELECT @UnionCount = 20
IF @UnionCount > 250 OR @UnionCount <1
BEGIN
RAISERROR ('@UnionCount has to be between 1 and 250', 16, 1)
RETURN
END
SELECT identity(int,1,1) AS id,QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) AS tablename
INTO #Tables
FROM information_schema.tables
WHERE table_type ='base table'
AND OBJECTPROPERTY(OBJECT_ID(table_name),'IsMSShipped') = 0
ORDER BY table_name
DECLARE @maxloop int
DECLARE @loop int
DECLARE @tablename varchar(200)
SELECT @maxloop = MAX(id)
FROM #Tables
BEGIN
DECLARE @OuterLoopCount int, @OuterLoop int
SELECT @OuterLoopCount = COUNT(*) FROM #Tables
WHERE id %@UnionCount =0
SELECT @OuterLoopCount = COALESCE(NULLIF(@OuterLoopCount,0),1)
IF (SELECT COUNT(*) FROM #Tables) % 10 <> 0
SELECT @OuterLoopCount = @OuterLoopCount +1
SELECT @OuterLoop =1
SELECT @Loop = MIN(id),@maxloop=MAX(id)
FROM #Tables WHERE ID <= @UnionCount * @OuterLoop
WHILE @OuterLoop <=@OuterLoopCount
BEGIN
SELECT @Loop = MIN(id),@maxloop=MAX(id)
FROM #Tables WHERE ID <= @UnionCount * @OuterLoop
AND id > (@UnionCount * @OuterLoop) - @UnionCount
PRINT'-- **************************** '
PRINT'-- **** View Starts Here ***** '
PRINT'-- **************************** '
PRINT 'CREATE VIEW DoNotDropMe_' + CONVERT(VARCHAR(10),@OuterLoop) + ' WITH SCHEMABINDING'+ char(10) + 'AS'
WHILE @Loop <= @maxloop
BEGIN
SELECT @tablename = tablename
FROM #Tables
WHERE id = @Loop
PRINT 'SELECT 1 As Col1 FROM ' + @tablename + char(10) + 'WHERE 1=0'
IF @Loop < @maxloop
PRINT UNION ALL'
SET @Loop = @Loop + 1
END
SET @OuterLoop = @OuterLoop + 1
PRINT 'GO'
PRINT ''
PRINT ''
END
END
DROP table #Tables
Cross-posted from SQLBlog! - http://www.sqlblog.com/
0 no
1 yes
I dropped it immediately. 5 minutes went by and suddenly the CRM application was broken. They ran the debugger and found out a table was missing. Luckily for me it was very easy to recreate this table. And yes, we did get rid of it soon after. Now had the table be used by a view which had been created with schemabinding I would not be able to drop the table without dropping the view first. You see even I became a SQL villain one time.
What the code below does is it will loop through all the user created tables then union them all, I created a where 1 =0 WHERE clause just in case someone decides to open the view. Since a union can only have 250 selects or so, I have created the code so that you can specify how many tables per view you would like, you do that with the @UnionCount variable.
The code does print statements it does not create the views
If you run the code in the msdb database and you specify 5 as the @UnionCount your output will be this
-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_1 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[log_shipping_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_monitor]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_history]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plans]
WHERE 1=0
GO
-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_2 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[RTblClassDefs]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDatabaseVersion]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBMProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBXProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDTMProps]
WHERE 1=0
GO
The code is not very complex if there are more tables in the DB than you specify in the @UnionCount variable then it will do them in chunks of whatever you specified, if there are less then it will do all of them in 1 view.
Below is the code, if you have any questions then feel free to leave a comment.
USE msdb
SET NOCOUNT ON
DECLARE @UnionCount int
SELECT @UnionCount = 20
IF @UnionCount > 250 OR @UnionCount <1
BEGIN
RAISERROR ('@UnionCount has to be between 1 and 250', 16, 1)
RETURN
END
SELECT identity(int,1,1) AS id,QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) AS tablename
INTO #Tables
FROM information_schema.tables
WHERE table_type ='base table'
AND OBJECTPROPERTY(OBJECT_ID(table_name),'IsMSShipped') = 0
ORDER BY table_name
DECLARE @maxloop int
DECLARE @loop int
DECLARE @tablename varchar(200)
SELECT @maxloop = MAX(id)
FROM #Tables
BEGIN
DECLARE @OuterLoopCount int, @OuterLoop int
SELECT @OuterLoopCount = COUNT(*) FROM #Tables
WHERE id %@UnionCount =0
SELECT @OuterLoopCount = COALESCE(NULLIF(@OuterLoopCount,0),1)
IF (SELECT COUNT(*) FROM #Tables) % 10 <> 0
SELECT @OuterLoopCount = @OuterLoopCount +1
SELECT @OuterLoop =1
SELECT @Loop = MIN(id),@maxloop=MAX(id)
FROM #Tables WHERE ID <= @UnionCount * @OuterLoop
WHILE @OuterLoop <=@OuterLoopCount
BEGIN
SELECT @Loop = MIN(id),@maxloop=MAX(id)
FROM #Tables WHERE ID <= @UnionCount * @OuterLoop
AND id > (@UnionCount * @OuterLoop) - @UnionCount
PRINT'-- **************************** '
PRINT'-- **** View Starts Here ***** '
PRINT'-- **************************** '
PRINT 'CREATE VIEW DoNotDropMe_' + CONVERT(VARCHAR(10),@OuterLoop) + ' WITH SCHEMABINDING'+ char(10) + 'AS'
WHILE @Loop <= @maxloop
BEGIN
SELECT @tablename = tablename
FROM #Tables
WHERE id = @Loop
PRINT 'SELECT 1 As Col1 FROM ' + @tablename + char(10) + 'WHERE 1=0'
IF @Loop < @maxloop
PRINT UNION ALL'
SET @Loop = @Loop + 1
END
SET @OuterLoop = @OuterLoop + 1
PRINT 'GO'
PRINT ''
PRINT ''
END
END
DROP table #Tables
Cross-posted from SQLBlog! - http://www.sqlblog.com/
Wednesday, May 30, 2007
Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise Logic
You have all seen websites where you can pick a bunch of categories by selection a bunch of check boxes. usually what you do is store those in a lookup table and then you create another table where you store all the categories for each customer.
What if I tell you that you can store all that info in 1 row instead of 10 rows if a customer picked 10 categories.
Take a look at this
1 Classic Rock
2 Hard Rock
4 Speed/Trash Metal
You will store a value of 1 + 2 + 4 = 7(you just sum the values)
Now run this to check, the result will be 7 for a match and some other value otherwise
select 7 | 1,
7 | 2,
7 |3,
7 |4,
7 |5,
7 |6,
7 |7,
7 |8,
7 |20
What is this |(pipe symbol)?
From Books on line
The bitwise operator performs a bitwise logical OR between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if either or both bits (for the current bit being resolved) in the input expressions have a value of 1; if neither bit in the input expressions is 1, the bit in the result is set to 0.
The bitwise operator requires two expressions, and it can be used on expressions of only the integer data type category.
Here is how you would typically use this, first create this table
CREATE TABLE NumbersTable (Num int)
INSERT NumbersTable VALUES(1)
INSERT NumbersTable VALUES(2)
INSERT NumbersTable VALUES(3)
INSERT NumbersTable VALUES(4)
INSERT NumbersTable VALUES(5)
INSERT NumbersTable VALUES(6)
INSERT NumbersTable VALUES(7)
INSERT NumbersTable VALUES(8)
INSERT NumbersTable VALUES(9)
INSERT NumbersTable VALUES(10)
INSERT NumbersTable VALUES(11)
INSERT NumbersTable VALUES(12)
GO
Now run this
SELECT Num,
CASE 7 |Num WHEN 7 THEN 'Yes' ELSE 'No' END AS COL
FROM NumbersTable
Here is the output
Num COL
---- ---
1 Yes
2 Yes
3 Yes
4 Yes
5 Yes
6 Yes
7 Yes
8 No
9 No
10 No
11 No
12 No
Okay enough theory let's start with some SQL code. First create this table which will hold all the categories
CREATE TABLE MusicChoice (ID INT PRIMARY KEY,
ChoiceDescription VARCHAR(100))
INSERT MusicChoice VALUES(2,'Hard Rock')
INSERT MusicChoice VALUES(3,'Speed/Trash Metal')
INSERT MusicChoice VALUES(4,'Classical')
INSERT MusicChoice VALUES(5,'Rap')
INSERT MusicChoice VALUES(6,'Blues')
INSERT MusicChoice VALUES(7,'Jazz')
INSERT MusicChoice VALUES(8,'Alternative Rock')
INSERT MusicChoice VALUES(9,'Easy Listening')
INSERT MusicChoice VALUES(10,'Progressive Rock')
INSERT MusicChoice VALUES(11,'Punk Rock')
INSERT MusicChoice VALUES(12,'Swing')
INSERT MusicChoice VALUES(13,'Techno')
INSERT MusicChoice VALUES(14,'Pop')
INSERT MusicChoice VALUES(15,'Disco')
INSERT MusicChoice VALUES(16,'Big Band')
INSERT MusicChoice VALUES(17,'Gospel')
INSERT MusicChoice VALUES(18,'Heavy Metal')
INSERT MusicChoice VALUES(19,'House')
INSERT MusicChoice VALUES(20,'Celtic')
Now create the Bitwise table
CREATE TABLE BitwiseMusicChoice (ID INT PRIMARY KEY,
ChoiceDescription VARCHAR(100))
We will use the POWER function to create the correct values
run this
SELECT id,POWER(2,id-1)BitID,ChoiceDescription
FROM MusicChoice
id BitID ChoiceDescription
1 1 Classic Rock
2 2 Hard Rock
3 4 Speed/Trash Metal
4 8 Classical
5 16 Rap
6 32 Blues
7 64 Jazz
8 128 Alternative Rock
9 256 Easy Listening
10 512 Progressive Rock
11 1024 Punk Rock
12 2048 Swing
13 4096 Techno
14 8192 Pop
15 16384 Disco
16 32768 Big Band
17 65536 Gospel
18 131072 Heavy Metal
19 262144 House
20 524288 Celtic
Now insert it into the BitwiseMusicChoice table
INSERT BitwiseMusicChoice
SELECT POWER(2,id-1)BitID,ChoiceDescription
FROM MusicChoice
Now create this customer table
CREATE TABLE Customer (CustomerID int identity, CustomerCode uniqueidentifier not null)
INSERT Customer VALUES('1DAB5C03-BC23-4FB5-AC3D-A46489459FE9')
INSERT Customer VALUES('F7DDCDBC-F646-493A-B872-4E2E82EA8E14')
INSERT Customer VALUES('E8A4C3D2-AEB0-4821-A49D-3BF085354448')
INSERT Customer VALUES('52581088-C427-4D2F-A782-250564D44D8C')
INSERT Customer VALUES('1B2622C4-6C17-4E74-99D6-336197FBBCFF')
Now we will insert a total of 10000 customers
SET NOCOUNT ON
BEGIN TRAN
DECLARE @LoopCounter INT
SET @LoopCounter = 6
WHILE @LoopCounter <= 10000
BEGIN
INSERT Customer VALUES(NEWID())
SET @LoopCounter = @LoopCounter + 1
END
COMMIT WORK
GO
ALTER TABLE Customer ADD CONSTRAINT pk_Customer PRIMARY KEY (CustomerCode)
Create another table to hold the choices
CREATE TABLE CustomerMusicChoice (id INT identity, MusicChoiceID int, CustomerCode uniqueidentifier)
SET NOCOUNT ON
BEGIN TRAN
DECLARE @LoopCounter INT
DECLARE @CustID uniqueidentifier
SET @LoopCounter = 1
WHILE @LoopCounter <= 10000
BEGIN
SELECT @CustID = CustomerCode
FROM Customer
WHERE CustomerID = @LoopCounter
INSERT Customer VALUES(NEWID())
INSERT CustomerMusicChoice(MusicChoiceID,CustomerCode)
SELECT TOP 10 id,@CustID
FROM MusicChoice
ORDER BY NEWID()
SET @LoopCounter = @LoopCounter + 1
END
COMMIT WORK
GO
Now add these indexes
CREATE INDEX ix_CustomerMusicChoice_Cust On CustomerMusicChoice(CustomerCode)
CREATE
INDEX ix_CustomerMusicChoice_ID On CustomerMusicChoice(MusicChoiceID)Create the BitwiseCustomerMusicChoice which will hold the Bitwise values
CREATE TABLE BitwiseCustomerMusicChoice (id INT identity, MusicChoiceID int, CustomerCode uniqueidentifier not null)
INSERT INTO BitwiseCustomerMusicChoice
SELECT SUM(POWER(2,MusicChoiceID-1)) as MusicChoiceID,CustomerCode
FROM CustomerMusicChoice
GROUP BY CustomerCode
ALTER
Now let's test performance. Hit CTRL + K (SQL 2000) or CTRL + M (SQL 2005)
These 2 queries will return something like this
ID ChoiceDescription Picked
8 Alternative Rock No
16 Big Band No
6 Blues No
20 Celtic No
1 Classic Rock No
4 Classical Yes
15 Disco Yes
9 Easy Listening Yes
17 Gospel No
2 Hard Rock No
18 Heavy Metal Yes
19 House Yes
7 Jazz Yes
14 Pop Yes
10 Progressive Rock Yes
11 Punk Rock No
5 Rap No
3 Speed/Trash Metal Yes
12 Swing Yes
13 Techno No
SELECT mc.ID,ChoiceDescription,CASE WHEN CustomerCode IS NULL THEN 'No' ELSE 'Yes' END Picked
FROM CustomerMusicChoice cmc
RIGHT JOIN MusicChoice mc on cmc.MusicChoiceID = mc.id
AND CustomerCode ='1DAB5C03-BC23-4FB5-AC3D-A46489459FE9'
ORDER BY ChoiceDescription
SELECT bmc.ID,ChoiceDescription,
CASE WHEN bmc.ID |MusicChoiceID =MusicChoiceID THEN 'Yes'
ELSE 'No'
END AS Picked
FROM BitwiseCustomerMusicChoice cmc
CROSS JOIN BitwiseMusicChoice bmc
WHERE CustomerCode ='1DAB5C03-BC23-4FB5-AC3D-A46489459FE9'
ORDER BY ChoiceDescription
67.60% against 32.40% not bad right?
Now run this, we will add AND bmc.ID > 0 to both queries. This will change an index scan to an index seek in the bottom query
SELECT mc.ID,ChoiceDescription,CASE WHEN CustomerCode IS NULL THEN 'No' ELSE 'Yes' END Picked
FROM CustomerMusicChoice cmc
RIGHT JOIN MusicChoice mc on cmc.MusicChoiceID = mc.id
AND CustomerCode ='1DAB5C03-BC23-4FB5-AC3D-A46489459FE9'
AND mc.ID > 0
ORDER BY ChoiceDescription
SELECT bmc.ID,ChoiceDescription,
CASE WHEN bmc.ID |MusicChoiceID =MusicChoiceID THEN 'Yes'
ELSE 'No'
END AS Picked
FROM BitwiseCustomerMusicChoice cmc
CROSS JOIN BitwiseMusicChoice bmc
WHERE CustomerCode ='1DAB5C03-BC23-4FB5-AC3D-A46489459FE9'
AND bmc.ID > 0
ORDER BY ChoiceDescription
That improved the performance a little. 82.75% against 17.25%
Now look at the tables, after running dbcc showcontig you can see that the BitwiseCustomerMusicChoice is about 1/10th the size of the CustomerMusicChoice table which is as expected.
dbcc showcontig ('BitwiseCustomerMusicChoice')
---------------------------------------------------------------------------
DBCC SHOWCONTIG scanning 'BitwiseCustomerMusicChoice' table...
Table: 'BitwiseCustomerMusicChoice' (772197801); index ID: 1, database ID: 26
TABLE level scan performed.
- Pages Scanned................................: 41
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 6.8
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 48.0
- Avg. Page Density (full).....................: 99.41%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbcc showcontig ('CustomerMusicChoice')
---------------------------------------------------------------------------
DBCC SHOWCONTIG scanning 'CustomerMusicChoice' table...
Table: 'CustomerMusicChoice' (724197630); index ID: 0, database ID: 26
TABLE level scan performed.
- Pages Scanned................................: 428
- Extents Scanned..............................: 55
- Extent Switches..............................: 54
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 98.18% [54:55]
- Extent Scan Fragmentation ...................: 40.00%
- Avg. Bytes Free per Page.....................: 386.5
- Avg. Page Density (full).....................: 95.22%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
What happens if you want to get the total count of for example Classical?
SELECT COUNT(*)
FROM CustomerMusicChoice cmc
JOIN MusicChoice mc on cmc.MusicChoiceID = mc.id
WHERE mc.ChoiceDescription ='Classical'
SELECT COUNT(*)
FROM BitwiseCustomerMusicChoice cmc
JOIN BitwiseMusicChoice bmc ON bmc.ID |MusicChoiceID =MusicChoiceID
WHERE bmc.ChoiceDescription ='Classical'
Here are execution plans for SQl Server 2000 and 2005
As you can see SQL Server 2005 has a bigger difference than SQL Server 2000
Now let's look at the overal picture, on a busy system you will have the customer queries running many times an hour/day. The report queries will run maybe a couple a times a day. I think this trade off is perfectly acceptable because overall your system will perform better. Another thing to keep in mind is that instead of 10 inserts you only have to do 1, same with updates, all these little things add up to a lot eventualy.
So as you can see using bitwise logic is a great way to accomplish a couple of things
Reduce table size
Speed up backup and recovery because your table is much smaller
Improve performance
Of course you have to do some testing for yourself because it might not be appropriate for your design. If your system is more of an OLAP than OLTP type of system then don't bother implementing this since it won't help you.
Cross-posted from SQLBlog! - http://www.sqlblog.com
Subscribe to:
Posts (Atom)