Session: Microsoft SQL Server 2008: New and Future T-SQL Programmability


Agenda
-what’s new in SQL 2008
what we are thinking for the future

what’s new in sql server 2008
-new data type -> date& time
-improvement in sql language: merge grouping set, table value constructor
-procedure programming
-others : object dependency, collation, beyond relational

New Date and Time types
-date or time only data
-historical date before year 1753 (yay!)
-Datetime data with larger factional precision ( same precision as .net and windows OS)
-time zone offset aware (time zone is included in date time)

Name of the new types:
DATE (1~9999), storage saving, easy programming
TIME (Large precision(0~100ns))
DATETIME2 (large year range, large or optional precision)
DATETIMEOFFSET (DATETIME + time zone offset) UTC enabled

Speaker encourages using new date & time type rather than the old one.

New Date and time type are supported in ODBC/OLEDB Sqlclient/ADO.NET

Demo New Date & time implementation
– kinda daylightsaving aware

New Merge statement
-OLTP: merging recent info from external source
-data warehouse: incremental updates of fact
-single statement that combines multiple dml operations
-operates on a join between source & target
-sql 2006 compliant

OUTPUT XXX($action)->tells you what’s happening in the Store prod.


new grouping sets clause
define multiple groupings in the same query
produces a single result set that is equivalent to a union all of differently grouped rows
SQL 2006 standard compatible

table clause constructor support through the values clause
-use values clause to construct a set of rows
-insert multiple rows based on values in a single insert statement
-sql 2006 standard compatible

in sql 2008-> you can pass table as variables from or to sql sever 2008

how to work on tabular data pre-2008
using local temporary tables
increasing the disk i/o
frequent store prod recompilation

TVP- Table Value Parameter
-input parameters of table type on SPs/Functions
optimized to scale and perform better for large data
behaves like BCP inside server>
fully supported in ado.net 3
new parameter type: sqldbtype.structured
supported odbc.oledb
SQL_SS_Table

T-SQL Delighters…

new collations
30k columns table in sql2008 using sparse -hierarchy

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s