Search results matching tags 'SQL Server' and 't-sql tuesday'

[T-SQL Tuesday] Some code is born crap, some code achieves crapness and some code has crapness thrust upon it!

jamiet — Wed, 10 Aug 2011 15:04:00 GMT

I recently returned to a client at which I last worked back in 2006 and, as you might imagine, that gives me ample material for a blog post dedicated to Crap Code; conveniently the subject of the latest T-SQL Tuesday.

I first worked for this client back in 2004 and that is significant because back then we were writing code on SQL Server 2000. Ah, thems were the days!!! I smiled wryly to myself when I encountered the following code in a stored procedure that I once wrote:

--Simplified for convenience/demo
CREATE PROCEDURE [dbo].[usp_PersonInsert]
(
   @pXMLDataInsert     TEXT
)
AS
BEGIN
   DECLARE @vDoc            INT
   EXECUTE sp_xml_preparedocument @vDoc OUTPUT, @pXMLDataInsert
   SELECT
       ID, 
       Name
   INTO    #Insert
   FROM    OpenXML(@vDoc, 'NewDataSet/Table', 2 ) 
   WITH    (
       ID  INT     'ID',
       Name    VARCHAR(70) 'Name'
       )
   EXECUTE sp_xml_removedocument @vDoc
   INSERT INTO dbo.Person(ID,Name)SELECT ID,Name FROM #Insert
END

If you have never had the pleasure(!!!) of dealing with XML data prior to SQL Server 2005 then you're probably looking at this with a slightly confused look on your face wondering quite why we had to make this so complicated. TEXT datatype? OPENXML? System stored procs? What's in this @vDoc variable? On the other hand if you have had to deal with XML pre-2005 then right now you're probably nodding sagely to yourself and remembering the not-so-good-old-days!

No-one would ever write code like this today but back in 2004, before wonderous things like the XML datatype and table-valued-parameters, this was how one passed datasets into a stored procedure (well, its how we did it anyway). If any of my colleagues were to write this today they would probably be despatched home with a copy of Inside SQL Server 2008 : T-SQL Programming tucked under their arm and ordered not to come back until they could recite it ad infinitum but back in 2004, well, we thought we were pretty cool!

Times change, people change and coding practices change too. Code isn't born crap, it just tends to crapness the longer that it lives! Bear that in mind when coding today because in five years time you'll probably be coming back and having a little chuckle at it!

@Jamiet

T-SQL Tuesday #006: Tiger/Line Spatial Data

Mike C — Tue, 11 May 2010 23:11:00 GMT

This month’s T-SQL Tuesday post is about LOB data http://sqlblog.com/blogs/michael_coles/archive/2010/05/03/t-sql-tuesday-006-what-about-blob.aspx.

For this one I decided to post a sample Tiger/Line SQL database I use all the time in live demos. For those who aren't familiar with it, Tiger/Line data is a dataset published by the U.S. Census Bureau. Tiger/Line has a lot of nice detailed geospatial data down to a very detailed level. It actually goes from the U.S. state level all the way down to street, feature and landmark level. Tiger/Line data is very complete and detailed--but the best part is it's FREE. There are lots of applications for Tiger/Line, like national [U.S.] and local mapping and geocoding applications [applications that convert street addresses to (latitude, longitude) coordinates]. All this great data is distributed in the form of a ton of ESRI shapefiles.

A shapefile is basically a file format that contains shape objects like points, lines and polygons. SQL Server doesn’t natively understand ESRI shapefiles, but it also stores geospatial objects like lines, points and polygons. There some handy utilities out there for loading these files into SQL Server. Morten Nielsen has a great utility for loading shapefiles into Geometry and Geography data types at http://www.sharpgis.net/page/Shape2SQL.aspx. Because of the volume of data involved in this project (I loaded hundreds of shapefiles) I decided to roll my own small set of SSIS custom components that read ESRI shapefiles and convert them to SQL Server spatial data types [keep an eye out -- these components are scheduled to be published with source code by SQL Server Standard magazine in the near future].

