COALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value. This tip describes two creative uses of the COALESCE() function in SQL Server.   Read more...

You can use arithmetic operators in date/time calculations in SQL Server, but sometimes the date/time functions provided by Microsoft are the better option. Follow a few scenarios that demonstrate when arithmetic operators are safe and when they are risky.  Read more...

Chess makes a fantastic game for programming examples. You will find hundreds of examples on the internet. Some dedicated to OO patterns, others to algorithms and so forth. Unfortunately, most of these examples do not use a database or if they do, treat the database as nothing more than a storage repository. In this series of articles we will use SQL Server and T-SQL to implement the game of chess with an emphasis on thinking in sets.  Read more...

Robyn Page and Phil Factor take on GROUP BY queries in SQL server, starting on the nursery slopes but finishing with a wild ride off-piste.  Read more...

In an interesting exercise in data conversion, Stephen Lasham brings us a nice article on basic manipulations.   Read more...

Sql Server comes with a host of built in functions such as ISNULL, CONVERT and CAST. Now if that wasn't enough rope to hang ourselves with, as of Sql Server 2000 we gained the ability to create our own user defined functions. In this article I will be looking at the three main date functions DATEADD, DATEPART and DATEDIFF (there is a fourth called DATENAME but I want to get to the end of this article before you fall asleep so I decided to leave it for another date and time! And no it doesn't foretell the name of your future blind date so it's not as interesting as it sounds anyway) Then I will be combining all three in a user defined function of our own by which time our necks will be well and truly stretched  Read more...

Truth tables are an important part of working with logical values in SQL Server. Yousef Ekhtiari brings us some T-SQL that can help you construct those many variable truth tables and ensure you are getting the results you expect.   Read more...

Part I of this series illustrated how to use simple case functions in queries. This installment discusses how to use case functions in different types of scenarios.  Read more...

Have you ever wanted to ensure that keywords in your data are easily searchable? Have you struggled with full-text search? New author Michael Ahmadi brings us an interesting idea for a keyword tracking and searching subsystem based on T-SQL and triggers.   Read more...

An IP address is something we all recognize and is a piece of data that is quite prevalent in many systems. However it is a piece of data tha presents some challenges in its storage and retrieval. SQL Server guru David Poole presents us with a look at how you can work with this strange formatting.   Read more...

One of the seeminly more popular enhancements in SQL Server 2005 to T-SQL is the PIVOT operator. There have been quite a few articles, but new author Peter Larsson decomposes in detail how you can perform this operation with previous versions.   Read more...

Robyn Page and Phil Factor demonstrate some set-based techniques for string manipulation and time interval-based reporting, which use helper tables rather than iterative logic or the dreaded cursor.   Read more...

They are a basic type of query that every DBA and developer should be able to write, but aggregates are sometimes misunderstood and result in strange behaviors and results. Kathi Kellenberger brings us a tutorial on what aggregate queries are and a few hints on how to become more proficient at writing them.   Read more...

Learn how to build date generators without loops using SQL, and some useful techniques to help you manipulate date and time data.   Read more...

Dealing with NULL data is something that often confuses new SQL Server developers, but even experienced DBAs might not understand all the intricacies of NULL operations. In a follow up to his highly acclaimed Four Rules of Null article, Michael Coles brings us a few new tricks with NULLs.   Read more...

Gregory Larsen demonstrates several examples of how to deal with different situations related to the case of character strings.  Read more...

Expanding on his series of string manipulation in T-SQL, Steve Jones takes a look at how you go about removing those unseen characters from your strings.   Read more...

In this session, Kathi continues her popular series and shows some of the more advanced joining techniques in T-SQL. She shows T-SQL self, outer and cross joins and gives many examples on how to use them.   Read more...

The topic of cursors is the ultimate "hot potato" in the world of SQL Server. Everyone has a view on when they should and mainly should not be used. By example and testing Robyn Page proves that, when handled with care, cursors are not necessarily a "bad thing".  Read more...

Most SQL Server DBAs have been taught that cursors are bad and should not be used. However there are some cases and places where they might be useful. SQL Server guru Andy Warren brings us an example of where they may be handy.   Read more...