Custom SQL Stored Procedure Best Practices Analyzer - SQL Cop, Maybe?

Recently, I moved to Seattle as a Data Architect for a product our company was developing. I also started taking up most of the DBX roles in that project.

Though our architecture is predominantly CRUD based and we have our in-house CRUD proc generator, some business logic invariably seeps into the SPs. And we had let it to the discretion of the developers on whether to do the processing at Data or the Business Layer. I thought it would make sense to keep a rudimentary check on what logic was going into the SP and make sure our developers don't misuse the liberty and continue to follow the guidelines defined.

Read more »

Solving Sudoku using SQL Server 2005 - Step by Step - Part #6

Implementation of RunSolveAlgorithm4:

This is the last post in this series. The previous algorithm is the last one that tries to solve the puzzle logically. This one will take the latest unsolved sudoku board that we get after running through the first three algorithms and use brute force to solve the puzzle. I am not going to reinvent the wheel here.

Read more »

Solving Sudoku using SQL Server 2005 - Step by Step - Part #5

Implementation of RunSolveAlgorithm3:

The last algorithm that we implemented was able to solve an easy puzzle. Now, lets take a hard one and see if the solution we have built till now in this series can solve it.

 EXEC SolveSudoku 
'030,001,000,,006,000,050,,500,000,983,,080,006,302,,000,050,000,,903,800,060,,714,000,009,,020,000,800,,000,400,030'

Read more »

Solving Sudoku using SQL Server 2005 - Step by Step - Part #4

Implementation of RunSolveAlgorithm2:

We implemented RunSolveAlgorithm1 in previous post of this series . The next algorithm is the implementation of Solve Method A from sudoku solver.

In this algorithm, we check all the cells (having mutiple values) in each row and see if a particular value occurs only once in that row. Then update that as the solution for the cell having that value. We do the similar check for column and the 3X3 block.

Read more »

Solving Sudoku using SQL Server 2005 - Step by Step - Part #3

Implementation of RunSolveAlgorithm1:

In the previous post of this series, we created the procedure stub for each algorithm. This post will implement RunSolveAlgorithm1.

The first algorithm will do the primary clean up on the solution board. It will implement the basic rules of Sudoku. The rule is that a number cannot appear in a cell if it already appears in any other cell in the same row or column or the block in the sudoku board. It will remove those numbers from the possible cadidate values of each cell in the solution board.

Read more »

Solving Sudoku using SQL Server 2005 - Step by Step - Part #2

In the first part of this series, we created the base objects needed to work on our solution. Now, there are two parts to building the solution:

• The core algorithms that will solve the puzzle for us
• The surrounding objects that will facilitate the solve and execute the core algorithms

The core algorithms are the crux of this whole exercise. Each of these algorithm will ideally take the unsolved sudoku board and try to fill as many cells as possible using the logic implemented in the specific alogorithm.

Read more »

Solving Sudoku using SQL Server 2005 - Step by Step - Part #1

This one is going to be a series. I thought I was going to come up with a single query to solve Sudoku (without choosing the brute force method). When I started creating the tables I needed, I figured out there there are way too many aspects to solving Sudoku logically. I thought it would be a good idea to give a continous update as I go about building the solution.

This post is all about creating the necessary tables and basic procedures which I think we need to build the solution. 

Read more »

Pivot Query Generator for SQL Server 2000

This post is written with reference to the post Pivot Query Generator for SQL Server 2005. Please read through the post to get the context and the data setup before you read any further.

Now, that you have read through the other post, you will realise that in SQL Server 2005, we had the PIVOT operator to generate the crosstab reports. But in case of SQL Server 2000, we will have to use an indirect approach to generate the cross-tab. Here is the SQL Server 2000 counter part of the Pivot Query Generator of SQL Server 2005.

Read more »

Calculating the value of pi

I accept that this post looks a bit out of context for SQL, but its defenitely an interesting "garbage" to collect. For all having some aptitude in maths, you would wonder how the value of pi is derieved? Here is a small attempt in finding the value of pi from what we already know.

Read more »

Missing sp_who

The issue I am going to write on is something I had seen on a website quite sometime back. I don't remember any explaination given about the behavior. I tried to search for that site now but in vain. So, I am posting it anyways. And I will also be writing about the attitude I get from the guys who are Anti-Microsoft. My thoughts on it will be intertwined in my post and hence might seem that I am digressing from the topic at times :)
 

Read more »

Pivot Query Generator for SQL Server 2005

SQL Server 2005 has a new feature called the PIVOT operator. It's useful in generating cross tab reports. Well, let me start by giving an example.

Lets say we have a table called the distance_tbl that gives the distance between any two cities.

Read more »

Issue (seemingly) with the SET PARSEONLY option

This was actually a question raised in a discussion forum. Thought I will write about it anyways. In a database, lets say we don't have a table called orders.

Then the following script works fine, since syntactically its right and the select statement is just parsed.

Read more »

Interesting queries using recursive Common Table Expressions

I have just given an attempt to show how the recursive member of the common table expression (CTE) can be used.

A recursive CTE has 2 parts, the anchor member and the recursive member. The anchor member will first put in a set of rows into the CTE and the recursive member will use the newly added rows in the CTE to get a few more rows and adds it to the CTE. Now the recursive member will act upon the newly added rows again and this will go on till the recursive member does not produce any rows by acting upon the penultimate rows added to it.

Read more »

Function to get the fraction given a decimal value

This User-defined function, written in SQL Server 2000, which uses Euclid’s algorithm to convert the decimal to a fraction. The function has an accuracy of 0.0001 i.e. the input decimal value is rounded off beyond this when it’s converted to a fraction. If you need a better accuracy change the function accordingly.

Read more »

Feel the power of the new T-SQL

Just thinking if I could come up with a query that will use most, if not all, of the T-SQL enhancements of SQL Server 2005, using the adventureworks database here. And this was the best I could come up with.

Read more »

Funny behavior in SQL Server 2000 UDF

This is one interesting observation in User defined functions (UDF) in SQL Server 2000.
Be careful when you use a group by and have a scalar UDF in the select. What you get out of a query depends on what the query execution plan is. Try this out..

I am creating a very simple function.

Read more »

Recursive function with cross apply vs CTE in SQL Server 2005

Common Table Expressions (CTE) may not be the best way, performance wise, (in all cases) for recursive output. I don't want to come out too strong.
But I guess it can be proved with a small test.

Lets take the database AdventureWorks.
Say, I want to find all the subordinates for a given manager (here I take it as null for the top most manager) from the table HumanResources.Employee.

Read more »