SQL Server 2008 TSQL enhancements - what I like?
Simple, short and crisp - I would like to say about TSQL enhancements within SQL Server 2008.
You may already aware about what's new and how best you can take advantage within your code, most of the Developers would agree about IntelliSense functionality that supports (gives you advantage on) frequently used TSQL elements, we can expect to see few enhancements and extended support to this element in upcoming releases. In this regard I would like to copy the BOL information for your joy:
(source: SQL 2008 Books Online) The Database Engine Query Editor also provides IntelliSense support for Transact-SQL statements shown in the following table.
| Statement |
Syntax supported |
|
DECLARE @local_variable (Transact-SQL) |
All syntax. |
|
SET @local_variable (Transact-SQL) |
All syntax. |
|
EXECUTE (Transact-SQL) |
Execution of user-defined stored procedures, system stored procedures, user-defined functions, and system functions. |
|
CREATE TABLE (Transact-SQL) |
All syntax. |
|
CREATE PROCEDURE (Transact-SQL) |
All syntax, with the following exceptions:
- There is no IntelliSense support for the EXTERNAL NAME clause.
- In the AS clause, IntelliSense supports only the statements and syntax that are listed in this topic.
|
|
ALTER PROCEDURE (Transact-SQL) |
All syntax, with the following exceptions:
- There is no IntelliSense support for the EXTERNAL NAME clause.
- In the AS clause, IntelliSense supports only the statements and syntax that are listed in this topic.
|
Ok, what I like about these new features here we go:
Extensions to DateTime data type and support, such as DataTime values (1753 to 9999 years) with 0.00333 second accuracy. Also the new data types such as seperate data and time to save space and not to mention about ANSI compatibility. New one is DateTime2 and DateTimeOffset and not having the daylight saving time support. Also the new date related functions such as DATENAME (datepart, date), DATEPART (datepart,date), DATEDIFF (datepart, startdate, enddate), DATEADD (datepart, number, date)
Next on the list is Table Valued Parameter, what I refer is more on web from Bob Beauchemin's TVP post Ben Hall' blog - TVP post.
On the go, I feel amused on performing single statement of declare and initialize, recently I have been going through Teched presentation where Bob has given much insight about it. This will also allow you to use with the += operator which allows you add a number to itself, further Bob clarified that it works for STRING data type too (as below)!
declare @Column1 varchar(12) = 'Hello'
set @Column1 += ' World
Print @Column1
Then I go to MERGE (UPSERT) statement, where you can perform multiple set operations in a single TSQL statement, magic isn't it. Using the multiple sets as (input) MERGE target USING source ON where the operations can be INSERT/UPDATE/DELETE with the conditions WHEN MATCHED or WHEN [TARGET] NOT MATCHED or WHEN [SOURCE] NOT MATCHED sets. I have also read that when you are using TRIGGERS (which is a common tactics to keepup the business rules) it fires only once and can be transactional, rows affected result is the total rows by the all clauses. Further explanationon this feature from BenHall-Merge & DavidPortas-Merge blog posts.
When it comes to the database tables, you need to know the object dependancies and you may be aware about SP_DEPENDS and this will now have more information with new DMVs - sys.dm_sql_referenced_entities & sys.dm_sql_referencing_entities and further interesting read on about security too in using the DMV TECHNET434691 link. Lastly, on the performance aspect look at PLAN GUIDE improvements with easy to create plan guides that too on DML statements.
Further more to come or enhance what has been referred above when SQL Server 2008 is RTMd, so keep watching the SQL 2008 space.