New SQL Azure lab available (How to move data to SQL Azure)

The SQL Azure labs site is a great place to explore and play with SQL Azure! You are probably already using SQL Server and may be working with or wondering about SQL Azure. Often when I want to explore a technology, I find reading a blog post or MSDN article isn’t enough to make me comfortable. I need something more tangible to help me grasp a new tool or offering. The SQL Azure team provides a series of videos and labs to help you explore and master different SQL Azure technologies.

There are a number of labs on the SQL Azure site, but I just want to take a minute to point out one in particular that just went live this week.

The Microsoft Codename “Data Transfer” Lab provides an easy-to-use Web application for importing data into SQL Azure or Blob storage. Learn how to transfer structured data and files into Azure using any standard modern browser. One of the goals for this lab is to take the complexity out of typical operations, such as loading structured data in CSV and Excel files into SQL Azure. The lab service provides simple parsing, type discovery and conversion over files in a wizard-like experience.

This is a new lab, and the SQL Azure team wants to learn from you! They will use your feedback to shape the service in the future! So if you get a pop-up questionnaire please take the time to answer, you could identify a feature to include in the next release! You can also post on the SQL Azure Labs Support Forum to give feedback as well.

Quick SQL Tips – Indexed Views

Although not a new feature, Indexed views can still be a useful tool for increasing query performance. Of course you have to be careful of the trade-offs. Just like indexing a table, indexing a view may speed up a query, but will increase your storage requirements and slow down insert and update operations. So make sure you benchmark performance before and after you add an index to a view.

With that caveat in mind, I’d like to do a review of Indexed Views because of a post I saw from a SQL User having trouble creating a Fulltext index on an indexed view.

When we create a view we simply specify the select statement that will return the data we want displayed in the view.

CREATE VIEW OrderInfo 
AS SELECT od.SalesOrderId, od.productid, od.unitprice, od.orderqty, p.name
FROM Sales.SalesOrderDetail od
Production.product p
ON od.productid =p.productid

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

If you want to add indexes to a view you must make the view schema bound by adding the WITH SCHEMABINDING clause to the CREATE VIEW statement and you must specify the schema for each table specified in the select statement for the view.

CREATE VIEW OrderInfo WITH SCHEMABINDING
AS SELECT od.SalesOrderId, od.productid, od.unitprice, od.orderqty, p.name
FROM Sales.SalesOrderDetail od
Production.product p
ON od.productid =p.productid

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The first index you create on a view must be a unique clustered index. So in this example I create a unique clustered index on the combination of SalesOrderId and ProductId

CREATE UNIQUE CLUSTERED INDEX idx_orderinfo_salesOrderid 
ON orderinfo(SalesOrderid,productid)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

I can now add additional nonclustered indexes as desired to the view

CREATE NONCLUSTERED INDEX idx_unitprice 
ON orderinfo(unitprice)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Now coming back to the question posted on reddit, can you create a fulltext index on the view? The steps for full text indexes changed quite a bit for SQL Server 2008 from SQL Server 2005. The steps listed below are for SQL Server 2008 and higher where full text indexing no longer requires a separate service and is enabled automatically.

In order to create a full text index you first need a full text catalog, unless you have already created one for other fulltext indexes in your database.

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Next I try to create a fulltext index on the product name column, you must specify the name of the column you wish to index and the name of the unique clustered index for the view.

CREATE FULLTEXT INDEX ON dbo.orderinfo(name) 
KEY INDEX idx_orderinfo_salesorderid

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

At this point I receive an error message, because there are restrictions on the key indexes used for creating full text indexes. The key index must be:

• Unique
• Non-nullable
• Single-column
• Online
• Cannot be on a non-deterministic column
• Cannot be on a nonpersisted computed column
• Cannot be a filtered index
• Cannot be based on a column that exceeds 900 bytes

My key index is based on two columns, so I am unable to create a full text index for this view. So can you create a full text index on a view? It depends. If my view above had a key index that met the requirements listed above then yes! If my key index does not meet the requirements I may need to redesign my index or my view so that I can create a key index that meets the requirements.

So we finish with everyone’s favourite answer. It depends. Don’t forget if you know SQL you know SQL Azure, read about the differences between on premise SQL Server and SQL Azure database development and you will find it’s easier than you think.

How to Move Data from One Table to Another

