The problem is: how to get one million numbers to table with less time? We can solve this problem using different approaches but not all of them are quick. Let’s go now step by step and see how different approaches perform.
First idea for many guys is using WHILE. It is robust and primitive approach … Read More →
Gaps and islands problems involve missing values in a sequence. Solving the gaps problem requires finding the ranges of missing values, whereas solving the islands problem involves finding the ranges of existing values. The sequences of values in gaps and islands problems can be numeric, such as a sequence of order IDs, some of which … Read More →
DOTNET exposes several ways to achieve mutual exclusion in code such as Mutex, Lock, SynLock, Manual reset event or Thread wait etc. The same thing can be achieved even in SQL Server using application locks. It is achieved by using 2 system stored procs (sp_getapplock and sp_releaseapplock)
However to be able to call sp_getapplock a user … Read More →
LOCK_TIMEOUT is not only a session-level setting, it also needs to be set in a separate batch. But, it cannot be set via a variable. So, this can be accomplished by using Dynamic SQL in the "master" Stored Procedure. This will allow for setting LOCK_TIMEOUT and then executing whatever other Stored Procedure that should run … Read More →
xp_dirtree has three parameters:
directory - This is the directory you pass when you call the stored procedure; for example 'D:\Backup'.
depth - This tells the stored procedure how many subfolder levels to display. The default of 0 will display all subfolders.
file - This will either display files as well as each folder. The default of 0 … Read More →
Scripts those can be used by SQL Server DBAs and SQL Server Developers on daily basis.
Data Analysis / Data Validation / Data Cleansing Scripts
How to Search in all Columns for all tables in a database for Date Value in SQL Server
How to Find Percentage of Null Values in every Column of all the Tables in … Read More →
In the following example we will looking for the LOCAL FAST_FORWARD parameter in to the declaration of cursor code.
Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by … Read More →
SQL Server APPLY operator has two variants; CROSS APPLY and OUTER APPLY
The CROSS APPLY operator returns only those rows from the left table expression (in its final output) if it matches with the right table expression. In other words, the right table expression returns rows for the left table expression match only.
The OUTER APPLY operator … Read More →
There is a stored procedure that must be run by only one user at a time. How do to I prevent multiple users from running the stored procedure at the same time?
SQL Server provides an application manageable lock mechanism through the sp_getapplock / sp_releaseapplock pair of system stored procedures. They provide a way for application … Read More →
In a typical data warehousing application, quite often during the ETL cycle you need to perform INSERT, UPDATE and DELETE operations on a TARGET table by matching the records from the SOURCE table.
You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do … Read More →