One of the most important concepts pertaining to any DBMS that every database programmer must know is the Transaction Isolation Levels. Sometimes, even the most seasoned database developers get confused on how multiple connections running in different isolation levels affect each other. This post is an attempt to explain through examples on how the isolation levels differ from each other.
This post is about achieving pagination using stored procedure, without using dynamic SQL. The solution presented should work for both SQL Server 2000 and 2005.
Most of the web application displaying transactional data will have a data grid with sorting and paging functionality and some filter criteria.
We usually use the default sorting/paging functionality available in the datagrid. But, this requires the entire search resultset to be cached in the client side, which is fine for a few hundered records but can be a drag as the number of records increase.
Thinking in sets:
The thought process that goes into set- based programming is slightly different from writing procedural code.
Say, I have 2 tables -one holding order header information and the other holding the details of the products ordered. I want to get the items ordered for all the orders:
I don't really know why I am starting this series, if it will be really useful for anyone and whether I will be able to keep it running for long. But, its here because I am convinced that sometimes the most obvious escapes our mind. And as someone rightly said "common sense is not so common".
I just intend to illustrate few common issues(one per post) where we try to come up with complicate queries and realise, in the end, that we have a painfully obvious alternative. But, the question is - Is it obvious and is it really an alternative? Read ahead...
This post is an attempt to explain what parameter sniffing is all about and how it affects the performance of a stored procedure. I would be walking through an example to demonstrate what the effect of parameter sniffing is. I will be showing the query execution plan generated for SQL Server 2005. To my knowledge, there is no change, in how this process works, between the two versions and you should be able to relate it to SQL Server 2000 as well.
I had already blogged on how we can split a comma separated list using CTEs in my other post on Interesting queries using recursive Common Table Expressions , the idea of which was a slight modification to the one conceived and presented by Erland Sommarskog in Arrays and Lists in SQL Server.
After reading through the blog of Hugo Kornelis on using stuff for string manipulation, I realised that the STUFF function, indeed, made splitting a comma separated values string (CSV) pretty much easy.
This was actually a question that popped in the discussion forum. I just want to keep all the good things that come out on the discussion forum (where I had SOME role to play) in this blog :)
Here is the issue.
Say, I have a table like this..
I would like to add a note before you go ahead and read this article. Please do not take this as an approach guaranteed to work. I have come across instances when the solution I suggested didn't work. Please read through this, purely from an academic perspective, and do not, I repeat, do not incorporate these suggestions in your production environment.
I am still keeping the article as I had originally written since I would like to maintain the arguements and the counter arguements.
Before I start, please do not use these procs in your production code since they are undocumented, which means Microsoft does not support these procs and might remove it in future versions. With that said, I am just writing about three procedures which I find very useful. Its more of a shortcut for the DBAs to get their work done faster :)
For those who didn't know, the following function definition will throw an error in SQL Server 2000.
create function fn1()
returns datetime
as
begin
return getdate()
end
The error thrown is "Invalid use of 'getdate' within a function".
Thats because built in non deterministic functions cannot be used inside UDF. Well there is a work around for this.
Many a times, we get a question as to get all the days or sundays or weeks between two given dates and join it with someother table and get the result and the suggestion given in the discussion forum is to use a calendar table.
The scenario I am trying to discuss is to have a table which gives the running total of the values when you select from it. What I am not discussing is to generate the running total for a select query.
Okay, so here is the table definition.
This requirement was actually a question in the discussion forum. I thought I will keep a copy of my solution here for reference.
Okay, so here it goes.
I am taking the narration from the actual post (thanks to him), with a few changes.
Lets say I have a table like this.
Using a NOT operator, be it NOT IN or NOT EXISTS had been a performance bottleneck in most of the queries.
No knowledgeable SQL prgrammer will ever say that he has achieved the best performing query that can ever be written for a given functionality. Because, performance doesn't just depend on the query we write, but goes far beyond that. It includes (but not limited to) the indexes on the table, the data, fragmentation of the data and indexes, server load and server settings.
There are lots of queries on date operations like getting the first day of the month or week or quarter, last day given some date. So I thought I will put over here some basic implementations.
I will keep updating the post as and when I get to solve more queries on this.
There are quite a few number of ways by which we can delete the duplicate records from a table. I am just discussing 3 methods which I find interesting. I don't take ownership for any of these methods. I had seen these implementations put to use at different places. And I am just compiling it so that you get the different options in one place and chose the best one for yourself..
Most of the times when we talk about the ANSI join syntax, we say that we use the ON clause for the joining columns and where for filter. Though there is no rule that we need to include joining columns in the on clause or the filter in the where clause. It is only for readability.
Before you read further: A more detailed and original post of this scenario is in the msdn blog: Achieving Selective Uniqueness in SQL Server Tables . Thanks to the comment from an anonymous reader. Please read through that if you need to understand the mechanics behind the suggestion. And it obviously is a better read as it comes from the creators of SQL Server :)
-Omni
-------------------------------------------------------------
Okay. The scenario is this. I have a table tlb1 and a nullable int column col1. I want to create an unique constraint on col1 satisfying the following requirements.
1. Col1 can have multiple nulls.
2. All non-null values in Col1 should be unique.
Please Note: Before you go through this article, the approach I have given for SQL Server 2000 is unpredicatable and might fail. The only predictable way is to use a cursor and loop through it and concatenate the values. Please go through the links I had given in the post to understand the potential issues. I would suggest you not to use this approach in production environment. The suggestions for SQL Server 2005 (both XML and CLR), however, shouldn't have any problem.
-Omni
---------------------------------------------------------------
This is the most common question that comes in most discussion forums that I feel its high time Microsoft brings in an inbuilt function for it.
I cannot find a real necessity to do a case sensitive search in the database. And if it was needed the column or the database should have had the proper collation.
But beyond all these you want to do a case sensitive search on a column, then you can do it the following ways.
Posts
All Comments
