SSMS: Query Shortcuts

No reason to type out SELECT TOP 1000 * FROM or SELECT COUNT(*) FROM anymore.

The SQL MCM Might Not Be For Me

I've been thinking a lot about the Microsoft Certified Master Certification recently, trying to decide if it's worth it.

On one hand, I really like being identified as a smart guy, and perhaps MCM will help that effort. I can picture it now - I walk into SQL Pass, standing proud and tall in my scottish kilt, legs hairy, smiling brightly...and I hear hushed whispers of "There's Ike...the Microsoft Certified Master." Reverence on their faces as I stride into the room...right up to the point where I trip, legs sprawling and they realize I'm wearing that kilt as a true scotsman.

Also, I seem to learn better when I have a goal. It gives the learning purpose, context, and urgency, which I (and many people) require in order to learn.

On the other hand, my clients do not care about this certification. I doubt I would ever even mention it. My clients are not SQL DBAs. They are corporations and institutions that don't have great SQL knowledge in-house. Some of them are large software companies that just haven't found the right SQL resource. They've never heard of MCM and therefore, wouldn't add to my bill rate by a single dollar. MCM = time-consuming = expensive and <> cost-justified. Basically, I'd be going against the advice I constantly give my customers. If it doesn't make you more money, it's not worth it.

My clients employ me for one basic reason. I'm always concerned about their profitability, efficiency, and productiveness. Getting better at making them money will make me more money. I'm unconvinced that MCM helps me achieve that.

Is ego enough of a reason to pursue this?

EDIT - 3/31/2011: Joe Sack, acting PM for MCM:SQL, has been talking to me about this post for quite some time. He gave me the details of a study done to prove the value of MCM...the non-ego-driven value. Here is a link to the study:

http://download.microsoft.com/download/7/9/5/795B3672-1B65-49DE-9180-7B7BEB0E1F52/MCM_Research_and_Evidence.pdf

Looks like my SQL MCM journey is going to begin today.

Blind Spot - T-SQL $Identity

I've been working with SQL Server for 12 years, but this short cut escaped me.

In Transact-SQL, the $IDENTITY keyword allows us to refer to the identity column without knowing its name. Since identity columns are often primary keys, we could use this as a short cut for always referring to the PK. This would be awesome to use in a code generator or an ORM.

This is not to be confused with @@IDENTITY or SCOPE_IDENTITY(), which tells us the actual number auto generated by the IDENTITY column (though each behaves in different ways.)

Test it out with this script:

use tempdb
go

create table test
(
id int identity(1,1)
, descr varchar(100)
)
go

insert into test
(descr)
values
('first record')
, ('second record')
, ('third record')
go

select $identity, descr from test

T-SQL Session, Promised Script

Several people asked that I post this script on T-SQL fundementals:

USE Northwind

SELECT *
FROM Customers;

SELECT CompanyName
, ContactName
, ContactTitle
--, Address
--, City
--, Region
--, PostalCode
FROM Customers;


SELECT CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
FROM Customers
ORDER BY ContactName;




SELECT CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
FROM Customers
ORDER BY CompanyName, ContactName DESC; --ASC is the default.

SELECT CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
FROM Customers
WHERE CompanyName = 'Alfreds Futterkiste'
ORDER BY ContactName DESC; --ASC is the default.

--Comparison Operators
--=
--<>
--!=
--<
--<=
--!<
-->
-->=
--!>
--BETWEEN
--IS NULL

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight > 100;

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight !> '100';

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight <= '100' ;

SELECT CustomerID
, Region
FROM Customers
WHERE Region <> NULL;

SELECT CustomerID
, Region
FROM Customers
WHERE Region <> 'CA';

SELECT CustomerID
, Region
FROM Customers
WHERE Region <> 'CA' OR Region IS NOT NULL;

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight BETWEEN '10.14' AND '43.90'
ORDER BY Freight ;

--Logical Operators
--AND
--OR
--NOT

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight BETWEEN '10.14' AND '43.90'
OR ShippedDate BETWEEN '1/1/1997' AND '12/1/1997'
ORDER BY ShippedDate, Freight ;

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight BETWEEN '10.14' AND '43.90'
OR ShippedDate BETWEEN '1/1/1997' AND '12/1/1997'
ORDER BY ShippedDate, Freight ;


