SQL Server

SQL Server Leave a comment

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

SQL Server – Parsing, Casting and Conversion

Sometimes the data stores in the table in a different format in comparison to the real data type it belongs to. The date might be stored as an integer value or a string value, numerical values might be stored as a character string, the currency might be stored in number format. The reason or explanation could…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

SQL Server 2016 – Compatibility Issues – Invalid object name

If you are using SQL Server 2016 version and still you are getting the error Invalid object name ‘STRING_SPLIT’ as shown below while using the functions which are newly introduced (with the same version) –   that means the database compatibility is set to the level in which SQL Server is unable to find the functions.…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

Hijri Date in SQL Server – with Islamic Month Name

This article focuses on converting the Gregorian date into an Islamic date along with Islamic month name in textual format. In SQL Server there are two methods to convert the Gregorian date to Islamic date, however, for Islamic month name we need to depend on our own code.   Either you can write a function…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

Json in SQL Server – Load and convert Json data

Data is the backbone of the business, and it can be in any sort of format. It can be lying in your relational databases or it can be a tweet in the twitter or a post in the Facebook or a message in the Instagram. Worldwide data is expected to hit 175 zettabytes by 2025…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

Word Count in SQL Server – String_Split Usage

STRING_SPLIT is a table-valued function that splits the words from the input text and returns into rows. This function has been introduced in SQL Server 2016. String_split will help in many ways. 1) You can get the word count from the given text. 2) You can pass the string values to a query. Word Count…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

SQL Server – INSERT through Stored Procedure

There is one post in this blog in which we explored how to use the INSERT statement in different ways. Here is another instance of inserting the data through a stored procedure into the table. Let us create a stored procedure that takes student data and marks as input and returns the student ‘s total…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

Multiple Ways to Find Missing Serial Numbers in SQL

In my previous blogs, I had mentioned that there will be many ways to solve a problem. The below is one more example.  Often, in some tables where identity column exists, will have some missing sequences due to some data fixes. Or we may have some sequential numbers or numeric ranges in a table from…

Keep reading
by SHAFI SHAIK

Adding Multiple Columns With Default Value

Almost a decade before, I came across a requirement where I needed to add multiple columns in a table with default value. It took a while on that time to figure it out. I found it in my old SQL Projects repository and thought to share it with you. — Creating a table and adding…

Keep reading
by SHAFI SHAIK

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)…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

SQL Server – GETDATE(), CURRENT_TIMESTAMP in DEFAULT statements

Most of the transactional tables generally do require to have a column with DEFAULT value as current date time to identify when the transaction had took place. To accomplish this, in CREATE TABLE statement you can use GETDATE() function or CURRENT_TIMESTAMP (ANSI SQL equivalent to GETDATE function) as DEFAULT. However there is no difference in…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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,…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

Oracle SQL Emp and Dept Tables For SQL Server

Below are Oracle’s most popular “emp” and “dept” tables compatible with 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,…

Keep reading
by SHAFI SHAIK

SQL Server – Insert Data From Linked Server Table

In a situation where Servers are linked, it is possible to retrieve data from a table in one server to a table on the other server. Following is the method to carry this out: SELECT @@SERVERNAME This will return the server’s name to which you are currently connected to. SELECT * FROM [bngstagedb]. testDB.dbo.temp2 WITH…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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,…

Keep reading
by SHAFI SHAIK

SQL Server – Hourly Report – Group by Hour

Often it is important to know how many inserts occur per hour in a transaction table OR how many payments are made per hour OR to retrieve the hourly report, whatever term it is, the necessity is to produce a report from a datetime column based on hours. If the date column is SMALLDATETIME or…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

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…

Keep reading
by SHAFI SHAIK

Leave a Reply