Posted by: Karlo Bartels | 2009/05/13

Developer training kit

Today, Microsoft released a SQL Server 2008 Developer Training Kit at the Microsoft Download center. Download it here.

This training kit will “help you understand how to build web applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008”.

It includes the following content:

  • Presentations
    • Filestream
    • Spatial
    • T-SQL
    • Date and Time Types
    • SQLCLR
    • Reporting Services
  • Demos
    • AdventureWorks Racing All-Up SQL Server 2008 Demo
    • SQL Server 2008 All-Up Spatial Demo
    • Spatial Types Demo
    • Intro to Filestream Demo
    • SQL CLR Nullable Types Demo
    • Programming with Filestream Demo
    • Reporting Services Web Application Integration Demo
    • Date and Time Support in SQL Server 2008 Demo
    • T-SQL Table-Valued Parameters Demo
    • T-SQL Row Constructors Demo
    • T-SQL Grouping Sets Demo
    • T-SQL Merge Demo
  • Hands-on Labs
    • Using Spatial Data in T-SQL
    • Using Spatial Data in Managed Code
    • Using SQL CLR in SQL Server 2008

Download the training kit here.

Advertisements
Posted by: Karlo Bartels | 2009/04/29

DayOfWeek bitmask

The other day I was working on a large data migration job for a customer. For some reason the customer had decided to store day-of-week data in a text field as a single number. The idea was to use a 7 digit number, where each digit represents a day of the week. So ‘1100001’ would mean that something would happen on Monday, Tuesday and Sunday, but not on any other day of that week. Creative, isn’t it?

While the customer was happy with this format, my normalized database wasn’t. I had to find a way to parse each digit (either True or False) into a couple of Bit fields that represent weekdays. I came up with the following UDF:

CREATE FUNCTION [dbo].[ParseDayOfWeekBitmask]
(
  
@Bitmask CHAR(7)
)
RETURNS @Result TABLE
(
  
Monday bit, Tuesday bit, Wednesday bit, Thursday bit, Friday bit,
  
Saturday bit, Sunday bit
)
AS
BEGIN
  
-- Validate bitmask
  
IF @Bitmask IS NULL
      
-- Must be 7 characters long
      
OR LEN(@Bitmask) < 7
      
-- Should only contain zeros and ones
      
OR LEN(REPLACE(REPLACE(@Bitmask, '0', ''), '1', '')) > 0
      
BEGIN  
           INSERT INTO
@Result
              
SELECT 0, 0, 0, 0, 0, 0, 0;    
          
RETURN
       END

   -- Perform an OR operation on each digit 
  
INSERT INTO @Result
      
SELECT
          
Monday = (SUBSTRING(@Bitmask, 1, 1) | 0),
          
Tuesday = (SUBSTRING(@Bitmask, 2, 1) | 0),
          
Wednesday = (SUBSTRING(@Bitmask, 3, 1) | 0),
          
Thursday = (SUBSTRING(@Bitmask, 4, 1) | 0),
          
Friday = (SUBSTRING(@Bitmask, 5, 1) | 0),
          
Saturday = (SUBSTRING(@Bitmask, 6, 1) | 0),
          
Sunday = (SUBSTRING(@Bitmask, 7, 1) | 0)  

   RETURN
END

The function starts by checking whether the passed value is actually a seven digit number and if it contains ones and zeros. If it doesn’t, it returns a table with each weekday set to False.

If everything checks out OK, it performs an OR operation on each digit to retrieve its Boolean representation. Parsing the ‘1100001’ bitmask returns the following results:

bitmaskresult

Posted by: Karlo Bartels | 2009/03/18

Migrating spatial data

Converting latitude/longitude to geography

I have a lot of spatial data stored in a SQL Server 2005 database. Now that SQL Server 2008 is around, I wanted to migrate that data to a new database, which would come down to converting the ‘old’ latitude/longitude fields to the new geography data type.

