http://www.sqlservercentral.com/Content tagged SQL Server 2005 - TSQL posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)SQL Server 2005 has added many new T-SQL commands, one of which is the OUTPUT command. Longtime SQL Server author Dinesh Asanka brings us a short look at how you can use this command. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/3041/2007/07/09http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/3041/How many of you have written resursive queries in SQL? Or any language since school for that matter? Not many people write recusrive queries because of the complexity, the difficulty to understand how they work, and the chance for heap overflows. However, SQL Server 2005 implements Common Table Expressions and recursion in a way that is much easier to code and incorporates some safeguards. New author SQL Server MVP Frederic Brouard has written a fantastic article looking at resursive queries. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/2006/09/15http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/A common activity in applications is to page results or record sets from a database. This is usually done on the client using the client's paging functionality or on the server through a variety of methods. In SQL Server 2000 those server side methods typically used dynamic SQL or nested TOP clauses and weren't very efficient. Using Common Table Expressions in SQL Server 2005 we have a better way to page record sets on the server.http://www.sqlservercentral.com/redirect/articles/2816/2008/01/17http://www.sqlservercentral.com/redirect/articles/2816/How many of you have ever used SET CONTEXT_INFO in your SQL Server applications? Chances are it is not something you have been exposed to, but new author Yousef Ekhtiari brings us an example of how this particular construct can be used in your application. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/2765/2007/12/18http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/2765/Continuing on with a look at SQL Server 205 partitioning features, Andy Warren delves into archiving techniques. http://www.sqlservercentral.com/articles/Miscellaneous/3242/2007/10/08http://www.sqlservercentral.com/articles/Miscellaneous/3242/In the simplest terms, a NULL value represents an unknown value. It's unknown in the sense that the value is: missing from the system, may not be applicable in the current situation, or might be added later. NULL values are different than any other value and are sometimes hard to compare and handle.http://www.sqlservercentral.com/redirect/articles/3237/2007/09/20http://www.sqlservercentral.com/redirect/articles/3237/T-SQL Try…Catch block for SQL Server exception handling has new, improved functions to handle errors when executing T-SQL code in SQL Server 2005.http://www.sqlservercentral.com/redirect/articles/3114/2007/08/01http://www.sqlservercentral.com/redirect/articles/3114/Phil Factor presents a poetic stored procedure for storing any VARCHAR(MAX) to file.http://www.sqlservercentral.com/redirect/articles/3106/2007/07/23http://www.sqlservercentral.com/redirect/articles/3106/SQL Server 2005 has added many new T-SQL commands, one of which is the OUTPUT command. Longtime SQL Server author Dinesh Asanka brings us a short look at how you can use this command. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/3041/2007/07/09http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/3041/http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61075/2007/07/06http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61075/http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61067/2007/06/28http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61067/One of new features in SQL 2005 that I haven't seen much talk about is that you can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function. Unfortunately, it isn't especially powerful, and you can't do running totals with it, but it does help you make your code a little shorter and in many cases it might be just what you need.http://www.sqlservercentral.com/redirect/articles/3035/2007/06/20http://www.sqlservercentral.com/redirect/articles/3035/http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61062/2007/06/20http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61062/http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61061/2007/06/19http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61061/http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61049/2007/06/05http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61049/http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61047/2007/05/29http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61047/http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61013/2007/04/16http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/61013/The NEWSEQUENTIALID system function is an addition to SQL Server 2005. It seeks to bring together, what used to be, conflicting requirements in SQL Server 2000; namely identity-level insert performance and globally unique values.http://www.sqlservercentral.com/redirect/articles/2947/2007/04/10http://www.sqlservercentral.com/redirect/articles/2947/http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/60983/2007/03/07http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/60983/A common activity in applications is to page results or record sets from a database. This is usually done on the client using the client's paging functionality or on the server through a variety of methods. In SQL Server 2000 those server side methods typically used dynamic SQL or nested TOP clauses and weren't very efficient. Using Common Table Expressions in SQL Server 2005 we have a better way to page record sets on the server.http://www.sqlservercentral.com/redirect/articles/2816/2008/01/17http://www.sqlservercentral.com/redirect/articles/2816/New to SQL Server 2005 is the PIVOT operator, which gives you the ability to rotate row level data into tabular data without the use of the CASE statement, as was necessary in previous versions of SQL Server.http://www.sqlservercentral.com/redirect/articles/2791/2007/01/11http://www.sqlservercentral.com/redirect/articles/2791/How many of you have ever used SET CONTEXT_INFO in your SQL Server applications? Chances are it is not something you have been exposed to, but new author Yousef Ekhtiari brings us an example of how this particular construct can be used in your application. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/2765/2007/12/18http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/2765/In this video for beginners you'll begin to learn how to write queries that use more than one table using T-SQL. Kathi walks you through common tactics to efficiently pull out data from a normalized system. http://www.sqlservercentral.com/redirect/articles/2766/2006/12/11http://www.sqlservercentral.com/redirect/articles/2766/There are so many new features in SQL Server 2005 that there's one that can make a significant impact in solving many different types of problems. Andy Warren takes a look at one of the features that has been enhanced and is extremely useful, albeit quirky: TOP. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/2612/2006/10/05http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/2612/Tim Chapman discusses using table valued functions with the APPLY function.http://www.sqlservercentral.com/redirect/articles/2619/2006/10/04http://www.sqlservercentral.com/redirect/articles/2619/How many of you have written resursive queries in SQL? Or any language since school for that matter? Not many people write recusrive queries because of the complexity, the difficulty to understand how they work, and the chance for heap overflows. However, SQL Server 2005 implements Common Table Expressions and recursion in a way that is much easier to code and incorporates some safeguards. New author SQL Server MVP Frederic Brouard has written a fantastic article looking at resursive queries. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/2006/09/15http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/In this video by Kathi Kellenberger, she starts beginners interested in T-SQL programming the teminology and some of the basiics of T-SQL. She also covers the what the various editions of SQL Server mean and the tools that you will use. http://www.sqlservercentral.com/redirect/articles/2591/2006/09/12http://www.sqlservercentral.com/redirect/articles/2591/A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on. http://www.sqlservercentral.com/redirect/articles/2570/2006/09/07http://www.sqlservercentral.com/redirect/articles/2570/http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/60825/2006/07/26http://www.sqlservercentral.com/questions/SQL+Server+2005+-+TSQL/60825/The benefits and drawbacks of GUIDs as primary keys are generally known. Despite the drawbacks, some people use them to fulfill specific business requirements (the IDs are unique across multiple servers).http://www.sqlservercentral.com/redirect/articles/2477/2006/06/30http://www.sqlservercentral.com/redirect/articles/2477/