Systems Engineering and RDBMS

Archive for April, 2008

A simple inventory query

Posted by decipherinfosys on April 30, 2008

This is one of the common requirements so we thought we would put this out there on the blog as a solution.  At one of our clients, they had running inventory list of the items in their warehouse.  This inventory list was stored in a table which for the sake of brevity had these attributes that we were interested in:

CREATE TABLE INVN_ITEM
(
ITEM_NBR        VARCHAR2(20),
ITEM_QTY        NUMBER(10),
BIN_NBR            VARCHAR2(10),
DATE_CREATED    DATE
)
/

And here is a sample of how the data looked like:

item_nbr item_qty bin_nbr date_created
——– ——– ——- ————-
Item_1         10  D1        02/01/2008
Item_1         20  D2        02/01/2008
Item_1        100  D1        04/01/2008
Item_1         50  D2        04/02/2008
Item_1         30  D3        04/03/2008
Item_1         20  D8        04/04/2008
Item_2         10  D1        02/01/2008
Item_2         20  D2        02/01/2008
Item_2         77  D1        04/01/2008
Item_2         32  D2        04/02/2008
Item_2         52  D3        04/03/2008
Item_2         33  D8        04/04/2008

Whenever a new record was created in this table, the running total of the item’s quantity was input into the item_qty column based on an item and the bin_nbr that it was placed in.  Example: If Item_1 is present in bins D1, D2, D3 and D8, when a new record got created on say April 1st 2008 for item_1 in bin D1, the new 80 quantity got added to the existing 20 in that bin for that item and the record was created with 100.

What the report was supposed to show was the total quantity as of any given run for an item regardless of the bin numbers.

There are a lot of ways to do this.  We will show some of the approaches.

Approach #1: This approach uses the analytic function first_value() in Oracle.  Using that function and by ordering on the date_created in a desc fashion and by partitioning on the item_nbr and bin_nbr, one can select the max value for the item per bin and then apply the grouping at the top of that.

SQL> select item_nbr, sum(item_qty)
2    from (
3  select distinct item_nbr, bin_nbr,
4         first_value(item_qty) over(partition by item_nbr, bin_nbr
5                                       order by date_created desc) item_qty
6    from invn_item
7    )
8   group by item_nbr
9  /

ITEM_NBR   SUM(QTY)
——– ———-
Item_1        200
Item_2        194

Approach #2: This will work in Oracle as well as SQL Server:  This approach uses the MAX() function and a co-related sub-query:

SQL> select item_nbr, sum(item_qty)
2    from invn_item
3   where date_created = ( select max(date_created)
4                              from invn_item it2
5                             where it2.item_nbr = invn_item.item_nbr
6                               and it2.bin_nbr = invn_item.bin_nbr )
7   group by item_nbr
8  /

ITEM_NBR   SUM(QTY)
——– ———-
Item_1        200
Item_2        194

Another approach is to use the row_number() function (this is also available in SQL Server 2005) and partition on the item_nbr and bin_nbr and order by date_created desc and then pick up the first record in the outer query and do a grouping on it.  It will prevent the usage of the DISTINCT clause as well that was used in Approach #1 above.

Posted in Oracle, SQL Server | Leave a Comment »

Latches, Locks, Enqueues and Semaphores

Posted by decipherinfosys on April 30, 2008

A developer at a client site asked this question yesterday – “I know what is the difference between latches and locks but what are enqueues and semaphores – I have heard those terms used by the DBAs a lot…could you explain those for me?”.

Our answer was that latches and enqueues are both types of locks where as a semaphore is an OS supplied serialization device which a programmer uses to implement latching/enqueuing.  Latches are essentially lightweight serialization devices.  They are similar to spinlocks that spins inside a loop for the bit to be cleared up by another process – it is a mutex (mutual exclusion device).  Latches are used to serialize access to memory data structures like the SGA data structures.  Enqueues on the other hand are heavyweight serialization devices.  Enqueues are used to perform row level locking.  Unlike latches, these are actual locks on the data records.

Posted in Oracle | Leave a Comment »

Online version of Microsoft Dynamics CRM

Posted by decipherinfosys on April 29, 2008

Microsoft has provided an alternative to Salesforce.com with their latest offering of Dynamics CRM’s online version. You can read up more over here: Intelligent Enterprise Dynamics CRM and the Microsoft’s press release.

Posted in CRM | Leave a Comment »

SQL Server 2008 Deployment Guides from Dell

Posted by decipherinfosys on April 28, 2008

