SQL Server

The below is a list of all of the articles that have been written on this blog.

SQL Server Error Log – Search

The SQL Server error log contains user-defined events and certain system events you can use for troubleshooting. The errors and the informational data will be stored in the error logs. The data will be huge and sometimes it will be difficult to find the information or error from it.  Below is the query to retrieve…

Continue reading

Keep reading

SQL Server 2016, 2017 and 2019 New Functions

There are several string and analytical functions introduced with the newer versions of SQL Server and are listed below. STRING_SPLIT is a table-valued function introduced in SQL Server 2016 (13.x) that splits a string into rows of sub-strings, based on a specified separator character. Discussed about its functionality and usage in my previous blog. Please…

Continue reading

Keep reading

Convert Delimited Data Into Columns In SQL Server

This article addresses the conversion of the delimited data into columns in SQL Server. There are two functions called “STRING_SPLIT” and “PARSENAME” that helps us convert the delimited data into a single and multiple columns.  Consider the below data set for practice. Data1456, ConvertedData1, SomeOtherData11466, ConvertedData2, SomeOtherData21476, ConvertedData3, SomeOtherData4 Let’s create the table and load…

Continue reading

Keep reading

Reserved Keywords As Columns In SQL Server

In this post, we’re going to talk about using reserved keywords as column names. This is very similar to the previous “IMPALA – Reserved Keywords As Columns” article, but we will see how it can be implemented in SQL Server this time. It is not best practice to use the reserved keywords for tables and…

Sending Email Through SQL Server (Part-2)

If you’re an aspiring learner and you haven’t been through the previous part, please click here. If you are already aware of the basic implementation, read on. 8) Send a mail in a simple text format based on a query with clauses.USE msdbGO DECLARE@iDeptID INT,@SQL NVARCHAR(MAX),@Tab char(1)=char(9) SELECT @iDeptID = MAX(DeptNo) FROM TestDB.dbo.Dept WITH (NOLOCK)SELECT…

Continue reading

Keep reading

Sending Email Through SQL Server (Part-1)

This topic describes how to use DBMail in various scenarios. To avail the functionality, database mail must be configured in your environment. 1) Send a test email from SQL Server using DBMail.2) Send a test email to multiple recipients in “TO” section.3) Send a test email to different recipients in different fields (To, CC and…

Continue reading

Keep reading

Status of SQL Server Agent

SQL Server Agent is a Microsoft Windows service that executes scheduled jobs and DBAs need to ensure the Agent is running. There are several methods to know the status of the SQL Server Agent. 1) Check if the SQL Server Agent is running in Services. Control panel –> Windows Administrative Tasks –> Services 2) In SSMS…

Continue reading

Keep reading

XML in SQL Server

This is my second post on XML after 10 years. I was contented with the earlier post however I experienced more over these years and learned many things which I wanted to share with the current and future generations. Let me begin with the basics. XML is a file extension for an Extensible Markup Language…

Continue reading

Keep reading

XML in SQL Server – Part-2

In the previous part, we learned how to use the default modes and how we can change the shapes of the XML model using the same available modes. We will learn how to read the XML file that is generated by SQL Server. The code shown in the screenshot below returns a different mode than the actual modes that are available. Once the query is executed, the results are shown in…

Continue reading

Keep reading

Approximate Count of Unique Values in SQL Server

In my previous blog, I discussed the approximate count of unique values for which a predefined function is available in Impala (NDV), Oracle 12c (APPROX_COUNT_DISTINCT) and in MongoDB (estimatedDocumentCount()).  However, I didn’t mention about SQL Server since there is no such function available in the versions I am using. However, the function has been introduced…

Continue reading

Keep reading

Identify the active node in SQL Server Cluster

Probably most of the DBAs are already aware of how to identify which is active SQL Server node in the clustered environment, however, this will certainly help the beginners. The dynamic management views and functions return server state and database state information. A user who has permissions to VIEW SERVER STATE on the server and…

