Sql Server 2005

...now browsing by tag

 
 

Using the field name alias in SQL Where clause

Tuesday, March 17th, 2009

Often I encounter SQL queries where I would love to use the alias names of my columns in the WHERE clause, today while trawling around the web I found a cool and easy way to do it. Here’s an example:

WITH Customer_Info AS
(
SELECT (Surname + ‘, ‘ + Firstname) AS FullName,
(Address_Line_1 + ‘,’ + Address_Line_2) AS Customer_Address
FROM Customers
)
SELECT FullName, Customer_Address
FROM Customer_Info
WHERE FullName LIKE ‘Smith%’

Written by Deepak Vasa - Visit Website

Query to get the column names/types for all tables in SQL 2005

Wednesday, February 11th, 2009

SELECT schemas.name AS [Schema],
tables.name AS [Table],
columns.name AS [Column],
CASE
WHEN columns.system_type_id = 34 THEN ‘byte[]’
WHEN columns.system_type_id = 35 THEN ‘string’
WHEN columns.system_type_id = 36 THEN ‘System.Guid’
WHEN columns.system_type_id = 48 THEN ‘byte’
WHEN columns.system_type_id = 52 THEN ‘short’
WHEN columns.system_type_id = 56 THEN ‘int’
WHEN columns.system_type_id = 58 THEN ‘System.DateTime’
WHEN columns.system_type_id = 59 THEN ‘float’
WHEN columns.system_type_id = 60 THEN ‘decimal’
WHEN columns.system_type_id = 61 THEN ‘System.DateTime’
WHEN columns.system_type_id = 62 THEN ‘double’
WHEN columns.system_type_id = 98 THEN ‘object’
WHEN columns.system_type_id = 99 THEN ‘string’
WHEN columns.system_type_id = 104 THEN ‘bool’
WHEN columns.system_type_id = 106 THEN ‘decimal’
WHEN columns.system_type_id = 108 THEN ‘decimal’
WHEN columns.system_type_id = 122 THEN ‘decimal’
WHEN columns.system_type_id = 127 THEN ‘long’
WHEN columns.system_type_id = 165 THEN ‘byte[]’
WHEN columns.system_type_id = 167 THEN ‘string’
WHEN columns.system_type_id = 173 THEN ‘byte[]’
WHEN columns.system_type_id = 175 THEN ‘string’
WHEN columns.system_type_id = 189 THEN ‘long’
WHEN columns.system_type_id = 231 THEN ‘string’
WHEN columns.system_type_id = 239 THEN ‘string’
WHEN columns.system_type_id = 241 THEN ‘string’
WHEN columns.system_type_id = 241 THEN ‘string’
END AS [Type],
columns.is_nullable AS [Nullable]

FROM sys.tables tables
INNER JOIN sys.schemas schemas ON (tables.schema_id = schemas.schema_id )
INNER JOIN sys.columns columns ON (columns.object_id = tables.object_id)

WHERE tables.name <> ‘sysdiagrams’
AND tables.name <> ‘dtproperties’

ORDER BY [Schema], [Table], [Column], [Type]

Written by Deepak Vasa - Visit Website

How to convert ntext to nvarchar(max) in SQL 2005 – the right way

Tuesday, August 5th, 2008

Today, I found this very very useful blog on how to convert ntext fields to nvarchar(max) in Sql 2005. Apparently when converting a field from ntext to nvarchar(max), SQL Server does not change the internal structure and this is bad as the performance slows down significantly. However the following blog has got a simple fix to make SQL do the correct change.

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

Written by Deepak Vasa - Visit Website