After doing some research it turned out I had to:

  • convert my latitude/longitude floats to varchars,
  • construct a POINT string and
  • call the STPointFromText method of the geography type to get what I wanted.

This is quit a hassle if you already have a database full of spatial data!

To make things easy, I created two UDFs that take care of this. The first one converts a float to a varchar, while preserving its precision:

CREATE FUNCTION [dbo].[ConvertFloatToVarChar]
(
  
@In float
)
RETURNS varchar(MAX)
AS
BEGIN 
   RETURN
CONVERT(varchar(MAX), CAST(@In AS decimal(38, 13))); 
END

 

The second one wraps the aforementioned UDF into a new function that constructs the POINT string and calls the STPointFromText method:

 

CREATE FUNCTION [dbo].[ConvertLatLonToGeography]
(
  
@Latitude float,
  
@Longitude float,
  
@SRID int = 4326
)
RETURNS geography
AS
BEGIN 
   RETURN
geography::STPointFromText('POINT(' +
         
dbo.ConvertFloatToVarChar(@Longitude) + ' ' +
         
dbo.ConvertFloatToVarChar(@Latitude) + ')', @SRID)
END

 

This function accepts a spatial reference identifier (SRID) that specifies the reference system you want to use. I used the default one which maps to the WGS 84 spatial reference system.

 

Data migration

Migrating ‘old’ data (in latitude/longitude form) from a table called ‘Locations’ to another table ‘LocationsNew’ which contains a geography column can now be realised as follows:

 

INSERT INTO LocationsNew
SELECT
   
Name,
   
dbo.ConvertLatLonToGeography(Latitude, Longitude, 4326)
FROM Locations
 

This inserts the location name and geographic location into the new table.

Download the UDTs and the sample script here

Posted by: Karlo Bartels | 2009/03/12

SQLDoc Lite

Creating XML documentation for T-SQL

Download SQLDoc Lite

Popular tools like Sandcastle and DocProject allow you to create MSDN style help files for your projects by using the XML documentation file created by Visual Studio. SQL Server Management Studio lacks the ability to generate documentation files for database objects that can be used by the aforementioned tools. Let’s look at a way of generating such files by using plain-old T-SQL.

Documenting database objects

There are several ways to document database objects, but the easiest and most widely accepted method is by using extended properties. Right-click on any database object, click Properties and select the Extended Properties page. Create a property called Description and enter the documentation details.

Here’s a example for the Catalog table in the ReportServer database:

Add extended property

Retrieving documented objects

The next step is retrieving the documented objects by querying the sys.extended_properties table. This system table contains a row for each extended property of each object in the database. Retrieve this information in a CTE, so you can easily parse the results into an XML document.

Creating the XML file

To return result sets as XML instead of standard rowsets, you can execute SQL queries by using the FOR XML clause of the SELECT statement. I used the PATH mode to construct an XML document that contains the required nesting for an XML Documentation file.

SQLDoc Lite

My solution to generate XML Documentation files contains of the following procedures:

  • sqldoc_CreateXMLDocumentation: this sproc does all the hard work
  • sqldoc_ShowUndocumentedObjects: this one identifies any undocumented objects.

If you create these stored procedures in the master database, they will be available for every database that you create from that point on. These procedures will document the following objects only:

  • Tables
  • Views
  • Stored Procedures
  • Scalar-valued Functions
  • Table-valued Functions

Results

This is what a generated XML Documentation file looks like for the ReportServer database:

 XML Documentation

Added bonus

As a bonus, I included 2 stored procedures in the download package that generate HTML documentation files and plain documentation in the Results grid. The HTML output looks like this:

HTML Documentation

Happy documenting!

Download SQLDoc Lite

Posted by: Karlo Bartels | 2009/02/10

Date range search

How to search a date range without an end date

The other day I was asked to create a query which retrieves a value for a specific date from a table containing prices that only had a start date, but no end date. This is what the table looked like:

date range table 1

As you can see, water prices increased three times last year. So how would you query this table to get the water price at 31 May 2008?

Since you can’t do this directly (because there is no record with start date 2008-05-31) you have to query the table using a date range. However, this table does not contain a column with an end date, so you have to create a self-join on the table which refers to the next row to get the end date. You can’t simply refer to the CostID column, because the IDs are not consecutive. I used the ROW_NUMBER() function to generate a column called RowNumber that contains a consecutive sequence of row numbers:

SELECT CostID, StartDate, WaterCost,
      
ROW_NUMBER() OVER (ORDER BY CostID) AS RowNumber
FROM   EnergyCosts;

This yields the following result:

date range table 2

Now that we have consecutive row numbers, we can self-join the results to generate an EndDate column. To do so, I used two CTEs: the first returns the results you saw above, while the second self-joins the first to generate the EndDate column:

;WITH CostsNoDateRange(StartDate, WaterCost, RowNumber) AS
(
  
SELECT  StartDate, WaterCost,
          
ROW_NUMBER() OVER (ORDER BY CostID) AS RowNumber
  
FROM    EnergyCosts
),
CostsWithDateRange(StartDate, EndDate, WaterCost) AS
(
  
SELECT  T1.StartDate, T2.StartDate AS EndDate, T1.WaterCost
  
FROM    CostsNoDateRange T1 LEFT OUTER JOIN CostsNoDateRange T2
  
ON      T1.RowNumber = T2.RowNumber - 1
)
SELECT * FROM CostsWithDateRange;

This will return the following:

date range table 2

Now that we have an end date column, we can use it in the WHERE condition of the CostsWithDateRange CTE. I created a stored procedure that does all that:

CREATE PROCEDURE [dbo].[GetWaterCost]
  
@Date date
AS
BEGIN
   SET NOCOUNT ON
;
  
DECLARE @LastDate date
  
SET @LastDate = CONVERT(date, '31-12-9999', 105)

   ;WITH CostsNoDateRange(StartDate, WaterCost, RowNumber) AS
  
(
  
SELECT
           
StartDate, WaterCost
          
,ROW_NUMBER() OVER (ORDER BY CostID)
  
FROM    EnergyCosts
  
),
  
CostsWithDateRange(StartDate, EndDate, WaterCost) AS
  
(
  
SELECT 
           
-- Get start date of period. If the requested date is
            -- not in the table, no records will be returned.
           
T1.StartDate
           
-- Get end date of period. If the requested date is
            -- not present, use the highest date possible.
          
,ISNULL(T2.StartDate, @LastDate)
           
-- Get cost column
          
,T1.WaterCost
  
FROM    CostsNoDateRange T1 LEFT OUTER JOIN CostsNoDateRange T2
  
ON      T1.RowNumber = T2.RowNumber - 1
  
)
  
SELECT  WaterCost FROM CostsWithDateRange
  
WHERE   @Date >= StartDate AND @Date < EndDate;
  
END

Please note I added the ISNULL statement to prevent a NULL value in the EndDate column, which allows queries for prices later than the last StartDate entry. 

The following table contains some results for different dates:

Date WaterCost
01-02-2008 0,50
01-06-2008 1,421
30-08-2008 1,421
01-09-2008 3,2357
30-11-2008 3,2357
Posted by: Karlo Bartels | 2009/01/28

Profiler

Knowing how to master the SQL Server Profiler tool ensures that you can troubleshoot SQL Server health and performance issues quickly and efficiently. We all know how to start and configure a simple trace, but dealing with complex performance issues such as deadlocking and other blocking issues can form serious challenges to the casual Profiler user.

To help you master the SQL Server Profiler tool, Red Gate released a free e-book to get you up-to-speed. Subjects include tips and tricks, identifying slow running queries, troubleshooting and auditing. Download your copy here. A recommended read!

Posted by: Karlo Bartels | 2008/12/12

Apostrophes