I recently saw a post on Stack Overflow asking how do you copy specific table data from one database to another in SQL Server. It struck me I should share the solution to this with others because it is such a handy trick. Often I set up test data and want to quickly copy it to another table, or a co-worked wants a copy of my data, or I want to copy some data from production to a local database.

If all you want to do is copy data from one table to another in the same database, just use the INSERT clause on the SELECT statement.

INSERT INTO PlayerBackups
SELECT * FROM NhlPlayer

If you do not have a second table and you want to make a quick and dirty backup of some test data, you can create a table based on the data you choose in your select statement.

SELECT * INTO PlayerBackups
FROM NhlPlayer

If you want to move data between tables across databases, you will have to use a fully qualified name

INSERT INTO YourDatabase.YourSchema.PlayerBackups
SELECT * FROM MyDatabase.MySchema.NhlPlayer

If you want to move data across servers, you will need to set up a linked server, this will require working with the DBA because there are authentication issues around linked servers (how will your account log in to the other server? what permissions will you have on the other server?) Once you have a linked server set up, then you can just use the fully qualified name to specify the server name.

INSERT INTO YourServer.YourDatabase.YourSchema.PlayerBackups
SELECT * FROM MyServer.MyDatabase.MySchema.NhlPlayer

I am always forgetting the syntax for these commands, so thought I would share them. Don’t forget, if you know SQL, you know SQL Azure! Try it out now

SQL Azure Federations (Because you have a lot of data)

Last week I explained SQL Azure Essentials for the Database Developer where I covered some of the differences between working in SQL Azure instead of an on-premise database from the perspective of a SQL Server database developer. Today I will continue in that vein and explain one of the upcoming features for SQL Azure: Federations.

When you first create a SQL Azure database, you are asked if you want to create a Web or a Business Database. A web database can have a maximum size of 1GB or 5 GB.  A Business database can be up to 50 GB. But in this day and age 50 GB is not that big. There must be a way to use SQL Azure for databases larger than 50 GB! One of the great benefits of cloud computing is the ability to handle peak loads and peak volumes by increasing your usage as needed. We can do that with Windows Azure using the pay as you go model, but how do we get that same sort of flexibility for our database in SQL Azure? The answer is federations! With federations you can expand and contract the number of nodes that service the database. Your application can scale to have 10 to 100s of SQL Azure databases and provide you with data storage beyond the limits of a single SQL Azure database.

The concept is very similar to partitioned tables. When you set up a partitioned table in SQL Server, you choose one of the columns on the table to use to partition the data into different data files. For example you could partition an order table based on country id. All orders with country ids of 1 or 2 go into partition A, all orders with country ids between 3 and 9 go into partition B, all orders with country ids from 10 to 20 go into partition C and so on.

You would end up with something like this

OrderId	CustomerId	OrderTotal	OrderDate	CountryId	Country	Partition
100	402	500.00	1-Jan-2010	1	Canada	A
101	251	300.25	1-Jan-2010	1	Canada	A
102	406	199.99	1-Jan-2010	5	England	B
103	406	50.00	1-Jan-2010	5	England	B
104	397	75.00	1-Jan-2010	6	France	B
105	216	50.00	1-Jan-2010	2	USA	A
106	411	19.99	1-Jan-2010	10	China	C
107	203	315.00	2-Jan-2010	2	USA	B
108	87	100.00	2-Jan-2010	10	China	C

We usually partition a table in a on-premise SQL Server database to speed up queries (since they may only need to search through one partition and data file depending on the query), or to provide more backup options since you can back up one partition at a time. We can also merge two partitions together or split a single partition into two partitions using the SPLIT and MERGE statements.

In SQL Azure, you create one or more Federations in your database. A federation represents all the data being partitioned and contains one or more federation members or shards (The federation member is like a partition in a partitioned table.) The federation members are stored across different nodes.

So if we had a table called Orders in SQL Azure and we wanted to federate that table we would do the following

1. Create a federation in our SQL Azure database and specify we want to be able to divide up our records based on a range of integer values

CREATE FEDERATION order_federation (c_id RANGE BIGINT)

2. Create the tables we want to partition in our federation. For each table we must specify which column contains the integer values we should use to divide up the records across federation members

USE FEDERATION order_federation

