LifeAsBob

My Running list of new things i find in SQL Server from 2008 on, 2017, 2019, Azure paas and Managed Instance new features.

Removal of restriction of 1024 columns.
SQL Server 2008 has lifted the limit of 1024 columns per table with a new option called "sparse columns".  While this seems like a "poor design"; it happens and sometimes you just can't go back.  I've seen this in data warehousing type applications where something is really wide; and in a program that parses XML and dynamically creates new columns.

 Example is:  CREATE TABLE products (product_num int, item_num int, price decimal(7,2), ...,
color char(5) SPARSE, width float SPARSE...)
Reference: http://www.infoq.com/news/2007/06/SQL-Server-Sparse

Compress White space in database
Ability to compress white space in the database without affecting performance.
Source, meeting with Microsoft Representatives.

BLOBs
There is a new FileStream data type which allows large binary data to be stored in the file system and yet remain an integral part of the database with transactional consistency. Very good to store the blobs on cheaper storage etc.
http://blogs.msdn.com/pedram/archive/2007/06/04/store-any-data-in-sql-server-2008-katmai.aspx
http://www.21apps.com/2007/06/sql-server-2008-helping-sharepoint.html

Intellisense
Source from a meeting with Microsoft and same as

Resource Governor
How often have you needed to hold back a user so that the application could perform!
http://www.sqlservercentral.com/columnists/sjones/3044.asp

Passing table valued parameters in SQL Server 2008
Source: http://blogs.techrepublic.com.com/datacenter/?p=168#comments

I have always wanted to be able to pass table variables to stored procedures. If a variable is able to be declared, it should have the functionality to be passed as necessary.

I was thrilled to learn that SQL Server 2008 offers this functionality. Here are instructions on how to pass table variables (and the data in them) into stored procedures and functions.

Why pass tables as parameters?

Over the years, I have run into hundreds of situations where it was necessary to pass a container of values rather than individual values into a stored procedure. In most programming languages, passing container data structures in and out of routines is not only common but absolutely necessary. TSQL is no different, especially since the database is where the data lives.

SQL Server 2000 made this possible with the use of OPENXML, which allows you to store data into a VARCHAR data type and pass it around. This became even easier in SQL Server 2005 with the advent of the XML data type and XQuery. You still need to construct and shred the XML data before you can use it; this is functional but not simple.

SQL Server 2008 introduces the ability to pass a table data type into stored procedures and functions. The table parameter feature can greatly ease the development process because you no longer need to worry about constructing and parsing XML data. The feature also allows the client-side developer to pass data tables from client-side code to the database as a simple parameter.

How to pass table parameters

I need to set up my SalesHistory table, which holds my product sales. The following script will create the table in the database of your choice:

IF OBJECT_ID('SalesHistory')>0    
  DROP TABLE SalesHistory;
 GO
   
  CREATE TABLE [dbo].[SalesHistory]
 (          
                   
        [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,[Product] [varchar](10) NULL,                
        [SaleDate] [datetime] NULL,                
        [SalePrice] [money] NULL
  )
  GO

The first step in setting up the use of table valued parameters is creating a specific table type; this is necessary so that the structure of the table is defined in the database engine. This allows you to define the type of table and reuse it as needed in your procedure code. This code creates the SalesHistoryTableType table type definition:

CREATE TYPE SalesHistoryTableType AS TABLE
 (                     
        [Product] [varchar](10) NULL,                
        [SaleDate] [datetime] NULL,                
        [SalePrice] [money] NULL
  )
  GO

If you want to view other types of table type definitions in your system, you can execute the following query, which looks in the system catalog:

SELECT * FROM sys.table_types

I want to define the stored procedure that I plan on using to handle my table valued parameter. The following procedure accepts a table valued parameter, which is of the specific SalesHistoryTableType, and loads the SalesHistory with the records in the table parameter with a value of ‘BigScreen’ in the Product column.

CREATE PROCEDURE usp_InsertBigScreenProducts
 (
 @TableVariable SalesHistoryTableType READONLY
 )
 AS
 BEGIN
       INSERT INTO SalesHistory
       (
             Product, SaleDate, SalePrice
       )
       SELECT
             Product, SaleDate, SalePrice
       FROM
             @TableVariable
       WHERE
             Product = 'BigScreen'    

 END
 GO

You can use the table variable being passed in as any other table for querying data.

Limitations to passing table parameters

You must use the READONLY clause when passing in the table valued variable into the procedure. Data in the table variable cannot be modified — you can use the data in the table for any other operation. Also, you cannot use table variables as OUTPUT parameters — you can only use table variables as input parameters.

Putting my new table variable type to use

First, I must declare a variable of type SalesHistoryTableType. I don’t need to define the structure of the table again because it was defined when I created the type.

DECLARE @DataTable AS SalesHistoryTableType

The following script adds 1,000 records into my @DataTable table variable:

DECLARE @i SMALLINT
 SET @i = 1          

 WHILE (@i <=1000)
 BEGININSERT INTO @DataTable(Product, SaleDate, SalePrice)                      
 VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))INSERT INTO @DataTable(Product, SaleDate, SalePrice)          
 VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))INSERT INTO @DataTable(Product, SaleDate, SalePrice)              
 VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))                            
    SET @i = @i + 1
 END

Once I have data loaded into my table variable, I can pass the structure to my stored procedure. (Make sure you add the data to the table variable and pass the table to the procedure all in the same batch. Table variables go out of scope as soon as the procedure or batch returns.)

Note: When table variables are passed as parameters, the table is materialized in the tempdb system database rather than passing the entire data set in memory; this makes handling large amounts of data rather efficient. All server side passing of table variable parameters are passed by reference, using the reference as a pointer to the table in the tempdb.

EXECUTE usp_InsertBigScreenProducts
 @TableVariable = @DataTable

To see if my procedure performed the way I expect, I’ll run this query to see if the records were inserted into the SalesHistory table:

SELECT * FROM SalesHistory

Considerations

SQL Server 2008’s table parameter feature is a huge step forward in terms of development and potentially performance. The benefits to this feature are that it can: reduce server round trips, use table constraints, and extend the functionality of programming on the database engine.

There are some limitations to keep in mind, such as not being able to alter the data in the parameter and not being able to use the variable as output. Despite these minor drawbacks, I definitely recommend exploring the table parameter feature when SQL Server 2008 comes out.