Continue reading

Keep reading

Complete TSQL with Examples

Complete document on TSQL with multiple examples I created a document on TSQL (Transact SQL) in Microsoft SQL Server. While I have written many of the pages and examples, I do not take all the credit as an author, as few blocks or pages or examples are copied from various web sources. This will give…

Continue reading

Keep reading

SQL Sample project

A simple project in SQL Server for beginners and intermediate levels It is important to practice and thus apply the concepts in various situations in order to effectively understand each definition in SQL. This will give a better understanding of each function and how they can be manipulated to make your job easier! Hence, I…

Continue reading

Keep reading

SQL Assignment-1

For beginners to intermediate levels, this is a quick test / assignment to check how familiar they are with SQL. Such types of exercises will certainly help to improve skills. Practice and exercise problems are the easiest way to learn something. This has nothing to do with Microsoft exams or its pattern. This is just…

Continue reading

Keep reading

TOP, LIMIT, ROWNUM vs DENSE_RANK

What would you do if you were asked to identify top-ten products based on their prices? In SQL Server, using a TOP clause with a specified number of records with descending order of price? In MySQL and Impala, using a LIMIT clause with a specified number of records with descending order of price? Basically, TOP…

Continue reading

Keep reading

Analytical & Window Functions

Please refer to my previous post in which schema and data for EMP and DEPT tables available. In this article we are about to discuss about SQL Server Analytical and Window functions. As stated in Microsoft docs, analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, however, analytic functions…

Continue reading

Keep reading

Get Table Row Count And Used Size

The following query will help you in checking the total number of rows and the size of the table(s).  SELECTs.Name AS SchemaName,t.Name AS TableName,p.rows AS RowCounts,CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,CAST(ROUND((SUM(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MBFROM sys.tables tINNER JOIN sys.indexes…

Continue reading

Keep reading

Last Modified Date – Stored Procedure

Usually, several stored procedures will be provided to DBAs to deploy in the Production environment. Some of the existing stored procedures need to be replaced or sometimes the stored procedures are new to the environment. If the deployment process is not automated, it can cause some confusion in DBAs if the existing stored procedure is…

Continue reading

Keep reading

Import from Excel – Named Sheet

This article is to let you know how to import Excel data into SQL Server. First of all download a set of components i.e OLE DB support that can be used to facilitate the transfer of data between Microsoft Office System files and non-Microsoft Office applications. Please click here for the Microsoft download link. The…

Continue reading

Keep reading

Generate multiple CSV data files from SQL

Assume that there are thousands of rows in a table and the rows need to be split and saved in different CSV files based on date or category or status. Have you ever come across such a requirement? If so, here is the code to accomplish it. USE TestDB1GO DECLARE @TestData20 TABLE(IntValCol INT, DateCol DATETIME)…

Continue reading

Keep reading

Find a specific column from the database objects

There are many ways to find out a specific column from the database objects like Tables and stored procedures. 1) Red Gate’s SQL Search:This tool will be integrated into SQL Server Management Studio once installed. This will help in searching across multiple object types and multiple databases. 2) Using a query:The following query will find the…

Continue reading

Keep reading

Aggregate Functions in Analytic Context

The functions SUM, AVG, COUNT, MIN, and MAX are well-known aggregate functions that we use every day. They are to compute/summarize the multiple rows by grouping them and provide a single summary value. However, when it comes to analysis, the aggregate functions carry out twofold responsibility: The same aggregate function which computes on each row…

Continue reading

Keep reading

Best Usage of NTILE function in SQL Server and other RDBMSs

SQL Server NTILE() is a window function that distributes rows of an ordered partition right into a unique quantity of approximately same partition or group or buckets. The use of the function is described with a real time problem scenario.  Problem Scenario: An agency is doing unique sort of promotional events on 1st of each month. The administration wanted to peer, which promotional event has benefited the organization in each region.  USE dbTestGO CREATE TABLE…

Continue reading

Keep reading

Creating Linked Server for PostgreSQL in MSSQL

Creating a linked server in Microsoft SQL Server to connect PostgreSQL 1) Download the PostgreSQL Unicode (x64) driver to support ODBC. 2) Go to ODBC Data Source Administrator in your machine and create the system DSN. 3) Go to SQL Server Management Studio and execute the below command in a new query window. EXEC master.dbo.sp_addlinkedserver @server…