CREATE TABLE orders (orderid INT NOT NULL, country_id BIGINT NOT NULL, …) FEDERATED ON (c_id = country_id)

3. Split our federation into federation members

ALTER FEDERATION order_federation SPLIT AT (c_id=3)

ALTER FEDERATION order_federation SPLIT AT (c_id=10)

This will split all records with a country id of 1 or 2 into one federation member, and all records with a country id of 3 to 9 into a second federation member and all records with a country id of 10 or higher into a third federation. You can continue to use split commands to create as many federation members as you want.

All the data is still available as you split and merge federation members!

Now what’s truly powerful about this concept on SQL Azure is that the federation scales out across multiple nodes which gives your virtually unlimited scalability in your application. You can build applications that scale from 10s to 100s of SQL Azure databases. You can also change this on demand as needed because you can repartition without downtime.

Federation Technology will be available in the final quarter of calendar year 2011 in all SQL Azure geographies.

Todays My 5

5 Places to learn more about SQL Azure federations

1. SQL Azure Federations: Building Scalable, Elastic, and Multi-tenant Database Solutions
2. Cihan Biyikoglu’s Blog post Building Scalable Database Solution with SQL Azure – Introducing Federation in SQL Azure
3. Recording from PDC Building Scale-Out Database Solutions on SQL Azure
4. Recording from TechEd 2011 Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations
5. Building Scalable Database Solutions Using SQL Azure – Scale-out techniques such as Sharding or Horizontal Partitioning

This post is also on the Canadian Solution Developer blog

SQL Azure Essentials for the Database Developer

I admit it, I am a SQL geek. I really appreciate a well designed database. I believe a good index strategy is a thing of beauty and a well written stored procedure is something to show off to your friends and co-workers. What I, personally, do not enjoy, is all the administrative stuff that goes with it. Backup & recovery, clustering, installation are all important but, it’s just not my thing. I am first and foremost a developer. That’s why I love SQL Azure. I can jump right in to the fun stuff: designing my tables, writing stored procedures and writing code to connect to my awesome new database, and I don’t have to deal with planning for redundancy in case of disk failures, and keeping up with security patches.

There are lots of great videos out there to explain the basics: What is SQL Azure,  Creating a SQL Azure Database. In fact there is an entire training kit to help you out when you have some time to sit down and learn. I’ll be providing a few posts over the coming weeks to talk about SQL Azure features and tools for database developers. What I’d like to do today is jump right in and talk about some very specific things an experienced database developer should be aware of when working with SQL Azure.

You can connect to SQL Azure using ANY client with a supported connection library such as ADO.NET or ODBC

This could include an application written in Java or PHP. Connecting to SQL Azure with OLEDB is NOT supported right now. SQL Azure supports tabular data stream (TDS) version 7.3 or later. There is a JDBC driver you can download to connect to SQL Azure. Brian Swan has also written a post on how to get started with PHP and SQL Azure. .NET Framework Data Provider for SQLServer (System.Data.SqlClient) from .NET Framework 3.5 Service Pack 1 or later can be used to connect to SQL Azure and the Entity Framework from .NET Framework 3.5 Service Pack 1 or later can also be used with SQL Azure.

You can use SQL Server Management Studio (SSMS) to connect to SQL Azure

In many introduction videos for SQL Azure they spend all their time using the SQL Azure tools. That is great for the small companies or folks building a database for their photography company who may not have a SQL Server installation. But for those of us who do have SQL Server Management Studio, you can use it to manage your database in SQL Azure. When you create the server in SQL Azure, you will be given a Fully Qualified DNS Name. Use that as your Server name when you connect in SSMS. For those of you in the habit of using Server Explorer in Visual Studio to work with the database, Visual Studio 2010 allows you to connect to a SQL Azure database through Server Explorer.

 

The System databases have changed

• Your tempdb is hiding – Surprise, no tempdb listed under system databases. That doesn’t mean it’s not there. You are running on a server managed by someone else, so you don’t manage tempdb. Your session can use up to 5 GB of tempdb space, if a session uses more than 5GB of space in tempdb it will be terminated with error code 40551.
• The master database has changed – When you work with SQL Azure, there are some system views that you simply do not need because they provide information about aspects of the database you no longer manage. For example there is no sys.backup_devices view because you don’t need to do backups (if you are really paranoid about data loss, and I know some of us are, there are ways to make copies of your data). On the other hand there are additional system views to help you manage aspects you only need to think about in the cloud. For example sys.firewall_rules is only available in SQL Azure because you define firewall rules for each SQL Azure server but you wouldn’t do that for a particular instance of SQL Server on premise.
• SQL Server Agent is NOT supported – Did you notice msdb is not listed in the system databases. There are 3rd party tools and community projects that address this issue. Check out SQL Azure Agent on Codeplex to see an example of how to create similar functionality. You can also run SQL Server Agent on your on-premise database and connect to a SQL Azure database.