Finding apostrophes in (n)varchar columns

Have you ever tried to find apostrophes in your data? Looking for that row of data that breaks your JavaScript? Look no further, try the solution below!

Let’s say you query the Person.Address table in the AdventureWorks database for an address and display it on a web page. The web page implements JavaScript to handle the selected address. You notice that JavaScript errors occur only if the user selects an address that contains an apostrophe.

At first you try to find the culprit by escaping the apostrophe character in the address:

SELECT AddressLine1
FROM   Person.Address
WHERE  AddressLine1 LIKE '%'''%'

This doesn’t work; you need another way of escaping the apostrophe you are looking for. That’s where the ESCAPE keyword after the LIKE statement comes in:

SELECT AddressLine1
FROM   Person.Address
WHERE  AddressLine1 LIKE '%!''%' ESCAPE '!'

This will give you the results you were looking for.

Essentially, you are telling the parser to escape the character that follows the character you specified after the ESCAPE keyword. In this case, I'm using the exclamation mark to designate the next character to escape. I included the percent signs (%) because I don't know the exact position of the apostrophe in the address.

Next you need a function to replace the apostrophes so your code doesn't break:

CREATE FUNCTION [dbo].[sys_RemoveApostropheFromText]
(
  
@txt VARCHAR(250)
)
RETURNS VARCHAR(250)
AS
BEGIN
   DECLARE
@i INT
   DECLARE
@ret VARCHAR(250)
 
  
SELECT @i = 1, @ret = ''
  
WHILE (@i <= LEN(@txt))
  
SELECT @ret = @ret +
     
