T-SQL – SQLpathy

Application Tuning vs Database Tuning

sqlpathy — Sat, 04 Aug 2018 04:57:39 +0000

In the war between Application developers and Database developers - A win-win situation is one where both become friends. Performance Tuning is an art, and requires both application and database developer minds to work in sync!

ERROR:17892 Database login issue due to logon-trigger

sqlpathy — Wed, 12 Dec 2012 18:07:25 +0000

I was working with Logon triggers and was stuck-up in a very interesting issue. Tried to create one trigger named “Logon_trigger” which writes auditing information in audit table in DB named “TestDB”. Once I finished my testing, I dropped the database [TestDB] without dropping the trigger. Unfortunately when I tried to connect to SSMS after …

Continue reading ERROR:17892 Database login issue due to logon-trigger

List jobs residing on a particular database

sqlpathy — Thu, 04 Oct 2012 10:10:57 +0000

Below query become very handy when it is required to list all the jobs residing on a specific database. If it is required to get the complete list of jobs on a server along with the database they reside on, below query helpful.

Myth: Truncate cannot rollback

sqlpathy — Tue, 25 Sep 2012 04:09:39 +0000

There is plethora of reasons which people give when asked if truncate can rollback. Some say it does not rollback as it is a DDL (Data Definition Language) statement and DDL statements are not allowed in explicit transactions. Other view is that as truncate does not create entries in transaction log file it will not …

Continue reading Myth: Truncate cannot rollback

ISDATE() revisited

sqlpathy — Tue, 28 Aug 2012 16:41:25 +0000

ISDATE is a wonderful function used to test if the underlying value is a valid date. I have often found developers wink-their-eyes in shock over ISDATE’s non-deterministic behaviour. I hope this small presentation will help developers use ISDATE with care. What this function does? ISDATE function returns 1 if the value/expression under test is a …

Continue reading ISDATE() revisited

How to acquire a table lock for specific time?

sqlpathy — Wed, 22 Aug 2012 14:32:01 +0000

Recently one of my colleagues asked me how to acquire an exclusive lock on a database table for a specific amount of time. And this lock should be released once the specified time expires. You need two components to accomplish this task: 1) Table hints – that puts exclusive lock on the table 2) Delay …

Continue reading How to acquire a table lock for specific time?

LIST Partitioning in SQL Server

sqlpathy — Thu, 09 Aug 2012 16:14:08 +0000

SQL Server offers only range partitioning our tables, but what if I want to partition my table on the basis of List of values? Is it not doable? The answer is Yes and No both – Yes because SQL Server offers only range based partitioning and No because, there is a trick to partition the …

Continue reading LIST Partitioning in SQL Server

Date sequence generator using CTE

sqlpathy — Sun, 29 Jul 2012 09:46:11 +0000

Today we will learn how to generate a sequence of dates. Usually “Date sequence generator” has a wide variety of use, we will touch up this in our next blog. Before getting into generating a sequence of dates let us first generate a number sequence . Here is the example to generate a sequence from …

Continue reading Date sequence generator using CTE

Operating on consecutive rows/columns

sqlpathy — Sun, 22 Jul 2012 12:27:53 +0000

Today we will learn how to operate upon two consecutive rows. The logic here can be extrapolated and applied to as many rows as you can. To start with, suppose there is a process which runs every day and it produces certain quantity of product (as output). I need to know by how many products …

Continue reading Operating on consecutive rows/columns

Performing SUM over a column of datatype TIME

sqlpathy — Sun, 15 Jul 2012 14:31:02 +0000

Few days back one of my colleague asked me a very interesting problem of adding time. He wanted to know how to add time in a similar fashion just add we add integers using SUM() function. This can be done very easily: Let us first create a table time_store to store TIME format [hh:mm:ss.msc] and …

Continue reading Performing SUM over a column of datatype TIME