You don’t know which server you will connect to when you execute a query

When you create a database in SQL Azure there are actually 3 copies made of the database on different servers. This helps provide higher availability, failover and load balancing. Most of the time it doesn’t matter as long as we can request a connection to the database and read and write to our tables. However this architecture does have some ramifications:

• No  4 part names for queries –  Since when you execute a query you do not know which server it will use, 4 part queries that specify the server name are not allowed.
• No USE command or cross database queries – When you create two databases there is no guarantee that those two databases will be stored on the same physical server. That is why the USE command and cross database queries are not supported.

Every database table must have a clustered index

You can create a table without a clustered index, but you won’t be able to insert data into the table until you create the clustered index. This has never affected my database design because I always have a clustered index on my tables to speed up searches.

Some Features are not currently supported

• Integrated Security – SQL Server authentication is used for SQL Azure, which makes sense given you are managing the database but not the server.
• No Full Text Searches – For now at least, full text searches are not supported by SQL Azure. If this is an issue for you, there is an interesting article in the TechNet Wiki on a .NET implementation of a full text search engine that can connect to SQL Azure.
• CLR is not supported – You have access to .NET through Windows Azure, but you can’t use the .NET to define your own types and functions, but you can still create your own functions and types with T-SQL.

You can connect to SQL Azure from your Business Intelligence Solutions

• SQL Server Analysis Services – Starting with SQL Server 2008 R2 you can use SQL Azure as a data source when running SQL Server Analysis Services on-premise.
• SQL Server Reporting Services – Starting with SQL Server 2008 R2, you can use SQL Azure as a data source when running SQL Server Reporting Services on-premise.
• SQL Server Integration Services – You can use the ADO.NET Source and Destination components to connect to SQL Azure, and in SQL Server 2008 R2 there was a “Use Bulk Insert” option added to the Destination to improve SQL Azure performance.

Today’s My 5 of course has to relate to SQL Azure!

5 Steps to get started with SQL Azure

1. Create a trial account and login
2. Create a new SQL Azure server – choose Database | Create a new SQL Azure Server and choose your region (for Canada North Central US is the closest)
3. Specify an Administrator account and password and don’t forget it! – some account names such as admin, administrator, and sa are not allowed as administrator account names
4. Specify the firewall rules – these are the IP Addresses that are allowed to access your Database Server, I recommend selecting the “Allow other Windows Azure services to access this server” so you can use Windows Azure services to connect to your database.
5. Create a Database and start playing – You can either create the database using T-SQL from SSMS, or using the Create New Database in the Windows azure Platform tool which gives you a wizard to create the database.

Now you know the ins and outs, go try it out and come back next week to learn more about life as a database developer in SQL Azure

This post is also featured on the Canadian Solution Developer Blog

Why You Should Never Use DATETIME Again!

Dates, we store them everywhere, DateOrdered, DateEntered, DateHired, DateShipped, DateUpdated, and on and on it goes. Up until and including SQL Server 2005, you really didn’t have much choice about how you stored your date values. But in SQL Server 2008 and higher you have alternatives to DateTime and they are all better than the original.

DATETIME stores a date and time, it takes 8 bytes to store, and has a precision of .001 seconds

In SQL Server 2008 you can use DATETIME2, it stores a date and time, takes 6-8 bytes to store and has a precision of 100 nanoseconds. So anyone who needs greater time precision will want DATETIME2. What if you don’t need the precision? Most of us don’t even need milliseconds. So you can specify DATETIME2(0) which will only take 6 bytes to store and has a precision of seconds. If you want to store the exact same value you had in DATETIME, just choose DATETIME2(3), you get the same precision but it only takes 7 bytes to store the value instead of 8. I know a lot of you are thinking, what’s one byte, memory is cheap. But it’s not a question of how much space you have on your disk. When you are performance tuning, you want to store as many rows on a page as you can for each table and index. that means less pages to read for a table or query, and more rows you can store in cache. Many of our tables have multiple date columns, and millions of rows. That one byte savings for every date value in your database is not going to make your users go ‘Wow everything is so much faster now’, but every little bit helps.