The sample database can be downloaded from http://www.sqlkings.com/downloads/Tiger_Sample.zip

Download the ZIP file from http://www.sqlkings.com/downloads/Tiger_Sample.zip.
Extract the database backup file and restore it to a SQL Server 2008 instance.

In the future I’ll be sharing some code samples on the blog to demonstrate Tiger/Line data (as well as spatial data from other sources) based on this database.

Here are a couple of quick queries you can run against this sample database to view the spatial data in SSMS 2008.

SELECT *

FROM Tiger.State;

SELECT c.*

FROM Tiger.State s

INNER JOIN Tiger.County c

ON s.STATEFP = c.STATEFP

WHERE s.STUSPS = 'TX';

SELECT *

FROM Tiger.ZCTA z

WHERE z.ZCTA5CE00 LIKE '0[7-8]%';

This last one works because all of the ZIP Code tabulation areas for the state of New Jersey start with '07' and '08'. There are similar relationships between other ZCTA prefixes and their states.

Next time we'll look at using SQL Server-based spatial data with online mapping programs like Bing maps.

*For more information about Tiger/Line data visit http://www.census.gov/geo/www/tiger/tgrshp2009/tgrshp2009.html.

T-SQL Tuesday #004: Why Doesn't TDE Encrypt My FILESTREAM Data?

Mike C — Tue, 09 Mar 2010 02:42:00 GMT

This post is my entry for Adam Machanic's T-SQL Tuesday #004, hosted this time by Mike Walsh. I was at the RSA Conference in San Francisco last week discussing database encryption options in SQL Server 2008 and one question seemed to keep coming up. The question concerns FILESTREAM and Transparent Data Encryption (TDE), but first a little background:

FILESTREAM is a new SQL Server 2008 feature. When you apply the FILESTREAM attribute to a varbinary(max) column SQL Server stores your BLOB data in a "FILESTREAM data container" (an NTFS directory structure) instead of directly in the database (the MDF and NDF files that normally hold all your data). The advantages of FILESTREAM are speed (streaming NTFS access for large files) and the ability to store BLOB data larger than 2.1 GB.

One of the downsides of FILESTREAM concerns another new feature, TDE. TDE transparently encrypts your database, adding a layer of protection against physical theft of your database files and storage devices. But TDE does not encrypt FILESTREAM data. The question I kept getting was "why?" The answer is fairly simple -- but a picture's worth a thousand words:

As you can see in the picture, TDE sits midway between your physical storage and SQL Server's IO buffers. This ensures that everything that passes through the IO buffers gets encrypted on its way to persistent storage and decrypted on its way back out of storage. This is also why it's "transparent" to your applications, developers and users. SQL Server reads and writes data through the IO buffers in 8 KB pages. FILESTREAM achieves much of its performance enhancement for BLOB data by simply bypassing the IO buffers. But since TDE acts only on data passing through the IO buffers, it misses FILESTREAM BLOB data completely.

Fortunately there are plenty of other options for encrypting your FILESTREAM data -- you can use Windows Encrypting File System (EFS), BitLocker or third-party file/folder/volume encrypting software, for instance.

T-SQL Tuesday: Easy Extended Properties

Mike C — Tue, 12 Jan 2010 05:18:00 GMT

Ahhh, attention to detail. I misread Adam's T-SQL Tuesday rules and posted early. So here I go again :) This time the puzzle is how to add and update metadata via extended properties in the database with minimal headaches.

SQL Server supports a great feature for storing database object metadata in the database in the form of extended properties. The main benefits of extended properties over custom solutions are: (1) extended properties are managed and stored internally by SQL Server; (2) many third-party applications retrieve/use extended properties. Perhaps the best known extended property is the "MS_Description" property, which is set by the "Description" field in the SSMS designer pages (shown below):

