Thursday, March 22, 2007

Why to prefix Database objects?

Reviewing a database with a lot of tables is no easy task, especially when you didn't make the database. It seems like it would make everyone's life easier is if people would prefix their database objects in a way that helps group DB objects logically.
For example, there are tables in a database specifically used for
Metadata for the application. It would be nice if they were prefixed with "MD_"(Meta data) to denote that they are
Metadata tables. Instead of tables X, Y, and Z intermingled with the rest of the tables in the database, they would be nicely grouped together as MD_X, MD_Y and MD_Z.
But even if everyone were to start prefixing their databases the way I want right now, it wouldn't help me out with the one I've already got.

Table variables in SQL Server 2005

In SQL Server 2000 there is an alternative to the use of temporary tables. We can use the table variables as an alternative to the table variable. Table variables store a set of records. The declaration syntax looks very similar to a CREATE TABLE statement
DECLARE @Blogs TABLE( BlogID int, BlogTitle varchar(100))
We can insert some data in the table variable very easily using the select statement to populate data in the table variables.
INSERT INTO @Blogs (BlogID, BlogTitle)
SELECT BlogID, BlogTitle FROM [Blog]Table
variables can be used in batches, stored procedures, User defined functions. We can also use the update and delete keywords with the table variables to modify or delete records.Here are some examples of working with the table variables.
SET BlogTitle = ‘Changing the data of the blog Title’
WHERE BlogID = 6
WHERE BlogID = 60
TOP 5 *
FROM @Blogs