tag:blogger.com,1999:blog-15371713704454120502024-02-18T17:46:50.859-08:00SQL DabblerCelebrating those small victories with Microsoft SQL Server.Wayne Stubbshttp://www.blogger.com/profile/17584509159989812270noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-1537171370445412050.post-78979861358161270162012-03-26T13:26:00.000-07:002012-03-26T13:26:57.250-07:00Generating a Comma Separated List with an Implicit RecursionAs Dabblers, we love our data structured, ordered and indexed, but occasionally this runs up against the requirements of the data's consumers. Developers who access our Microsoft SQL Servers will insist on working with comma separated lists, chopping up and concatenating data in their own, substandard, non-relational way. In order to avoid dark mutterings, and to persuade them to continue to make us coffee, it is often useful to indulge them.<br />
<br />
Returning nice structured recordsets as ugly comma (or tab, or bar) separated lists can seem onerous, and seems like a good candidate for a cursor. As usual, cursors are the last things you need. Instead, you can use a neat little trick that creates, in effect, a recursive structure that iterates through a recordset, by referencing a field within that recordset as a variable.<br />
<br />
Here's some code:<br />
<br />
Firstly, let's create some data<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>CREATE TABLE CSLTest (ID int, vval varchar(20))</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>GO</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>INSERT INTO CSLTest VALUES(1, 'abc'),(2, 'def'), (3, 'ghi')</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>GO</b></span><br />
<br />
And here's the (rough and ready) code to get the comma separated list:<br />
<br />
<br />
<b><span style="font-family: 'Courier New', Courier, monospace;">DECLARE @ CSLString varchar(5000)</span></b><br />
<b><span style="font-family: 'Courier New', Courier, monospace;">SELECT @ CSLString = isnull(@ CSLString + ', ','') + vval</span></b><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>FROM</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>(SELECT vval from CSLTest) A</b></span><br />
<b><span style="font-family: 'Courier New', Courier, monospace;">SELECT @CSLString </span></b><br />
<br />
Let's walk through this, Dabblers. There are two key elements here, both of them SELECT statements. The inner select retrieves the main recordset that we will be working with. Crucially, however, it wraps it in parentheses, and gives it an alias. Any use of Microsoft SQL Server will be familiar with aliasing, but some may not realise that it allows us to treat the aliased data as a single lump, even if it contains multiple rows.<br />
<br />
The top level select is an example of us doing exactly this. By referencing vval in the aliased data, we iterate through each record, and append it to the existing string (the isnull() is required to cope with the very first iteration, so that we don't add a comma at the beginning).<br />
<br />
With a little bit of tweaking, we can put this functionality inside a User Defined Function. This allows us to return the CSL from a function. Of course, most dabblers will be familiar with the Table Value functions available in newer versions of Microsoft SQL Server, but most developers have yet to see the light...<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>CREATE FUNCTION funReturnCSL</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>(@MinID int, @MaxID int)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>RETURNS varchar(5000)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>AS BEGIN</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> DECLARE @CSLString varchar(5000)</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> SELECT @CSLString = ISNULL(@CSLString + ', ','') + vval</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> FROM</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> (SELECT vval FROM CSLTest WHERE ID BETWEEN @MinID AND @MaxID) A</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> RETURN @CSLString</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b> END</b></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>GO</b></span><br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><b>SELECT dbo.funReturnCSL(2,3)</b></span>Wayne Stubbshttp://www.blogger.com/profile/17584509159989812270noreply@blogger.com0tag:blogger.com,1999:blog-1537171370445412050.post-58242331765185708372012-03-13T08:36:00.001-07:002012-03-13T08:36:21.919-07:00The good RESEEDIdentity fields in Microsoft SQL Server are a great tool for bringing order to chaos, providing a nice, predictable key to data that doesn't necessarily lend itself to a natural keys. The problem is that, like taxis at a rank, you always have to take the next one that comes along. This can be a problem for SQL Dabblers who want to bump the identity field up to a particular value. Maybe you want all records created after a certain date to start at a particular nice round value, for instance. Or you want to reserve particular values for testing or reference data.<br />
<br />
Through the endless power of DBCC, you can do this. The syntax is<br />
<br />
DBCC CHECKIDENT ([yourtablename], reseed, [new value you want to start at])<br />
<br />
(incidentally, Dabblers who are not familiar with DBCC should take some time to look into it. It's exceptionally powerful, and rather dangerous - essentially the equivalent of opening SQL Server's casing, and hitting the innards with a spanner.)<br />
<br />
So, that's all you need to know, right?<br />
<div style="text-align: center;"><br />
</div><div style="text-align: center;"><span style="font-size: x-large;">STOP!</span></div><br />
As I mentioned, DBCC is powerful and dangerous, and the reseed operation is no exception. Let's dabble in some code, and see what happens.<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">create table identitytest (ID int identity, vvalue int)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">go</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into identitytest (vvalue) VALUES (101),(102),(103)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">select * from identitytest</span><br />
<br />
The results here are what we expect. The identity field starts at 1, and increments each time by 1, which is the default.<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">DBCC CHECKIDENT (identitytest, reseed, 10)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into identitytest (vvalue) VALUES (104) </span><br />
<br />
we've reset the identity value to 10, so the inserted record should have an ID of 10, right?<br />
<br />
<div style="text-align: center;"><span style="font-size: x-large;">D'OH</span></div><span style="font-family: 'Courier New', Courier, monospace;">select * from identitytest</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0LEkLYCwg7yuckiogjKuBy0lEoMWSzuquPfn3XU_AySrG-2CnI19MFGZYI5s2ML8MOA60SReJJGb5n03ce5kKBRXpTpuWhyyt0ryCOAsz38W3o17vtDT6-Td9e7y8i4ZUNaldqCV_jNn_/s1600/results1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0LEkLYCwg7yuckiogjKuBy0lEoMWSzuquPfn3XU_AySrG-2CnI19MFGZYI5s2ML8MOA60SReJJGb5n03ce5kKBRXpTpuWhyyt0ryCOAsz38W3o17vtDT6-Td9e7y8i4ZUNaldqCV_jNn_/s1600/results1.jpg" /></a></div><br />
11, eh? So the thing to take away here is that after a reseed, the next inserted record gets an identity value of the reseed value + 1. That's why we get 11 rather than 10.<br />
<br />
<div style="text-align: center;"><span style="font-size: x-large;">BUT</span></div><br />
This is only true for tables that have, or have had, records in them. For a new 'virgin' table, the reseed behaves differently<br />
<br />
<br />
<div style="text-align: left;"><span style="font-family: 'Courier New', Courier, monospace;">create table identitytest2 (ID int identity, vvalue int)</span></div><div style="text-align: left;"><span style="font-family: 'Courier New', Courier, monospace;">DBCC CHECKIDENT (identitytest2, reseed, 10)</span></div><div style="text-align: left;"><span style="font-family: 'Courier New', Courier, monospace;">insert into identitytest2 (vvalue) VALUES (100)</span></div><div style="text-align: left;"><span style="font-family: 'Courier New', Courier, monospace;">select * from identitytest2</span></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtF7rurnjEK5G94Tnj0GZBW3sXmvedfwemzjMl4fBnyI7sYVRcuJx4H5E_LsNdgJpcJXxJV2YvHoBJzPhe8U-mWBhLf6uKoyjDnV2WWs4axcqhENqDTI_GN8gDXhfcUoZH86Qxr-g7KvuT/s1600/results2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtF7rurnjEK5G94Tnj0GZBW3sXmvedfwemzjMl4fBnyI7sYVRcuJx4H5E_LsNdgJpcJXxJV2YvHoBJzPhe8U-mWBhLf6uKoyjDnV2WWs4axcqhENqDTI_GN8gDXhfcUoZH86Qxr-g7KvuT/s1600/results2.jpg" /></a></div><br />
<br />
So, that only works with a brand new table, right?<br />
<br />
<div style="text-align: center;"><span style="font-size: x-large;">NO!</span></div><br />
Yeah, we're not done yet, dabblers. Using TRUNCATE on a table causes SQL Server to view it as a 'new' table for the purposes of reseeding identity values.<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">truncate table identitytest2</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">DBCC CHECKIDENT (identitytest2, reseed, 20)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into identitytest2 (vvalue) VALUES (200)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">select * from identitytest2</span><br />
<br />
And you can see that we get 20, rather than 21.<br />
<br />
So, to summarize,<br />
<br />
DBCC CHECKIDENT ([tablename], reseed, x) means that the next row inserted will have an identity value of x+1 EXCEPT if the table has never had any rows in it EXCEPT if you truncate it first. Have fun dabblers.<br />
<br />
<div style="text-align: center;"><span style="font-size: x-large;">BUT HANG ON...</span></div><br />
If you're following along on Microsoft SQL Server 2000 (<a href="http://www.sqlskills.com/BLOGS/PAUL/post/You-guys-still-use-SQL-Server-2000-Really.aspx" target="_blank">no shame in that</a>) then you're probably scratching your head right now (actually, you're probably calling me rude names, but I like to think well of my Dabblers). This is because all the above is true only on later versions of SQL Server. On 2000, reseeding will always give the next record the identity value of whatever you reseed as.<br />
<br />
I've no idea what happens for those still using versions before 2000. You guys really are weirdos :-D.Wayne Stubbshttp://www.blogger.com/profile/17584509159989812270noreply@blogger.com0tag:blogger.com,1999:blog-1537171370445412050.post-15194343567416910362012-02-25T14:49:00.000-08:002012-02-25T14:49:58.006-08:00Any equivalent Sunday...Welcome, Dabblers, and let me begin with a small gift for you. Our time is measured in a series of overlapping cycles - 60 minutes, 24 hours, 7 days, months that vary from 28 to 31 days in a year of 365 give or take. Thus, handing dates can be a royal pain. Apparently we have the ancient Egyptians to blame for this mess, but as they are no longer around to sue, we just have to fight our way through.<br />
<br />
I recently had a requirement to generate a report comparing a set of figures to the nearest equivalent day on the previous year. For example, the business wanted to compare figures for a two Thursdays, so pick a Thursday this year (say 23-02-2012) and find the nearest Thursday to the same date on the previous year (i.e. 24-02-2012). This function returns you the date of that 'nearest equivalent day'.<br />
<br />
<blockquote class="tr_bq">create FUNCTION [dbo].[funGetSameDayLastYear] <br />
<br />
( <br />
@inputdate date, -- the date which you wish to compare <br />
<div><br />
@yearoffset int = -1 -- the offset in years, e.g. -1 is last year, 3 is 3 years ahead etc. </div> ) <br />
RETURNS smalldatetime <br />
<br />
AS <br />
<br />
BEGIN <br />
<br />
declare @inputDateDayOfWeek int <br />
declare @equivalentDateOnOffsetYear date <br />
declare @equivalentWeekdayOnOffsetYear int <br />
declare @dayOffset int <br />
declare @dateAdjustment int <br />
declare @adjustedDay smalldatetime <br />
<br />
SET @inputDateDayOfWeek = datepart(dw,@inputdate) <br />
SET @equivalentDateOnOffsetYear = DATEADD(YY,@yearoffset,@inputdate)<br />
<div class="gmail_quote"> <br />
SET @equivalentWeekdayOnOffsetYear = DATEPART(dw,@<wbr></wbr>equivalentDateOnOffsetYear) <br />
SET @dayOffset = @inputDateDayOfWeek - @<wbr></wbr>equivalentWeekdayOnOffsetYear <br />
<br />
-- there is probably formula for this</div><div class="gmail_quote"> -- but it escapes me</div><div class="gmail_quote"><br />
SELECT @dateAdjustment = <br />
CASE <br />
WHEN @dayOffset = 0 THEN 0 <br />
WHEN @dayOffset IN (1,-6) THEN 1 <br />
WHEN @dayOffset IN (2,-5) THEN 2 <br />
WHEN @dayOffset IN (3,-4) THEN 3 <br />
WHEN @dayOffset IN (4,-3) THEN -3 <br />
WHEN @dayOffset IN (5,-2) THEN -2 <br />
WHEN @dayOffset IN (6,-1) THEN -1 <br />
END <br />
<br />
SET @adjustedDay = dateadd(day, @dateAdjustment,@<wbr></wbr>equivalentDateOnOffsetYear ) <br />
<br />
RETURN(@adjustedDay) <br />
<br />
END</div></blockquote>Wayne Stubbshttp://www.blogger.com/profile/17584509159989812270noreply@blogger.com0