If you are building any brand new tables in SQL Server 2008, I recommend staying away from DATETIME and DATETIME2 altogether. Instead go for DATE and TIME. Yes, one of my happiest moments when I first started learning about SQL Server 2008 was discovering I could store the DATE without the time!! How many times have you used GETDATE() to populate a date column and then had problems trying to find all the records entered on ‘05-JUN-06’ and got no results back because of the time component. We end up truncating the time element before we store it, or when we query the date to ignore the time component. Now we can store a date in a column of datatype DATE. If you do want to store the time, store that in a separate column of datatype TIME. By storing the date and time in separate columns you can search by date or time, and you can index by date and or time as well! This will allow you to do much faster searches for time ranges.

Since we are talking about the date and time datatypes, I should also mention that there is another date datatype called DATETIMEOFFSET that is time zone aware. But that is a blog for another day if you are interested.

Here is a quick comparison of the different Date and Time Data types,

Datatype	Range	Precision	Nbr Bytes	User Specified Precision
SMALL DATETIME	1900-01-01 to 2079-06-06	1 minute	4	No
DATETIME	1753-01-01 to 9999-12-31	.001 seconds	8	No
DATETIME2	0001-01-01 to 9999-12-31 23:59.59.9999999	100 ns	6-8	Yes
DATE	0001-01-01 to 9999-12-31	1 day	3	No
TIME	00:00:00.0000000 to 23:59.59.9999999	100 ns	3-5	Yes
DATETIMEOFFSET	0001-01-01 to 9999-12-31 23:59.59.9999999	100 ns	8-10	Yes

Today’s My 5 is of course related to the Date and Time datatypes.

My 5 Important Date functions and their forward and backwards compatibility

1. GETDATE() – Time to STOP using GETDATE(), it still works in SQL Server 2008, but it only returns a precision of milliseconds because it was developed for the DATETIME datatype.
2. SYSDATETIME() – Time to START using SYSDATETIME(), it returns a precision of nanoseconds because it was developed for the DATETIME2 datatype and it also works for populating DATETIME columns.
3. DATEDIFF() – This is a great little function that returns the number of minutes, hours, days, weeks, months, or years between two dates, it supports the new date datatypes.
4. ISDATE() – This function is used to validate DATETIME values. It returns a 1 if you pass it a character string containing a valid date. However if you pass it a character string representing a datetime that has a precision greater than milliseconds it will consider this an invalid date and will return a 0.
5. DATEPART() – This popular function returns a portion of a date, for example you can return the year, month, day, or hour. This date function supports all the new date datatypes.

Also one extra note, because I know there are some former Oracle developers who use this trick. If you have any select statements where you select OrderDate+1 to add one day to the date, that will not work with the new date and time datatypes. So you need to use the DATEADD() function.

This blog is also posted to the Canadian Solution Developer

Table Valued Parameters Save Code and Traffic

Do you use Stored Procedures? I hope so, they are great for performance and security. But, on early versions of SQL Server you could only pass one record at a time to a stored procedure. So those of us who nobly followed best practice and used stored procedures for inserting, updating, and deleting records often found ourselves having to write loop logic to call the same stored procedure over and over to insert, update, or delete multiple records. With Table Valued Parameters you can pass a set of records to a stored procedure. Yay! This feature was long overdue and is one of my favourite developer features added in SQL Server 2008.

In SQL Server 2005, if you had a table that contained a list of Hockey Players, and you wanted to load three records into that table using a stored procedure, your code would look something like this:

CREATE TABLE HockeyPlayer
(id       INT                 NOT NULL,
name VARCHAR(50) NULL,
team  VARCHAR(50) NULL)
GO
 
CREATE PROCEDURE InsertPlayer
(@id int, @name VARCHAR(50), @team VARCHAR(50))
AS
INSERT HockeyPlayer (id, name, team)
VALUES (@id, @name, @team)
GO
 