I was having a discussion with one of our client’s IT directors and he pointed out some deployment guides from Dell for SQL Server 2008 deployments. This client is one of the early adopters of SQL Server 2008 and is evaluating going live on it by the end of the year. These guides have a wealth of information in them and I would encourage everyone to read through them:

SQL Server 2008 CTP5, SQL Server 2008 on Windows 2003 and SQL Server 2008 on Windows 2008.

Posted in SQL Server, Windows | Leave a Comment »

Grid Computing for SQL Server?

Posted by decipherinfosys on April 28, 2008

There was an interesting post at Information Week. If the information is correct, then post the release of SQL Server 2008, MSFT will be looking at building up an enterprise scale out solution in the next release of SQL Server. Hmmmm…reminds one of RAC (Real Application Clusters) and Grid Computing which Oracle has had since ages 🙂 Well, that is good news for us, the consumers. You can read more about the difference in the clustering architecture between SQL Server and Oracle over here. This was/is one feature which will really help establish SQL Server as a big player in the enterprise roll outs.

Posted in SQL Server, Technology | Leave a Comment »

Traversing the hierarchy – Part II

Posted by decipherinfosys on April 26, 2008

Yesterday, we had covered how to traverse a hierarchy using CTEs. We had used the schema sample from a pharmaceutical industry as an example. In today’s post, let’s make use of a more generic example. This one pertains to a recursive 1:N relationship within the same table – and this scenario exists in almost all of the HR applications out there. An employee has a manager and the top dog in the company does not have anyone to report to (well, they do have to report to the board and the shareholders of the company but they do not have those set up as employees in the day to day activities of the organization :-)).

So, let’s take a look at the table and the data that we will use to illustrate this: The hierarchy relationship example can also be applied to the employees of a company. Let’s say that the table name is EMP_MASTER with this structure:

CREATE TABLE EMP_MASTER
(
EMP_NBR INT NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR(20) NOT NULL,
MGR_NBR INT NULL
)
GO

/******************************************
Now, let us insert some data into this
This uses the SQL Server syntax
*******************************************/

SET NOCOUNT ON

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO EMP_MASTER VALUES (1, ‘DON’, 5);
INSERT INTO EMP_MASTER VALUES (2, ‘HARI’, 1);
INSERT INTO EMP_MASTER VALUES (3, ‘RAMESH’, 2);
INSERT INTO EMP_MASTER VALUES (4, ‘JOE’, 2);
INSERT INTO EMP_MASTER VALUES (5, ‘DENNIS’, NULL);
INSERT INTO EMP_MASTER VALUES (6, ‘NIMISH’, 5);
INSERT INTO EMP_MASTER VALUES (7, ‘JESSIE’, 6);
INSERT INTO EMP_MASTER VALUES (8, ‘KEN’, 7);
INSERT INTO EMP_MASTER VALUES (9, ‘AMBER’, 8);
INSERT INTO EMP_MASTER VALUES (10, ‘JIM’, 6);
INSERT INTO EMP_MASTER VALUES (11, ‘JOHN’, 5);
INSERT INTO EMP_MASTER VALUES (12, ‘LUTHER’, 11);
INSERT INTO EMP_MASTER VALUES (13, ‘GARY’, 11);
INSERT INTO EMP_MASTER VALUES (14, ‘ROBERTO’, 12);
INSERT INTO EMP_MASTER VALUES (15, ‘IRA’, 13);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

/******************************************
Let’s do a simple select to look
at the data
*******************************************/

SELECT * FROM dbo.EMP_MASTER

EMP_NBR     EMP_NAME             MGR_NBR

----------- -------------------- -----------

1           DON                  5

2           HARI                 1

3           RAMESH               2

4           JOE                  2

5           DENNIS               NULL

6           NIMISH               5

7           JESSIE               6

8           KEN                  7

9           AMBER                8

10          JIM                  6

11          JOHN                 5

12          LUTHER               11

13          GARY                 11

14          ROBERTO              12

15          IRA                  13

/******************************************
Let’s create a view which will use a
CTE in order to traverse through
the hierarchy
*******************************************/

IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ’emp_hierarchy’
AND TABLE_SCHEMA = ‘dbo’
)
BEGIN
PRINT ‘View emp_hierarchy already exists…Dropping it and recreating’
DROP VIEW emp_hierarchy
END
ELSE
BEGIN
PRINT ‘Creating View emp_hierarchy’
END
GO
create view dbo.emp_hierarchy
as
with emp (traverse_path, emp_id, mgr_id, emp_name, level, lineage)
as (
select CONVERT(VARBINARY(MAX), emp_name) AS traverse_path,
emp_nbr,
Null as mgr_id,
emp_name,
1,
cast((‘/’ + str(emp_nbr,6,0) + ‘/’) as varchar(255))
from dbo.emp_master
where mgr_nbr is null
/*– the highest level in the organization*/

union all

select e.traverse_path + CONVERT(VARBINARY(MAX), em.emp_name) as traverse_path,
emp_nbr,
mgr_nbr,
em.emp_name,
e.level + 1,
cast((e.lineage + str(em.emp_nbr,6,0) + ‘/’) as varchar(255))
from dbo.emp_master as em
inner join emp e
on e.emp_id = em.mgr_nbr
)
select * from emp;
GO

If you look at the above view, it is only slightly different than the example that we had shown yesterday. The reason for the difference is that in yesterday’s example, we were handling a M:N recusrive relationship between the same entity and thus there were two tables to deal with while in today’s example, we are dealing with a single table and a 1:N recursive relationship. The logic remains exactly the same as before. We get the top dog in the company as the first record and then we do a UNION ALL operation to the recursive tree and use the traverse path as an ordering mechanism so that we can see the actual tree. The trick is to cast the employee name as a VARBINARY(MAX) and keep on appending it so that we can later on order by on it to see the complete tree structure for the organization. That helps us to see from the CEO – down to the SVP and their individual department immediately down from there till the deepest level is reached. And that is then followed by the next SVP and their hierarchy in the same fashion. And this continues that way to show a complete tree representation. The lineage column is used to just show the lineage of the tree.

You can see it by executing the following command:

select * from emp_hierarchy order by traverse_path
GO

Since the output is rather large, we will leave it up to you to execute these in your environment to see it. This is a quick and fast way to recursively build up hierarchy trees in SQL Server 2005 or Oracle and DB2 (with slight syntax differences). SQL Server 2008 also introduces a new data-type for hierarchy which we will cover in a later post.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

Traversing the hierarchy – Part I

Posted by decipherinfosys on April 25, 2008

Traversing the hierarchy in the user data is a common requirement in most of the software products. You must have faced this requirement when you are:

a) building up a tree for navigation, or
b) whether you are recursively trying to get the employee hierarchy in a HR application module, or
c) whether you are establishing the tree structure for drugs in pharmaceutical inudstry, or
d) you are traversing the hierarchy of the different parts in a manufacturing industry.

Above are just some of the examples. There are many such places where one is required to write code to help with traversing the hierarchy from the top level down to the bottom. If you look at our previous post which explains how to design a M:N recursive relationship within a schema – that example pertains to the pharmaceutical industry. It shows how to model a recursive M:N relationship within an entity into physical tables. We will use that as an example to write up T-SQL (SQL Server) code to show how to traverse through that hierarchy. We will make use of CTEs (Common Table Expressions) which are also present in Oracle and DB2 LUW (with slight differences). In Oracle, one can also make use of the CONNECT BY clause in order to traverse the hierarchy – we will cover that in a later post.

Let us create the view which will have the CTE and the logic for recursion:

IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ‘drug_hierarchy’
AND TABLE_SCHEMA = ‘dbo’
)
BEGIN
PRINT ‘View drug_hierarchy already exists…Dropping it and recreating’
DROP VIEW drug_hierarchy
END
ELSE
BEGIN
PRINT ‘Creating View drug_hierarchy’
END
GO
create view dbo.drug_hierarchy
as
with drug (traverse_path, drug_id, parent_drug_id, drug_name, level, lineage)
as (
select CONVERT(VARBINARY(MAX), drug_name) AS traverse_path,
drug_master_id,
Null as parent_drug_id,
drug_name,
1,
cast((‘/’ + str(drug_master_id,6,0) + ‘/’) as varchar(255))
from dbo.drug_master
where drug_master_id not in (select child_drug_id from dbo.drug_rltn)
/* the highest level in the tree*/

union all

select d.traverse_path + CONVERT(VARBINARY(MAX), dm2.drug_name) as traverse_path,
dr.child_drug_id,
dr.parent_drug_id,
dm2.drug_name,
d.level + 1,
cast((d.lineage + str(dm.drug_master_id,6,0) + ‘/’) as varchar(255))
from dbo.drug_master as dm
inner join dbo.drug_rltn as dr
on dm.drug_master_id = dr.parent_drug_id
inner join dbo.drug_master as dm2
on dr.child_drug_id = dm2.drug_master_id
inner join drug as d
on d.drug_id = dm.drug_master_id
)
select * from drug;
GO

