Saturday, March 24, 2007

T-SQL Convert Hex value to Integer


--Convert Hex value to int
SELECT CONVERT(INT, 0x000FFEFF)

--Convert int value to hex
SELECT CONVERT(VARBINARY(8), 1048319)

Friday, March 23, 2007

SQL Server 2005 Access to System tables

In SQL2K5 system data is hidden in resource tables and cannot be accessed. Instead, there are number of views which can provide the information.

Catalog Views – Shows metadata that describes the objects in an instance
Information Schema Views - They have the same functionality as in 2000 Provides internal view of SQL Server meta data
Compatibility Views – These are for backward compatibility. Beware that columns that previously were undocumented typically now only have NULL or 0
Dynamic Management Views – They provide inside details of SQL Server state data, which can be used to check the health of SQL Server instance. Consists of both views and table valued functions

Also from BOL, the catalog views and the compatibility views have restricted visibility, so by default they only return information about objects to which the user has some rights.

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
http://msdn2.microsoft.com/en-us/library/ms187997.aspx

SQL Server Management Studio – Editor Color Coding

In Management studio, when you open query editor, there is a thin yellow vertical line in the left margin for each line you type. Sometimes the line disappears and sometimes it is green in color. Did you ever notice that? If not, continue reading……

These colors represent the modifications done to the file.

Yellow –modification, not saved
Green – modification, saved
No Color – no modification

Now, can you change these colors? Ofcourse, goto Tools->Options->Environment ->Fonts and Colors. You can change the colors for various display items.