EXECUTE InsertPlayer 1,’Michel’,’Ottawa’
EXECUTE InsertPlayer 2,’Mike’,’Toronto’
EXECUTE InsertPlayer 3,’Alexei,’Vancouver’

We had to execute the stored procedure once for every record we wanted to insert.

In SQL Server 2008, they added a new feature called Table Valued Parameters. This feature allows you to pass a one or more records to the stored procedure. In order to create a stored procedure using Table Valued Parameters, you must perform two steps.

1. Create a Table Type in the database that defines the structure of the records you will pass to the stored procedure.

CREATE TYPE PlayerTableType AS TABLE
(id INT, name VARCHAR(50), team VARCHAR(50))

2. Create your stored procedure and declare an input parameter with the type set to the Table Type you created. This input variable must be declared as READONLY. (I know my stored procedure isn’t very useful, but you get the idea)

CREATE PROCEDURE InsertManyPlayers (@PlayerRecords PlayerTableType READONLY)
AS BEGIN
   INSERT INTO HockeyPlayer (id, name, team)
   SELECT * FROM @PlayerRecords
END

Now you have a stored procedure that can accept a record set. Now how do you call it?

Calling your stored procedure from T-SQL

To call a stored procedure with a table valued parameter from T-SQL you have to:

1. Create a variable based on your Table Type
2. Populate that table variable with the records you want to pass to your stored procedure.
3. Execute your stored procedure, passing in your table variable

DECLARE @MyPlayers PlayerTableType

INSERT INTO @MyPlayers
VALUES
(4,’Ken’,’Calgary’),
(5,’Neil’,’Edmonton’),
(6,’Mitch’,’Toronto’)

EXECUTE InsertManyPlayers @MyPlayers

Calling your stored procedure from .NET

You can pass a DataTable, a Data Reader or any other object that implements the iList interface to a table valued parameter. When you declare the command parameter in .NET you must specify SqlDbType as Structure, and TypeName as the name of your table type, then execute the call to your stored procedure.

In the example below, I create a data table with the same structure as the table type and populate the data table with the records I want to send to the stored procedure.

Dim dt As New DataTable("player")
dt.Columns.Add("id", System.Type.GetType("System.Int32"))
dt.Columns.Add("name",System.Type.GetType("System.String")
dt.Columns.Add("team",System.Type.GetType("System.String"))

Dim newRow As DataRow = dt.NewRow()
newRow("id") = 7
newRow("name") = "Chris"
newRow("team") = "New York"
dt.Rows.Add(newRow)

newRow("id") = 8
newRow("name") = "Chris"
newRow("team") = "New York"
dt.Rows.Add(newRow)

The DataTable implements the iList interface, so I can pass this data table to a table valued parameter in a stored procedure. The code below defines the SqlParameter that will pass the data table and executes the stored procedure.

Dim Cmd As New SqlCommand("InsertManyPlayers", myCon)
Cmd.CommandType = System.Data.CommandType.StoredProcedure
Dim tvp As New SqlParameter
tvp.ParameterName = "@PlayerRecords"
tvp.SqlDbType = SqlDbType.Structured
tvp.TypeName = "dbo.PlayerTableType"
tvp.Value = dt Cmd.Parameters.Add(tvp)

myCon.Open()
Cmd.ExecuteNonQuery()

You have now passed multiple records to a stored procedure in the database with a single call using the magic of table valued parameters. SQL rocks .

Of course I can’t forget My 5. This week:

My 5 Places you can go learn something new about SQL Server (in no particular order)

1. SQLTeach May 30 – June 3, 2011, Montreal, driving distance for Eastern Canada and lots of great content!
2. TechEd North America May 16-19, Atlanta, don’t forget most of the content is also available online after the conference, even if you did not attend the show!
3. SQLPass October 11-14th, Seattle, fabulous SQL conference for those on the West Coast.
4. Greg Low’s blog (this guy knows his SQL Server, and he is a great presenter if you ever get a chance to catch one of his sessions)
5. SQL Server Developer Center has links to lots of great resources including SQL Server compare editions, to compare features, I frequently get asked what is the difference between Express and Standard, between Standard and Enterprise, this is where I always look it up!

I know many of us are just upgrading to SQL Server 2008 which is why I brought up this particular feature, but I am curious, what do you want to read about in the blog, would you like to be reminded of what is in SQL 2008, or do you want to know what’s new in Denali? Both? Let us know!