Configure SQL Jobs in SQL Server using T-SQL

Total: 2 Average: 5

SQL Server Agent is a component used for the database tasks automation. For instance, we need to perform Index maintenance on Production servers during the non-business hours only. So, we create a SQL Server job of running index maintenance and schedule it for “off” hours.

When we install SQL Server, the SQL Server Agent service is disabled. First, we enable it and start it manually. Then, we configure the SQL Server job, using SQL Server Management Studio and the system stored procedures of the MSDB database.

This article explains how to create a SQL Server Job using the system stored procedures of the MSDB database.

Read More

Learn Basic Data Analysis with SQL Window Functions

Total: 10 Average: 3.8

This article is about T-SQL (Transact-SQL) Window functions and their basic use in day-to-day data analysis tasks.

There are many alternatives to T-SQL when it comes to data analysis. However, when improvements over time and introduction of Window functions are considered, T-SQL is capable of performing data analysis on a basic level and, in some cases, even beyond that. Read More

SQL Server Triggers: Understanding and Alternatives

Total: 80 Average: 4.1

SQL Server triggers types

The SQL Server trigger is a particular type of stored procedures – it runs automatically when an event occurs in a specific database server. There are two main types of triggers: DML Triggers and DDL triggers.

DML triggers

SQL Server DML Triggers are specific stored procedures designed to react to Data Manipulation Language (DML) events by performing a sequence of actions on a database table, to which the trigger is attached.

DML events are INSERT, UPDATE, or DELETE actions, which occur to modify the database tables’ content or views. Here, the DML triggers fire, no matter if the table rows get affected by those events. Though triggers belong to stored procedures, they have particular differences. The critical one is that triggers always work automatically when the predefined data modification occurs.

Read More

Methods to Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

Total: 75 Average: 4.2

SQL Server provides us with many window functions, helping to perform calculations across a set of rows without the need to repeat calls to the database. “Window” has no relations to Microsoft Windows. It is a term for the rows set that the function will process.

SQL Server provides us with many window functions, helping to perform calculations across a set of rows without the need to repeat calls to the database. 

Ranking Window Functions are among the most useful window functions types. They rank specific field values and categorize them according to each row’s rank. As a result, we get a single aggregated value for each participant-row. But unlike the standard aggregate functions, window functions don’t group rows into a single output row. Instead, they return a single aggregated value for each row, keeping separate identities for those rows.

Note that “Window” has no relations to Microsoft Windows. It is a term for the rows set that the function will process.

Read More

Introducing Common Table Expressions in SQL Server

Total: 1 Average: 5

Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.

Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.

Read More