CASE WHEN SUBSTRING(@txt,@i,1) = '''' THEN ''
     
ELSE SUBSTRING(@txt,@i,1) END,
     
@i = @i + 1

   RETURN

@ret

END

View the results side-by-side by including the function in your query:

SELECT AddressLine1, dbo.sys_RemoveApostropheFromText(AddressLine1)
FROM   Person.Address
WHERE  AddressLine1 LIKE '%!''%' ESCAPE '!'

The final step is creating an UPDATE statement that removes the apostrophe altogether:

UPDATE Person.Address
SET    AddressLine1 = dbo.sys_RemoveApostropheFromText(AddressLine1)

WHERE  AddressLine1 LIKE '%!''%' ESCAPE '!'

There you have it!

Posted by: Karlo Bartels | 2008/11/26

Permissions

Last week I ran into some permission problems while running a change script on a client’s acceptance server. I assumed the script also contained the object’s access permissions which I specified on my development machine, but I was wrong.

While reviewing the DELETE permission denied on object X messages, I decided to create a script that would list object permissions and generate an executable script for the target database.

Of course, this script would only make sense if you had applied an object change script on the target database first, but that being said, let’s look at the script:

Permissions script for SQL Server 2005 and 2008

Click here to download a properly formatted version

----------------------------------------------------------------------
-- This script generates a permissions script for a specific user or
-- database role. The script comes in handy when transferring
-- existing permissions to a new database.
--
-- Run this script on the source database to generate the permissions script.
-- Select the item 'Results to Text' in the 'Query | Results To' menu
-- to generate executable script. Copy the text in the results pane
-- to a new query window in the target database and run it.
--
-- NOTE: This script works on SQL Server 2005 and 2008.
-- There is also a SQL Server 2000 version of this script.
-- See https://linktosql.wordpress.com/2008/11/26/permissions/ for more information.
-----------------------------------------------------------------------
USE [YourDatabaseNameHere]
GO
SET NOCOUNT ON;
GO
DECLARE @UserOrDatabaseRole nvarchar(128)
-- Set this variable to the user or database role for which to create the script
SET @UserOrDatabaseRole = '[YourUserOrRoleHere]'
;
 
-- Create temporary table to hold the script
CREATE TABLE #Script(ScriptLine varchar(4000
));
 
-- Generate permission script
INSERT INTO #Script
SELECT CASE WHEN pm.state = 'W' THEN 'GRANT' ELSE pm.state_desc END + ' ' + pm.permission_name + ' ON [' + sch.name + '].[' + obj.name +
'] TO [' + usr.name + ']' + CASE WHEN pm.state = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END + CHAR(10)+ 'GO'
FROM   sys.database_permissions AS pm INNER JOIN sys.objects AS obj
ON     pm.major_id = obj.object_id INNER JOIN sys.database_principals AS usr ON pm.grantee_principal_id = usr.principal_id LEFT OUTER JOIN sys.columns AS cl ON cl.column_id = pm.minor_id AND cl.object_id = pm.major_id INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id WHERE usr.name = @UserOrDatabaseRole ORDER BY obj.name
;
 
-- Return script to client
SELECT ScriptLine AS Run_this_on_the_target_database FROM #Script
;
 
-- Clean-up
DROP TABLE #Script
;
 

Permissions script for SQL Server 2000

Click here to download a properly formatted version

----------------------------------------------------------------------
-- This script generates a permissions script for a specific user or
-- database role. The script comes in handy when transferring
-- existing permissions to a new database.
--
-- Run this script on the source database to generate the permissions script.
-- Select the item 'Results to Text' in the 'Query | Results To' menu
-- to generate executable script. Copy the text in the results pane
-- to a new query window in the target database and run it.
--
-- NOTE: This script works on SQL Server 2000 only. There is also a
-- SQL Server 2005 / 2008 version of this script.
-- See https://linktosql.wordpress.com/2008/11/26/permissions/ for more information.
-----------------------------------------------------------------------
USE [YourDatabaseNameHere]
GO
SET NOCOUNT ON;
GO
DECLARE @UserOrDatabaseRole nvarchar(128)
-- Set this variable to the user or database role for which to create the script
SET @UserOrDatabaseRole = '[YourUserOrRoleHere]'
;
-- Create temporary table to hold the script
CREATE TABLE #Script(ScriptLine varchar(4000
));
-- Generate permissions script
INSERT INTO #Script
SELECT CASE
       
WHEN sysprotects.protecttype = 204 THEN 'GRANT '
       
WHEN sysprotects.protecttype = 205 THEN 'GRANT '
       
WHEN sysprotects.protecttype = 206 THEN 'DENY '
  
END +
  
CASE
       
WHEN sysprotects.action = 26 THEN 'REFERENCES'
       
WHEN sysprotects.action = 178 THEN 'CREATE FUNCTION'
       
WHEN sysprotects.action = 193 THEN 'SELECT'
       
WHEN sysprotects.action = 195 THEN 'INSERT'
       
WHEN sysprotects.action = 196 THEN 'DELETE'
       
WHEN sysprotects.action = 197 THEN 'UPDATE'
       
WHEN sysprotects.action = 203 THEN 'CREATE DATABASE'
       
WHEN sysprotects.action = 207 THEN 'CREATE VIEW'
       
WHEN sysprotects.action = 222 THEN 'CREATE PROCEDURE'
       
WHEN sysprotects.action = 224 THEN 'EXECUTE'
       
WHEN sysprotects.action = 228 THEN 'BACKUP DATABASE'
       
WHEN sysprotects.action = 233 THEN 'CREATE DEFAULT'
       
WHEN sysprotects.action = 235 THEN 'BACKUP LOG'
       
WHEN sysprotects.action = 236 THEN 'CREATE RULE'
  
END +
  
' ON [' +
   (
SELECT SU.name FROM sysusers SU WHERE SU.uid = sysobjects.uid) +
  
'].[' + sysobjects.name + '] TO [' + sysusers.name + ']' +
  
CASE WHEN sysprotects.protecttype = 204 THEN ' WITH GRANT OPTION '   ELSE '' END + CHAR(10)+ 'GO'
FROM   sysusers FULL JOIN sysobjects
ON     (sysobjects.xtype IN ('P', 'U') AND sysobjects.Name NOT LIKE 'dt%') LEFT JOIN sysprotects ON sysprotects.uid = sysusers.uid AND sysprotects.id = sysobjects.id
WHERE sysusers.name = @UserOrDatabaseRole ORDER BY sysobjects.name;
-- Clean-up
DROP TABLE #Script
  
 
-- Return script to client
SELECT ScriptLine AS Run_this_on_the_target_database FROM #Script;

 

Please note: select the item Results to Text in the Query | Results To menu to generate executable script that you can run on the target machine.

Download properly formatted version (SQL Server 2005, 2008)

Download properly formatted version (SQL Server 2000)

Posted by: Karlo Bartels | 2008/10/31

Week numbers

Displaying correct week numbers in a table header

So today I had to develop a report which features aggregated data for a specific period in time. The underlying stored procedure returns data starting at the current week up to twelve weeks after that. I created a table in my report and defined the first column header expression as follows:

="Week " & ReportItems!txtCurrentWeek.Value

The second through the twelfth column header got this expression:

="Week " & Mid(ReportItems!txtWeekHeader[n-1].Value, 6) + 1

Works like a charm… as long as the data doesn’t span multiple years. Which, in my case, it did… What I wanted is this (note that the year 2008 has 52 weeks, whereas 2009 has 53):

What I wanted (click for a larger image)

Fortunately, reports allow you to add some ‘code-behind’, so you can add your own algorithms. I added two functions: one to determine the amount of weeks in a specific year (CountWeeksInYear) and another that returns the proper week number for my table header (GetNextWeekNumber):

Public Shared Function CountWeeksInYear(ByVal Year As Integer) As Integer
   Dim
dutchCI As New System.Globalization.CultureInfo("nl-NL")
  
Dim lastDay As New DateTime(Year, 12, 31, dutchCI.Calendar)
  
Dim week As Integer = dutchCI.Calendar.GetWeekOfYear(lastDay, System.Globalization.CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday)
  
If ((week = 53) AndAlso (((lastDay.DayOfWeek = DayOfWeek.Monday) OrElse (lastDay.DayOfWeek = DayOfWeek.Tuesday)) OrElse (lastDay.DayOfWeek = DayOfWeek.Wednesday))) Then
      Return
52
  
End If
   Return
week
End
Function
Public Shared Function GetNextWeekNumber(ByVal YearOfFirstWeek As Integer, ByVal PreviousWeek As Integer) As Integer
   Dim
weekNumber As Integer = (PreviousWeek + 1)
  
If (weekNumber > 52) Then
      Dim
yearsInWeek As Integer = CountWeeksInYear(YearOfFirstWeek)
     
If ((yearsInWeek = 53) AndAlso (weekNumber = yearsInWeek)) Then
         Return
53
     
End If
     
weekNumber = (weekNumber - yearsInWeek)
  
End If
   Return
weekNumber
End

The next step was to change the second through the twelfth column header expressions into this:

="Week " & Code.GetNextWeekNumber(ReportItems!txtYear.Value, Mid(ReportItems!txtWeekPlus[n-1].Value, 6))

The MID function removes the “Week ” part from the header textbox value. The [n-1] part means that it references the textbox of the previous week.

Download the sample RDL file to see how it works!

Posted by: Karlo Bartels | 2008/10/14

Free E-books

Red Gate published another set of free e-books containing – and I quote – “essays and ideas from the SQL Server community”. You’ll get two e-books called “The Best Of SQLServerCenter.com Vol. 5 and 6”, plus a great book called “Dissecting SQL Server Execution Plans”. The best of… books contain articles about any aspect of SQL Server, ranging from administration, T-SQL, ETL and BI to XML. The other book is about execution plans (you guessed it!) and deals with several aspects of query tuning and how to use execution plans in the process. A recommended download!

« Newer Posts - Older Posts »

Categories