Here are the top 10 most viewed SQL Server development tips of 2008. Whether you were converting date/time data into character types, working with DATETIME and SMALLDATETIME in SQL Server 2005, using a stored procedure to find the size of SQL Server tables or retrieving XML data values with XQuery, these were the topics that piqued your interest this year.
#1 - SQL Server data conversions from date/time values to character types
T-SQL supports two built-in methods for converting date/time data to character data and vice versa. These are implicit and explicit conversions, and this tip will take you through a step-by-step process for using each method. Included in these steps is advice on how to use the CAST and CONVERT functions when converting date/time values.
#2 - Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005
Understanding the DATETIME, SMALLDATETIME and TIMESTAMP data types in SQL Server is not always a straightforward process. Learn how data is stored within DATETIME and SMALLDATETIME and find out why TIMESTAMP is different and often confused with these two primary date/time data types.
#3 - Find size of SQL Server tables and other objects with an original stored procedure
Getting an overview of user table sizes in a database, a summary of the total space owned by a group of tables and the top 10 biggest indexed objects are among the important reasons to calculate the specific disc space used by an object in SQL Server. Sp_SOS, an original stored procedure, can replace the often inadequate sp_spaceused stored procedure that ships with SQL Server.
#4 - Using SQL Server datetime functions GETDATE, DATENAME and DATEPART
There are several functions used for retrieving the current date and time, or individual parts of a DATETIME or SMALLDATETIME value, in SQL Server. Learn how to extract the day, month or year from a datetime value, as well as the quarter, week, hour or even the millisecond.
#5 - Retrieve XML data values with XQuery in SQL Server 2005
XQuery is the scripting language to use when accessing specific XML data values, rather than XML as a single data type value. Find out how to use two XQuery methods within your T-SQL statements -- Value() to retrieve a single XML value and Query() to retrieve multiple XML values.
#6 - Stored procedure to monitor long-running jobs in SQL Server 2000
Monitoring long-running jobs in SQL Server 2000 is not a simple practice, but it is a best practice among DBAs. Discover a stored procedure that easily identifies long-running jobs while also alerting DBAs, through email alerts, of situations that may poorly affect SQL Server performance.
#7 - Create DDL table in SQL Server 2005 to audit DDL trigger activity
Whenever DDL statements in SQL Server 2005 make changes with commands, such as CREATE TABLE and ALTER, the DDL trigger fires an event. But, it's possible to log these events by designing an audit table and defining a custom-made DDL trigger, along with testing the auditing solution.
#8 - Using DATEADD and DATEDIFF to calculate SQL Server date/time values
The DATEADD and DATEDIFF functions in SQL Server can perform easy calculations, such as adding or subtracting a time interval from a date/time value or comparing two date/time values. Get examples of how to use these handy functions in table definitions, queries and data modification statements.
#9 - Configure SQL Server Service Broker for sending stored procedure data
The Service Broker feature in SQL Server 2005 allows you to queue data of stored procedures and other application development components. Follow these steps for setting up Service Broker and learn how to use this communications platform to transmit data and messages from one SQL Server system to another.
#10 - Simplify queries with SQL Server 2005 common table expressions (CTEs)
Common table expressions (CTEs) in SQL Server 2005 are an alternative to using derived tables and views for retrieving data. CTEs separate code into unique units, so there's no need to repeat complex code, and they are self-referencing within your query.
