SQL 2008 filtered indexes - there are so many cool new features available in sql server 2008 that many a DBA are excited. as an index-tuning man i've been intrigued by filtered indexes which essentially allow you to slap a WHERE clause on an index making it small and focused on a strategic subset of the tables data. you know, the sort of thing that would have required an indexed view in the past. in order to test them out i created a simple table in a scratch database and filled it with data using DataDude. CREATE TABLE [dbo].[Sales]( [Sale_ID] [int] IDENTITY NOT NULL, [Complete] [bit] NOT NULL, [Location] [int] NOT NULL, CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED ( [Sale_ID] ASC ) the table was filled with 10000 rows with [Complete] = 1 and 100 rows with...
SQL 2008 and powershell - with the 2008 releases from microsoft powershell is really making its way into the mainstream. sql server 2008 ships with standard powershell as well as an add-in that provides an interesting degree of integration that will allow DBAs to write scripts that are far more generalized than T-SQL scripts. getting ready to play with it is pretty strait forward. all you have to do is click the "Start PowerShell" context menu option which is relatively ubiquitous in SSMS 2008's object explorer, located on the "Tables" folder within a database for example. essentially the schematic structure of database is layed out into a virtual filesystem with database objects represented as files and directories. for example every table is a directory with subdirectories for each column and each column having files for the...
SQL 2008 FILESTREAM - this post was written during the early release candidates and the actual implementation has changed slightly. your mileage may vary with this post. over the years people have attempted to put large amounts of non relational data in SQL. images strike me as the most popular case. generally people either stored the data directly in SQL or stored it on the filesystem with the path in SQL. neither of these were terrific options. the former was difficult to manage and frequently resulted in poor performance while the later tended to become inconsistent very quickly because SQL had no way of understanding how the filesytem was modified behind its back. enter SQL 2008's FILESTREAM storage which allows you to store LOB fields directly on the filesystem but still function as columns in...
SQL 2008's table valued parameters - One of my favorite new features available in SQL 2008 is table valued parameters. For years SQL developers have been dying for this abilty and have been throwing some miserable duct tape at the problem. Before sql 2005 it was common to see strings being passed into stored procedures and then parsed into temporary tables. This approach suffered several major disadvantages including being limited to 8000 bytes, requiring T-SQL parsing logic and little guarantee you would receive the parameter structured exactly as you desired. SQL 2005 not only lifted the 8000 byte limitation by introducing (n)varchar(max) but also added extensive XML support which allowed structured data to be passed in as XML and later queried as if it were relational data. While very useful I was always a little bothered by...
SQL 2008's MERGE statement - One of the coolest new features of SQL Server 2008 (and there are many) is the MERGE statement. This construct has existed in oracle and DB2 for a while now but it's new to SQL Server. Effectively it allows you to merge two tables together, combining UPDATE, INSERTS and DELETEs into one nifty package. For the purposes of demonstration I'm going to provide a simple example that merges a table variable into a real table by updating rows with matching primary keys with the table variable's values. It will also insert into the real table where there's no match. Consider the following table: CREATE TABLE [dbo].[Things]( [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Name] [varchar](16) NOT NULL ) Go With the following content ID Name ----------- ---------------- 1 computer 2 box...
