PASS 2009's "Call for Speakers"

Well, PASS's 2009 Summit "Call for Speakers" deadline got moved out two weeks, so I had a bit more time to get everything ready. I submitted four abstracts tonight. They are posted here.

Now ... I wait.

Using REPLICATE to Build Long Strings

REPLICATE is a very useful tool, but there are limitations to its "out of the box" functionality.

Problem
This code shows the problem:

SELECT LEN(REPLICATE('X',  1000))
SELECT LEN(REPLICATE('X',  5000))
SELECT LEN(REPLICATE('X', 10000))

The result is:

----
1000

----
5000

----
8000

REPLICATE will not, by default, create a string longer than 8,000 characters. Here is the test that shows that the longest string is exactly 8,000 characters:

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE('X', @ct)

  IF len(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', len(@s1) AS 'Len @s1'
    BREAK
  END
END

---

@ct   Len @s1
----  -------
8001     8000

Fix
How can you make it build a longer string? Use CAST.

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE(CAST('X' AS varchar(max)), @ct)

  IF LEN(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', LEN(@s1) AS 'LEN @s1'
    BREAK
  END
END
SELECT LEN(@s1) AS 'Final String Length'

---

Final String Length
-------------------
              10000

Skip the Splash Screen on SQL Server Management Studio Start-Up

Simply add "-nosplash" to the target line in your SSMS shortcut, and it will skip displaying the splash screen.

According to a SQL Server Magazine article, "disabling the splash screen can cut the load time for SSMS in half."

Cut-and-Paste from Grid-Mode Output in SSMS

If you've ever cut-and-pasted output from the grid-mode output of SSMS to Excel, you've probably had to insert a row above the results and type in the column headings.

Well, you can get SSMS to include those headings in the cut-and-paste output.

Go to Tools, Options, Query Results, SQL Server, Results to Grid. Check "Include column headers when copying or saving the results".

Without that set, copying this from SSMS

and pasting it into Excel gives you this:

With it set, you get this:

Much better.

(Thanks to Roman Rehak's tip in SQL Server Magazine.)