Search results matching tags 'T-SQL Tuesday' and 'SQL Server 2008'

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 #006: "What About BLOB?"

Mike C — Mon, 03 May 2010 23:29:00 GMT

Invitation for T-SQL Tuesday #006: "What About BLOB?"

It's getting warm outside just in time for the May T-SQL Tuesday blog party. I’ll be your host this month--and the secret word for this T-SQL Tuesday is "Large Object (LOB) Data".

What’s T-SQL Tuesday?

About 6 months ago Adam Machanic (Twitter: @AdamMachanic) decided to throw a worldwide blog party. Every month Adam picks a host to post the topic and the rules. Everyone who wants to participate publishes a blog entry on the topic of the day, all during the same 24 hour time period.

Here’s the shortlist of the roundups from the first 5 T-SQL Tuesdays:

#001 Adam Machanic asked for your favorite Date/Time tricks
#002 Adam followed up by asking for your favorite Puzzling Situations
#003 Rob Farley made the commitment with his topic: Relationships
#004 Mike Walsh kicked asked for your input on IO
#005 Aaron Nelson asked us to report on "Reporting"

And that brings us up-to-date for #006: LOB Data. Anyone can participate - just post a blog entry related to the topic and link it back to this page.

To participate your post must go live between 00:00:00 GMT on Tuesday the 11th of May and 00:00:00 GMT on Wednesday the 12th. See the table below for some quick conversions to other time zones.

Time Zone	Start	End
GMT	2010-05-11 00:00:00	2010-05-12 00:00:00
EST	2010-05-10 20:00:00	2010-05-11 20:00:00
PST	2010-05-10 17:00:00	2010-05-11 17:00:00

What Is LOB Data?

MSDN conveniently defines Large Object ("LOB") data types for us: "LOB data types are those that exceed the maximum row size of 8 kilobytes (KB)."

There have been a several improvements in LOB data functionality in SQL Server 2008 (there were even some in SQL Server 2005). In 2008 the XML, GEOMETRY, GEOGRAPHY data types can all hold 2.1 GB of data. CLR data types can also hold up to 2.1 GB of data. So the question of the day is how do you use LOB data? Here are a few possible starting points:

LOB data storage, optimization, limitations, "under-the-hood"
Indexing, querying, optimization, tricks, tips, performance tuning of LOB data
Interesting uses/projects for LOB data types:

The MAX data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX))
XML
GEOMETRY/GEOGRAPHY (spatial)
CLR data types

FILESTREAM hints, tips, tricks, .NET SqlFileStream Class

The only rule is that your topic has to involve SQL Server's LOB data types in some form. If you want to demonstrate handling LOB data in .NET, for instance, go for it. If you want to demonstrate Oracle LOB data handling, this might not be the place to do it (although a comparison of the two might be interesting...) :)

Here Come the Lawyers...

Once again, please note the time is in GMT (aka UTC). As other hosts have mentioned, feel free to write your post ahead of time and schedule it for publication. The rules are based on when the post goes live, not when it is written. The rules for T-SQL Tuesday haven’t changed, but I’ll cover them again here:

Your post must go live between 00:00:00 GMT on Tuesday the 11th of May and 00:00:00 GMT on Wednesday the 12th (see my handy-dandy time zone conversion table above for more info). If your post doesn’t go live in the time limits, it won’t be included in the round-up post.
Your post must link back to this post (by trackback or comment).
"T-SQL Tuesday #006" MUST be in the title of the post.
It is your responsibility to verify the trackback or comment appears here on this post. If you don’t see your trackback add your own comment with a link back to your T-SQL Tuesday post and it will be included in the roundup.

Note that some bloggers have reported an issue that their blog sites don’t properly generate a trackback when they preschedule a post for publication. After you publish your post, be sure to check here to see if a trackback was generated. Feel free to post a comment here after your post goes live if you just want to play it safe.

Twitter

A lot of the T-SQL Tuesday bloggers and bloggees are on Twitter. Follow the hashtag #TSQL2sDay and when your post goes live, tweet a link to it with that tag.

Want To Host?

It's easy to become a host (hey, even I did it!):

1. Participate in at least two events.

2. Let Adam Machanic know you want to host.

You can tweet Adam or leave a comment on his blog, as described in his first T-SQL Tuesday Invitation.

