Showing posts with label sql server 2014. Show all posts
Showing posts with label sql server 2014. 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...
Friday, October 02, 2015
Dropping all objects in a schema
I had the need to drop all objects that I created in a specific schema as part of a unit test. Then I decided to test my script on the Adventure Works 2014 sample database and I got a couple of errors. Some of these errors were that there were foreign key constraints from a different schema pointing to a table in this schema. I also got an error complaining about an XML SCHEMA collection living in my schema.
If you want to follow along by running the scripts, first download the Adventure Works 2014 sample database
After you are done downloading the database, create a folder named training on your C drive.
Open up SSMS, open a query window, run the following script
Now that the DB is available, we can look at what the script looks like. Connect to the database you just created and open up a new query window. Before running the script make sure that you are in text mode, just hit CTRL + T in your SSMS query window
Here is what the script looks like
As you can see, there are 4 things going on here.
Of course if you have a view in another database or schema referencing your objects inside this schema you will have problems...I will get to this later
Running that script will give you the following output
Run that and it should all run fine, the schema should be gone
Now change @SchemaName NVARCHAR(100) ='HumanResources' to @SchemaName NVARCHAR(100) ='Sales'
Run the script again, run the output from the script
Finally change the variable to Person @SchemaName NVARCHAR(100) ='Person'
Run the script, run the output. All 3 schemas we specified should be gone now
Now restore the database again, This time run the code to remove the Sales Schema, Then run the code to remove the Person Schema.
Here is the error you will receive
Msg 6328, Level 16, State 1, Line 39
Specified collection 'AdditionalContactInfoSchemaCollection' cannot be dropped because it is used by object 'HumanResources.vEmployee'.
However, it is really used by the Person.Person table which we just dropped. In this case you will need to drop the view. If you have stuff like this in the database, you probably don't want to just start dropping objects in another schema that point to objects in your schema
I am not having these kind of issues so the code is good enough for me to wipe out objects that have been created as part of my unit testing
If you want to follow along by running the scripts, first download the Adventure Works 2014 sample database
After you are done downloading the database, create a folder named training on your C drive.
Open up SSMS, open a query window, run the following script
USE [master] --DROP DATABASE AdventureWorks2014 GO RESTORE DATABASE AdventureWorks2014 FROM disk= 'C:\Training\AdventureWorks2014.bak' WITH MOVE 'AdventureWorks2014_data' TO 'C:\Training\AdventureWorks2014.mdf', MOVE 'AdventureWorks2014_Log' TO 'C:\Training\AdventureWorks2014.ldf' ,REPLACE, stats =20
Now that the DB is available, we can look at what the script looks like. Connect to the database you just created and open up a new query window. Before running the script make sure that you are in text mode, just hit CTRL + T in your SSMS query window
Here is what the script looks like
SET NOCOUNT ON DECLARE @SchemaName NVARCHAR(100) = 'HumanResources' SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) +'.'+ QUOTENAME(OBJECT_NAME(s.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(s.name) FROM sys.foreign_keys s JOIN sys.tables t on s.parent_object_id = t.object_id JOIN sys.tables t2 on s.referenced_object_id = t2.object_id WHERE t2.schema_id = SCHEMA_ID(@SchemaName) SELECT 'DROP ' + CASE WHEN type IN ('P','PC') THEN 'PROCEDURE' WHEN type = 'U' THEN 'TABLE' WHEN type IN ('IF','TF','FN') THEN 'FUNCTION' WHEN type = 'V' THEN 'VIEW' END + ' ' + QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name) FROM sys.objects WHERE schema_id = SCHEMA_ID(@SchemaName) AND type IN('P','PC','U','IF','TF','FN','V') ORDER BY CASE WHEN type IN ('P','PC') THEN 4 WHEN type = 'U' THEN 3 WHEN type IN ('IF','TF','FN') THEN 1 WHEN type = 'V' THEN 2 END SELECT 'DROP XML SCHEMA COLLECTION ' + QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name) FROM sys.xml_schema_collections WHERE schema_id = SCHEMA_ID(@SchemaName) SELECT 'DROP SCHEMA ' + QUOTENAME(@SchemaName)
As you can see, there are 4 things going on here.
- Remove the constraints
- Drop Views, Function, Stored Procedures and Tables, drop functions and views first, procs last
- Drop XML Schema Collections
- Drop the Schema
Of course if you have a view in another database or schema referencing your objects inside this schema you will have problems...I will get to this later
Running that script will give you the following output
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID] ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID] ALTER TABLE [Production].[Document] DROP CONSTRAINT [FK_Document_Employee_Owner] ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_BusinessEntityID] ALTER TABLE [HumanResources].[EmployeePayHistory] DROP CONSTRAINT [FK_EmployeePayHistory_Employee_BusinessEntityID] ALTER TABLE [HumanResources].[JobCandidate] DROP CONSTRAINT [FK_JobCandidate_Employee_BusinessEntityID] ALTER TABLE [Purchasing].[PurchaseOrderHeader] DROP CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID] ALTER TABLE [Sales].[SalesPerson] DROP CONSTRAINT [FK_SalesPerson_Employee_BusinessEntityID] DROP VIEW [HumanResources].[vEmployee] DROP VIEW [HumanResources].[vEmployeeDepartment] DROP VIEW [HumanResources].[vEmployeeDepartmentHistory] DROP VIEW [HumanResources].[vJobCandidate] DROP VIEW [HumanResources].[vJobCandidateEmployment] DROP VIEW [HumanResources].[vJobCandidateEducation] DROP TABLE [HumanResources].[Shift] DROP TABLE [HumanResources].[Department] DROP TABLE [HumanResources].[Employee] DROP TABLE [HumanResources].[EmployeeDepartmentHistory] DROP TABLE [HumanResources].[EmployeePayHistory] DROP TABLE [HumanResources].[JobCandidate] DROP PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo] DROP PROCEDURE [HumanResources].[uspUpdateEmployeeLogin] DROP PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo] DROP XML SCHEMA COLLECTION [HumanResources].[HRResumeSchemaCollection] DROP SCHEMA [HumanResources]
Run that and it should all run fine, the schema should be gone
Now change @SchemaName NVARCHAR(100) ='HumanResources' to @SchemaName NVARCHAR(100) ='Sales'
Run the script again, run the output from the script
Finally change the variable to Person @SchemaName NVARCHAR(100) ='Person'
Run the script, run the output. All 3 schemas we specified should be gone now
Now restore the database again, This time run the code to remove the Sales Schema, Then run the code to remove the Person Schema.
Here is the error you will receive
Msg 6328, Level 16, State 1, Line 39
Specified collection 'AdditionalContactInfoSchemaCollection' cannot be dropped because it is used by object 'HumanResources.vEmployee'.
However, it is really used by the Person.Person table which we just dropped. In this case you will need to drop the view. If you have stuff like this in the database, you probably don't want to just start dropping objects in another schema that point to objects in your schema
I am not having these kind of issues so the code is good enough for me to wipe out objects that have been created as part of my unit testing
Labels:
DDL,
maintenance,
Schemas,
sql server 2014,
unit testing
Subscribe to:
Posts (Atom)