Recent PostsDuring data cleansing of imported data from some legacy system, we find that some values are actually spelled incorrectly and therefore, they are not getting included in the result of a query. Finding all the misspelled versions of a given value is quite difficult if you have thousands or millions of records.Fortunately, there is a [...]
Today I want to share with you a great resource to learn about SQL Server 2005. Just download the SQL Server Samples and Samples Databases from
http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en
It contains more than 100 Samples and five databases which are compatible with SQL Server 2005 SP2 and will give you excellent idea about nearly every type of task you [...]
Retrieving n most recent orders from each customer is a quite frequent requirement from management reports. Before SQL Server 2005, there were tricky and sometimes unmanageable TSQL solutions for this problem. In SQL Server 2005, you can use Row_Number() to get the n most recent orders. Following query uses AdventureWorks database to demonstrate the [...]
If you want to retrieve the permissions available for the currently logged in user then you can use the function called fn_my_permissions in SQL Server 2005. Following are examples of its usage:
Use AdventureWorksGo—Return Caller’s permission on the current serverSELECT * FROM fn_my_permissions(NULL, ‘SERVER’);Go
—Return Caller’s permission on the current databaseSELECT * FROM fn_my_permissions(NULL, ‘DATABASE’);Go
—Return Caller’s permission [...]
Have you ever faced the problem of assigning more than 8000 characters to varchar data type or more than 4000 characters to nvarchar data type? If your answer is yes, then I can understand your frustration due to this small limit. Since SQL Server uses 8KB page to store data to disk therefore, it [...]
Sometimes we require to display values for all days of a month. The problem with this is if you have properly normalized database then there is a chance that you may be recording data for only those days which have any value or in simple words you are recording the data as date, value pair. [...]
Finding Nth highest value is one of the most common requirements of Reports. For example, you may have a requirement of finding 5th Highest salaried employee or 3rd most expensive item etc. In SQL Server 2005, it is now very easy to find Nth item in the result because of the Row_Number function. Using Row_Number [...]
Sometimes you need to find gaps in values of the given integer column. For example, you need to find gaps in the id column of a given table so that you can use those values for new records to fill the gaps. Mostly it happens when we import data from some other system which does [...]
SQL Server 2005 has introduced a new operator called “EXCEPT”. It is a good shortcut for Distinct Not In queries. EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table. On the other hand “NOT IN” will return all rows from left hand side table [...]
Every SQL developer feels the need for INSERT statements generator script for a given table data. I have found a very good script to accomplish this task. With some minor modifications I have created the following stored procedure which takes table name as input and generates insert statements. Following is the stored procedure code:
Create Proc [...]