If you have any questions, concerns or general comments, leave me a message on this post. I'll respond to your post and/or make updates to this post as required. Have fun thinking of a topic and I’m looking forward to all the great posts!

Sponsors

This episode of T-SQL Tuesday was brought to you by the letters "M", "A", "X", and the number 2,147,483,647.

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 #001: Exploring "Fuzzy" Interval Islands Without Using SQLCLR

Peso — Tue, 08 Dec 2009 22:20:00 GMT

In response to Adam's new series of T-SQL Tuesday, I wanted to write that there are faster ways to get the data in a set-based manner without resorting to SQLCLR.
http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx

Many companies today still think of SQLCLR's as security risks, so I wanted to rewrite Adam's set-based code to a more efficient algorithm.
On my computer, Adam's code run in about 25 seconds. These two new algorithms below, runs in less than a second each. Which one to choose is how you interpret the definition Adam wrote. Also, this is not a blog post to make someone feel bad, it is a blog post to show there are other ways to write code, with better performance. You just have to think a little different. If someone is interested in how this algorithm works, please ask nice and I'll probably write a more in-depth blog post about the algorithm.
Complaining in the comments doesn't work.

Take 1:

DECLARE @Interval INT = 7

;WITH cteSource
AS (
    SELECT ProductID,
            TransactionDate,
            ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID
    FROM    Production.TransactionHistory
), cteMatch
AS (
    SELECT     s1.ProductID,
                s1.TransactionDate AS FromDate,
                s2.TransactionDate AS ToDate
    FROM        cteSource AS s1
    INNER JOIN cteSource AS s2 ON s2.ProductID = s1.ProductID
    WHERE       s1.recID = s2.recID - 1
), cteYak
AS (
        SELECT     ProductID,
                    FromDate,
                    ToDate,
                    y - ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY FromDate) AS grp
        FROM        (
                        SELECT ProductID,
                                FromDate,
                                ToDate,
                                CASE
                                    WHEN DATEDIFF(DAY, FromDate, ToDate) <= @Interval
                                        THEN ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY FromDate)
                                    ELSE NULL
                                END AS y
                        FROM    cteMatch
                    ) AS d
        WHERE       y IS NOT NULL
)
SELECT      ProductID,
            MIN(FromDate) AS FromDate,
            MAX(ToDate) AS ToDate
FROM        cteYak
WHERE       DATEDIFF(DAY, FromDate, ToDate) <= @Interval
GROUP BY    ProductID,
            grp
ORDER BY    ProductID,
            MIN(FromDate)

This runs in less than a second.

Take2:

DECLARE @Interval INT = 7

;WITH cteSource
AS (
    SELECT ProductID,
            TransactionDate,
            ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID
    FROM    Production.TransactionHistory
), cteMatch
AS (
    SELECT      s1.ProductID,
                s1.TransactionDate AS FromDate,
                s2.TransactionDate AS ToDate
    FROM        cteSource AS s1
    INNER JOIN cteSource AS s2 ON s2.ProductID = s1.ProductID
    WHERE       s1.recID = s2.recID - 1
), cteYak
AS (
    SELECT      ProductID,
                FromDate,
                ToDate,
                CASE
                    WHEN DATEDIFF(DAY, FromDate, ToDate) <= @Interval THEN 1
                    ELSE -1
                END * ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY FromDate) AS recID
    FROM        cteMatch
), cteFinal
AS (
    SELECT      ProductID,
                MIN(FromDate) AS StartDate,
                DATEADD(DAY, @Interval, MAX(ToDate)) AS EndDate,
                1 + MAX(recID) AS recID
    FROM        (
                    SELECT ProductID,
                            FromDate,
                            ToDate,
                            recID - ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY FromDate) AS grp,
                            recID
                    FROM    cteYak
                    WHERE   recID > 0
                ) AS d
    GROUP BY    ProductID,
                grp

    UNION ALL

    SELECT      ProductID,
                FromDate AS StartDate,
                DATEADD(DAY, @Interval, FromDate) AS EndDate,
                ABS(recID) AS recID
    FROM        cteYak
    WHERE       recID < 0
)

SELECT      ProductID,
            MIN(StartDate) AS StartDate,
            MAX(EndDate) AS EndDate
FROM        cteFinal
GROUP BY    ProductID,
            recID

This also runs in less than a second. And produces the exact same result as Adam's code.

//Peter