|
|
This weeks tip:
What’s the fastest animal on earth? The Cheetah.
What’s the fastest way to get a definition of a unencrypted stored procedure? Use SP_HELPTEXT
If you don’t know, SP_HELPTEXT returns, in the results pane (use ALT+T to put the results to text), the TSQL of the following:
- unencrypted stored procedure
- view
- trigger
- user-defined function
- user-defined rule
- default
- computed column
- CHECK constraint
- system object
Here’s an example from the AdventureWorks database…..
SP_HELPTEXT uspGetBillOfMaterials
Here are some related posts from the intraweb:
Previous Tips O’ the Week
This weeks tip:
Often I’ll need to copy a tables structure, i.e. all the columns and data types, to a new table. Some examples of times when this is needed are when you are migrating data, staging data, etc. A quick and easy way to do this is to select an empty record set into a new table. For Example:
Select * into Table2 from Table1 where 1 = 2
Obviously one never equals two, so there will be no rows affected, but this will create Table2 with the same columns as Table1.
Note this method will include:
- All columns
- All data types and sizes
- Identity columns
- Nullability of all columns
Note this method will NOT include:
- Indexes
- Foreign keys
- Constraints
- Defaults
- Triggers
Here’s what it looks like in SSMS with the AdventureWorks database – I copied the Production.BillOfMaterials table to a new table called Production.BillOfMaterials2:
Here are some related posts from the intraweb:
Previous Tips O’ the Week
Sponsor: PASS
Date: June 21st, 2011
Time: 3:00 PM – 5:00 PM
Location: 8300 Norman Center Drive, 9th Floor, Bloomington, MN 55437
Live Meeting:
Agenda:
- 2:45-3:00 : Registration / hospitality / networking
- 3:00-3:15 : Announcements etc.
- 3:15-3:45 : “11 Pages that Changed the World” (relational DB’s) – Mark Knutson
- 3:45-4:45 : NoSQL – Dan McCreary
- 4:45-5:00 : Closing comments/prize giveaways
Presentation:
11 Pages that Changed the World
Mark Knutson, Hennepin Faculty Associates
In 1970, the widely implemented database products were based either on a network or an inverted tree structure. E. F. Codd, an IBM research mathematician, presented a paper to the Association of Computing Machinery proposing a database management system based on mathematical objects called relations.
Mark will briefly review the topical coverage of the paper, including the formal definition of a relation. Mark will also place the paper in historical context and review how IBM’s response to it shaped the DBMS product landscape that we see today.
The discussion will be led by Mark Knutson who began his career programming IBM mainframes. Mr. Knutson is now programming team lead and database administrator at Hennepin Faculty Associates (The doctors of Hennepin County Medical Center), presiding over a modest, but fully up to date, SQL Server system.
Presentation:
NoSQL
Dan McCreary
This presentation will discuss the "NoSQL movement", a new set of data solutions that leverage non-relational data stores. These data stores are frequently designed to leverage thousands of commodity processors, are "schema free", use simple key-value stores or document stores and are often associated with web-scale "BigData" challenges. This presentation will cover the key business factors and technology issues driving this movement.
Dan McCreary is a Minneapolis-based data consultant with a focus on helping organization use innovative technologies. Dan has over 25 years experience in the IT industry including working for Bell Labs and Steve Jobs at NeXT computer, and is an "invited expert" of the world-wide web consortium. Dan was the founder of Eagan-based Integrity Solutions which employed over 75 people before being sold to Vance Opperman’s KeyTech. Dan has also consulted for many Minnesota organizations including the CriMNet project, the BCA, the Department of Pubic Safety, the Department of Education, the Department of Revenue, Thrivent Financial and Syntactica. Dan is currently working on projects for the US Department of State, NueMeta and JPMorgan. He has a BA in Physics from Carleton College and a MSEE from the University of Minnesota
This weeks tip:
In the programming world, there are a few times when dealing with uniqueidentifiers and/or GUIDs that you need an empty GUID – a GUID of all zeros (in fact .NET has a Guid.Empty field).
In TSQL, there are a few ways to get ‘00000000-0000-0000-0000-000000000000’
- You could bookmark this page and every time you need it, come back here and copy and paste the value above into all your code (fine by me, but not the most efficient).
- You could copy and paste any GUID into text pad and line up all the 0s below it, then paste that value into your code (that’s how I used to do it).
- You could replicate the 0s a few times, throw in a few ‘-‘ and cast the entire thing as a uniqueidentier like this:
Cast(replicate('0', 7) + '0-' + replicate('0', 4) + '-' + replicate('0', 4) + '-' + replicate('0', 4) + '-' + replicate('0', 12) as UNIQUEIDENTIFIER)
Or….
The way I recommend, CAST 0 as a binary, then CAST that binary as a uniqueidentifier. This seems the most versatile and easiest to remember –> turn 0 into a binary, then turn that into a uniqueidentifier, you don’t have to count zeros, or line up any dashes in notepad. Here is the syntax:
Select CAST(CAST(0 as BINARY) as UNIQUEIDENTIFIER)
Here are some related posts from the intraweb:
Previous Tips O’ the Week
Sponsor: PASS
Date: June 21st, 2011
Time: 3:00 PM – 5:00 PM
Location: 8300 Norman Center Drive, 9th Floor, Bloomington, MN 55437
Live Meeting:
Agenda:
- 2:45-3:00 : Registration / hospitality / networking
- 3:00-3:15 : Announcements etc.
- 3:15-3:45 : “11 Pages that Changed the World” (relational DB’s) – Mark Knutson
- 3:45-4:45 : NoSQL – Dan McCreary
- 4:45-5:00 : Closing comments/prize giveaways
Presentation:
11 Pages that Changed the World
Mark Knutson, Hennepin Faculty Associates
In 1970, the widely implemented database products were based either on a network or an inverted tree structure. E. F. Codd, an IBM research mathematician, presented a paper to the Association of Computing Machinery proposing a database management system based on mathematical objects called relations.
Mark will briefly review the topical coverage of the paper, including the formal definition of a relation. Mark will also place the paper in historical context and review how IBM’s response to it shaped the DBMS product landscape that we see today.
The discussion will be led by Mark Knutson who began his career programming IBM mainframes. Mr. Knutson is now programming team lead and database administrator at Hennepin Faculty Associates (The doctors of Hennepin County Medical Center), presiding over a modest, but fully up to date, SQL Server system.
Presentation:
NoSQL
Dan McCreary
This presentation will discuss the "NoSQL movement", a new set of data solutions that leverage non-relational data stores. These data stores are frequently designed to leverage thousands of commodity processors, are "schema free", use simple key-value stores or document stores and are often associated with web-scale "BigData" challenges. This presentation will cover the key business factors and technology issues driving this movement.
Dan McCreary is a Minneapolis-based data consultant with a focus on helping organization use innovative technologies. Dan has over 25 years experience in the IT industry including working for Bell Labs and Steve Jobs at NeXT computer, and is an "invited expert" of the world-wide web consortium. Dan was the founder of Eagan-based Integrity Solutions which employed over 75 people before being sold to Vance Opperman’s KeyTech. Dan has also consulted for many Minnesota organizations including the CriMNet project, the BCA, the Department of Pubic Safety, the Department of Education, the Department of Revenue, Thrivent Financial and Syntactica. Dan is currently working on projects for the US Department of State, NueMeta and JPMorgan. He has a BA in Physics from Carleton College and a MSEE from the University of Minnesota
I’m starting a new series of posts that will have a TSQL tip. Some of them will be simple, some will be complicated, but hopefully they will all be helpful.
This weeks tip:
When using SQL 2008 use the feature that allows you to declare and set your variables in the same line. Here’s an example of how to do it:
Declare @tester varchar(100) = ‘This saves time’
Note – This saves a ton of time for debugging stored procedures – any default value can be left as is.
Here are some related posts from the blog-o-sphere:
|
|