Continue reading

Keep reading

SQL Server – CASE Function- Examples

This article talks about an equivalent of Oracle SQL’s DECODE function in SQL Server. There is no such function in SQL Server but through CASE function we can construct the same expression. (Update: IIF can be used as an alternative since SQL Server 2012 onwards) CASE function evaluates a list of conditions and returns one…

Continue reading

Keep reading

SQL Server – Self Joins

To join tables, at-least two tables are required. This does not indicate two different tables. It is possible to join a table with itself to retrieve the required data. When a table is joined with itself then such type of join is called Self Join. This is useful when you want to retrieve the data…

Continue reading

Keep reading

SQL Server – CHARINDEX – Oracle INSTR

There is a ‘CharIndex’ function in SQL Server which is similar to the Oracle ‘Instr’ function. In Oracle, the syntax of the INSTR function is :instr( string1, string2 [, start_position [, nth_appearance ] ] ) ‘String1’ in the above syntax represents the string that you need to search in, whereas String2 is the substring that…

Continue reading

Keep reading

SQL Server – Date Time Functions

Here are a few examples of functions that can help you manipulate DATE in different manners. These will comprise most of your daily job routines. The below set of Date/Time functions are used consistently by SQL Users as they are most oftenly required to retrieve specific data from the database. ——————————————————————————— Please note that this…

Continue reading

Keep reading

Database Restore Information with Restore Start and Completed Date Time

Following points to be accomplished – Get all the databases restoration information From all reporting, staging or testing environments Information about when the database restoration started and completed If the restoration is a full restore or differential When the backup file was ready Above said information is to be received through Email in a tabular…

Continue reading

Keep reading

SQL Server – Daily Report – Group by Date

Like hourly report, the following example guide you to retrieve a daily report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a daily report by GROUPING DATE column. CREATE TABLE…

Continue reading

Keep reading

SQL Server – Working with Duplicate Records

The following examples deals with how to retrieve or find duplicate records and how to remove them in various scenarios. Someone has asked me that he had imported a file twice by mistake and which caused duplicates in the data and he wanted to revert back it. In such scenarios, the following code will help…

Continue reading

Keep reading

Introduction to Databases – DBMS History

When I was asked to write something on ORDBMS, I thought it would be better to explain RDBMS first. While I am writing about RDBMS, I am thinking again of discussing about DBMS before I move to RDBMS. Hence, this article has become a bit lengthy but covers the core concepts of FMS, DBMS, RDBMS,…

Continue reading

Keep reading

Logical vs Physical Computed Columns

Many people have asked me about the difference between using and not using ‘PERSISTED’ in a computed column; and how do we know whether the column values are being stored logically or physically.  I would like to share the answer to these questions and some more information here. Let me start the with the definitions…

Continue reading

Keep reading

Handling “Json” and “Unstructured” Data in SQL

The below is to understand how we can handle JSON data in prior versions of SQL Server 2016. Sample JSON data is:   {“accountNumber”: 2020112, “pin”: 2525},  {“accountNumber”: 2567899, “pin”: 1462}  {“accountNumber”: 6789925, “pin”: 2614}  {“accountNumber”: 9925678, “pin”: 6142} This can be extracted into columns easily in SQL Server 2016 with the help of following…

Continue reading

Keep reading

SQL Server – Data Export to XML

