http://www.sqlservercentral.com/Articles tagged T-SQL posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)Over the years, I have witnessed many developers being confused about when to use data-manipulation language (DML) triggers vs. when to use constraints.http://www.sqlservercentral.com/redirect/articles/63705/2008/07/23http://www.sqlservercentral.com/redirect/articles/63705/With ranking functions in SQL Server 2005, you can rank rows in result sets. This tip defines rank functions and gives examples of how to use the four available functions.http://www.sqlservercentral.com/redirect/articles/63700/2008/07/22http://www.sqlservercentral.com/redirect/articles/63700/While working on a project recently I was asked to develop a mechanism that would provide the dates for state and federal holidays in a given year. Since this project deals with all states and territories of the United States, the list had to be comprehensive and the client asked that this list be in the form of a SQL query calculated on-the-fly, rather than a static list.http://www.sqlservercentral.com/redirect/articles/63696/2008/07/21http://www.sqlservercentral.com/redirect/articles/63696/To create your own spelling suggestion feature, you need a look-up with correct spellings and misspellings. I walk you through it.http://www.sqlservercentral.com/articles/T-SQL/63404/2008/07/21http://www.sqlservercentral.com/articles/T-SQL/63404/This is a follow up to the article "Return Query Text Along With sp_who2 Using Dynamic Management Views".http://www.sqlservercentral.com/articles/SQL+Server+2000/63542/2008/07/18http://www.sqlservercentral.com/articles/SQL+Server+2000/63542/Histograms help people analyze large amounts of data, whether you display them as tables or as charts. This article shows you how to do both. http://www.sqlservercentral.com/redirect/articles/63651/2008/07/16http://www.sqlservercentral.com/redirect/articles/63651/One thing you may need to do is dynamically return a set amount of rows based on user input. This could be for a search function, reports, dropdown lists or whatever. Instead of hard coding a set value you would like to pass in a variable that will then determine the number of rows to return. How can this be done with T-SQL?http://www.sqlservercentral.com/redirect/articles/63694/2008/07/16http://www.sqlservercentral.com/redirect/articles/63694/I need to write a function to determine if particular year is a leap year (i.e. February contains 29 days rather than 28 days). I know that there are various rules for calculating leap years. Is there an easy way to figure this out? Can you provide an example or two to validate various years?http://www.sqlservercentral.com/redirect/articles/63531/2008/07/14http://www.sqlservercentral.com/redirect/articles/63531/How many times have you wanted to know which child or grandchild records exists for a parent record? SSMS doesn't make this information easy to find beyond one level. New author Narasimhan Jayachandran brings us an article and a recursive solution. http://www.sqlservercentral.com/articles/T-SQL/63473/2008/07/10http://www.sqlservercentral.com/articles/T-SQL/63473/Enhanced variable declaration, one of the many enhancements that Microsoft added to SQL Server 2008 allows you to declare and initialize a value at the same time. This article shows you how to do it.http://www.sqlservercentral.com/redirect/articles/63525/2008/07/09http://www.sqlservercentral.com/redirect/articles/63525/This objective of the article is to give readers in depth understanding of INNER JOIN with different joining conditions. http://www.sqlservercentral.com/articles/T-SQL/63322/2008/07/08http://www.sqlservercentral.com/articles/T-SQL/63322/Longtime author Leo Peysakhovich brings us a new article on error handling when you have procedures nested inside each other.http://www.sqlservercentral.com/articles/T-SQL/63353/2008/07/07http://www.sqlservercentral.com/articles/T-SQL/63353/Using the TOP clause in a procedure to delete records in batch.http://www.sqlservercentral.com/articles/Top/63301/2008/07/03http://www.sqlservercentral.com/articles/Top/63301/Alex Kozak returns with another Date puzzle. A question from a Simple-Talk reader gives Alex the inspiration to see if is possible to list unused date ranges in one Select statement.http://www.sqlservercentral.com/redirect/articles/63520/2008/07/03http://www.sqlservercentral.com/redirect/articles/63520/Many times people come across the Coalesce function and think that it is just a more powerful form of ISNULL. In actuality, I have found it to be one of the most useful functions with the least documentation. In this tip, I will show you the basic use of Coalesce and also some features you probably never new existed.http://www.sqlservercentral.com/redirect/articles/63456/2008/07/03http://www.sqlservercentral.com/redirect/articles/63456/The problem arises when the hierarchy level increases as SQL Server is limited to 32 levels of recursion. We need a better way to implement recursive queries in SQL Server 2005. How do we do it?http://www.sqlservercentral.com/redirect/articles/63455/2008/07/02http://www.sqlservercentral.com/redirect/articles/63455/How you use dynamic SQL, when you should - and when you should not.http://www.sqlservercentral.com/redirect/articles/63452/2008/07/01http://www.sqlservercentral.com/redirect/articles/63452/T-SQL calculations can get tricky at times, and since you often work with multiple rows, it's good to be exact in your data manipulation. New author Lynn Pettis brings us an article about calculating ages.http://www.sqlservercentral.com/articles/T-SQL/63351/2008/06/30http://www.sqlservercentral.com/articles/T-SQL/63351/This article from new author Peter Kierstead shows us how to implement your own "fuzzy" dedup/merge logic without resorting to RBAR in T-SQL.http://www.sqlservercentral.com/articles/T-SQL/63233/2008/06/23http://www.sqlservercentral.com/articles/T-SQL/63233/Discusses the use of the newly introduced APPLY Statement in SQL Server 2005http://www.sqlservercentral.com/articles/T-SQL/63115/2008/06/16http://www.sqlservercentral.com/articles/T-SQL/63115/T-SQL does some things wonderfully, but cursors are the bane of the language, often causing performance issues. Changing your queries around to remove cursors can be tricky and new author Kamran Ali brings us one technique he has used to dramatically improve performance. http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/2008/06/13http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/MAK illustrates the use of Compound Assignment Operator that is introduced in SQL Server 2008.http://www.sqlservercentral.com/redirect/articles/63384/2008/06/13http://www.sqlservercentral.com/redirect/articles/63384/When Nigel Rivett takes us on a tour of the apparently innocuous subject of Identity Columns in TSQL, even the seasoned programmer is due for one or two surprises.http://www.sqlservercentral.com/redirect/articles/63250/2008/06/10http://www.sqlservercentral.com/redirect/articles/63250/Enforcing your data's integrity is probably the single most important issue you face when designing a database. Validating user input is one way of keeping bad data from making its way into your analyses and reports.http://www.sqlservercentral.com/redirect/articles/63204/2008/06/04http://www.sqlservercentral.com/redirect/articles/63204/In this article Randy Dyness shows you how to avoid temp tables whenever to maximize the performance of your queries. http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/2008/05/30http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/Find available keys when merging two tables with duplicate keys.http://www.sqlservercentral.com/articles/T-SQL/62698/2008/05/26http://www.sqlservercentral.com/articles/T-SQL/62698/It's a well known fact that there's no such thing as a true "array" in SQL Server and that you can't pass a table as a parameter... but you can get a lot closer than you think.http://www.sqlservercentral.com/articles/T-SQL/63003/2008/05/22http://www.sqlservercentral.com/articles/T-SQL/63003/This article illustrates different methods to insert data into a table, including the new Row Value Constructor, which simplifies the data insertion.http://www.sqlservercentral.com/redirect/articles/63015/2008/05/16http://www.sqlservercentral.com/redirect/articles/63015/One of the very common questions posted about T-SQL is how to traverse a hierarchy in a set based manner. New author Craig Hatley brings us his techniques for handling the common scenario of employees and managers. http://www.sqlservercentral.com/articles/Advanced+Querying/3001/2008/05/16http://www.sqlservercentral.com/articles/Advanced+Querying/3001/One developer's journey through PIVOTing incorrectly.http://www.sqlservercentral.com/articles/pivot/62808/2008/05/13http://www.sqlservercentral.com/articles/pivot/62808/