--I want all orders from CustomerID = VINET with a freight over $10 and all orders from CustomerID TOMSP with a freight over $10.
SELECT CustomerID
, OrderID
, Freight
FROM Orders
WHERE Freight > '10'
AND CustomerID = 'VINET'
OR CustomerID = 'TOMSP'
ORDER BY CustomerID;

--SQL is reading this from left to right. So technically we can do this:
SELECT CustomerID
, OrderID
, Freight
FROM Orders
WHERE CustomerID = 'VINET'
AND Freight > '10'
OR CustomerID = 'TOMSP'
AND Freight > '10'
ORDER BY CustomerID;

SELECT CustomerID
, OrderID
, Freight
FROM Orders
WHERE (CustomerID = 'VINET'
OR CustomerID = 'TOMSP')
AND Freight > '10'
ORDER BY CustomerID;

--IN Operator
SELECT CustomerID
, OrderID
FROM Orders
WHERE (CustomerID = 'VINET'
OR CustomerID = 'TOMSP'
OR CustomerID = 'HANAR'
OR CustomerID = 'SUPRD');


SELECT CustomerID
, OrderID
FROM Orders
WHERE CustomerID IN ('VINET', 'TOMSP', 'HANAR', 'SUPRD');

SELECT CustomerID
, OrderID
FROM Orders
WHERE CustomerID NOT IN ('VINET', 'TOMSP', 'HANAR', 'SUPRD');


--WILDCARDS
SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'A%';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '%A';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '%A%';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'W%A';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'F%A%';

SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '[FM]%';


SELECT CompanyName + ' - ' + ContactName
+ ' - ' + ContactTitle AS HeaderLine1
FROM Customers
WHERE CompanyName + ' - ' + ContactName
+ ' - ' + ContactTitle LIKE 'A%'
ORDER BY HeaderLine1;

--Order of Operation
--1.FROM clause
--2.WHERE clause
--3.GROUP BY clause
--4.HAVING clause
--5.SELECT clause
--6.ORDER BY clause

--Calculated fields - Math
SELECT
OrderID
, ProductID
, UnitPrice
, Quantity
, UnitPrice * Quantity AS TotalPrice
FROM [Order Details];

--Math operators
-- +, -, *, /


--FUN WITH FUNCTIONS!

--Figuring out envelope size for a mailing
SELECT CompanyName
, LEN(CompanyName) AS NumberOfChars
FROM Customers;

--Uppercase CompanyName
SELECT UPPER(CompanyName) AS UpperCompany
FROM Customers;

--STRING FUNCTIONS
--LTRIM, RTRIM
--SUBSTRING
--RIGHT
--LEFT
--SOUNDEX

SELECT ContactName
FROM Customers
WHERE SOUNDEX(ContactName) = SOUNDEX('paul');

--Date Functions
--DATEDIFF
--DATEADD
--MONTH
--YEAR
--DATEPART

--The difference between order date and ship date in days - T-SQL help is great with Functions - SHIFT-F1
SELECT
OrderDate
, ShippedDate
, DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
FROM Orders;

--The orders that took longer than 15 days to ship
SELECT
OrderDate
, ShippedDate
, DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
FROM Orders
WHERE DATEDIFF(d, OrderDate, ShippedDate) > 15;


--Aggregates
--MAX
--MIN
--SUM
--AVG
--COUNT
--COUNT DISTINCT

--Most recent Order Date
SELECT MAX(OrderDate) AS MaxOrderDate
FROM Orders;

--First Order Date
SELECT MIN(OrderDate) AS MaxOrderDate
FROM Orders;

--Let's combine this into a subquery
SELECT OrderID
FROM Orders
WHERE OrderDate =
(
SELECT MAX(OrderDate) AS MaxOrderDate
FROM Orders
);