Extended properties are created as user-defined key/value pairs assigned to database objects. You can define the extended propery keys and their associated values to be anything you like, although there are a few commonly used ones (like "MS_Description"). The values are defined as sql_variant data types. Here are some sample key/value pairs:

Key	Value
MS_Description	This table represents the culmination of a lifelong dream.
Caption	GrandTotal Column
Last_Update	2009-07-09
Version	1.0.1

Extended properties allow you to store metadata in the database closely tied to database objects. So what's the downside to extended properties? Basically the only real issue with them is the nonintuitive syntax. In order to use extended properties you have to use stored procedures like sp_addextendedproperty. The following stored procedure call sets the MS_Description extended property on the bar column of the dbo.foo table:

EXECUTE sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'This is the bar column',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'foo',
@level2type = N'COLUMN',
@level2name = N'bar';

As you can see in the example, this syntax requires you to specify not only the extended property name/value pair and the individual components of the column's fully-qualified name, but also the types of each object. Updating and deleting extended properties using the system stored procedures requires the same type of information. A painful process to say the least.

The attached code sample creates an extended properties management schema named ExtProps. The ExtProps schema contains stored procedures that make it easier to add, update and delete extended properties. With these procedures you specify the object name using the standard SQL Server 1-, 2-, or 3-part naming convention along with the extended property name/value pair. The procedure will automatically figure out what type of object you're referencing. The following sample performs the same function as the previous example:

EXECUTE ExtProps.PropInsert N'dbo.foo.bar',
N'MS_Description',
N'This is the bar column';

The ExtProps.PropInsert, ExtProps.PropDelete and ExtProps.PropUpdate procedures use the object name you pass in to narrow down the type of object you're referencing. They then use the SQL Server catalog views to figure out exactly which object it should modify the extended properties on. The following table lists the types of object and the names expected by the procedures:

Name	Object Type
NULL	Current database
1-part	Schema
2-part (schema.object)	Table, View, Procedure, Function, Aggregate, Synonym, Queue
3-part (schema.object.column)	Column

Note that these procedures do not yet handle all the different objects to which you can assign extended properties. For instance, it does not currently handle extended properties for procedure parameters, event notifications, and a handful of others.

When you want to retrieve extended properties from the database you can use the standard system view, sys.extended_properties, or the fn_listextendedproperty system function. As an alternative, the ExtProps schema includes a new view -- ExtProps.Properties. This view has two advantages over the built-in system views:

In addition to all the data returned by the sys.extended_properties view the ExtProps.Properties view also includes schema, object, and column names.
The ExtProps.Properties view has triggers built on it that allow you to insert, delete, and update extended properties using standard SQL DML statements. The triggers actually call the relevant stored procedures, once for each row of extended property data being modified.

The figure below show sample output from the ExtProps.Properties view:

The script attached to this posting creates the ExtProps schema, procedures and view.

Unambiguous date formats : T-SQL Tuesday #001

jamiet — Tue, 08 Dec 2009 20:23:41 GMT

One of the most commonly used data types in SQL Server is [datetime] which unfortunately has some vagaries around how values get casted. A typical method for defining a [datetime] literal is to write it as a character string and then cast it appropriately. The cast syntax looks something like this:

DECLARE @dt NVARCHAR(19) = '2009-12-08 18:00:00';

SELECT CAST(@dt AS datetime);

Unfortunately in SQL Server 2005 the result of the cast operation may be dependent on your current language setting. You can discover your current language setting by executing:

SELECT @@LANGUAGE

To demonstrate how your language setting can influence the results of a cast take a look at the following code:

ALTER  DATABASE tempdb

SET        COMPATIBILITY_LEVEL = 90 ; --Behave like SQL Server 2005

USE tempdb

GO

DECLARE @t TABLE (

      dateString  NVARCHAR(19)

);

INSERT @t (dateString)

VALUES ('2009-12-08 18:00:00') --'yyyy-MM-dd hh24:mi:ss'

