Posts Tagged ‘TSQL’

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.

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.

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.

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.

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

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.

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 calledDATETIMEOFFSET that is time zone aware. But that is a blog for another day if you are interested.

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.

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 Parametersyou 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:

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.

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)

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.

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.

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!

Don't miss a post

Enter your email address to subscribe to this blog and receive notifications of new posts by email.