Today I demonstrate a more efficient method to retrieve data using a stored procedure, while providing different sort options. If you show the query plan from yesterday you will see that it calculates the sort for all options, and then uses the one specified by the user. This query differs in that it creates a separate sub plan for each […]

The best place to sort data from your database is not on your SQL Server. There are times when you have no choice. So, now you have to sort your results from a query. Your DBA says you can’t use dynamic SQL. You are only allowed to use stored procedures. Now What do you do? Today I am demonstrating a […]

I have been a proponent of set operations in SQL Server as being preferred over procedural logic. If possible, work with sets instead of loops, and or cursors. The primary reason is that SQL Engines are tuned to do set operations. It does filtering and joins very quickly. Below, I have a VERY contrived query demonstrating how you can use […]

Tonight I was helping my son with his first term paper. He was using a program from school to layout his thoughts according to the format required by his teacher. I could have done the work in any different number of tools with ease. But he had to use the tool provided, and it was different than anything I had […]

What SQL permissions do an application really need in order to work? The requirements will be based on the functions exposed by an application. If you have an off the shelf application that is self-contained, it may require permissions for database backup and restore actions. Bulk Copy for import and export is often included. If your application allows users to […]

When you use a relational database engine for an application, where shared credentials are used to gain access, it is very important how those shared credentials are configured. We’ll take a little look into why. I have consulted with a few companies that used this approach of a single login for database access. Oftentimes, they used the SA account, or […]

Yesterday I demonstrated a set based technique allowing you to build a Hex string from a comma separated value list. The list would allow you to select items from 1 to 63, and build a hex string, representing all of the selections. If you were to represent the data in a binary form a string of number 1, 2, 3, […]

Yesterday we demonstrated how to convert a delimited list into a set using a TSQL function. Once we have the ability to convert a list of items into a set, if the set consists of a set of integers we can convert that list into a binary representation, a Decimal representation (if the list is 64 items or less) and […]

Our function today is demonstrated below. It uses a Sequence (Or Tally) table containing the numbers 0 thru [some number that is big enough for your requirements]. I am using two common table expressions. The first CTE, Pointers, locates the first position of the Delimiter string. The second CTE, Segments, uses three queries to find the start position of the […]