|
|
|
|
Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at user group meetings and SQL Saturday events.
-
I see a lot of people suggest while loops instead of cursors in situations where row-based processing is required (or, at least, where folks think that row-based processing is required). Sometimes the justification is that constructing a while loop is simpler and more straightforward than constructing a cursor. Others suggest that a while loop is faster than a cursor because, well, it isn't a cursor. Of course the underlying mechanics still represent a cursor, it's just not explicitly stated that way using DECLARE CURSOR.
The difficulty of writing a piece of code should not be the primary factor in avoiding that type of code. I use MERGE - while I will likely never memorize that syntax, I know it is safer and less likely to cause deadlocks than typical "UPSERT" methodologies. Looking up the syntax diagram in Books Online, or using templates or snippets, are good workarounds to having to avoid coding constructs because they're "too hard." The same is true for cursors - sure the syntax is cumbersome, but templates or snippets can do most of the work for you.
Besides, is it that much simpler to set up and use a WHILE loop? And is it really that much more efficient?
Simplicity
I asked a few colleagues to take this code sample and change it from an explicit cursor to a WHILE loop:
DECLARE
@schema_name SYSNAME,
@object_name SYSNAME,
@index_name SYSNAME
@s NVARCHAR(MAX) = N'';
DECLARE indexes CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT s = OBJECT_SCHEMA_NAME(o.[object_id]), o = o.name, i = i.name
FROM sys.objects AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN
(
SELECT [object_id], index_id, row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS s
ON o.[object_id] = s.[object_id]
AND i.index_id = s.index_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
ORDER BY s.row_count DESC, s, o, i;
OPEN indexes;
FETCH NEXT FROM indexes INTO @schema_name, @object_name, @index_name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- we're just concatenating here, but pretend we needed to,
-- say, call a stored procedure for each row in the cursor:
SET @s += CHAR(13) + CHAR(10) + N'ALTER INDEX '
+ QUOTENAME(@index_name) + ' ON '
+ QUOTENAME(@schema_name) + '.'
+ QUOTENAME(@object_name) + ' REORGANIZE;';
FETCH NEXT FROM indexes INTO @schema_name, @object_name, @index_name;
END
CLOSE indexes;
DEALLOCATE indexes;
|
Before you crucify me about the code sample, or tell me that I could do this without a cursor or a while loop, please understand that I wasn't really trying to solve this problem, I was only trying to come up with a simple string-building exercise that can be accomplished with a cursor. I know that a cursor isn't required to just return a concatenated string, for example you can use FOR XML PATH:
DECLARE @s NVARCHAR(MAX);
SELECT @s =
(
SELECT CHAR(13) + CHAR(10) + 'ALTER INDEX '
+ QUOTENAME(i) + ' ON '
+ QUOTENAME(s) + '.'
+ QUOTENAME(o) + ' REORGANIZE;'
FROM
(
SELECT TOP (1000000)
s = OBJECT_SCHEMA_NAME(o.[object_id]),
o = o.name,
i = i.name
FROM sys.objects AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN
(
SELECT [object_id], index_id, row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS s
ON o.[object_id] = s.[object_id]
AND i.index_id = s.index_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
ORDER BY s.row_count DESC, s, o, i
) AS x
FOR XML PATH(''), TYPE
).value('.[1]', 'NVARCHAR(MAX)');
|
Or even simpler concatenation:
DECLARE @s NVARCHAR(MAX) = N'';
SELECT @s += CHAR(13) + CHAR(10) + 'ALTER INDEX '
+ QUOTENAME(i) + ' ON '
+ QUOTENAME(s) + '.'
+ QUOTENAME(o) + ' REORGANIZE;'
FROM
(
SELECT TOP (1000000)
s = OBJECT_SCHEMA_NAME(o.[object_id]),
o = o.name,
i = i.name
FROM sys.objects AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN
(
SELECT [object_id], index_id, row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS s
ON o.[object_id] = s.[object_id]
AND i.index_id = s.index_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
ORDER BY s.row_count DESC, s, o, i
) AS x;
|
(While it's true that these alternatives don't use an explicit cursor or while loop, so may appear to be "set-based," it is important to note that in these approaches the actual output order is not guaranteed. And if you think this is accomplished under the hood without a cursor-like operation, you're only fooling yourself. :-))
Again, the point of the exercise was not to eliminate the cursor, but to see if a simpler, more straightforward while loop could achieve the same result. One of the complications of the desired result is that I want the resulting output to order the indexes by row_count descending. So there is no unique, incrementing column or index to take advantage of, like you might be able to do if you have an IDENTITY column and you don't care about order otherwise. So this was a potential wrench I threw in intentionally because, in my experience, you will often care about order and you won't always have the luxury of a column inherent in the data set that supports the desired order of processing.
I'll show the four while loops that were submitted. They're all quite similar, using either #temp tables or @table variables to hold the intermediate data set before looping. I'm not going to reveal who submitted these approaches, but rather simply label them Colleague #1, #2, #3 and #4 (both to keep them straight and to protect the innocent). I hope they aren't offended that I took a few liberties with their syntax (mostly indenting and statement termination). These are all fairly similar constructs, and in fact I received a 5th contribution just prior to publishing - I didn't include it because it didn't have any elements not present below.
Colleague #1
DECLARE @indexes TABLE
(
s SYSNAME,
o SYSNAME,
i SYSNAME,
c BIGINT,
rn INT
);
INSERT @indexes(s, o, i, c, rn)
SELECT s, o, i, c, rn = ROW_NUMBER() OVER
(ORDER BY c DESC, s, o, i)
FROM
(
SELECT s = OBJECT_SCHEMA_NAME(o.[object_id]),
o = o.name,
i = i.name,
c = s.row_count
FROM sys.objects AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN
(
SELECT [object_id], index_id, row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS s
ON o.[object_id] = s.[object_id]
AND i.index_id = s.index_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
) AS x;
DECLARE
@min INT,
@max INT,
@cur INT = 0,
@s NVARCHAR(MAX) = N'';
SELECT @min = MIN(rn), @max = MAX(rn) FROM @indexes;
WHILE (@cur < @max)
BEGIN
SET @cur += 1;
SELECT @s += CHAR(13) + CHAR(10)
+ 'ALTER INDEX ' + QUOTENAME(i)
+ ' ON ' + QUOTENAME(s) + '.'
+ QUOTENAME(o) + ' REORGANIZE;'
FROM @indexes
WHERE rn = @cur;
END
|
Colleague #2
SELECT
RowID = ROW_NUMBER() OVER (ORDER BY s.row_count DESC,
OBJECT_SCHEMA_NAME(o.[object_id]), o.name, i.name),
s = OBJECT_SCHEMA_NAME(o.[object_id]),
o = o.name,
i = i.name
INTO #Temp
FROM sys.objects AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN
(
SELECT [object_id], index_id, row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS s
ON o.[object_id] = s.[object_id]
AND i.index_id = s.index_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
ORDER BY s.row_count DESC, s, o, i;
DECLARE @CurrentRowID INT,
@s NVARCHAR(MAX) = N'';
SELECT @CurrentRowID = MIN(RowID)
FROM #Temp;
WHILE @CurrentRowID IS NOT NULL
BEGIN
SELECT @s += CHAR(13) + CHAR(10) + N'ALTER INDEX '
+ QUOTENAME(i) + ' ON '
+ QUOTENAME(s) + '.'
+ QUOTENAME(o) + ' REORGANIZE;'
FROM #Temp
WHERE RowID = @CurrentRowID;
SELECT @CurrentRowID = MIN(RowID)
FROM #Temp
WHERE RowID > @CurrentRowID;
END
DROP TABLE #Temp;
|
Colleague #3
DECLARE @Temp TABLE
(
RowID INT IDENTITY PRIMARY KEY,
SchemaName SYSNAME,
ObjectName SYSNAME,
IndexName SYSNAME
);
INSERT INTO @Temp (SchemaName, ObjectName, IndexName)
SELECT
s = OBJECT_SCHEMA_NAME(o.[object_id]),
o = o.name,
i = i.name
FROM sys.objects AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN
(
SELECT [object_id], index_id, row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS s
ON o.[object_id] = s.[object_id]
AND i.index_id = s.index_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
ORDER BY s.row_count DESC, s, o, i;
DECLARE
@CurrentRowID INT,
@s NVARCHAR(MAX) = N'';
SELECT @CurrentRowID = MIN(RowID)
FROM @Temp;
WHILE @CurrentRowID IS NOT NULL
BEGIN
SELECT @s += CHAR(13) + CHAR(10) + N'ALTER INDEX '
+ QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(ObjectName) + ' REORGANIZE;'
FROM @Temp
WHERE RowID = @CurrentRowID;
SELECT @CurrentRowID = MIN(RowID)
FROM @Temp
WHERE RowID > @CurrentRowID;
END
|
Colleague #4
DECLARE
@Row INT = 1,
@s NVARCHAR(MAX) = N'';
CREATE TABLE #IndexList
(
RowID INT IDENTITY(1,1)
,SchemaName SYSNAME
,TableName SYSNAME
,IndexName SYSNAME
);
INSERT INTO #IndexList
(
SchemaName ,
TableName ,
IndexName
)
SELECT
s = OBJECT_SCHEMA_NAME(o.[object_id]), o = o.name, i = i.name
FROM sys.objects AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN
(
SELECT [object_id], index_id, row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS s
ON o.[object_id] = s.[object_id]
AND i.index_id = s.index_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
ORDER BY s.row_count DESC, s, o, i;
WHILE @Row <= (SELECT MAX(RowID) FROM #IndexList)
BEGIN
SELECT @s += CHAR(13) + CHAR(10)
+ N'ALTER INDEX ' + QUOTENAME(IndexName)
+ ' ON ' + QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REORGANIZE;'
FROM #IndexList
WHERE RowID = @Row;
SET @Row = @Row + 1;
END
DROP TABLE #IndexList;
|
I validated that the resulting value of @s matched the cursor result in all four cases. I don't know about you, and maybe I'm just being dense, but I had to
think a lot harder about that than I would have with a cursor,
cumbersome syntax and all. As far as I'm concerned, based on these four submissions from esteemed colleagues, constructing a while loop to solve this problem isn't any easier or more straightforward than a cursor. Especially since a DECLARE CURSOR block can easily be constructed for you using Management Studio's templates (or the new snippets feature in SQL Server 2012).
But what about performance? Surely a cursor is slower than a while loop, because it's a cursor, right?
Performance
As I mentioned in a previous article, the cursor usually gets a bad rap for two reasons: (1) folks think a cursor is bad simply because it says DECLARE CURSOR, and (2) people use a cursor (or any row-by-row processing methodology) when it isn't necessary. In cases where it is the best option (e.g. running totals) or it is necessary (e.g. maintenance tasks, or cases where a stored procedure call must be made for every row or conditionally depend on processing that occurred for the previous row), people just say DECLARE c CURSOR FOR ... when they should usually be using the most efficient cursor declaration possible (LOCAL STATIC FORWARD_ONLY READ_ONLY). And yes, some will argue that FORWARD_ONLY READ_ONLY could be replaced by FAST_FORWARD, but I prefer the former, because the latter can't be combined with STATIC:
Msg 1048, Level 15, State 1, Line 1
Conflicting cursor options STATIC and FAST_FORWARD.
|
Now, you are more than welcome to do your own tests with the various
cursor options, to see which perform best in your scenario, but I've
always come back to the set of options described above. I put my cursor code above into a stored procedure, as well as the two "set-based" concatenations, and the four colleague-provided options. I then ran some performance tests where I measured just duration of each method, executing 1000 times (I ran the middle section three times, then took the average):
SET NOCOUNT ON;
GO
CREATE TABLE #stats
(
rownum INT IDENTITY(1,1),
procname SYSNAME,
dt DATETIME2
);
GO
INSERT #stats(procname,dt) SELECT '-', SYSDATETIME();
GO
EXEC dbo.while_cursor;
GO 1000
INSERT #stats(procname,dt) SELECT 'cursor', SYSDATETIME();
GO
EXEC dbo.while_xmlconcat;
GO 1000
INSERT #stats(procname,dt) SELECT 'xml concat', SYSDATETIME();
GO
EXEC dbo.while_simpleconcat;
GO 1000
INSERT #stats(procname,dt) SELECT 'simple concat', SYSDATETIME();
GO
EXEC dbo.while_colleague1;
GO 1000
INSERT #stats(procname,dt) SELECT 'colleague 1 while', SYSDATETIME();
GO
EXEC dbo.while_colleague2;
GO 1000
INSERT #stats(procname,dt) SELECT 'colleague 2 while', SYSDATETIME();
GO
EXEC dbo.while_colleague3;
GO 1000
INSERT #stats(procname,dt) SELECT 'colleague 3 while', SYSDATETIME();
GO
EXEC dbo.while_colleague4;
GO 1000
INSERT #stats(procname,dt) SELECT 'colleague 4 while', SYSDATETIME();
GO
-- using the new LAG functionality in SQL Server 2012:
SELECT
procname,
duration = DATEDIFF(MILLISECOND, LAG(dt, 1, NULL) OVER (ORDER BY rownum), dt)
FROM #stats
ORDER BY rownum;
GO
DROP TABLE #stats;
GO
|
The results (the "winner" highlighted in green):
The XML and simple concatenation approaches aside, the cursor clearly outperformed all of the while loops. This may not necessarily be true in simpler scenarios, so I'm not suggesting that a cursor will always be as good as or better than a while loop. But by the same token, I think I've demonstrated that a while loop isn't always faster or easier to write and understand than a cursor.
I would love to hear your experience, especially any examples where a while loop clearly outperforms a cursor or where it is much easier to write or comprehend.
|
-
I see a lot of people getting really excited. There is a "virtual launch event" for SQL Server 2012 being held on March 7, 2012. You can read more about the event at http://sqlserverlaunch.com/. Let me throw out a dose of reality: if you are not on a TAP or otherwise going live with a private build or release candidate, you will not be installing and deploying SQL Server 2012 on March 7th. I promise. This date will not mark a release, go-live, or general availability. These launch events are marketing-centric sessions to get you excited about the product. Will you be able to download Express editions from the Microsoft web site, and other SKUs from MSDN or your volume licensing portal, shortly after that? Sure. The next day? Almost certainly not. I actually don't remember the lag between the launch event (we'll call it a soft launch) and the RTM availability for previous releases, so I'm not going to throw out any conjecture there. But from all previous launch events by Microsoft, across various product
lines, the CD/DVD/download has never been available at the same time as
the event. Let's not forget that this virtual launch event is not the only "launch event" in March - later in the month, both SQL Connections and SQL Bits are also serving as launch events. Launch and release are similar words, but not identical.
Just wanted to keep it real, and prevent people from getting too excited or planning deployments around this launch event. It is just a marketing event, folks. Worth attending, no doubt, but you won't get magic golden tickets with a download code on that day. PS If I end up being wrong, I'll just delete this post. :-)
|
-
This one is quite subjective, and I'm sure I will face plenty of
opposition - not only because it's a preference thing and many people
are married to their preferences, but also because it violates the
strict interpretation of the standard. Personally, I'm more worried
about the former than the latter - I have no concerns whatsoever that
SQL Server will eradicate the = notation for column aliases, nor do I
worry that the code I write needs to work when ported to Oracle, DB2,
MySQL, etc. (I highly doubt this will be the biggest problem in such an
event). If these concerns are important to you, you may want to stop
reading.
To be honest, there are four different ways you can define the alias for a column in a SELECT statement - the alias being the column name the application or API sees when the result is returned from SQL Server. The four methods I've seen used are:
SELECT 1 AS x; -- #1
SELECT x = 1; -- #2
SELECT 1 'x'; -- #3
SELECT 1 x; -- #4 |
But I really want to discuss only two of these four methods.
First, I want to discard item #3 entirely. Why? Because using string literals as column aliases has been deprecated for some time now. You can see how prevalent this is in your environment with the following query:
SELECT [object_name], instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Deprecated Features%'
AND instance_name LIKE 'String literals as column aliases%'; |
I'd also like to discard #4, simply because I find it completely unreadable. Without using either = or AS, it makes it very hard to interpret if that is intended to be an alias, or if x is actually a column name and the author simply forgot a comma. I feel the same way about leaving out AS to denote a table alias, or WITH to denote a table hint (maybe I'll treat those in another post).
So that leaves #1 and #2.
I prefer #2 simply for readability reasons. Remember that even in cases where you're "the database guy," the T-SQL code you write is not consumed solely by you - it is also consumed by application developers who want to understand your query (and in a lot of cases, the priority is understanding the shape of the result set); it will also be consumed by your successors, should you move on to a different project, a different company, or worse. Let's take a look at a very simple example of a query against the Sales.SalesOrderHeader table in AdventureWorks2008R2. I've added some new columns using calculations, and you can see how different it can be for someone trying to read the code and determine what the column names are. On the left pay attention to how your eyes have to move all over the script to locate the alias names that have been provided; on the right, it is a much simpler scan down the left hand side. (Click for larger.)
Now, this all depends on whether you are already following somewhat
logical coding conventions in the first place. If your SELECT list looks
like stream of consciousness from James Joyce, which I see more often than I'd like, then whether you use AS or = isn't
going to matter at all. This is with word wrap turned on in the T-SQL editor, and is much worse with word wrap turned off.
Raise your hand if you've seen this kind of code (or have written such a mess yourself). I suggest always writing code with word wrap turned off - in T-SQL at least, there aren't
many cases where a line *needs* to go off-screen, and having word wrap
turned off will protect you from doing it inadvertently.
In SQL Server 2012, there are easier ways of determining the shape of a resultset from a stored procedure, as long as it is the *first* resultset; in this case, it doesn't matter how ugly your queries are, you never have to look at them to determine the names of columns, their data types, or the order they are returned:
SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(N'dbo.UsingAS'), NULL)
ORDER BY column_ordinal;
|
Results:
That doesn't mean you should stop paying attention to formatting once you've upgraded to SQL Server 2012; your code should still be tidy and readable, this just gives you a quick way to examine result sets from code written by less motivated people, without having to refactor the code first (which is what I often end up having to do when I am trying to troubleshoot unreadable code). If I haven't convinced you to use = for column aliases, I hope I've at least convinced you to use AS if you're currently using method #3 or #4 above.
|
-
My favorite blog post Picking favorites is never easy. While I definitely feel like I had some more thought-provoking, controversial and laborious blog posts, my favorite would have to be my summary of 18456 errors: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
When I initially wrote that post, it started as a quick listing of the new states introduced in SQL Server 2012 (involving contained databases). When I started testing various issues using both contained and non-contained databases, I quickly realized that there isn't really a comprehensive resource out there that lists the various states and helps decipher the root of the problem. There also isn't a lot of help about a more fundamental point: the error message that's relayed to the end user or application is intentionally vague. To find the real reason for the permissions issue, you need to look at the SQL Server error log.
In a more general sense, I am proud to have started getting serious again about the Connect digests. Even though most issues are way too late to consider for SQL Server 2012, I still think there is a lot of value in voting for items you think are important or worthwhile. There is even more value in expressing your business case for the change or fix - how specifically will your organization make better use of SQL Server, or in fact be able to justify a migration or an upgrade, if the issue in the Connect item is resolved?
My most popular blog post
By a wide margin, my most popular post in 2011 was actually a post I wrote in 2010. The purpose was to both express and prevent frustration in the removal of evaluation editions of SQL Server:
Fun with Software : uninstalling SQL Server 2008 R2 Evaluation Edition
This process is unnecessarily complex, particularly if the expiration date has already passed. And since there's no straightforward or documented way to determine when an evaluation edition will expire, this is kind of a double-whammy for some folks. I have two related Connect items I'd like to point out:
- #257649 : Make expiration date of eval edition more visible
Written in February of 2007, I expressed the fact that we have to use all kinds of cumbersome workarounds to determine when an evaluation edition installation will expire. My favorite is one that I learned of a lot more recently: check the earliest create date in sys.logins. But it is an ugly hack not unlike checking create_date of tempdb in sys.databases to determine system uptime. They've exposed this value in DMVs in the meantime; why not store the expiration date somewhere easily accessible?
- #329054 : Setup : Please provide a utility that removes an instance
The steps you have to go through sometimes to remove an instance of SQL Server are infuriating. In this connect item, filed in February of 2008, Microsoft initially responded that they were going to create a utility for cleaning up instances. Then they closed it as a duplicate. Then they closed it as "done." Then I re-opened it after discovering that the utility was not in the plans. Then it was closed as fixed again. Then I re-opened it. Then they explained that the utility would not be produced, but a KB article would be published instead. Three months later, they advised it was in edit. Four more months later, I inquired about the status of the KB article. They waited a few days and then explained that, like the utility, the KB article that they promised also wouldn't be published. Since they still haven't fixed a lot of the issues with removal of an instance, I guess they'd rather see folks wipe and pave a machine than take the risk of using MSIZAP and/or following my instructions in the blog post above. This is an embarrassing demonstration of how Connect fails (now, there are many examples out there of how Connect succeeds, so don't take this as an overall summary).
While I'm glad that I continue to help people get out of this sticky situation, and it's nice that so many express their appreciation, I do sincerely hope that Microsoft considers fixing this issue. Adding your votes and, more importantly, your comments, to the above issues will help show Microsoft that they should make these usability concerns a priority for the release after SQL Server 2012.
My Travels
I had some great travel experiences last year. For work, I went to England twice (I spoke at both SQL Bits 8 in Brighton, and SQL Bits 9 in Liverpool), spoke at the SQL Rally Nordic event in Sweden and on the SQL Cruise to Alaska, and spoke for the first time at the PASS Summit in Seattle. I also spoke at countless SQL Saturdays, a handful of user group meetings, and visited our offices in Charlotte multiple times. I also got to enjoy Negril, Jamaica, with my wife, for our 1st anniversary - we went to the same resort where we were married on 10/10/10. We also went to Canada multiple times. This year I have even more travel planned - next week I embark on SQL Cruise Caribbean, then in February I'll be taking a trip to Quebec City and attending the MVP Summit in Seattle. In March I'll be hosting a curling bonspiel up near North Bay, speaking at SQL Saturday #105 in Ireland, and speaking again at SQL Server Connections in Vegas (another first for me). In April, Nicole's sister is getting married on the Mayan Riviera. Sound like a busy four months? Yeah, I thought so too.
My Goals
At the end of 2010, I wrote a goals post for 2011. I did not meet all of them for various reasons, but rather than dwell on my failures (I'll be doing that quite enough on my own, thanks!), I'll treat a couple of successes: - I certainly met my speaking goals. I gave 20 presentations across 14 different events in 3.5 different countries (including a couple from my house). The .5 is Canada, since I believe I gave my SQL Cruise Alaska talk while we were floating west of Vancouver Island.
- I definitely had lulls in my blogging, but am happy with my overall results here. I also met my goal of personal blogging - I abandoned aaronbertrand.com three years ago, but started a new blog for topics non-SQL over at bertrandaaron.wordpress.com.
- I wanted to attend more sporting events. I think we did that - went to some Bruins games, and lots of Merrimack basketball and hockey games. This year we've already been to a Patriots' playoff game and have tickets to see the Bruins beat up on the Penguins in April.
I have plenty of items on my goals list for 2012, but not in a state to share just yet.
|
-
On Monday, I completely missed that the SQL Server Release Services team published two cumulative updates for SQL Server 2008. I'd blame jury duty, but that didn't start until Tuesday, and only lasted two days. Well, better late than never.
Cumulative Update #8 for SQL Server 2008 Service Pack 2
Cumulative Update #3 for SQL Server 2008 Service Pack 3
As usual, I'll post my standard disclaimer here: these updates are NOT for SQL Server 2008 R2 (where @@VERSION will report 10.50.xxxx).
P.S. this is blog post #499. You're probably not counting, but I am. :-)
|
-
First of all, I want to congratulate both Kendal van Dyke (@SQLDBA) and James Rowland-Jones (@jrowlandjones) on their recent appointment to the PASS Board of Directors. I have no doubt they will go above and beyond to fulfill their duties and represent the SQL Server community at large. Some others feel that more deserving candidates were slighted by this process, and argue that the immediate runners-up in the previous election should get called upon to fill vacated roles. You can read about them here: Steve Jones: http://voiceofthedba.wordpress.com/2012/01/13/ethics-and-power/
Andy Warren: http://www.sqlandy.com/index.php/2012/01/pass-time-to-change-the-by-laws/
Steve's main argument is that the next highest vote recipients in the recent election should automatically fill vacated slots. He contends that Sri has done a lot of work for the community,
organizing SQL Saturdays and bringing SQL Rally to Dallas. I know it is
not his intention, but stating these things, in my mind, tends to
belittle the efforts of the men the board did choose. Kendal's resume is
quite similar to Sri's - he works tirelessly for the Orlando SQL Server
user groups, speaking at every SQL Saturday there, helping plan their
most recent SQL Saturday, and starting a user group (MagicPASS). He was
also a key player in bringing the first SQL Rally to Orlando, and having attended as both a speaker and a sponsor, I know that Kendal went to great lengths in making the event as flawless as possible. And James'
effort with SQLBits should not be overlooked either - I have been to multiple events over there and I can tell you first-hand that the UK SQL community
is quite happy with his efforts on their behalf. He has also served as
an international advisor to the PASS board since early 2011, and is
involved with many community groups in addition to PASS and SQLBits. Both have been recognized as SQL Server MVPs and constantly demonstrate their commitment to the community. I don't want to take away from Sri's accomplishments, but do want to have equivalent context for the other choices as well.
Andy simply argues that, while the by-laws allow the board to vote for whomever they please, they have traditionally appointed the person with the next highest votes from the previous election -- so that's what he expected to happen this time, too. This is a fair expectation, but not a perfect one: to me it seems similar to when people get bitten by the fact that ORDER BY in a view no longer guarantees order without an explicit ORDER BY on the outer query: they're relying on observed behavior vs. what is (or what isn't) written in stone. The process that happened here is not at odds with the way the by-laws are written, it just so happens that in previous incidents the board's decision happened to coincide with the community's vote. (He also suggests that they could have changed the size of the board in order to give James a vote and still take the next two highest vote-getters to fill the vacated seats.)
The idea Steve and Andy have is not without merit, but this is not how the by-laws are currently written (Thomas LaRock explains). I also think there are time limit issues at play. If these appointments happened in March, July, or October 2012, would the 2011 election results be as relevant? If we were to rely solely on community votes, at what point would we need to hold a new vote? I'm just trying to think of how the by-laws could be explicitly written to allow for the community to control every member of the board without having to hold elections all the time.
When I tried to think of real-life (well, non-tech) examples of this, I could only come up with two off the top of my head. - President of the United States
When the President becomes unable to serve, the Vice President - someone who the President just happened to choose as their running mate - takes his/her place. Not the runner-up in the Presidential race, who quite likely does not hold the same values as the person the people elected. That doesn't mean they are not capable of the position, or that they wouldn't have done a good job, it's just not how the law is written.
- Beauty Pageant Queen
When these ladies lose their crown, the runner-up takes their place. This is more in-line with what Steve and Andy are arguing for, though it is still not the community at large who determined the queen, runner-up, and so on, it was a smaller group of judges. As with the Vice President, the runner-up may not be the city's / country's / world's favorite choice (especially considering these people can change over time), but those are the rules.
In any case, this is part of the by-laws as they are currently written. There is no magic, voodoo or unethical behavior going on. The board voted 11-1 in favor of Kendal and James, and frankly I am quite satisfied that they did so in the best interests of the organization and the community it serves. There were only two spots to fill and at least five willing and capable people to choose from. I am also thankful that the circumstances surrounding their decision is private - while I agree that there needs to be some transparency, if people were not chosen due to personal or other reasons that are not public, they should stay that way. If there were such circumstances, the board could choose to inform those who were not chosen about why they were not chosen, and leave it up to the individual to make that known to the greater community if they so choose. Does that mean the by-laws don't need to be revisited and potentially re-written, such that both initial and replacement board members are decided by the community in some way? No. I think that the stir this has caused shows that the current process is imperfect and should be considered carefully before the next election. If they do change the by-laws to make the vacancy appointments more dependent on the previous election, I hope they factor in my point about timeliness.
|
-
This month's T-SQL Tuesday is being hosted by Dave Howard (@DaveH0ward), and the topic is "Second Chances." What he's letting us do is look back at all the previous T-SQL Tuesdays and pick a topic that we missed or maybe thought we needed another crack at.
I chose a recent topic that I missed due to travel, "Data Presentation," hosted by Robert Pearl in September 2011 (T-SQL Tuesday #22).
At my previous job, one of the most complicated pieces of presentation work we had to do was to provide users with data in their preferred time zone. We separated data by customer in separate databases, which would have made things relatively easy, if only all the users of each customer were in the same time zone. In that case, we could have just stored all the customer's data in that time zone.
But of course, customers had offices spread throughout the country and in fact the world, so we had to implement a way to show the same data, translated to local time, for users in different time zones. A complication to the requirement was that, based on the user's login to the web application, we needed to show the data in their preferred time zone even if they were using a computer in a different office - so we couldn't rely on the web browser's preferences or local machine's regional settings to determine the time zone. And in addition to these requirements, we also had to know the actual UTC offset for given dates, both in the past and in the future, since several of the supported time zones would observe daylight saving time.
In order to fulfill these requirements, we decided to implement the logic within the database. This way the application wouldn't have to know anything except who the user was, and the logic would work the same way regardless of which application was requesting the data (we also had several applications that weren't tied to the web application, that wouldn't always know the user, so each customer had a default to fall back on). The servers in the data center were all set to GMT and to *not* observe daylight savings time. This is actually on my informal best practices list because it prevents ever having to worry about data collisions or gaps twice a year.
So here is a skeleton of the schema and minimal data to demonstrate the functionality. First I'll let you create a dummy database to target:
USE [master];
GO
IF DB_ID('TSQLTuesdayAaron') IS NOT NULL
BEGIN
DROP DATABASE TSQLTuesdayAaron;
END
GO
CREATE DATABASE TSQLTuesdayAaron;
GO
USE TSQLTuesdayAaron;
GO
|
Now we need tables for TimeZones and UTCOffsets:
CREATE TABLE dbo.TimeZones
(
TimeZoneID TINYINT PRIMARY KEY,
Name VARCHAR(32) NOT NULL UNIQUE
);
GO
INSERT dbo.TimeZones(TimeZoneID, Name)
VALUES
(1,'Eastern US Time'),
(2,'Greenwich Mean Time');
CREATE TABLE dbo.UTCOffsets
(
TimeZoneID TINYINT NOT NULL,
[Date] SMALLDATETIME NOT NULL,
Offset SMALLINT NOT NULL,
PRIMARY KEY CLUSTERED(TimeZoneID, [Date])
);
GO
ALTER TABLE [dbo].[UTCOffSets] ADD CONSTRAINT [FK_utc_TZ]
FOREIGN KEY([TimeZoneID]) REFERENCES [dbo].[TimeZones] ([TimeZoneID]);
|
For brevity, I'm just going to populate the transition days, e.g. when daylight savings time changes. In actuality, the table is truly a calendar table; each date from 2005-01-01 through 2030-12-31 is represented, once for each time zone:
INSERT dbo.UTCOffsets(TimeZoneID, [Date], Offset)
VALUES
(1,'2011-03-12',-5),
(1,'2011-03-13',-4),
(1,'2011-11-05',-4),
(1,'2011-11-06',-5),
(1,'2012-03-10',-5),
(1,'2012-03-11',-4),
(1,'2012-11-03',-4),
(1,'2012-11-04',-5),
(2,'2011-03-26', 0),
(2,'2011-03-27', 1),
(2,'2011-10-29', 1),
(2,'2011-10-30', 0),
(2,'2012-03-24', 0),
(2,'2012-03-25', 1),
(2,'2012-10-27', 1),
(2,'2012-10-28', 0);
|
Now of course we need some customers and users:
CREATE TABLE dbo.Customers
(
CustomerID INT PRIMARY KEY,
Name NVARCHAR(32) NOT NULL UNIQUE,
TimeZoneID TINYINT NOT NULL
);
GO
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [FK_c_TZ]
FOREIGN KEY([TimeZoneID]) REFERENCES [dbo].[TimeZones] ([TimeZoneID]);
GO
INSERT dbo.Customers(CustomerID, Name, TimeZoneID)
VALUES
(1, N'Teks', 1),
(2, N'MSDN', 2);
GO
CREATE TABLE dbo.Users
(
UserID INT PRIMARY KEY,
Email VARCHAR(320) NOT NULL UNIQUE,
CustomerID INT NOT NULL,
TimeZoneID TINYINT NOT NULL
);
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_u_TZ]
FOREIGN KEY([TimeZoneID]) REFERENCES [dbo].[TimeZones] ([TimeZoneID]);
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_u_Cust]
FOREIGN KEY([CustomerID]) REFERENCES [dbo].[Customers] ([CustomerID]);
GO
INSERT dbo.Users(UserID, Email, CustomerID, TimeZoneID)
VALUES
(1, 'foo@bar.com', 1, 1),
(2, 'bar@foo.com', 1, 2),
(3, 'foo@bar.net', 2, 1);
GO
|
And then functions to determine the offset for a given day and user or customer:
CREATE FUNCTION [dbo].[GetUTCOffSet_ByUserID]
(
@UserID INT,
@Date SMALLDATETIME
)
RETURNS SMALLINT
AS
BEGIN
RETURN
(
SELECT utc.Offset
FROM dbo.UTCOffsets AS utc
INNER JOIN dbo.Users AS u
ON utc.TimeZoneID = u.TimeZoneID
WHERE utc.[Date] = DATEDIFF(DAY, 0, @Date)
AND u.UserID = @UserID
);
END
GO
CREATE FUNCTION [dbo].[GetUTCOffSet_ByCustomerID]
(
@CustomerID INT,
@Date SMALLDATETIME
)
RETURNS SMALLINT
AS
BEGIN
RETURN
(
SELECT utc.Offset
FROM dbo.UTCOffsets AS utc
INNER JOIN dbo.Customers AS c
ON utc.TimeZoneID = c.TimeZoneID
WHERE utc.[Date] = DATEDIFF(DAY, 0, @Date)
AND c.CustomerID = @CustomerID
);
END
GO
|
So now, given a date/time value and a user or company, we could determine the offset that should be used, depending on the known CustomerID or UserID:
SELECT dbo.GetUTCOffset_ByCustomerID(1, '20111106');
SELECT dbo.GetUTCOffset_ByCustomerID(2, '20111029');
SELECT dbo.GetUTCOffset_ByUserID(1, '20111106');
SELECT dbo.GetUTCOffset_ByUserID(2, '20111029');
SELECT dbo.GetUTCOffset_ByUserID(3, '20111106') | Once a user is logged in, we could simply store their timezone in a session variable, so we wouldn't have to retrieve that value more than once. For any date we needed to display, we could just pass in the date and the timezone to the following function:
CREATE FUNCTION [dbo].[GetPresentationDateTime]
(
@TimeZoneID TINYINT,
@DateTime SMALLDATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN
(
SELECT DATEADD(HOUR, @Offset, @DateTime);
);
END
GO
SELECT dbo.GetPresentationDateTime(1, '20111106 04:32');
SELECT dbo.GetPresentationDateTime(2, '20111029 04:32');
|
We could have done all of the above in a single, overloaded function or procedure, but decided it would be better to make the access methods much more defined. You also might suggest DATETIMEOFFSET, but this solution was initially implemented in SQL Server 2005, long before the new date/time data types were introduced. Besides, I'm not sure how it would have helped, since most values were input without any TZ awareness (e.g. GETUTCDATE()), relying on the system clock to store UTC date/time correctly. Most data did not need to be timezone aware until presented to the user, and since it could be different for every user, the value of storing one user's TZ information with the data was pretty weak. The above didn't really help if we wanted to pull a report with lots of datetime values - we would have to call the scalar function for every row (and sometimes for multiple values) - in those cases we just wrote out a join the long way (if we didn't already know the offset) or used DATEADD inline when we did. Now of course, you're probably thinking this isn't isn't quite complete. And you'd be right. There's that funky time we haven't accounted for - the first couple of hours in each DST-observing time zone on the forward/back day, before the change is actually supposed to take place. We opted to defer that since most events happen during business hours, and the issue never came up with any of our customers. Certainly a concern you will want to take into account if you're thinking about this type of solution. And in the spirit of keeping with the topic, if I were to be presented with this same challenge today, I would probably use it as a second chance to get the job done a little better, rather than take the easy "I-solved-this-before" route.
|
-
Hide databases from users who shouldn't be able to see them
This is a long-standing request from Erland Sommarskog which I've highlighted in previous digests. But the underlying problem keeps coming up in multiple venues, so I thought it would be good to call attention to the item one more time. Some will argue that the contained database feature provides a solution for this, but that only works well if you want to restrict a user to exactly one database, and only works well if your application is compatible with the limitations of the feature. Please comment on the item and explain how this feature will help you in your environment.
#273830 : Need VIEW DEFINITION permissions per database
Contained Database users are people too
In playing with the contained database feature as a solution to Erland's concern above, I discovered an unfortunate bug: a database-level user (with password) who has connected to their contained database using SSMS will not enjoy most of the important IntelliSense features. I'm highlighting this Connect item not so that you can vote for it, but rather just to be sure you're aware of this limitation if you intend to utilize contained databases in the short term. As an side effect, I also discovered that there doesn't exist a straightforward way to set up a contained user that can bypass the password policy in place, unlike server-level logins (where you can say CHECK_POLICY = OFF). Personally I think they got this backwards - logins are the security entity where you want to make it harder to implement simple passwords. If you want a contained user with a simple password, you can create a server-level login, associate it with a database user, and then use sp_migrate_user_to_contained (note that I haven't tried this).
#717063 : SSMS : IntelliSense does not function for a contained user
#717069 : Contained User syntax does not support bypassing password policy
Please just go parallel, regardless of other factors
Paul White (@SQL_Kiwi) has asked for an option that is kind of the opposite of MAXDOP. I say "kind of" because he doesn't want to be able to say MINDOP x, but rather try to coerce the optimizer to use a parallel plan and then follow the same rules it normally would in determining the level of parallelism.
#714968 : Provide a hint to force generation of a parallel plan
Expose SHOW_STATISTICS through a DMV
Greg Low has proposed adding a DMV that would mirror DBCC
SHOW_STATISTICS output, making it easier to work with the results. I'm
all for this, as it can be quite a hassle to mix monitoring queries with
DBCC calls.
#611155 : DBCC SHOW_STATISTICS info should be available as a DMV
Check constraints during CHECKDB
Thanks to Ola Hallengren, they are considering adding the ability to
check all constraints (and, where appropriate, mark them as trusted) as a
part of the DBCC CHECKDB process (specifically, using the
EXTENDED_LOGICAL_CHECKS option). There are already plenty of votes, but
more votes (and, more importantly, comments about how this will help in
your environment) will help.
#508837 : Option to check constraints in DBCC CHECKDB
|
-
I’ve seen the question a few times now: “How do I compare two plans side-by-side in Plan Explorer?” Management Studio allows you to view multiple graphical plans that are visible simultaneously, either by generating plans for every statement in a batch, or by generating plans in separate query windows and then splitting the panes. However, at least in my estimation, a lot of the other metrics and properties are more valuable when performing comparisons. In SSMS this is tough, because you can only have one tooltip visible at a time (regardless of how many plans are visible). Same for the Properties panel – only a single plan or operator can populate this panel at one time. So even though you can compare high-level graphical details quickly, you will likely end up with multiple instances of SSMS to perform true side-by-side comparisons of the lower-level details.
In Plan Explorer (free download) it is very easy to compare a lot of the metrics side-by-side for two (or more) plans. Since Plan Explorer can generate estimated or actual plans for you, it’s as simple as entering multiple queries in the Command Text pane, and then generating an estimated or actual plan:
I recommend trying the Vertical Layout, so that the various plan-related tabs and the Statements Tree are a lot closer together (reducing mouse and eye movement):
Here is a quick comparison of the two default options (vertical on the left, horizontal on the right – click to enlarge):
(There is also a Custom Layout option if you wish to change from the default options.)
And you can still compare a lot of the other details about two different statements by looking at the other columns in the Statements Tree tab. For example, comparing costs, row counts and operation counts between estimated or actual plans can give you a much more accurate picture of the performance impact of different statements:
And you can switch between graphical plans quickly by using arrow keys or mouse clicks to switch between statements:
Since the other tabs are context sensitive, you can do this for any tab: simply highlight a tab (e.g. Top Operations) and, as you switch through the rows in the Statements Tree, the tab below will update accordingly.
For more complex plans, and depending on how much screen real estate you have, you may prefer to open two instances of Plan Explorer so that you can interact with the plans a little more independently. In this case you won’t be able to see percentages or other relative comparisons directly against one another, but for most eyeballing, it’s still better than what you get in Management Studio IMHO.
That all said, comparing two graphical plans side by side is definitely on the list for a future update; in the meantime, I hope the above is helpful.
|
-
Make SSMS start faster
A couple of years ago, I blogged about some ways to make Management Studio start faster.
With the latest builds of SQL Server 2012, it seems to be slower than
ever, at least when starting SSMS for the first time after a reboot or
install. So I've asked for them to do something in the background on
Windows start-up to cover whatever initialization costs have to be paid
on first launch. This is not something I expect to happen in this release, and perhaps it will be better by the time RTM comes around. But if you find it slow as well, in addition to voting, please post - in the comments on the Connect item - your machine config and how long it takes between clicking on the shortcut and being able to work. With enough stats in there they can probably extrapolate how much time is being lost waiting for the application to load. :-)
#714652 : SSMS : Background helper / pre-loader
Let me opt out of product updates during setup
I am a big fan of streamlining slipstreaming (say that 5 times fast) - in other words, letting SQL Server setup check for product updates *before* installation, rather than the current manual process of cobbling together a setup package with a service pack and/or a cumulative update. In SQL Server 2012 we finally have this. Unfortunately, it is currently not an option; so, if your machine is not connected to the Internet for whatever reason, it hangs indefinitely on that step, trying desperately to go find those updated files. This needs to be an option so that you can proceed, even if you want to install RTM (e.g. for testing purposes) or you can't get to the Internet (now or ever). Sadly most software companies think that high speed Internet is enjoyed by all, when in fact a lot of folks can't get it or intentionally block their servers from any kind of external access.
#714661 : Scan for Product Updates hangs on isolated system
Add a time limit option to index reorg
Greg Low had an interesting idea to add a time limit to index reorg. Since the most work you'll ever lose upon cancelling is the reorganization of a single page, unlike a rebuild which has to roll back ALL of the work, it is a common practice to do a little bit of reorg at a time. But it is quite tedious to set up some kind of watchdog or to wait and kill the process manually. I can see how the syntax could work - a simple TIME_LIMIT option with the same input format as WAITFOR DELAY. So, for example, if you wanted to allow this reorg to work for up to 45 minutes and then stop:
ALTER INDEX foo
ON dbo.bar
REORGANIZE WITH (TIME_LIMIT = '00:45:00');
|
The Connect item is currently closed as won't fix, and I think that's partially because it's had only 2 votes in over a year. Please vote and add a comment indicating why this could be useful in your environment: #611158 : Index Reorganize time limit
Gosh, I wish I could re-use that CTE
Several folks have filed suggestions to make it easier to work with the same query multiple times. Two of the popular ideas are temporary views and module-level table expressions. The current workaround of dumping data into a #temp table has significant overhead, and can still lead to errors if the same joins and where clauses need to be repeated over and over again. I like Erland Sommarskog's module-level table expressions idea best, but temporary views have a strong use case as well. I'm hoping to see some more comments and use cases on these items so that Microsoft can evaluate
#343067 : Module-level table expressions #640863 : Please allow creation of temporary views
#714617 : [T-SQL] : Please provide temporary views
Truncate a table with foreign keys
We all know that you can't truncate a table that has foreign keys, but why should that be the case if all the referencing tables are empty (or have nullable referencing columns that are all NULL)? They've closed this one as won't fix, but if you can see the value in this for your environment, please add a comment indicating your use case, as they seem more than willing to revisit it... #312074 : Permit TRUNCATE TABLE when referencing tables are empty
|
-
Microsoft has just released cumulative updates for both SQL Server 2008 R2 branches - RTM and Service Pack 1.
SQL Server 2008 R2 Service Pack 1 Cumulative Update #4
SQL Server 2008 R2 RTM Cumulative Update #11
As usual, I'll post my standard disclaimer here: these updates are NOT for SQL Server 2008 (where @@VERSION will report 10.00.xxxx).
|
-
This month's T-SQL Tuesday is being hosted by Allen White (@SQLRunr) and is about sharing your T-SQL tips and tricks. Since I know many people will share their T-SQL magic and wizardry, I thought I would share a couple of productivity tips - so still T-SQL related, but more how to take advantage of some features in SSMS.
Drag column list onto your query window
Whenever I give my Bad Habits to Kick presentation, I remind people that - even if they're not using 3rd party code completion tools (many of which offer SELECT * expansion) - there's little excuse to use SELECT *. This is because Management Studio makes it very easy to list out all of your columns in one motion. I'm always amazed when I ask how many people didn't already know about this - and most times it's more than half the room.
The process is quite simple - expand your table, click on the Columns node, drag it onto your query window and let go: Now, it's probably not in the exact format you want, it won't properly delimit any [keyword] or [reserved word] you use for a column name, and you'll have to do this multiple times when you're performing a join (and you'll have to add your own table/alias prefixes to each column as well). But I'd rather spend time using TAB and ENTER to correct formatting than to try and type out all the column names individually.
Use snippets
If you find yourself writing the same type of code over and over again, the new snippets feature in SQL Server 2012 will likely be very useful for you. What does this have over templates? For one, I find it much easier to use once you have your snippets created. There is no hokey VB6-style dialog to perform token replacements, and you can actually store all your custom snippets in a network location - so if you want to enforce coding standards you have a fighting chance by pointing everyone's SSMS at the same snippet location. I'll be honest, though, setting up the template is a bit of a pain - you need to hand-massage XML manually because as of yet there is no easy way to generate these from within Management Studio. But once they're in place, they're very easy to work with. Just right-click within the query window, choose "Insert Snippet", and follow the rabbit: 
One of the pieces of code I write a lot for administrative tasks (or for helping folks out in the wild) is a cursor. I tend to have a hard time memorizing the options I always try to use to make a cursor as efficient as possible (something else I cover in my Bad Habits to Kick presentation). The snippet eliminates this problem. Once I've selected the "Declare Cursor" snippet I've saved, I get the following in my query window:  You'll notice in the diagram that "c" is currently highlighted and the cursor is there; all other instances of "c" are lightly outlined. I can type any name I want to overwrite "c" and, once I hit tab (which moves me to the next token), it will replace all instances of that token with the new name I've typed. So this is a little easier to work with than the floating window in templates, though you lose this easy editing capability if you start doing other things with the script first (need to remind myself to file a bug or suggestion about that). Changing "c" to "x" throughout this code sample took two keystrokes, and I'm ready to add the actual query:
I plan to write a much more thorough blog post on the snippets feature, because I think it will become a pretty popular time saver once the latest version of Management Studio becomes more common on the desktop. In the meantime, if you're already playing with SQL Server 2012 RC0, I highly recommend you check this feature out! Avoid dynamic SQL in generated scripts I see a lot of people complain that when they script objects from Management Studio the object text ends up in dynamic SQL. I don't blame them - this makes it very cumbersome to read, never mind modify, the object text before copying, running or saving the script. In SQL Server 2008 R2, this is controlled by the option Tools > Options > SQL Server Object Explorer> Scripting > Include IF NOT EXISTS clause: In SQL Server 2012, the option has changed slightly to Tools > Options > SQL Server Object Explorer > Scripting > Check for Object Existence:
Why did it change? Because, depending on the action, it actually performs an IF EXISTS check (e.g. for a DROP or ALTER), not always an IF NOT EXISTS check (for a CREATE). Some background in the following Connect items:
(As an aside, I think it's funny that they call the options category "SQL Server Object Explorer" - good thing they're specific, because you might get it confused with the Lego Object Explorer, Oracle Object Explorer or Windows Explorer Object Explorer.) In any case, when these options are set to True, the scripts you generate often yield dynamic SQL, and even when they're set to false they still sometimes perform the check for existence (specifically in 2008 R2). Here is a brief wireframe of the options and how they affect each scripting method:  Note that with the "DROP and CREATE" option, the existence check for the CREATE is unnecessary - after all, if the object already exists, the script should have just dropped the object (though both checks may have failed if there is an object with the same name that is *not* a procedure). In general, most people won't want to perform a DROP and CREATE anyway - they lose dependencies and permissions when they do this (though permissions can be scripted with a different option in the same category). Until they develop "CREATE or REPLACE" functionality (please vote and comment!), ideally you should have a script that creates a stub for the object only if it doesn't already exist, then runs an ALTER - then you don't have to re-apply permissions, you don't have to worry about whether the object already exists, and as an added bonus you retain dependency chains. So your scripts can look like this (still using dynamic SQL, but it's very simple and doesn't present parsing or reading issues):
IF OBJECT_ID('dbo.proc_name') IS NULL
BEGIN
EXEC sp_executesql N'CREATE PROCEDURE dbo.proc_name AS SELECT 1;';
END
GO
ALTER PROCEDURE dbo.proc_name
AS
... -- actual script body from source control or elsewhere goes here |
Unfortunately there is no way to train SSMS to do this today, unless you write a scripting add-in.
Wrapping up
I hope that at least one of these little tricks helps you in some way. It's always fun participating in T-SQL Tuesday, even if I'm often trying to bend the rules a little bit.
|
-
Where is my graphical ShowPlan?
Many of you on SQL Server 2008 R2 are experiencing a major annoyance with SSMS since installing service pack 1 - when clicking a plan from grid results, you get raw XML instead of the graphical plan. I wrote about this symptom and a workaround in a previous post. In the original Connect item, they state that it will be fixed (likely in the next CU). But @AdamMachanic points out that there are some cases that still aren't correctly covered - they have marked this as "By Design" but I think it needs more input that all valid plans should be able to show valid graphical plans. This could use some more votes and, more importantly, some comments urging Microsoft that if SQL Server can produce a plan, Management Studio should be able to present it graphically. After all, these "invalid" plans work fine if you save them as .sqlplan files and then open them manually - it is something different about the path SSMS takes when evaluating the XML. So please vote and comment!
#652855 : Valid showplan XML fails to automatically produce graphical showplan
When will my evaluation edition expire?
I'm seeing more and more people thank me for my instructions on how to remove an expired evaluation edition. I also wrote a warning post when the first SQL Server 2012 CTP would start expiring for people. I think it's more important than Microsoft is willing to admit to make it more obvious that an evaluation edition is about to expire - so that people have a chance to prevent this hassle. Hell, it might even lead to an earlier sale. Since quality > quantity at Connect, please tell Microsoft why you think this is a good idea!
#257649 : Make expiration date of eval edition more visible
What database am I in?
When you use the "SELECT TOP n ROWS" feature in Management Studio, it creates a new query editor window with a query like "SELECT TOP (1000) * FROM [db].[dbo].[table]" - no semi-colon, no order by, and no USE statement. The query comes up in the master database (or perhaps your default database, I haven't tested this thoroughly). This means if you want to modify the query, you either need to manually switch to the correct database, or you need to keep adding the database prefix to each object. In this Connect item I point out the issue with arbitrary order, and the fact that the code is similar but not identical to the Script > Table As > SELECT options, but most importantly urge them to make the database context correct. They've deferred it for now, but may reconsider if they get more pushback.
#684247 : SSMS : SELECT TOP n ROWS should have correct database context
Why is SSMS so slow?
I've complained in the past about right-clicking a table hanging SSMS, eventually did experience the hang caused by hovering over the query line in an execution plan, have been very frustrated by query window behaviors when a database connection has been temporarily lost, and @PaulRandal has complained about delays when a network location is no longer available. Recently Erland Sommarskog filed an issue demonstrating that in SQL Server 2012 RC0, SSMS has some new chatty behaviors that can be quite detrimental on a slow connection:
#708296 : SSMS 2012 generates stray queries on single keypress
Why doesn't my CASE expression short circuit?
While CASE usually evaluates its clauses in left-to-right order, this behavior is not guaranteed. I demonstrated this and pleaded for an update to Books Online to make it clear to users that short-circuiting cannot be relied upon. I'm hoping you will add a comment to reinforce that the current documentation doesn't tell the whole story and in fact may be misleading users into believing that the behavior they see in one expression will remain true for all expressions...
#690017 : CASE / COALESCE won't always evaluate in textual order
|
-
Very early this morning, Microsoft released two new cumulative updates for the SQL Server 2008 platform.
Cumulative Update #7 for SQL Server 2008 Service Pack 2
Cumulative Update #2 for SQL Server 2008 Service Pack 3
Note: These updates are for SQL Server 2008, *NOT* for SQL Server 2008 R2. Check SELECT @@VERSION; if it shows 10.50.xxxx and not 10.00.xxxx, you're in the wrong place. :-)
|
-
Books Online articles referencing built-in CLR functions (such as FORMAT()) have a remark similar to the following:
|
"FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR)." |
A lot of people seem to interpret this as meaning:
|
"You must enable the sp_configure option 'CLR enabled' in order to use FORMAT()." |
Some then go on and suggest you run code similar to the following before you play with these functions:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'clr enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO |
The truth is, you do *not* need to enable CLR using sp_configure in order to use built-in CLR functions like FORMAT() or PARSE(), or .NET-based types like hierarchyid and geography / geometry. The functions will fail, however, if you install SQL Server and then later manage to disable or uninstall core components such as the .NET Framework (you can't install SQL Server without it, but I suppose you could try to uninstall it later).
The sp_configure setting merely relates to user-defined CLR objects that you might create and deploy yourself. Go ahead and try the following code on a test instance of SQL Server 2012 CTP3 or RC0:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'clr enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
DECLARE @d DATETIME = '20120505';
SELECT d = FORMAT(@d, 'yyyy/MMM');
GO
|
Results:
(Of course if you already had CLR enabled, you'll want to re-run the first code sample above to turn it back on.)
So, long story short, only open up the CLR surface area via sp_configure if you have to. And if you just want to take advantage of built-in CLR functionality, you don't have to.
|
|
|
|
|
|