The logic of the view is that we first get the top record in the DRUG_MASTER table as the first record (this one is not a child of any drug – if such a record does not exist, just create a dummy one in your table) and then we do a UNION ALL operation to the recursive tree and use the traverse path as an ordering mechanism so that we can see the actual tree. The trick is to cast the drug name as a VARBINARY(MAX) and keep on appending it so that we can later on order by on it to see the complete tree structure for the drug hierarchy. That helps us to see from the top drug – down to the next one in alphabetical order and their individual drug components immediately down from there till the deepest level is reached. And that is then followed by the next drug under the parent drug and their hierarchy in the same fashion. And this continues that way to show a complete tree representation. The lineage column is used to just show the lineage of the tree.

You can see it by executing the following command:

select * from drug_hierarchy order by traverse_path
GO

Since the output is rather large, we will leave it up to you to execute these in your environment to see it. This is a quick and fast way to recursively build up hierarchy trees in SQL Server 2005 or Oracle and DB2 (with slight syntax differences). SQL Server 2008 also introduces a new data-type for hierarchy which we will cover in a later post.

Tomorrow, we will cover the second part of this post which will go into how to recursively traverse a tree hierarchy by using a 1:N recursive relationship in a table like the Employee structure in an organization.

Posted in DB2 LUW, Oracle, SQL Server | 1 Comment »

Designing Physical tables for a M:N recursive relationship within an Entity

Posted by decipherinfosys on April 25, 2008

This requirement is pretty common in some of the industries: Many times, the same entity can have a recursive M:N (Many-to-Many) within itself.  For example:

In the pharmaceutical industry, a drug can be composed of many drugs and vice-versa.  Likewise, in a manufacturing industry, a part can be composed of many parts and vice-versa.  The way to model this logical design into it’s corresponding physical implementation is by splitting the entity out into two physical tables with two 1-n (one-to-many) relationship between the main table and the child table.  This table will only be used to capture the recursive relationship and any other attributes that are specific to that relationship.

Example:

/*********************************************************************************
We will only consider 2 attributes for the sake of the explanation
**********************************************************************************/
CREATE TABLE dbo.DRUG_MASTER (DRUG_MASTER_ID INT NOT NULL IDENTITY, DRUG_NAME NVARCHAR(100), CONSTRAINT PK_DRUG_MASTER PRIMARY KEY (DRUG_MASTER_ID));
CREATE TABLE dbo.DRUG_RLTN (PARENT_DRUG_ID INT NOT NULL, CHILD_DRUG_ID INT NOT NULL, CONSTRAINT PK_DRUG_RLTN PRIMARY KEY (PARENT_DRUG_ID, CHILD_DRUG_ID));

And we will now have two Foreign Keys from the child to the parent table to help support the two 1:N relations that we have put into place:

ALTER TABLE dbo.DRUG_RLTN ADD CONSTRAINT FK_DRUG_RLTN_PARENT_TO_DRUG_MASTER FOREIGN KEY (PARENT_DRUG_ID) REFERENCES dbo.DRUG_MASTER (DRUG_MASTER_ID),
CONSTRAINT FK_DRUG_RLTN_CHILD_TO_DRUG_MASTER FOREIGN KEY (CHILD_DRUG_ID) REFERENCES dbo.DRUG_MASTER (DRUG_MASTER_ID)
GO

Let us insert some data now to show how this all ties up:

SET NOCOUNT ON
GO
SET IDENTITY_INSERT DRUG_MASTER ON
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (1, ‘DRUG A’);
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (2, ‘DRUG B’);
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (3, ‘DRUG C’);
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (4, ‘DRUG D’);
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (5, ‘DRUG E’);
SET IDENTITY_INSERT DRUG_MASTER OFF

INSERT INTO dbo.DRUG_RLTN (PARENT_DRUG_ID, CHILD_DRUG_ID) VALUES (1, 2);
INSERT INTO dbo.DRUG_RLTN (PARENT_DRUG_ID, CHILD_DRUG_ID) VALUES (1, 3);
INSERT INTO dbo.DRUG_RLTN (PARENT_DRUG_ID, CHILD_DRUG_ID) VALUES (2, 5);
INSERT INTO dbo.DRUG_RLTN (PARENT_DRUG_ID, CHILD_DRUG_ID) VALUES (5, 4);

