Showing posts with label sql server 2012. Show all posts
Showing posts with label sql server 2012. Show all posts
Friday, March 04, 2016
Gotcha with the LEAD analytical function and date datatype
The other day some code that used the LEAD analytical function stopped working suddenly. It started to throw an error.
Running this query to see what is in this table
Here are our results
What will happen if someone changes the column from a datetime to a date? Maybe they looked at the data and saw that all the values had only dates and no time components... why use 8 bytes of storage when you can use 3 instead?
To find out exactly what will happen, run the following DDL statement....
Now go run your query again
Here is the error you get
The error is the same as if you would run the following
Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int
The code wasn't touched in a long time... so what could be the issue. Let's reproduce it here
First create the following table
CREATE TABLE testRunningTotal(Id tinyint,SomeDate datetime, Charge decimal(20,2)) INSERT testRunningTotal SELECT 1,'20120101',1000 UNION ALL SELECT 1,'20120401',200 UNION ALL SELECT 1,'20120501',300 UNION ALL SELECT 1,'20120601',600 UNION ALL SELECT 2,'20120101',100 UNION ALL SELECT 2,'20130101',500 UNION ALL SELECT 2,'20140101',-800 UNION ALL SELECT 3,'20120101',100
Running this query to see what is in this table
SELECT * FROM testRunningTotal
IdSomeDateCharge
12012年01月01日 00:00:00.0001000.00
12012年04月01日 00:00:00.000200.00
12012年05月01日 00:00:00.000300.00
12012年06月01日 00:00:00.000600.00
22012年01月01日 00:00:00.000100.00
22013年01月01日 00:00:00.000500.00
22014年01月01日 00:00:00.000-800.00
32012年01月01日 00:00:00.000100.00
Now run our fancy query...
SELECT id, someDate as StartDate, LEAD(SomeDate -1,1,'99991231') OVER (PARTITION BY id ORDER BY SomeDate) as Enddate, Charge, SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM testRunningTotal ORDER BY id, SomeDate
Here are our results
idStartDateEnddateChargeRunningTotal
12012年01月01日 00:00:00.0002012年03月31日 00:00:00.0001000.001000.00
12012年04月01日 00:00:00.0002012年04月30日 00:00:00.000200.001200.00
12012年05月01日 00:00:00.0002012年05月31日 00:00:00.000300.001500.00
12012年06月01日 00:00:00.0009999年12月31日 00:00:00.000600.002100.00
22012年01月01日 00:00:00.0002012年12月31日 00:00:00.000100.00100.00
22013年01月01日 00:00:00.0002013年12月31日 00:00:00.000500.00600.00
22014年01月01日 00:00:00.0009999年12月31日 00:00:00.000-800.00-200.00
32012年01月01日 00:00:00.0009999年12月31日 00:00:00.000100.00100.00
To see what the query actually does, visit this post Easy running totals with windowing functions
Let's take a closer look at the LEAD analytical function, this is what Books On Line has on its page
LEAD ( scalar_expression [ ,offset ] , [ default ] )
OVER ( [ partition_by_clause ] order_by_clause )
offset
The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.
Looks all cool to me..nothing that stands out that could cause a problem...
What will happen if someone changes the column from a datetime to a date? Maybe they looked at the data and saw that all the values had only dates and no time components... why use 8 bytes of storage when you can use 3 instead?
To find out exactly what will happen, run the following DDL statement....
ALTER TABLE testRunningTotal ALTER COLUMN SomeDate date
Now go run your query again
SELECT id, someDate as StartDate, LEAD(SomeDate -1,1,'99991231') OVER (PARTITION BY id ORDER BY SomeDate) as Enddate, Charge, SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM testRunningTotal ORDER BY id, SomeDate
Here is the error you get
Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int
The error is the same as if you would run the following
SELECT CONVERT(DATE,GETDATE() )-1
Instead what you have to do is use the DATEADD function to do arithmetic with dates
SELECT DATEADD(dd,-1,CONVERT(DATE,GETDATE()))
So to fix our query from before, we want to change the -1 to be DATEADD(dd,-1 instead, here is what our query looks like
SELECT id, someDate as StartDate, LEAD(dateadd(dd,-1,SomeDate),1,'99991231') OVER (PARTITION BY id ORDER BY SomeDate) as Enddate, Charge, SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM testRunningTotal ORDER BY id, SomeDate
And here are our results, it's all good now
idStartDateEnddateChargeRunningTotal
12012年01月01日2012年03月31日1000.001000.00
12012年04月01日2012年04月30日200.001200.00
12012年05月01日2012年05月31日300.001500.00
12012年06月01日9999年12月31日600.002100.00
22012年01月01日2012年12月31日100.00100.00
22013年01月01日2013年12月31日500.00600.00
22014年01月01日9999年12月31日-800.00-200.00
32012年01月01日9999年12月31日100.00100.00
I still think that maybe the LEAD function should do this conversion internally since all we are doing is specifying the OFFSET...what is your opinion?
Wednesday, March 02, 2016
Easy running totals with windowing functions
Back in the pre SQL Server 2012 days, if you wanted to do a running count, you either had to run a subquery or you could use a variable. This was slow because for each row the query that did the sum would be executed. With the additions to the windowing functions in SQL Server 2012, this is now running much faster.
Let's take a look, first create the following table
let's check that data we just inserted into the temporary table
The output looks like this
What we want is the following
For each row, we want to have the date that the row starts on and also the date when it end, we also want a running total as well. If there is no row after the current row for that id, we want the end date to be 9999年12月31日.
So we will use a couple of functions. The first one is LEAD, LEAD accesses data from a subsequent row in the same result set without the use of a self-join. So the LEAD part looks like this
LEAD(dateadd(dd,-1,SomeDate),1,'99991231') OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
What we are doing is subtracting 1 from the date in the subsequent row (ateadd(dd,-1,SomeDate))
We are using 1 as the offset since we want to apply this to the next row. Finally if there is no subsequent row, we want to use the date 9999年12月31日 instead of NULL
To do the running count, we will do the following
SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
What this means in English is for each id ordered by date, sum up the charge values for the rows between the preceding rows and the current row. Here is what all that stuff means.
ROWS BETWEEN
Specifies the rows that make up the range to use as implied by
UNBOUNDED PRECEDING
Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as window starting point.
CURRENT ROW
Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE.
CURRENT ROW can be specified as both a starting and ending point.
And here is the query
And running that query, gives us the running count as well as the dates
Let's take a look, first create the following table
CREATE TABLE #test(Id tinyint,SomeDate date, Charge decimal(20,10)) insert #test SELECT 1,'20120101',1000 UNION ALL SELECT 1,'20120401',200 UNION ALL SELECT 1,'20120501',300 UNION ALL SELECT 1,'20120601',600 UNION ALL SELECT 2,'20120101',100 UNION ALL SELECT 2,'20130101',500 UNION ALL SELECT 2,'20140101',-800 UNION ALL SELECT 3,'20120101',100
let's check that data we just inserted into the temporary table
SELECT * FROM #test
The output looks like this
Id SomeDate Charge 1 2012年01月01日 1000.0000000000 1 2012年04月01日 200.0000000000 1 2012年05月01日 300.0000000000 1 2012年06月01日 600.0000000000 2 2012年01月01日 100.0000000000 2 2013年01月01日 500.0000000000 2 2014年01月01日 -800.0000000000 3 2012年01月01日 100.0000000000
What we want is the following
id StartDate Enddate Charge RunningTotal 1 2012年01月01日 2012年03月31日 1000.0000000000 1000.0000000000 1 2012年04月01日 2012年04月30日 200.0000000000 1200.0000000000 1 2012年05月01日 2012年05月31日 300.0000000000 1500.0000000000 1 2012年06月01日 9999年12月31日 600.0000000000 2100.0000000000 2 2012年01月01日 2012年12月31日 100.0000000000 100.0000000000 2 2013年01月01日 2013年12月31日 500.0000000000 600.0000000000 2 2014年01月01日 9999年12月31日 -800.0000000000 -200.0000000000 3 2012年01月01日 9999年12月31日 100.0000000000 100.0000000000
For each row, we want to have the date that the row starts on and also the date when it end, we also want a running total as well. If there is no row after the current row for that id, we want the end date to be 9999年12月31日.
So we will use a couple of functions. The first one is LEAD, LEAD accesses data from a subsequent row in the same result set without the use of a self-join. So the LEAD part looks like this
LEAD(dateadd(dd,-1,SomeDate),1,'99991231') OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
What we are doing is subtracting 1 from the date in the subsequent row (ateadd(dd,-1,SomeDate))
We are using 1 as the offset since we want to apply this to the next row. Finally if there is no subsequent row, we want to use the date 9999年12月31日 instead of NULL
To do the running count, we will do the following
SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
What this means in English is for each id ordered by date, sum up the charge values for the rows between the preceding rows and the current row. Here is what all that stuff means.
ROWS BETWEEN
Specifies the rows that make up the range to use as implied by
UNBOUNDED PRECEDING
Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as window starting point.
CURRENT ROW
Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE.
CURRENT ROW can be specified as both a starting and ending point.
And here is the query
SELECT id, someDate as StartDate, LEAD(dateadd(dd,-1,SomeDate),1,'99991231') OVER (PARTITION BY id ORDER BY SomeDate) as Enddate, Charge, SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM #test ORDER BY id, SomeDate
And running that query, gives us the running count as well as the dates
id StartDate Enddate Charge RunningTotal 1 2012年01月01日 2012年03月31日 1000.0000000000 1000.0000000000 1 2012年04月01日 2012年04月30日 200.0000000000 1200.0000000000 1 2012年05月01日 2012年05月31日 300.0000000000 1500.0000000000 1 2012年06月01日 9999年12月31日 600.0000000000 2100.0000000000 2 2012年01月01日 2011年12月31日 100.0000000000 100.0000000000 2 2012年01月01日 2011-13-31 500.0000000000 600.0000000000 2 2012年01月01日 9999年12月31日 -800.0000000000 -200.0000000000 3 2012年01月01日 9999年12月31日 100.0000000000 100.0000000000That's all for this post...
Thursday, September 03, 2015
Did you know you can pass false and true to SQL Server bit parameters?
I noticed a stored procedure with a bit parameter and the default was false, I have never seen this before in SQL Server. You can do this in a language like c# but not in SQL Server right?
Let's take a look
Create this proc
create procedure prTest @BitTrue bit = true, @BitFalse bit = False, @BitNotFalse bit = -2 as Select @BitTrue as BitTrue, @BitFalse as BitFalse, @BitNotFalse as BitNotFalse GO
Now execute the sored procedure
EXEC prTest
BitTrueBitFalseBitNotFalse
101
As you can see true gets converted to 1, false gets converted to 0 and any number that is not 0 will be converted to 1 as well
What happens when you pass in false for the parameter that had a default of true?
EXEC prTest @BitTrue = false
BitTrueBitFalseBitNotFalse
001
How about variables..can you use true and false with bit datatype variables?
Let's try it out
DECLARE @BitTrue bit SET @BitTrue = true
Msg 207, Level 16, State 1, Line 2
Invalid column name 'true'.
DECLARE @BitTrue bit SET @BitTrue = false
Msg 207, Level 16, State 1, Line 3
Invalid column name 'false'.
As you can see you can use true and false with parameters in a proc but not with variables.
Monday, January 16, 2012
SQL Advent Recap and What is coming in SQL Server 2012 recap
In case you missed these, here is a recap of the SQL Advent and the SQL Server 2012 posts
SQL Advent Recap
Day 1: Date and time
In this post I covered the new date, datetime2 and time datatypes
In this post I covered the new date, datetime2 and time datatypes
Day 2: System tables and catalog views
In this post we took a look what the replacements are for the all system tables and also gave you a table with the new catalog view/compatibility view equivalent of the old system table
In this post we took a look what the replacements are for the all system tables and also gave you a table with the new catalog view/compatibility view equivalent of the old system table
Day 3: Partitioning
In this post I looked at partitioning in pre sql 2005 days by showing you how to create partitioned views. I also showed you how to user partitioned function in sql 2005 and up
In this post I looked at partitioning in pre sql 2005 days by showing you how to create partitioned views. I also showed you how to user partitioned function in sql 2005 and up
Day 4: Schemas
In this post I show you what schemas are and how they can help with security and logical grouping of objects
In this post I show you what schemas are and how they can help with security and logical grouping of objects
Day 5: Common Table Expressions
The Common Table Expressions post showed you what Common Table Expressions are and how they can be used to simplify your code
The Common Table Expressions post showed you what Common Table Expressions are and how they can be used to simplify your code
Day 6: Windowing functions
The Windowing functions post showed you how to do different kinds of rankings
The Windowing functions post showed you how to do different kinds of rankings
Day 7: Crosstab with PIVOT
This post was all about pivoting/transposing/crosstabbing data with the PIVOT operator, also was shown how to do it dynamically
This post was all about pivoting/transposing/crosstabbing data with the PIVOT operator, also was shown how to do it dynamically
Day 8: UNPIVOT
This post showed you how to use UNPIVOT to get the reversed effect of PIVOT
This post showed you how to use UNPIVOT to get the reversed effect of PIVOT
Day 9: Dynamic TOP
The dynamic TOP post showed you how to do dynamic TOP without dynamic SQL or SET ROWCOUNT
The dynamic TOP post showed you how to do dynamic TOP without dynamic SQL or SET ROWCOUNT
Day 10: Upsert by using the Merge statement
This post was all about how to use MERGE to do an UPSERT (Update if it exists otherwise insert)
This post was all about how to use MERGE to do an UPSERT (Update if it exists otherwise insert)
Day 11: DML statements with the OUTPUT clause
This post showed the usefulness of the OUTPUT clause
This post showed the usefulness of the OUTPUT clause
Day 12: Table Value Constructor
This post showed you how to use Table Value Constructor
This post showed you how to use Table Value Constructor
Day 13: DDL Triggers
The DDL trigger post showed you how to use DDL triggers and also explained why you might want to use them
The DDL trigger post showed you how to use DDL triggers and also explained why you might want to use them
Day 14: EXCEPT and INTERSECT SET Operations
This post was all about the two new SET Operations EXCEPT and INTERSECT
This post was all about the two new SET Operations EXCEPT and INTERSECT
Day 15: Joins
This post showed you how to use the newer ANSI SQL JOIN syntax and also showed you what was deprecated
This post showed you how to use the newer ANSI SQL JOIN syntax and also showed you what was deprecated
Day 16: CROSS APPLY and OUTER APPLY
Shown was how to use APPLY with derived tables as well as functions
Shown was how to use APPLY with derived tables as well as functions
Day 17: varchar(max)
In this post I showed you why varchar(max) is much better than the text data type
In this post I showed you why varchar(max) is much better than the text data type
Day 18: Table-valued Parameters
I showed you how to use Table-valued Parameters to pass around tables
I showed you how to use Table-valued Parameters to pass around tables
Day 19: Filtered Indexes
In this post I showed you how to create a filtered index and why it can be beneficial in your database
In this post I showed you how to create a filtered index and why it can be beneficial in your database
Day 20: Indexes with Included Columns
On this day I showed you how to cover you query by using Indexes with Included Columns
On this day I showed you how to cover you query by using Indexes with Included Columns
Day 21: TRY CATCH
Error handling go better in SQL Server 2005 and I show you how to use TRY CATCH
Error handling go better in SQL Server 2005 and I show you how to use TRY CATCH
Day 22: Dynamic Management Views
In this post I show how you can use Dynamic Management Views to get all kinds of information about your server and databases
In this post I show how you can use Dynamic Management Views to get all kinds of information about your server and databases
Day 23: OBJECT_DEFINITION
The OBJECT_DEFINITION covers ways to get the text of an object and also show you why it is better than sp_helptext or syscomments
The OBJECT_DEFINITION covers ways to get the text of an object and also show you why it is better than sp_helptext or syscomments
Day 24: Index REBUILD and REORGANIZE
This post is all about rebuilding and reorganizing(defragmenting) indexes
This post is all about rebuilding and reorganizing(defragmenting) indexes
What is coming in SQL Server 2012
First look at SQL Server Management Studio Denali
In this post I show you what the new SQL Server Management Studio which is based on Visual Studio 2010 looks like
In this post I show you what the new SQL Server Management Studio which is based on Visual Studio 2010 looks like
A first look at sequences in SQL Server Denali
Sequences finally made it into SQL Server, this post will show you how to use sequences
Sequences finally made it into SQL Server, this post will show you how to use sequences
Screenshots of the new look and feel of BIDS in Denali
Business Intelligence Development Studio has a new look and feel, SSIS has never looked better
Business Intelligence Development Studio has a new look and feel, SSIS has never looked better
Debugging In SQL Server Denali
Debugging got much better in SQL Server 2012, this post will look at some of the new things
Debugging got much better in SQL Server 2012, this post will look at some of the new things
Using OFFSET N ROWS FETCH NEXT N ROWS ONLY In SQL Server Denali for easy paging
With OFFSET N ROWS FETCH NEXT N ROWS ONLY paging has never been easier
With OFFSET N ROWS FETCH NEXT N ROWS ONLY paging has never been easier
Playing around with sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object
The sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object data management views makes it easy to find out what a proc or SQL statement returns
The sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object data management views makes it easy to find out what a proc or SQL statement returns
A Quick look at the new IIF function in Denali CTP3
SQL Server now has the IIF statement, this is just shorthand for CASE but it will make for shorter code
SQL Server now has the IIF statement, this is just shorthand for CASE but it will make for shorter code
A Quick look at the new EOMONTH function in SQL Server Denali CTP3
The EOMONTH function will give you the last date of the month
The EOMONTH function will give you the last date of the month
DATEFROMPARTS and DATETIMEFROMPARTS functions in SQL Server Denali CTP3
With DATEFROMPARTS and DATETIMEFROMPARTS you can construct a date by passing in a bunch of integers
With DATEFROMPARTS and DATETIMEFROMPARTS you can construct a date by passing in a bunch of integers
ColumnStore Index limitations in SQL Server Denali CTP3
Some limitation that the current version of ColumnStore Index have
Some limitation that the current version of ColumnStore Index have
Concat function in SQL Server Denali CTP3
Finally SQL Server 2012 has the Concat, this is something other RDBMSes had for years
Finally SQL Server 2012 has the Concat, this is something other RDBMSes had for years
Format function in SQL Server Denali CTP3
Formatting has never been easier, no need to use style parameters anymore
Formatting has never been easier, no need to use style parameters anymore
SQL Server Columnstore Index FAQ wiki page published
Columnar storage is new in SQL Server 2012, make sure to read the wiki for all your questions
Columnar storage is new in SQL Server 2012, make sure to read the wiki for all your questions
TRY_CONVERT in SQL Server Denali CTP3
Before converting, use TRY_CONVERT that will tell you if the value can be converted to the data type you want
Before converting, use TRY_CONVERT that will tell you if the value can be converted to the data type you want
SQL Server Code Name “Denali” CTP3 Product Guide available for download
There is some cool documentation available, this includes white papers and videos
There is some cool documentation available, this includes white papers and videos
Microsoft SQL Server, codename "Denali", will be the last release to support OLE DB, ODBC is the new new thing
Looks like OLE DB has run its course, ODBC is the new thing
Looks like OLE DB has run its course, ODBC is the new thing
Subscribe to:
Posts (Atom)