--Max Days to Ship --Null values out of aggregation
SELECT MAX(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders;

--Average Days to Ship
SELECT AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders;

--Orders above my average days to ship
SELECT OrderID
, CustomerID
, OrderDate
, ShippedDate
, DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
FROM Orders
WHERE DATEDIFF(d, OrderDate, ShippedDate) >
(
SELECT AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders
) ;


SELECT CustomerID
, AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders
GROUP BY CustomerID;

--Customers with an average greater than the average
SELECT CustomerID
, AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders
GROUP BY CustomerID
HAVING AVG(DATEDIFF(d, OrderDate, ShippedDate)) >= 15;

--JOINS
--INNER
--RIGHT OUTER
--LEFT OUTER

--Inner Join
SELECT c.CompanyName
, c.CustomerID
, o.OrderDate
, o.ShippedDate
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
ORDER BY c.CompanyName

--LEFT OUTER JOIN
SELECT c.CompanyName
, c.CustomerID
, o.OrderDate
, o.ShippedDate
FROM Customers c
LEFT OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL
ORDER BY c.CompanyName


SELECT *
FROM Customers

--CRUD
INSERT INTO Customers
(CustomerID, CompanyName)
VALUES
('ELLIS', 'EllisTeam')

DELETE FROM Customers
WHERE CustomerID = 'ELLIS'

UPDATE Customers
SET ContactName = 'Ike Ellis'

SELECT * FROM Customers
WHERE CustomerID = 'Ellis'

DELETE FROM Customers
WHERE CustomerID = 'ELLIS'


UPDATE o
SET Freight = Freight + 1
FROM Customers AS c
JOIN Orders AS o
ON c.CustomerID = o.CustomerID
WHERE Region = 'CA'


--Tips
--================
--Sometimes T-SQL doesn't read as intuitively as we like. Use white space and comments.

--Limit record results to as little as possible. (Good for network, disk, memory, users, and performance.)

--Nulls

--Connection Tips - Coloring and highlighting

--Always use Linenumbers

--Copy and paste results into EXCEL for adhoc stuff

--Show an error message (RAISEERROR) with severity
RAISERROR (N'This is message.', 18, 1);

--Learn about Query Plans
SELECT *
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
JOIN [Order Details] od
ON o.OrderID = od.OrderID
JOIN Products p
ON od.ProductID = p.ProductID
ORDER BY c.CompanyName

--Object Explorer Drag & Drop

--Avoid Cursors

--Be careful updating and deleting (WHERE clauses, backup, SELECT using the WHERE clause, then Delete with the same one.)

--Never change production without changing dev and beta first

--Don't bother coding for portability or language independence.

--Consider using synonyms for long table names
CREATE SYNONYM Cust FOR dbo.Customers
SELECT * FROM Cust
DROP SYNONYM Cust

--TRUNCATE VS DELETE
--Logged and WHERE
DELETE FROM Temp1

--Non-logged and no WHERE, but fast
TRUNCATE TABLE Temp1


--Common question - How to delete duplicates
CREATE TABLE dbo.duplicateTest
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

SELECT *
FROM dbo.duplicateTest

SELECT *
FROM dbo.duplicateTest
WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'

DELETE TOP(1) FROM dbo.duplicateTest WHERE ID = 1

DROP TABLE duplicateTest

UPDATE Customers
SET ContactName = 'Ike Ellis'


WHERE CustomerID = 'ELLIS'


DELETE FROM Customers
WHERE CustomerID = 'ELLIS'

Differences Between SSRS Report Builder 1.0 and Report Builder 2.0

I was going to draft a blog post on this topic when I found this one:

Andrew Fryer's Blog

Andrew wrote this very clearly and I don't feel the need to add to it.

SQL Server 2008 Shrink Log File

When DBAs wanted to shrink a log file in SQL 2000 and 2005, they would often backup the log with TRUNCATE_ONLY. When they attempt to do the same thing in SQL Server 2008, they will be greeted with this message:

'TRUNCATE_ONLY' is not a recognized BACKUP option.

Here is a replacement script for SQL Server 2008:

USE Test
GO
ALTER DATABASE Test
SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE(Test_Log, 10)

GO
ALTER DATABASE Test
SET RECOVERY FULL

This script puts the database in Simple Recovery mode. It then shrinks the log file. Finally, it puts the database back in Full Recovery mode.

New Index grayed out in SQL Server Management Studio

In SQL Server Management Studio, if you right-click on the indexes and you see new index grayed out, it is because the table is schema-locked in another window. Close the design view of the table and new index should now be black and usable.

SSRS 2008 List All Reports, Users, Roles, and Security

Someone on the newsgroups today asked the following question:

Is there any report that can be run in Reporting Services that lists
all reports and the users that have access?


I assumed he meant that he wanted a list of all the reports he has, and a list or roles and the users that are in them. I quickly wrote the following query for him. I hope some of you find it useful:

SELECT u.UserName, c.[Path], c.[Name], r.RoleName, *
FROM Users u
JOIN PolicyUserRole pur
ON u.UserID = pur.UserID
JOIN Policies p
ON pur.policyID = p.PolicyID
JOIN Catalog c
ON p.policyID = c.policyID
JOIN Roles r
ON pur.RoleID = r.RoleID
ORDER BY u.UserName, c.[Name], r.RoleName

SQL Server Support with VMWare

I have a client who is running SQL Server 2005 Standard Edition on a VMWare ESX operating system. I wondered if Microsoft supported this environment and did some digging. I had some difficulty finding current information, so once I found it, I thought I'd share it with you.

As of November 2007, Microsoft says they do not support third party software virtualization. They require that you reproduce the issue in a non-virtualized environment before they will help you. Here's the link confirming that:

http://support.microsoft.com/kb/897615

Tracing Events Appropriately

When using SQL Server Profiler, it is generally a good idea to only trace COMPLETED events, instead of starting events.

Completed events have all necessary performance information. The starting events do not have important performance statistics because they haven't been collected yet. This will reduce the load of tracing and give you less data to filter out later.

Performance Tuning and the Heisenberg Uncertainty Principle

Often when I visit a new client, they say, "We need you to start SQL Server Profiler and tell us why our server is so slow."

I'm reluctant to immediately resort to the SQL Server tracing utilities because of the performance load that they cause. Sometimes, I explain to the Heisenberg Uncertainty Principle:

http://en.wikipedia.org/wiki/Uncertainty_principle

and similarly the Observer Effect:

http://en.wikipedia.org/wiki/Observer_effect

Developed by Werner Heisenberg in 1927, this principal states that when you measure something, there is uncertainty caused by the measuring process. For instance, if we write our trace logs to a file and we're looking for disk contention, the tracing itself might be contributing to it. The uncertainty might also be caused by the measuring logic. We might be looking at excessive disk contention and determine that we need faster disks. But what if memory paging is causing the pages to be written to disk? In that case, our measuring logic was flawed, and we would need to add more RAM to resolve the performance bottlenecks.

So before I start SQL tracing, I like to use other tools first. Task Manager, System Monitor, Activity Monitor, and DMVs will tell us an awful lot about a server's performance before tracing is even needed. This allows me to observe the server's performance with an open mind, before using invasive tracing utilities.

Format dates and times in T-SQL

I stole this script off of the internet a few years ago. I wish I could give the author credit for it, but I can't remember who he is. If anyone knows, let me know and I'll add him to this post.

This function allows you to do date/time formatting just like you do it in C# or VB.NET.

I find it enormously useful. My code is littered with calls to it.


USE [ncu]
GO
/****** Object:  UserDefinedFunction [dbo].[FormatDateTime]    Script Date: 01/27/2011 17:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FormatDateTime]
                      (@f_datetime DATETIME
                     , @f_format   VARCHAR (60))
RETURNS VARCHAR (60)
AS
/*
||======================================================================
|| Purpose:  Provide a more intuitive method of converting DATETIME
||           values to character.  Only the parts specified within
||           the input format request are sent back, and any other
||           characters are returned in their respective places.
||
||           Here are the special-request symbols:
||
||           [YY] or [YYYY]   -> year
||           [MM] or [M]      -> month (numeric; 'M' means one digit
||                                      where possible)
||           [DD] or [D]      -> day
||           [DWEEK]          -> day of week
||           [DWK]            -> 3-characters day of week abbreviation
||           [MON]            -> 3-character month abbreviation
||           [MONTH]          -> entire month name
||           [HH] or [H]      -> hour
||           [24HH] or [24H]  -> 24-hour clock
||           [mm] or [m]      -> minute
||           [NN] or [N]      -> minute
||           [SS] or [S]      -> second
||           [AA] or [PP]     -> 'AM' or 'PM'
||           [AM] or [PM]     -> 'AM' or 'PM'
||
||           Every other character is considered part of the output.
||======================================================================
*/
BEGIN
  /*
  ||====================================================================
  || Data declarations...
  ||====================================================================
  */
  DECLARE @datetime    VARCHAR (60)
        , @symbol      VARCHAR (7)
        , @translation VARCHAR (9)
        , @hhmmss      CHAR (8)
        , @year        INT
        , @month       INT
        , @day         INT
        , @hour        INT
        , @minute      INT
        , @second      INT
        , @pos         INT
        , @pos2        INT
        , @len         INT

  /*
  ||====================================================================
  || Validations...
  ||====================================================================
  */
  /*
  ||====================================================================
  || A NULL input earns a NULL output...
  ||====================================================================
  */
  IF @f_datetime IS NULL
  OR @f_format   IS NULL
--THEN
    GOTO FNC_EXIT
--END IF

  /*
  ||====================================================================
  || End of validations...
  ||====================================================================
  */
  /*
  ||====================================================================
  || Main logic...
  ||====================================================================
  */
  /*
  ||====================================================================
  || Acquire the value of each of the date-time components in integer
  || form...
  ||====================================================================
  */
  SELECT @hhmmss   = CONVERT (VARCHAR, @f_datetime, 8)
       , @datetime = @f_format
  SELECT @year     = YEAR  (@f_datetime)
       , @month    = MONTH (@f_datetime)
       , @day      = DAY   (@f_datetime)
       , @hour     = CONVERT (INT, LEFT (@hhmmss, 2))
       , @minute   = CONVERT (INT, SUBSTRING (@hhmmss, 4, 2))
       , @second   = CONVERT (INT, RIGHT (@hhmmss, 2))

  /*
  ||====================================================================
  || For users' convenience, we allow small-case m to denote minutes
  || large-case M to denote month; or they can use the unintuitive
  || 'N' to denote minutes.
  ||
  || However, in order to support case-seensitivity even in a case-
  || insensitive environment, the code below locates each special symbol
  || within the specified format and replaces small-case '[m]' and
  || '[mm]' with '[N]' and '[NN]', or else all other small-case letters
  || with their large-case equivalents...
  ||====================================================================
  */
  SELECT @pos = CHARINDEX ('[', @datetime)

  WHILE @pos > 0
  BEGIN
    SELECT @pos2 = CHARINDEX (']', @datetime, @pos)

    IF @pos2 = 0
  --THEN
      SELECT @pos = 0
    ELSE
      BEGIN
        SELECT @len    = @pos2 - @pos + 1
             , @symbol = SUBSTRING (@datetime, @pos, @len)

        IF CONVERT (VARBINARY (120), @symbol)
         = CONVERT (VARBINARY (120), '[m]')
      --THEN
          SELECT @symbol = '[N]'

        ELSE IF CONVERT (VARBINARY (120), @symbol)
              = CONVERT (VARBINARY (120), '[mm]')
      --THEN
          SELECT @symbol = '[NN]'
        ELSE
          SELECT @symbol = UPPER (@symbol)
      --END IF

        SELECT @translation =
          CASE @symbol
            WHEN '[YYYY]' -- year
            THEN CONVERT (CHAR (4), @year)
            WHEN '[YY]'   -- year
            THEN RIGHT (CONVERT (CHAR (4), @year), 2)
            WHEN '[MONTH]'
            THEN UPPER (DATENAME (MM, @f_datetime))
            WHEN '[MON]'
            THEN LEFT (UPPER (DATENAME (MM, @f_datetime)), 3)
            WHEN '[M]'
            THEN CONVERT (VARCHAR, @month)
            WHEN '[MM]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @month), 2)
            WHEN '[D]'
            THEN CONVERT (VARCHAR, @day)
            WHEN '[DD]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @day), 2)
            WHEN '[DWEEK]'
            THEN UPPER (DATENAME (DW, @f_datetime))
            WHEN '[DWK]'
            THEN UPPER (LEFT (DATENAME (DW, @f_datetime), 3))
            WHEN '[H]'
            THEN
              CASE
                WHEN @hour BETWEEN 1 AND 12
                THEN CONVERT (VARCHAR, @hour)
                ELSE CONVERT (VARCHAR, ABS (@hour - 12))
              END
            WHEN '[HH]'
            THEN
              CASE
                WHEN @hour BETWEEN 1 AND 12
                THEN RIGHT ('0' + CONVERT (VARCHAR, @hour), 2)
                ELSE RIGHT ('0' + CONVERT (VARCHAR, ABS (@hour - 12)), 2)
              END
            WHEN '[24H]'
            THEN CONVERT (VARCHAR, @hour)
            WHEN '[24HH]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @hour), 2)
            WHEN '[N]'
            THEN CONVERT (VARCHAR, @minute)
            WHEN '[NN]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @minute), 2)
            WHEN '[S]'
            THEN CONVERT (VARCHAR, @second)
            WHEN '[SS]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @second), 2)  
            ELSE
              CASE
                WHEN @symbol IN ('[AM]', '[PM]', '[AA]', '[PP]')
                THEN
                  CASE
                    WHEN @hour < 12
                    THEN 'AM'
                    ELSE 'PM'
                  END
                ELSE @symbol
              END
          END

        SELECT @datetime = STUFF (@datetime, @pos, @len, @translation)
             , @pos      = CHARINDEX ('[', @datetime, @pos + LEN (@translation))
      END
  --END IF
  END

FNC_EXIT:
  RETURN @datetime
END


Do Your Foreign Key Columns Have Indices?

When I do a database audit, I always check for the existence of foreign keys that enforce referential integrity. That is often a great sign on whether or not a data-centric developer has been involved in the project. Once foreign keys have been created, indices must manually be configured, for SQL Server does not create them automatically.

I wrote this script to tell you if you have indices on all of your foreign key columns:

SELECT o.[Name] AS TableName, c.[Name] AS ColumnName
FROM sysForeignKeys f
JOIN sysObjects o
ON f.fkeyID = o.[id]
JOIN sysColumns c
ON c.[ID] = f.fkeyID AND c.[colid] = f.fkey
LEFT OUTER JOIN sysIndexKeys i
ON f.[fkeyid] = i.[id] AND f.[fkey] = i.[colid] AND f.[keyno] = i.[keyno]
WHERE i.[id] IS NULL

Even if referential integrity is being enforced at the application layer (GASP!), you should still have indices created on those virtual foreign key columns. Unfortunately, that will have to be audited manually by the application developer, which pretty much means it will never happen.

Hard Disk Configuration

When asked where hardware dollars are best spent, I always recommend a great disk subsystem.

Hard drives are often the source of performance bottlenecks. Any dime spent on a high-performing hard drives will not go to waste.

Disks are also the component that will most likely fail. Matt, the Microsoft blogger, claims that disks have a failure rate of 2% per year per disk. If that's the case, then an investment in RAID arrays would be very wise.

As far as specific drive components go, it's difficult to make a recommendation without knowing the purpose of the SQL Server.

Here's Matt's post:

http://blogs.msdn.com/matt_hollingsworth/archive/2007/04/02/mtbf-is-useless-how-to-decide-when-to-use-raid-for-database-high-availability.aspx

No More DBCC SHOWCONTIG

In SQL Server 2005, DBCC SHOWCONTIG has been replaced with the function sys.dm_db_index_physical_stats. Use this function if you need to diagnose fragmentation and page usage issues.

Pay attention to the fragmentation levels in the output. This is reporting more accurately than in SQL Server 2000.

Here's a Books Online link explaining the new function:
http://msdn2.microsoft.com/en-us/library/ms188917.aspx

And a link on index fragmentation:
http://msdn2.microsoft.com/en-us/library/ms189858.aspx

Dedicated Administrator Connection

In previous version of SQL, it was possible for the server to tie up so many resources, that one more connection was no longer possible. For instance, you could have one process that was blocking all the others, and have no way to open the SQL Server tools to kill it.

That all changed with the DAC. Here's the Microsoft article on how to use it:

http://msdn2.microsoft.com/en-us/library/ms189595.aspx

Kalen Delaney (Inside SQL Server 2005) has this great query for determining the SPID of the DAC:

SELECT t2.session_id
FROM sys.tcp_endpoints as t1
JOIN sys.dm_exec_sessions as t2
ON t1.endpoint_id = t2.endpoint_id
WHERE t1.name='Dedicated Admin Connection'; GO

32-bit vs 64-bit SQL

When it comes to hardware, most SQL Servers I have seen are over-powered. Memory and CPU are most often underuntilized. So when I'm asked whether a customer needs the 64-bit version of SQL server, I think it comes down to one question. Do you need more than 3GB of memory addressed?

It's important to note that the 32-bit version of Windows can only address 4GB of memory. By default, it reserves 2GB for itself, however this can be reduced to 1GB by using an operating system switch. Memory beyond the 4GB addressed by the OS can be accessed by SQL Server indirectly using the PAE switch.

http://msdn2.microsoft.com/en-us/library/ms190673.aspx

The 64-bit version of SQL Server can address an incredible amount of memory directly, with no need of these work-arounds.

Free SQL Server 2005

Just a friendly reminder that you can evaluate SQL Server 2005 for free for 120 days. This is an excellent way to learn the features of the product before purchasing it. It is the full Enterprise edition. You can download it here:

http://www.microsoft.com/technet/prodtechnol/eval/sqlsrv05/default.mspx