A blog about SQL Server, Books, Movies and life in general
Monday, May 14, 2007
Make Your Case Sensitive Searches 1000 Times Faster
Once I modified my WHERE clause the update took a little less than 3 seconds
Let's get started and see what I did
First create this table
CREATE TABLE #CaseSensitiveSearchTemp (Val CHAR(1))
INSERT #CaseSensitiveSearchTemp VALUES('A')
INSERT #CaseSensitiveSearchTemp VALUES('B')
INSERT #CaseSensitiveSearchTemp VALUES('C')
INSERT #CaseSensitiveSearchTemp VALUES('D')
INSERT #CaseSensitiveSearchTemp VALUES('E')
INSERT #CaseSensitiveSearchTemp VALUES('F')
INSERT #CaseSensitiveSearchTemp VALUES('G')
INSERT #CaseSensitiveSearchTemp VALUES('H')
Now we will insert some lowercase characters
INSERT #CaseSensitiveSearchTemp
SELECT LOWER(Val) FROM #CaseSensitiveSearchTemp
Now we will create our real table which will have 65536 rows
CREATE TABLE CaseSensitiveSearch (Val VARCHAR(50))
We will do a couple of cross joins to generate the data for our queries
INSERT CaseSensitiveSearch
SELECT t1.val + t2.val + t3.val + t4.val
FROM #CaseSensitiveSearchTemp t1
CROSS JOIN #CaseSensitiveSearchTemp t2
CROSS JOIN #CaseSensitiveSearchTemp t3
CROSS JOIN #CaseSensitiveSearchTemp t4
This should give you 65536 rows
SELECT * FROM CaseSensitiveSearch
Create an index on the table
CREATE INDEX IX_SearchVal ON CaseSensitiveSearch(Val)
This is how you do a case sensitive search
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
Now hit CRLK + K (SQL Server 2000) or CRLK + M(SQL Server 2005)
run these 2 queries in one batch by highlighting them both and hitting F5
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'ABCD'
Look at the execution plan, I get 98.71% for the first query and 1.29% for the second query. Just by adding the AND condition SQL server is able to do an index seek and run the query many times faster
Now try it with a lowercase a
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT * FROM CaseSensitiveSearch
WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
AND Val LIKE 'aBCD'
You see it all works without a problem, the correct result is returned
Monday, May 07, 2007
Three Ways To Return All Rows That Contain Uppercase Characters Only
1 Compare with BINARY_CHECKSUM
2 Use COLLATE
3 Cast to varbinary
Let's first create the table and also some test data
CREATE TABLE #tmp ( x VARCHAR(10) NOT NULL )
INSERT INTO #tmp
SELECT 'Word' UNION ALL
SELECT 'WORD' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'AbC' UNION ALL
SELECT 'ZxZ' UNION ALL
SELECT 'ZZZ' UNION ALL
SELECT 'word'
if we want only the uppercase columns then this is supposed to be our output
WORD
ABC
ZZZ
Let's get started, first up is BINARY_CHECKSUM
SELECT x
FROM #TMP
WHERE BINARY_CHECKSUM(x) = BINARY_CHECKSUM(UPPER(x))
Second is COLLATE
SELECT x
FROM #TMP
WHERE x = UPPER(x) COLLATE SQL_Latin1_General_CP1_CS_AS
Third is Cast to varbinary
SELECT x
FROM #TMP
WHERE CAST(x AS VARBINARY(10)) = CAST(UPPER(x) AS VARBINARY(10))
Of course if you database is already case sensitive you can just do the following
SELECT x
FROM #TMP
WHERE UPPER(x) = x
That will work, how do you find out what collation was used when your database was created? You can use DATABASEPROPERTYEX for that. I use the model DB here because when you create a new DB by default it inherits all the properties from the model DB.
When I run this
SELECT DATABASEPROPERTYEX( 'model' , 'collation' )
I get this as output: SQL_Latin1_General_CP1_CI_AS
What does all that junk mean? Well let's run the following function (yes those are 2 colons ::)
SELECT *
FROM ::fn_helpcollations ()
WHERE NAME ='SQL_Latin1_General_CP1_CI_AS'
The description column contains this info
Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
You can read some more info about Selecting a SQL Collation here: http://msdn2.microsoft.com/en-us/library/aa176552(SQL.80).aspx
Tuesday, April 24, 2007
You Should Never Use IN In SQL To JOIN With Another Table
SELECT *
FROM TABLE
WHERE CoLumn IN (SELECT Column FROM TABLE)
OR this?
SELECT *
FROM TABLE
WHERE CoLumn NOT IN (SELECT Column FROM TABLE)
Do NOT use that, it will cause problems sooner or later. Don't believe me? Let's take a look
First create these 2 tables and populate them with some sample data
CREATE TABLE TestTable1 (id1 int)
CREATE TABLE TestTable2 (id2 int)
INSERT TestTable1 VALUES(1)
INSERT TestTable1 VALUES(2)
INSERT TestTable1 VALUES(3)
INSERT TestTable2 VALUES(1)
INSERT TestTable2 VALUES(2)
Now let's run the IN query
SELECT *
FROM TestTable1
WHERE id1 IN (SELECT id2 FROM TestTable2)
-----
id1
1
2
No problems here right?
What if by mistake you wrote id1 instead of id2?
SELECT *
FROM TestTable1
WHERE id1 IN (SELECT id1 FROM TestTable2)
-----
id1
1
2
3
Oops all 3 rows are returned, if you just run this SELECT id1 FROM TestTable2 you will get this error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'id1'.
So what happens? SQL Server sees column id1 and says "yes I have that it is in the TestTable1 table, I can use that"
What can we do? Use EXISTS because you will get an error instead of a wrong resultset
SELECT *
FROM t1
WHERE EXISTS (SELECT * FROM TestTable2 t2 WHERE t2.id2 = t1.id1 )
-----
id1
1
2
A JOIN will do the same as EXISTS
SELECT t1.*
FROM TestTable1 t1
JOIN TestTable2 t2 ON t2.id2 = t1.id1
-----
id1
1
2
Now let's try NOT IN
SELECT *
FROM TestTable1
WHERE id1 NOT IN (SELECT id2 FROM TestTable2)
-----
id1
3
No problem right?
Add a NULL value to the TestTable2 table
INSERT TestTable2 VALUES(NULL)
Let's try running it again
SELECT *
FROM TestTable1
WHERE id1 NOT IN (SELECT id2 FROM TestTable2)
Where are my rows? Nowhere, since NULL is not equal to anything including another NULL SQL just returns nothing
What happens when you use NOT EXISTS?
SELECT *
FROM TestTable1 t1
WHERE NOT EXISTS (SELECT * FROM TestTable2 t2 WHERE t2.id2 = t1.id1 )
-----
id1
3
That works without a problem
What about a LEFT JOIN?
SELECT t1.*
FROM TestTable1 t1
LEFT JOIN TestTable2 t2 ON t2.id2 = t1.id1
WHERE t2.id2 IS NULL
-----
id1
3
That works without a problem also
So from now on use EXISTS, NOT EXISTS, JOIN and LEFT JOIN
DO NOT use IN or NOT IN ever again. Okay?
Thursday, March 29, 2007
TEMPDB and SQL Server 2005 Tip
This will make a big difference, SQL Server 2005 uses the tempdb different than 2000, by adding more files you will see a dramatic performance improvement for big queries
Our queries on SQL Server 2005 ran slower than on 2000, by adding more files the queries are twice as fast than on the SQL Server 2000 box now
This behaviour is documented here: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
Look at number 7 (Consider configuration of TEMPDB database)
and number 8 (Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads. )
Wednesday, March 14, 2007
How To Get The Database Name For The Current User Process
First up is the fastest method. this will run on SQL Server 200 and 2005
SELECT DB_NAME()
Next up is getting the name by joining the sys.dm_exec_requests dmv and sys.sysdatabases. this runs on SQL Server 2005 only
SELECT s.name
FROM sys.dm_exec_requests d
JOIN sys.sysdatabases s on d.database_id = s.dbid
WHERE session_id = @@SPID
Here is something similar, this also runs only on SQL Server 2005
SELECT name
FROM sys.sysdatabases
WHERE dbid = DB_ID()
And we end with something that runs on both SQL Server 2005 and 2000
SELECT name
FROM master..sysdatabases
WHERE dbid = DB_ID()
But like I said before you should always use DB_NAME()
Monday, March 05, 2007
How To Make A FileGroup Read Only in SQL Server 2005
How to make a filegroup read only in SQL Server 2005? This question popped up today on tek-tips. This is how you do that: First we will create a new database named TestFilegroup. Next we will add a filegroup named Test1FG1 which contains a file named test1dat3.
The next step is to do an alter database modift filegroup readonly command
Here is the complete script
USE master
go
--Create New DB For Testing
CREATE DATABASE TestFilegroup
go
--Create FileGroup
ALTER DATABASE TestFilegroup
ADD FILEGROUP Test1FG1;
--Add file to fileGroup
ALTER DATABASE TestFilegroup
ADD FILE
(
NAME = test1dat3,
FILENAME = 'c:\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1
--Make FileGroup ReadOnly
ALTER DATABASE TestFilegroup
MODIFY FILEGROUP Test1FG1 Read_Only;
Use Read_Only not ReadOnly because the keyword READONLY will be removed in a future version of Microsoft SQL Server. Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. Use READ_ONLY instead.
Now what happens when you try to create a table on filegroup Test1FG1?
USE TestFilegroup
GO
CREATE TABLE abc (id INT) ON Test1FG1
You will see the following error message
Server: Msg 1924, Level 16, State 2, Line 1
Filegroup 'Test1FG1' is read-only.
You can use sys.filegroups and check the is_read_only column to find out if a filegroup is read only
SELECT is_read_only
FROM sys.filegroups
WHERE name = 'Test1FG1'
Here is the result
is_read_only
------------
1
Using NTFS Compression with Read-Only User-defined Filegroups and Read-Only Databases
SQL Server 2005 supports NTFS compression of read-only user-defined filegroups and read-only databases. You should consider compressing read-only data in the following situations:
You have a large volume of static or historical data that must be available for limited read-only access.
You have limited disk space.
ASP.NET Library Site Launched
Applications
Controls
Database
HTML
So go ahead and check it out: http://aspnetlibrary.com/
Saturday, February 10, 2007
How to Backup Your Blogger Blog
http://googlesystem.blogspot.com/2007/02/how-to-backup-blogger-blog.html
Friday, February 09, 2007
Use A Combination OF NULLIF and COALESCE TO Display A Custom Value
What is the best way to do this? You can Use CASE and test for the values or you can use COALESCE with NULLIF which is much shorter. A lot of people don't know that you can stack these functions so that it makes your life so much easier.
Let's get started
CREATE TABLE Blah (SomeCol VARCHAR(33))
INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')
--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah
--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah
Output for both queries
-----------------------
N/A
N/A
N/A
A
B B
Monday, February 05, 2007
More Than 10 Tips To Help Your Virtual PC Performance.
http://blah.winsmarts.com/2007-2-10_tips_to_help_your_Virtual_PC_performance.aspx
Monday, January 29, 2007
How To Restart A Remote Computer
Well one way is to open a command window and executing shutdown -r
That will restart your computer
Here is the basic usage of the shutdown command
Usage: shutdown [-i -l -s -r -a] [-f] [-m \\computername] [-t xx] [-c "comment"] [-d up:xx:yy]
No args Display this message (same as -?)
-i Display GUI interface, must be the first option
-l Log off (cannot be used with -m option)
-s Shutdown the computer
-r Shutdown and restart the computer
-a Abort a system shutdown
-m \\computername Remote computer to shutdown/restart/abort
-t xx Set timeout for shutdown to xx seconds
-c "comment" Shutdown comment (maximum of 127 characters)
-f Forces running applications to close without warning
-d [u][p]:xx:yy The reason code for the shutdown
u is the user code
p is a planned shutdown code
xx is the major reason code (positive integer less than 256)
yy is the minor reason code (positive integer less than 65536)
Monday, January 15, 2007
sp_refreshview
Don't believe me? Run the following block of code
CREATE TABLE TestTable (id INT,SomeCol VARCHAR(666))
GO
INSERT TestTable VALUES(1,'ABC')
GO
SELECT * FROM TestTable
GO
CREATE VIEW TestView
AS
SELECT * FROM TestTable
GO
SELECT * FROM TestView
GO
ALTER TABLE TestTable
ADD Col2 DATETIME DEFAULT CURRENT_TIMESTAMP
GO
INSERT TestTable(id,SomeCol) VALUES(2,'XYZ')
GO
SELECT * FROM TestTable
GO
SELECT * FROM TestView
GO
See what happened? The TestView does not include the Col2 column. So what can you do? There are at least two things that you can do. You can recreate the view with a create or alter statement or you can use sp_refreshview, run the code below to see how that works
sp_refreshview TestView
GO
--All good now
SELECT * FROM TestView
GO
--Clean up this mess--
DROP VIEW TestView
GO
DROP TABLE TestTable
GO
And yes I know 'real' SQL programmers never use SELECT * and 'real' SQL programmers name their defaults ;-)
Monday, December 12, 2005
Fun With SQL Server Update Triggers
CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )
INSERT INTO TestTrigger
SELECT 'SQL',500.23
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
IF @@ROWCOUNT =0
RETURN
IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO
--Let's update the value to 100
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back all 3 rows
--Let's run the same statement
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back the first row
--Let's really update
UPDATE TestTrigger SET value = 200 WHERE testid =1
--we get back all 3 rows
--Let's update with NULL
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back rows 1 and 3, row 2 is not returned because it can't compare it
--Let's update with NULL again
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back row 1
--Let's update with 300
UPDATE TestTrigger SET value =300 WHERE testid =1
--we get back rows 1 and 3, row 2 doesn't return because it can't compare NULL to 300
--Let's update with 500
UPDATE TestTrigger SET value =500 WHERE testid =1
--we get back all 3 rows
Monday, October 17, 2005
Do Not Drop And Create Indexes On Your Tables
Use the DROP_EXISTING clause of the CREATE INDEX statement, this recreates the clustered indexes in one atomic step, avoiding recreating the nonclustered indexes since the clustered index key values used by the row locators remain the same.
Here is an example:
CREATE UNIQUE CLUSTERED INDEX pkmyIndex ON MyTable(MyColumn)
WITH DROP_EXISTING