An Alternative Method of Creating a ResultSet in SQL

Happy Mardi Gras!

I’ve got a few ideas for posts that take more time than the 5 minutes I can spare each day. However today’s a quick one as I’m snowed under with work and the joys of house hunting. I came across this in an old stored proc:

</p><p>select top 0 * into Sales.SalesPersons from sales.SalesPerson<br />

and after a bit of Googling I found out it’s a way of creating a duplicate of the columns in the table. What it does not do is include indexes, constraints and keys. So when is it useful? Well the stored proc it is used in creates a table and switches a partition into the table then drops it. This is to speed up deletes (yes there are checks from dropping multiple runs inside one partition, I am disregarding this for this post). I guess it takes the pragmatic approach that the data would not be in there if there were any constraint violations, we’re not querying this table so indexes are not required and keys are not required for reference or uniqueness.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s