,      ('2009-12-08T18:00:00') --'yyyy-MM-ddThh24:mi:ss'

,      ('20091208 18:00:00')   --'yyyyMMdd hh24:mi:ss'



SET        LANGUAGE french;

SELECT 'french' AS lang

,      DATENAME(MONTH,q.[dt]) AS mnth

,      q.[dt]

FROM   (

       SELECT  CAST(dateString AS DATETIME) AS dt

       FROM    @t

       )q;

SET        LANGUAGE us_english;

SELECT 'us_english' AS lang

,      DATENAME(MONTH,q.[dt]) AS mnth

,      q.[dt]

FROM   (

       SELECT  CAST(dateString AS DATETIME) AS dt

       FROM    @t

       )q;

We are taking the value which can be described in words as “6pm on 8th December 2009”, defining it in three different ways, then seeing how the @@LANGUAGE setting can affect the results. Here are those results:

Notice how the interpretation of the month can change depending on @@LANGUAGE. If @@LANGUAGE=’french’ then the string '2009-12-08 18:00:00' is interpreted as 12^th August 2009 (‘août’ is French for August for those that don’t know) whereas if @@LANGUAGE=’us_english’ it is interpreted as 8^th December 2009.

Clearly this is a problem because the results of our queries have a dependency on a server-level or connection-level setting and that is NOT a good thing. Hence I recommend that you only define [datetime] literals in one of the two unambiguous date formats:

yyyy-MM-ddTHH24:mi:ss
yyyyMMdd HH24:mi:ss

That was going to be the end of this blog post but then I found out that this behaviour changed slightly in SQL Server 2008. Take the following code (see if you can figure out what the results will be before I tell you):

ALTER  DATABASE tempdb

SET        COMPATIBILITY_LEVEL = 100 ; --Behave like SQL Server 2008

GO

USE tempdb

GO

SET        LANGUAGE french;

DECLARE    @dt NCHAR(10) = '2009-12-08 18:00:00'; --Ambiguous date format

SELECT CAST(@dt AS datetime) AS [ExplicitCast]

,      DATENAME(MONTH,@dt) AS [MonthFromImplicitCast]

,      DATENAME(MONTH,CAST(@dt AS datetime)) AS [MonthFromExplicitCast];

Here we are doing three different things with our nchar literal:

explicitly cast it as a [datetime]
extract the month name from the char literal using the DATENAME function (which results in an under-the-covers implicit cast)
extract the month name from the char literal using the DATENAME function after it has been explicitly casted as a [datetime]

Note that the compatibility level is set to SQL Server 2008 and @@LANGUAGE=’french’. Here are the results:

(Were you correct?)

Let’s take a look at what is happening here. The behaviour when we are explicitly casting as [datetime] hasn’t changed, our nchar literal is still getting interpreted as 12th August rather than 8th December when @@LANGUAGE=’french’. The [MonthFromExplicitCast] field is interesting though, it seems as though the implicit cast has resulted in the desired value of 8th December. Why is that?

To get the answer we can turn to BOL’s description of the DATENAME function syntax:

The implicit cast is not casting to [datetime] at all, it is actually casting to [date] which is a new datatype in SQL Server 2008. The new date-related datatypes in SQL Server 2008 (i.e. [date], [datetime2], [time], [datetimeoffset]) disregard @@LANGUAGE and hence we get behaviour that is more predictable and, frankly, better.

These new behaviours for SQL Server 2008 were unknown to me when I began this blog post so I have learnt something in the course of authoring it, I hope it has helped you too. No doubt someone somewhere is going to get nastily burnt by this at some point, make sure that it isn’t you by always using unambiguous date formats:

yyyy-MM-ddTHH24:mi:ss
yyyyMMdd HH24:mi:ss

regardless of which version you are on!

Don’t forget to check out other T-SQL Tuesday blog posts, see Adam Machanic’s blog post Invitation to Participate in T-SQL Tuesday #001: Date/Time Tricks for details!

@Jamiet