Born to Thrive

Table Value Constructor is a new feature within SQL Server 2008, and also can be part of existing features like INSERT or MERGE, which allows for new syntax only available within T-SQL (maybe that's another reason I decided to write about TVC after having studied Oracle SQL, which doesn't have this feature). So Table Value Constructor is nothing else than a T-SQL syntax that allows multiple rows of data to be specified in a single DML statement. TVC can be used only within the following DML statements:

• INSERT statement
• USING clause of MERGE statement
• inline view or derived table in FROM clause

It can only be used to construct 1000 rows of data. The syntax is the same as the VALUES clause within INSERT statement, with the difference that TVC allows providing a list of values separated by comma.
[Table Value Constructor within INSERT statement]

[Table Value Constructor as DERIVED TABLE]

As far as table value constructor within MERGE statement, I'll demonstrate that in the next post , which will be dedicated entirely to MERGE statement as it is also a brand new feature in SQL Server 2008 and it is a little different than the one in Oracle. So it is really important to revisit it before the test.

After having passed the 1Z0-047 Oracle Database SQL Expert Exam, I decided that it would make sense to take 70-433 (Microsoft SQL Server 2008, Database Development Exam) as my next exam. First of all, I have taken 70-431 (Microsoft SQL Server 2005 - Implementation and Maintenance) exam before, so it would make sense to update it to current version of SQL Server, plus I practices writing a lot of queries to the Oracle exam (even though the syntax sometimes can be so much different) and finally it would make sense to take this exam before I start studying to the .NET 4.0 exams, which based on my experience from previous exams might have a few questions regarding SQL Server 2008. So as always before starting studying to the exam, I try to learn about the exam as much as possible from those who have taken the exam.

• 70-433 study material
• MCTS Self-Paced Training Kit (Exam 70-433) Reviews
• Real-MCTS-Server-2008-70-433 Reviews
• Passed-70-433
• Best Study Guide for 70-433 exam

There isn't much of very useful information, however one can find that MCTS Self-Paced Training Kit book doesn't even come close to covering topics in the exam, so if you go for the test without a considerable amount of SQL2008 and other version experience or training you are going to be in trouble when you take the exam. One interesting point I found was that "when you seat for the real test, Microsoft will try to "kill" you with really strange T-SQL options and queries that you barely see in real world". As far as Real MCTS Server 2008 70-433 book, it can help a lot but it's not quite enough to actually pass the exam (like no information about Service Broker). Dave mentioned some things that are either new or not very well known that each test taker should know like (filtered index, using APPLY operator to run Table Value Function against all rows in the table, or that an indexed view needs to use WITH SCHEMABINDING, and as a consequence the index must be UNIQUE CLUSTERED). The Best Study Guide for 70-433 site mentions only that to continue preparing with the SQL 2005 materials if you have as SQL 2008 added *new* things to SQL 2005, but there’s nothing in SQL 2005 that won’t be valid knowledge in SQL 2008, and then focus efforts on the *new* things in SQL2008 using SQL Server Books Online.
So based on the information as above, my study plan is to use the following books:

• Microsoft-SQL-Server-2008-Bible
• SQL Server 2008 Books Online
• Inside-Microsoft-SQL-Server-2005 - T-SQL Querying
• Inside-Microsoft-Server-2005-Pro-Developer - T-SQL Programming
• MCTS-Self-Paced-Training-70-431-Pro-Certification
• MCITP-Self-Paced-Training-Exam-70-441 - Designing Database Solutions
• MCITP-Self-Paced-Training-Exam-70-442 - Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

One might ask why some many books on SQL Server 2005. The answer is very simple: because I already own them so I don't feel like spending all the money on the same books for 2008, which will contain only few pages that are not in the previous books. That's why I will use SQL Server 2008 Books Online to learn new features that I could not find the books I already have. As far as posts are considered, I'll probably try to write about the new features introduced by SQL Server 2008 or maybe about some syntax that is typical to T-SQL like Output clause or recursive CTE.

One of the abilities being checked on the 70-561 exam is the ability of handling special data types: Binary Large Objects (BLOBs), Filestream, Spatial, Table Value Parameters. From my experience I can tell that developers more of less are familiar with Large Objects whether it's BLOB (Varbinary (Max)) or CLOB (Varchar(Max)). But filestream, spatial (comprehensive support for geography and geometry) and table value parameters are new features of Microsoft SQL 2008, and as such they're still fairly new for  most .NET developers. And if to look closely at those new features, they can very useful for all developers working with MS SQL 2008 database. Below are the links to the information on those new features from the SQL Server 2008 perspective.

• BLOB
• Filestream
• Spatial
• Table value parameters

I, however, have focused on .NET code that would "handle special data types", primairly by writing code that would demonstrate how to read and write data stored by special data types. I have combinec BLOB with Filestream as BLOB is commonly used by developers so I didn't think it would deserve a separate attanetion. The code will use a sample database created in SQL Server 2008 with filestream enabled. Note that specified filestream folder must not exist during creation of the database.
[Database script]

The script creates a database with 3 tables - TABLE_FS will be used to demonstrate filestream by inserting a photo; TABLE_SPATIAL will be used to demonstrate spatial types - geography and geometry; USERS will be used to store username and hashed password to demonstrate using table type parameters.
[Reading Filestream]

[Writing Filestream]

[Reading Spatial]

[Writing Spatial]

[Testing table type parameters]

After having looked at the code, you can notice that there's a few things a developer needs to remember when using the aforementioned special data types. For example, table with filestream requires column with uniqueidentified type and also reading and writing data to filestream requires a transaction context. If you go to a filestream folder, you will see there files - without extensions. If you copy it and change extension, you will get an identical copy of the file you wrote to database. As far as spatial types, I used a linestring object, which represents a line connecting a sequence of points, and function STLength to demonstrate diffrences of the line in geography and geometry realm. Table type parameter was used within user defined function that checked if given username and password are correct by comparing it with the records in USER table. It uses DataTable to store records to pass to the function. Password is hashed with salt using private key and SHA512 algorithm. So there is not really a lot to remember or a lot of new things a developer have to learn - most of them are straightforward, however there are few important things that you have to remember like using transaction context, uniqueidentifier column, or using BinaryReader to get a value from a stream of a spatial data type column, but I don't think this topic now will be a killer on the exam.