http://www.sqlservercentral.com/Content tagged SQL Server 2005, 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/2008/06/06http://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/2008/05/16http://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/2008/06/06http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/3041/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/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/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/2008/05/16http://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/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/Continuing with his series on SQL Server 2005 from the developer's perspective, M. Choirul Amri examines the ROW_NUMBER function and gives a few examples of how you can use this in your application. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/sql2005forthesql2kdeveloperpart2/2381/2006/05/11http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/sql2005forthesql2kdeveloperpart2/2381/Processing text or long strings usually reduces SQL to a prosaic procedural language. Learn a few techniques for facilitating speedy text processing in SQL. http://www.sqlservercentral.com/redirect/articles/2401/2006/05/10http://www.sqlservercentral.com/redirect/articles/2401/There are many changes in SQL Server 2005, especially for the SQL Server developer. New options, features, and changes in the way you program. New author M. Choirul Amri brings us the first in a series on the changes from the point of the developer. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/sqlserver2005forsql2kdeveloperpart1/2313/2006/03/29http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/sqlserver2005forsql2kdeveloperpart1/2313/Server 2005 now supports a more programmable error trapping convention in T-SQL code. This error handling is known as the TRY/CATCH block. The TRY/CATCH block allows T-SQL to handling errors like other programming languages, such as Microsoft Visual C# and C++. In addition to the TRY/CATCH block, Microsoft has expanded the set of available system functions in SQL Server 2005 for returning error codes and messages when an error occurs in your T-SQL code. This article will explore the new TRY/CATCH block and explain the new system functions for returning error codes and messages.http://www.sqlservercentral.com/redirect/articles/2322/2006/03/13http://www.sqlservercentral.com/redirect/articles/2322/mong other enhancements in SQL Server 2005, enhancements were made to the OPENROWSET function. This article illustrates BULK operations of the OPENROWSET function. http://www.sqlservercentral.com/redirect/articles/2299/2006/02/23http://www.sqlservercentral.com/redirect/articles/2299/This article will acquaint you with aggregate functions such as MIN, MAX, COUNT, and AVG, which easily let you perform tasks that you may have thought needed extensive programming codes to accomplish. http://www.sqlservercentral.com/redirect/articles/2294/2006/02/20http://www.sqlservercentral.com/redirect/articles/2294/Many people have posed the question of how to generate sequential numbers for some report when the underlying data has no numbering system. The solutions have always been complicated derived or temporary tables and are difficult to implement. SQL Server 2005, however, brings us the ROW_NUMBER() function and author Michael Coles shows us how this can be used to easily add sequential numbering to your results. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/sequentialordering/2261/2006/02/14http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/sequentialordering/2261/One of the neatest T-SQL enhancements in SQL Server 2005 is the ROW_NUMBER() function. New author Charles Hawkins brings us an explanation of how you can use this function along with a Common Table Expression to remove duplicate data. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/dedupingdatainsqlserver2005/2260/2006/02/07http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/dedupingdatainsqlserver2005/2260/As we all know, SQL Server 2005 has new features when compared to SQL Server 2000. One of the features that we are going to discuss in this article is the Row_Number() function. SQL Server Database administrators and developers have been longing for this function for a long time--now the wait is over.http://www.sqlservercentral.com/redirect/articles/2239/2006/01/18http://www.sqlservercentral.com/redirect/articles/2239/