One of my friend requested me to explain how many ways are there to export the data from SQL Server 2005 into an XML file. Well, this article will let you know how to export the data from SQL Server 2005 to XML using different modes. –Test DataSELECT * FROM Customers WITH (NOLOCK) Result isCustomerID…

Continue reading

Keep reading

Disk Space Usage For Analysis

The following implementation helped me in analyzing the disk space usage.  There are many ways to get the available disk space in the server and using SCOM we can get the alerts whenever the disk space reaches below the threshold levels. This implementation fetches the available space information and stores it in a table on…

Continue reading

Keep reading

SQL Server – Replicate Function

SQL Server “Replicate” Function repeats a character expression to a specified number of times. It is equivalent to or alternative of Oracle’s RPAD function. Let’s’ check how this will be useful to the developers. SELECT LEN(REPLICATE(2, ’12’)) The above query will return 12 as a result due to 2 is called 12 times (222222222222) hence…

Continue reading

Keep reading

Alert on Scheduled SQL Jobs – Missed to enable

During the deployments, windows patching or any other activity, DBAs often disable the jobs to avoid data corruption and jobs failure. Once the activity is completed, DBAs need to re-enable them. Since it is a manual intervention, it is possible that a job might be left behind from re-enabling. It happened in my case long…

Continue reading

Keep reading

Oracle SQL Emp and Dept Tables For SQL Server

The below is Oracle’s most popular “emp” and “dept” tables compatible to SQL Server. –Table definition – Dept CREATE TABLE Dept( DeptNo INT, DName VARCHAR(14), Loc VARCHAR(13), CONSTRAINT pk_Dept PRIMARY KEY (DeptNo) ); — Tabe Definition – Emp:  CREATE TABLE Emp( EmpNo INT, EName VARCHAR(10), Job VARCHAR(9), Mgr INT, HireDate date, Sal INT, Comm INT, DeptNo…

Continue reading

Keep reading

SQL Server – INSERT Statements

INSERT can be used in different ways. The following examples will guide you the method of row constructions using INSERT. CREATE TABLE Test1( Column1 INT, Column2 VARCHAR(20)) INSERT INTO Test1 VALUES(1001, ‘Andrews’) INTO is an optional keyword and you can omit it from the statement. INSERT Test1 VALUES(1002, ‘Mathews’) SELECT statement can also be used…

Continue reading

Keep reading

SQL Server – List-out Procedures, Functions, Triggers

In order to know which procedures, functions, and triggers exist in the database, the following queries can help you to find this out.: The below set of statements can be used to retrieve stored procedures and the number of parameters in it: Use [Test1] Go SELECT SPECIFIC_NAME, COUNT(PARAMETER_NAME) FROM INFORMATION_SCHEMA.PARAMETERS GROUP BY SPECIFIC_NAME The below…

Continue reading

Keep reading

SQL Server – Monthly Report – Group by Month

The following example guide you to retrieve a monthly report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a monthly report by GROUPING DATE column. CREATE TABLE Test1( AccountID INT,…

Continue reading

Keep reading

SQL Server – List Out Number of Rows from all tables

To Find-Out Number Of Rows In Each Table In Database. METHOD-1 DECLARE @TotalRows INT, @FirstID INT, @table VARCHAR(255), @cmd VARCHAR(500) CREATE TABLE #tableData (TableName VARCHAR(255), TotalRows INT) CREATE TABLE #temp1 (col1 INT IDENTITY, TableName VARCHAR(255)) INSERT INTO #temp1 SELECT table_name FROM Information_Schema.Tables WHERE table_type = ‘base table’SET @TotalRows = @@ROWCOUNT SET @FirstID = 1 WHILE…

Continue reading

Keep reading

SQL Server – Foreign Key Relationships

To establish a connection between two or more tables, foreign key constraint will be used. One table’s primary key column can be referred to another table’s column as foreign key. It is to enforce a constraint to match the foreign key column data-type and values with parent key column data-type and values. The foreign key…

Continue reading

Keep reading

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s