As you can see from above, the M:N relationship is being captured in the relationship table DRUG_RLTN.

  • DrugA comprises of DrugB and DrugC
  • DrugB comprises of DrugE, and
  • DrugE comprises of DrugD

In the next post, we will consider how you go about traversing this hierarchy using SQL and will use this as an example.  We will also use the classic employee structure in an organization as an example to show how to traverse hierarchies in SQL Server, Oracle and DB2.

Posted in Data Model | 1 Comment »

Basic SQL Training Material

Posted by decipherinfosys on April 23, 2008

At the request of one of our clients, we are going to prepare and do basic SQL training sessions for them which will be later on followed with more advanced topics. Here is the current tentative outline:

Basic SQL Training: 20 hours

All the training will be provided using AdventureWorks database.

Class 1: 4 hours

1. Introduction to RDBMS (Relational Database Management Systems),
2. Concept of an Instance vs a Database in different RDBMS,
3. Concept of a login and a user and a schema,
4. Concept of a session,
5. Concept of a client and a server process,
6. Concept of session vs server level settings,
7. Introduction to SQL Server Editions,
* Express
* Standard
* Enterprise
* Workgroup
* Mobile
* Developer
8. Introduction to different SQL Server Tools and Utilities,
* SSMS
* SSRS
* SSIS
* Profiler
* BIDS
* SSAC
* SSCM
* SSNS
* Command Line Utilities

Class 2: 4 hours

1. Basic storage data types,
2. Tables, Relationships and Constraints,
* Tables, Constraints and Indexes
* Defining columns and attributes
* Selecting appropriate datatypes
* Setting the primary keys and indexes
* Modifying or removing columns
3. Introduction to SQL – Basic statements,
* INSERT (C)
* SELECT (R)
* UPDATE (U)
* DELETE (D)
4. Joins,
* INNER JOIN
* OUTER JOIN
* FULL JOIN

Class 3: 4 hours

1. Different basic SQL constructs,
* UNION, UNION ALL
* Different Types of functions
* Loops
* Cursors
* SET based Operations
2. Basic meta-data queries,
3. Concept of different object types – Views, Stored Procedures, Functions, Triggers etc.
4. Basic concepts of the storage – Pages/Blocks, Indexes, Heaps, blobs, XML etc.
5. Basic Security Concepts
* Allowing logins access to databases
* Creating SQL Server logins
* Implementing a security plan
* Defining database roles
* Granting permissions on tables to roles
6. Basic Data Export/Import
* Importing data into SQL Server
* Exporting data to text or CSV format

Class 4: 4 hours

1. Using SQL Profiler to monitor the activity.
2. Using SSRS as a reporting platform.
3. Solving some real life problems with SQL,
4. Using BIDS for SSIS packages,

Class 5: 4 hours

1. Performance Tuning,
2. Good coding Practices,
3. Tuning Tips and Guidelines.

If you are interested in receiving the training materials/videos/scripts, please send an e-mail to us at: Training Material and we will e-mail them to you at no cost.

Posted in SQL Server | Leave a Comment »

Dynamic SQL, Dynamic Ordering and Paging

Posted by decipherinfosys on April 22, 2008

In some of our previous posts, we have covered both Paging of recordsets as well as conditional ordering using the CASE statement in the Order By clause. In today’s post, we will address one of the questions that a reader had recently asked. She wanted to know how to support dynamic ordering in a procedure that does paging using one of the techniques that we had presented here (using the ROW_NUMBER() function).

Let’s create some sample tables first – her example used only 2 tables – a parent and a child so we will create along the same lines:

SET NOCOUNT ON

GO

CREATE TABLE TAB1 (COL1 INT NOT NULL IDENTITY PRIMARY KEY, COL2 NVARCHAR(10));

CREATE TABLE TAB2 (COL3 INT NOT NULL IDENTITY PRIMARY KEY, COL4 NVARCHAR(10), COL5 NVARCHAR(10), COL6 INT, COL1 INT,

CONSTRAINT FK_TAB2_TO_TAB1 FOREIGN KEY (COL1) REFERENCES TAB1(COL1));

INSERT INTO TAB1 (COL2) VALUES (‘ABC’);

INSERT INTO TAB1 (COL2) VALUES (‘XYZ’);

INSERT INTO TAB1 (COL2) VALUES (‘ZEF’);

INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST1’, ‘APPLE’, 10, 1);

INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST2’, ‘GUAVA’, 20, 1);

INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST3’, ‘MANGO’, 30, 2);

INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST4’, ‘ORANGE’, 40, 2);

INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST5’, ‘PINEAPPLE’, 50, 3);

She wanted to pass in dynamic filter criteria as well as dynamic sort criteria and dynamic sort column. The way that they had done was by making use of dynamic SQL and then executing it using EXEC(). Not only is that bad for performance but this also can cause SQL injection issues. That does not mean though that this cannot be done in a better way by using dynamic SQL. One can make use of sp_executeSQL and follow the right rules in order to make it work and perform nicely. We would like to point you to Erland’s site where he has covered this in great detail (he is a MVP and the article is considered a bible on dynamic SQL usage in SQL Server). Read the conclusion as well where he mentions that dynamic SQL (if used correctly), can give the best performance.

This will also help you understand that dynamic SQL is not always bad which some people in the industry have adopted as a thumb rule. It is useful in scenarios when you have dynamic search conditions on multiple table joins and dynamic filter criterias on different tables.

Here are the URLs:

http://www.sommarskog.se/dyn-search.html

http://www.sommarskog.se/dynamic_sql.html

Now, coming back to our example, we will make use of the STATIC SQL trick that Erland has mentioned as well as make use of sp_executeSQL and show both approaches and in both of them, we will cover how to achieve dynamic ordering as well since that remains the same regardless of the approach.

Here is the SQL code for the STATIC SQL (we have created a stored procedure for this):

http://www.decipherinfosys.com/SQL_STATIC.txt.txt

And here is the SQL code for the DYNAMIC SQL:

If you read Erland’s article, then understanding the flow of the code becomes easier so rather than going through the code line by line, we would recommend that you first read his article and then follow along. The key differences are that in these stored procedures, we also need to do paging and that is based off the input parameters for the starting record number and the max number of rows that need to be displayed for the page i.e. the page size. The code needs to return back only the specified sub-set of the rows and also needs to return the total count of the records that the actual query qualified so that one can display the total count (NOTE: There are other ways of doing this as well – read our post – one more paging records dilemma).

The STATIC code uses the (@X IS NOT NULL and COL5 = @X) trick in order to make sure that the performance is acceptable. It is easy to understand code and also allows easy usage. You do need to understand when to use this methodology. This search procedure meets all the three criterias which are necessary for going with the STATIC SQL route in this case:

a) Single Table search columns.

b) All columns are indexed.

c) Atleast one search condition needs to be provided for the filter condition.

The dynamic SQL code makes use of the system stored procedure SP_EXECUTESQL and uses a parameter list and then passes in the parameters into this stored procedure. That way, it makes use of parameterized queries and hence enables the plan re-use.

The dynamic ordering in the code is achieved by this portion of the code:

,ROW_NUMBER() OVER (ORDER BY

CASE WHEN @sortExpression = ‘COL4’ and @sortAscending = 0 THEN B.COL4 END DESC,

CASE WHEN @sortExpression = ‘COL5’ and @sortAscending = 0 THEN B.COL5 END DESC,

CASE WHEN @sortExpression = ‘COL6’ and @sortAscending = 0 THEN B.COL6 END DESC,

CASE WHEN @sortExpression = ‘COL2’ and @sortAscending = 0 THEN A.COL2 END DESC,

CASE WHEN @sortExpression IS NULL and @sortAscending = 0 THEN B.COL3 END DESC,

CASE WHEN @sortExpression = ‘COL4’ and @sortAscending = 0 THEN B.COL4 END ASC,

CASE WHEN @sortExpression = ‘COL5’ and @sortAscending = 1 THEN B.COL5 END ASC,

CASE WHEN @sortExpression = ‘COL6’ and @sortAscending = 1 THEN B.COL6 END ASC,

CASE WHEN @sortExpression = ‘COL2’ and @sortAscending = 1 THEN A.COL2 END ASC,

CASE WHEN @sortExpression IS NULL and @sortAscending = 1 THEN B.COL3 END ASC

) AS ROWNUM

Using this piece of code, we can avoid the data-type conversion that can happen when you have a single CASE statement. Also, we can just check for the sort expression and the sort order and then use the right column and order for the row numbering. The paging logic is based on the ROW_NUMBER() function like we have explained in some our paging posts before. Using either of the STATIC Or the DYNAMIC route, one can obtain the optimal performance as well as meet the functional requirements.

Posted in SQL Server | 1 Comment »