TSQL | HockeyGeekGirl

Posts Tagged ‘TSQL’

13 Sep

Quick SQL Tips – Indexed Views

Posted by susanibach in Uncategorized. Tagged: Full Text Index, Indexed View, SQL Server, TSQL. Leave a comment

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; }

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

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)

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

CREATE NONCLUSTERED INDEX idx_unitprice 
ON orderinfo(unitprice)

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

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

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.

6 Sep

How to Move Data from One Table to Another

Posted by susanibach in Uncategorized. Tagged: CREATE TABLE, INSERT, SQL Server, TSQL. Leave a comment

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

22 Jun

Why You Should Never Use DATETIME Again!

Posted by susanibach in Uncategorized. Tagged: best practice, Datatype, Date, SQL Server, Storage, TSQL. Leave a comment

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

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.
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.
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.
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.
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

9 May

Table Valued Parameters Save Code and Traffic

Posted by susanibach in Uncategorized. Tagged: Code, Example, SQL Server, TSQL. Leave a comment

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:

Create a variable based on your Table Type
Populate that table variable with the records you want to pass to your stored procedure.
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 Smile .

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)

SQLTeach May 30 – June 3, 2011, Montreal, driving distance for Eastern Canada and lots of great content!
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!
SQLPass October 11-14th, Seattle, fabulous SQL conference for those on the West Coast.
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)
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!

HockeyGeekGirl Developer Advocate, Geek & Runner