There is a ton of SQL Training resources on the information super highway. Take advantage of these opportunities, especially if it’s free. Here are some Free training opportunities:
Anytime anyone runs any command on a SQL server, it changes the conditions of that SQL server. Execution plans get created, different objects are stored in memory, etc. I don’t like to use time as a performance tuning metric (I’m a big fan of logical reads), but most people do – especially executives, so when measuring execution time, it’s important to compare apples to apples.
Below is a script, which I call “Almost a Reboot” as it’s almost like rebooting server, that I run on a server anytime I’m tesing performance. I got the initial script from Kimberly Tripp (Blog/Twitter) at DevConnections in about 2004 – I’ve tweaked it when SQL2005 came out and it’s probably due for a few changes for SQL2008, so don’t be shy to offer suggestions.
My steps for performance tuning any SQL code are:
Run this script
Run the original code
Record the results
Make my change
Run this script again
Run the changed code
Record/Compare the results
--Almost a reboot--
DECLARE @DBName Varchar(255)
Set @DBName = <<SET DBNAME HERE>> --Fill this in to the database you want to flush
CHECKPOINT --writes dirty pages to disk
DBCC FREEPROCCACHE --clears entire plan cache
DBCC DROPCLEANBUFFERS --clears all clean data cache pages
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = @DBName)
--Recompile the stored procedures
DBCC FLUSHPROCINDB (@intDBID) --clears all clean plan cache for specified database
Again, let me know if you have any good tips to update this.
His trick, which I’ve tried, is to change the batch separator in a co-workers SSMS to “Select”, as shown below – it doesn’t take effect until the next connection and it will drive them crazy:
Note for this tip, they will still get the same error:
The DATEDIFF function is very simple and very powerful…
With it you can:
Count the days, weeks, years, since an event occurred (I’ve done this to impress my kids by telling them how many day old they are)
Select DATEDIFF(D, ‘1/1’11’, GETDATE()) –How many days since New Years Day 2011
Count the minutes, seconds, milliseconds, microseconds, nanoseconds until something occurs (I’ve counted down until Friday at 5 PM a few times as well as to the day I retire).
Select DATEDIFF(D, GETDATE(), ‘1/1’15’) –How many days until the Coach Kill leads the Gopher Nation to the Rose Bowl
Add extra protection to your databases by adding a DDL trigger that will prevent users from deleting your databases.
Why?
In the last year, five times a user deleted the wrong database by mistake in SQL Server Management Studio – each time they thought they were connected to their local machine, but they were really connect to a shared development environment. If we would have had these triggers on our shared environments, we wouldn’t have wasted anytime restoring the databases.
Here’s a great article from Chriss K on MSDNwith code showing how easy it is to add this extra layer of protection.
Often times I’ll need to count how many records are added to a table based on the date. To do this, I use the GROUP BY and the CONVERT function.
The idea is to convert the datetime to just a date, then group and count the data by the date.
The Set Up
Here’s an example I got from this post, slightly altered:
SET NOCOUNT ON
Go
Create Table #RandomDates (DateTest datetime)
Go
Declare @Counter int
Set @Counter = 0
While @Counter < 1000
Begin
Insert #RandomDates (DateTest)
Select Dateadd(D, (-1)* ROUND((RAND() * (10)),0), Getdate()) --Random Date 0 to 10 days old
Set @Counter = @Counter + 1
End
The Example
Select Convert(date, DateTest) as 'Date',
Count(Convert(date, DateTest)) as 'Count'
from #RandomDates
Group by Convert(date, DateTest)
Order by Convert(date, DateTest)
Here are my results – yours will differ as the randomness implies
Recently I needed to convert a column to a file of comma separated values (CSV). There are a few ways to do this via TSQL, here’s a simple one – use COALESCE. The idea is to declare a variable, set it equal to itself and a comma and the next data value, finally select the variable. See the example as it’s much easier to follow.
The Setup:
Create table CSVTest (Name varchar(50))
GO
Insert CSVTest Values('Andy Lohn'),
('Adrian Lohn'),
('Maggie Lohn'),
('Nala Lohn')
Go
Select Name from CSVTest
Go
This returns, as expected the following:
The Example:
Declare @Names varchar(2000)
Select @Names = Coalesce(@Names + ', ','') + Name from CSVTest
Select @Names as 'CSV List'
This returns the same results, except in as a comma separated list instead of a column.
Here are some related posts from the intraweb – including other ways to do this with user defined functions and XML:
The World is Flat – I just started reading that book this weekend, I know I’m a bit behind the times, but it’s still interesting. If you don’t know the book – I would suggest reading it as it contains a lot of topics on outsourcing and specialized work – such as SQL development, hosting, etc. This hits real close to home for me these days.
What does this have to do with the TSQL Tip o’ the Week? Well if you are working on systems that are used in different time zones, consider using GETUTCDATE as the standard for recording the date/time, that way irreguardlessly regardless of what time zone you are in you will always be comparing apples to apples.
Here is a good post from the intraweb showing an example of where this is very helpful:
I use wildcards and the “LIKE” operator in my TSQL all the time – a lot of the time to find table names, column names etc.
Select OBJECT_NAME (OBJECT_ID),
NAME
from sys.columns
where name like '%file%'
Ninety eight percent of the time, I use the %. The other two percent, I need to look up the exact syntax. Well, here’s a quick reference for you:
Wild Card
Description
%
Any string of characters i.e. %ID%
_
Any single character i.e. _ndy
[]
Any single single character in a range specified or set i.e. [A-L] OR [ABC]
[^]
Any single single character not in a range specified or set i.e. [^A-L] OR [^ABC]
Sometimes an issue comes up where you need to search for the actual wildcard character, for more info on that, check out the section titled “Using Wildcard Characters As Literals” from the MSD article on LIKE.
