Translate

All about SQL

 You will learn followings

  • SQL Query Optimization Techniques
  • Fill Factor in SQL Server
  • How is data physically stored in a database:
  • How to Search SQL Object in Complete Database
  • Import one SQL Database into another
  • How to Generate Complete Database Script.
  • How to UPDATE using SELECT statement in SQL Server
  • Aggregate and Summarize Totals with SQL SUM
  • SQL Paging using OFFSET and FETCH
  • Is SQL NOLOCK bad practice?
  • Set Transaction Isolation Level in SQL 
  • SQL CASE Statement



SQL Query Optimization Techniques

In this article we will learn SQL query optimization techniques. There are some useful practices to reduce the cost of the query so that it can perform better. To achieve this, you need to write the query, check query performance using IO statistics and using execution plan, and then optimize it. Let’s discuss few of the points below.

What we'll cover:

Indexing:

An indexing is one of the best ways to optimize your query and it enhance data retrieval speed too. In indexing there are two operations scan or a seek. "Index seek” is used for filtering rows on a matching filter. "Index scan” is used for scanning the entire index for matching criteria. Overall, there are many types of "Indexes" but most common are:

  • Cluster Index: (It can be only one and applied on "Primary key" column of the table)
  • Non-Clustered Index: (There can be multiple Non-Clustered Index and generally applied on where clause columns)

Important points which one must be taken care while creating indexes:

  • We should create Indexes on keys that we use in WHERE clause and in our join statements. It can be either one cluster index or multiple non-cluster index.
  • Multiple non-cluster index should be avoided because it can reduce the performance rather than boosting. Specially your insert/update/ delete operation will slow down because of the logically reordering of the data.
  • If required, multiple non-cluster Indexes should NOT be made on columns that are frequently modified i.e., columns in which INSERT/UPDATE is occurring frequently.
  • Non-cluster Indexes should be made on ordering key values (like ASC or DESC order). This will enhance your query performance.
  •  Indexes should be made on WHERE clause and other columns which we use using AND operator within the query to filter data.

Avoid using SELECT with * Asterisks:

In next part of SQL Query Optimization techniques, we should not use * Asterisk in SQL select Query. It is big overhead because it first scans whole table i.e., it’s all columns and all rows then after this it will start fetching the data from table.  It better to pass the column names as per the requirement. If we'll pass the columns names explicitly then column scanning cost of the table can be avoided.

See below simple select query is showing the”7 rows affected” means first it scan the table then then it fetches the data of table (which can be seen in results tab).

To avoid the scan overhead we can use “set no count on” and using this we get rid of table scan and there is no message of ”7 rows affected” in below screen shot.



Avoid using SELECT with DISTINCT:

Select DISTINCT command in SQL query is used for fetching unique results and remove duplicate rows from the returned result set. Internally DISTINCT get all rows and then groups together related rows and then removes duplicate form it. Additionally, GROUP BY operation can also be used to get unique data but is also a costly operation. We should avoid using DISTINCT in our query considering its high execution cost. You can observe execution time cost with and without DISTINCT in below screen.



Without Distinct time is 122 ms and with DISTINCT it is 175 ms. So, with this example we observe that DISTINCT provides the extra overhead to the SQL Query and we should avoid this as much as possible.



Inner joins vs WHERE clause:

We should use inner join for merging two or more tables rather than using the WHERE clause. WHERE clause creates the CROSS join/ CARTESIAN product for merging tables. CARTESIAN product of two tables takes a lot of time. SQL Joins performance is far better than WHERE clause and CARTESIAN product of tables.

IN versus EXISTS:

IN operator is more costly than EXISTS in terms of scans cost. Moreover, cost increases when subquery returns a large dataset, so we should try with EXISTS rather than using IN for fetching results with a subquery.

--query with IN
SET STATISTICS TIME ON
Select  * from [dbo].[Orders]
 where orderId in(
			      Select  orderId from [dbo].[Order Details]
				 )  
--Query with EXISTS
 
SET STATISTICS TIME ON
Select  * from [dbo].[Orders]
 where  exists(
			      Select  orderId from [dbo].[Order Details]

)

Output of above query will give same result set but EXISTS is more efficient and faster way of getting result.

Loops versus Bulk insert:

The loops must be avoided because it requires running the same query many times. Instead, we should opt for bulk inserts for inserting the large data into SQL tables.

--Example of inserting DATA using SQL loops:
SET STATISTICS TIME ON
DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= 20)
BEGIN
    PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter)
INSERT INTO [dbo].[Customers]
           ([CompanyName]
           ,[ContactName]
		   ,[Country])
     VALUES
           ('ABC Tech. Pvt. Ltd.'
		   ,'Rakesh kumar'
           ,'INDIA')
    SET @Counter  = @Counter  + 1

END

--Example of Bulk insert:
BULK INSERT Sales
FROM 'C:\Rakesh\Sales.csv'
WITH (FIRSTROW = 2,
    FIELDTERMINATOR = ',',  -- each column data will be separated with comma 
    ROWTERMINATOR='\n',     -- each row data will ends with \n
    BATCHSIZE=25000,        -- this is number of records inserted in one go
    MAXERRORS=2);           -- Max error supported before complete

Note: You can create sales table with required columns, each row's data will end with “\n” and each column data will be separated with “,” comma. Datatype and number of columns should match in CSV and table to work it correctly. 


Fill Factor in SQL Server

In this article we’ll learn about “Fill factor in SQL server”. Fill factor is used to decide how the data will get stored on SQL 8KB pages. We all know that in SQL our data is stored in the form of pages and each page is of 8192 bytes. 8060 bytes are used for data rest of bytes are used by Header and Footer. Now, fill factor decide how much bytes of this page will be used for storing data. You can learn more on SQL page storage HERE.

Fill factor is defined in percentage like if we will set it to 50% then only 50% of page bytes (50% of 8060 bytes) will get used for data storage and rest empty space will stay as it is for future data addition/ updates on the same page.

Page Split and performance

During update when more data is added, and page has no space to add this data at that time page splitting occurred and data is saved on the leaf- level pages. So, if Fill factor is 0 or 100 then page splitting will take place because all page space is already used. On the other hand, if suppose we set fill factor to 50% then number of pages will become double because 50% data space will get occupied and 50% will remain free for future use. In this case page splitting will not happen but 2 X pages will be there which will increase reads operation too.

How to change fill factor value?

We can change fill factor value using command and UI too. Let’s discuss it below:

SQL command to see fill factor value and modify it at DB level

EXEC [sys].[sp_configure] 'fill factor'   -- get Fill factor value.
GO

EXEC sys.sp_configure 'fill factor', 90       -- Set fill factor value to 90  
GO
RECONFIGURE WITH OVERRIDE
GO

We can also set it for specific index like, it is set to 80 for index named ‘PK_Region’ in table ‘Region’

USE NorthWND     
GO
ALTER INDEX PK_Region ON [dbo].[Region]   
REBUILD WITH (FILLFACTOR = 80);
GO

Change Fill factor using Designer on UI

  • Select table and right click on this and select ‘Design’.
  • Design of table will open, select the row and right click, following list will open.
  • Select ‘Indexes/Keys...’ option.


  • Following pop up window will open, under ‘Fill Specification’ you can see ‘Fill factor’ and can change it. Please save it after change.



Change Fill factor using Index on UI

  • Select the table and expand the Indexes folder.
  • Right click on desired Index and Select ‘Property’
  • A new pop window will open, under ’Select a page’ select ‘options’.
  • Under 'Storage' you can see 'Fill factor'.
  • You can change its value as required and click 'OK'. That’s all you have to do.



How can we decide fill factor value?

It depends upon our application design and use of DB. If more frequent insert/update, then we can have 70 or 80 % Fill factor so that we can keep some space for future inserts/updates. If less insert/update and more read operations (Select statements) then we must go with 0 or 100 % fill factor value.

I hope you enjoyed reading this article. In this article you learned about Fill factor, its use and how to change it using SQL queries and also from UI. You can learn more SQL concepts on Microsoft Learn.




How is data physically stored in a database


In the article we'll discuss "How is data physically stored in a database" in the form of "series of 8kb pages". We all know that SQL server stores data in tables, if we think next step where table’s data internally get stored then SQL pages come into picture.

Yes, MS SQL data internally get stored in the form of pages and each page is of 8kb. So, when you insert any data into a SQL Server database, it saves the data to a “series of 8 KB pages” inside the data file. When data of any column increases then next data is saved on next new page and these pages are internally referenced to each other.

Below is the example of page which contains three main sections as:

  • Page header
  • Data Row
  • Row Offset  


Page header: Given the information about the page and also how many chunks are available. It has 96 bytes it store all this information.

Data Row: These are the data rows in which data is stored. Total bytes available for data storage are 8060 bytes.

Row Offset: It keep the record of “memory address” where actually data is stored.  It also stores the reference of other pages too. (In case of large data multiple pages are created). Total bytes available for storing row offset information are 36 bytes.

Let's check out video "How is data physically stored in a database"

https://www.youtube.com/watch?v=f-0ul_xbkow

Now in next section we’ll insert data into table and practically see the data on SQL pages. We’ll use DBCC command to get the required information. Let’s check out below:

Below DBCC command will show the path the “mdf” file created for storing the database  


   DBCC ShowFileStats

Insert some data in your SQL table and run the following command in SQL

 DBCC IND('TestDb', 'Students',-1)

DBCC stand for “Data Base Consistency Command”, IND is for index and it will accept 3 parameters, first is DB name and second one is table name which is Student, third parameter is -1 or -2 for different level of details.



On running the above query, you will get above shown details. Focus on “PagePID” and “PageType”. “PageType” =10 means header whereas “PageType” =1 is the data page. Now with this we have identified that page with id 310 is data page and 311 is header page.

Let's find "How is data physically stored in a database" and also see its address locations

https://www.youtube.com/watch?v=wqEFnDn2W4Y&list=PLQItHttujpyO138QeJhevfdz8s0uip7Hz&index=2

Now in next step we will see the data stored in page with ID= 310 as we have discussed above. You can also check out above video for more detailed explanation.

In order to see the details you have run first command which will on the trace. When you will run this query it will give error but no worries trace will on.    

DBCC traceon(3604)  -- it is used to on Trace
DBCC page('TestDB',1,310,1)

 Now we’ll run the second query given above which is used to get page level information, first parameter is DB name, second parameter indicate level of page info, third is page ID whose information we need, fourth parameter is level of details. Its value varies from 0 to 4 as per need, 0 give only header and 1 give information for all i.e., header, offset and data page. Following is the output of above query:



In above screen we can see the page id and header as pointer out using arrow. In below screen page Offset information of memory address and also the data which we have stored.  

Let's get memory address below, which will explain how data is physically stored in a database in form of 8kb pages.



I this article we have explained (with example) the concept of SQL server pages, its header, rows, and Offset footer, etc. We have also explained how pages link with each other, what information header footer has in it. Hope this will work as add on to your knowledge.


How to Search SQL Object in Complete Database


This article we'll learn how to search SQL object in complete database. You can search for database objects in SQL database such as tables, stored procedures, functions, and views.

In our day-to-day routine work some time we need to search object in SQL DB without any external software or add on etc. In below section we’ll explain you the ways using which you can search the required object in your DB.

      We’ll search using information_schema view. It provides various options like  

  • How one can search table.
  • How one can search any column in complete DB.
  • How one can search procedure/ function name.
  • How one can search any text within procedure or function in complete DB.

Search table using “information_schema” view:

Select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME
 like '%Pass your TABLE name here for search%'


Search procedures using “information_schema” view:

Select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME 
like '%Pass your procedure name here for search%'


Search any column using “information_schema” view:

Select * from INFORMATION_SCHEMA.COLUMNS  where COLUMN_NAME 
like '%Pass your COLUMN name here for search%'

Above queries will give result like below:



Let’s check out some other ways to do the above

How to Search a table in DB:

Below query will return all tables present in selected DB. 

SELECT name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, 
type_desc, create_date, modify_date
FROM sys.objects WHERE type ='u'

If you want to search specific table then you can alter above query like given below, "and Name like '%Jobs%'” is added at the end of above query so that it can give only required table information. I searched for “jobs” table you can modify this as per your needs.  

SELECT 
name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, 
type_desc, create_date, modify_date
FROM sys.objects WHERE type ='u'
and Name like '%Jobs%'

How to Search a Stored Procedure/Functions in DB:

Below query will return all stored procedures/ functions present in selected DB. If you have noted that current query is same as above, only difference is with param TYPE in where clauses  ’U’ means “user defined tables” and “P” is for “stored procedures”. “FN” for functions. You can modify above query as per your need.   

SELECT 
name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, 
type_desc, create_date, modify_date
FROM sys.objects WHERE type ='P'

We can search the required procedure by filtering above query like below. I searched for “Job “and it will display list all procedures having Job in its name. 

SELECT 
name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, 
type_desc, create_date, modify_date
FROM sys.objects WHERE type ='P' and Name like '%Job%'


Import one SQL Database into another


In this article we'll learn how to "Import one SQL Database into another". To start the SQL Server Import and Export Wizard using SSMS, go to Object Explorer, right click on a database for which want to export or import data; from the context menu under the Task sub-menu, choose the Import Data or Export Data option:



Once you’ll click on “Import Data” Following screen will appear to you. Here you need to select following

Source Database configurations:

Please follow below instructions for Source database



  • “Data Source” options (as shown in below image). 
  • Server Name which you want to connect.
  • SQL authentication – pass Username and password.
  • Select the source database name from which you want to import the data.
  • Now click the Next button.

Destination Database configurations:

Please follow below instructions for Destination database



  • “Data Source” options (as shown in below image). 
  • Server Name which have destination DB.
  • SQL authentication – pass Username and password.
  • Select the destination database name in which you want whole data.
  • Now click the Next button.

On next following screen will appear which have following two options.

  • Select first option which import data to destination DB.
  • Second’s option is for creating the Scripts.


On click on NEXT button of above screen following will appear where you need to select the Tables/ views etc. whose data you want to import. 



Select the required tables name and click next. Following window will appear where you can select “Run Immediately” and click on Next button in below screen.



Above Screen will give brief information of what data will get imported. Click FINISH and you will see below screen which shows status against each database object.



Click Close and you are done with IMPORT of data.

Now check your destination DB you will see the tables and view which you have selected to import.

Below in destination DB I can see table and views which I have selected to import into this DB.



I hope with this article you got information that how you can import data from one DB to another using SQL.




How to Generate Complete Database Script

In this article we'll learn how to generate complete database script. To generate full database script using SSMS, go to Object Explorer, right click on a database for which want to generate scripts; from the context menu under the Task sub-menu, choose the “Generate Script” option:

What we'll cover:



On selecting the “generate Script” following screen will open 



On click of the next button given on above screen you will get below screen in which you can select the object whose database script you want (Schema or with data).



Select the check box for required objects (tables/ procedure / functions names etc.).Now click of “Next button” following screen will appear where you will find “ADVANCED” button. Using this option you will get multiple ways to generate the scripts as per you requirement.



Above advance button will open the following screen

Check for Object existence:

Under this option script will be generated with if exist option in it. Means you can run such scripts multiple time and it will not give error because it will create the missing object in DB and will not do anything for the other which are already there in DB.      



Types of data to Script:

Under this you will get three options.

Schema Only: Script generated under this option will only copy schema and new blank DB can get created with same structure but without any data in it. 

DATA Only: Script generated under this option will create the Insert scripts of the data only, this script can be used to insert the data into DB. Please it will not create the table so table must be present prior to run this script in DB.   

Schema and Data: This option is combination of above two options. Generated scripts will contain the schema script and also data script in it. So, script generated using this option is like and complete script of database (like backups etc.).You can use this script to restore the same DB again on same or some other server.     



Script DROP and CREATE:

Scripts CREATE: Under this option you can generate Create scripts.

Script DROP: Under this option you can create DELETE scripts for the selected objects with in.

Scripts DROP and CREATE: This option is the combination of above two. Means generated script will first delete the object and then create it. You can opt any of the above options as per your needs.     



After doing the above advanced configurations click on “OK” button and advanced setting window will close.

On main window please select “Open in new query window” option and click on NEXT button. Same is shown in below screen.



After clicking the Next button of above screen following screen will appear and it will show you the progress while generating the scripts.



On click of finish button script will get generated and open in new window. You can save this script and can use this where you want.


How to UPDATE using SELECT statement in SQL Server

In this article, we will learn how to UPDATE using a SELECT statement in SQL Server. There are "multiple ways which are used to update the data in a given table using data from other table. The UPDATE can be done in simple way or using SELECT query structure. We will discuss both of these in this article.

An UPDATE query is used to update the existing record of table. It can update all records, or we are restrict it to update only few records by adding the “Where” clause in the Update statement.   

UPDATE employee
SET FirstName = ‘John’ , Age = 31 
where Emp_Id =101

Update data Using Joins

In SQL server using below query, we can update the table data by using SELECT statement.

UPDATE
    Emp
SET
    Emp.Age = User.Age,
    Emp .Salary = User.Salary
FROM
    Employee as Emp
    INNER JOIN UserInfo as User
        ON  Emp.id = User.id
WHERE
    Emp.id = 101

Using above query “Employee” table record (age and salary) will get updated using the data from “USER” table. Here only one employee data will get updated because of the where clause is updating only for employee with id =101. If we will remove where clause then it will update data of all employee who’s Id will match in “Emp” and “user” table.   

Update data without using Joins

In above example we have used JOINS but we can also do it without joins let’s check out how

UPDATE Emp, User
SET
 Emp.Age = User.Age,
 Emp.Salary = User.Salary
WHERE Emp.id = User.id
And Emp.id = 101

Apart from UPADTE, we can also INSERT data into our table by using the SELECT statement as given in the below sample query. One thing which is important is - we need to pass the same number of columns in INSERT and SELECT statement as I have passed only three column names (cols, col2, col3) in below sample query. You can use n-number of columns, but number of columns and sequence must be same in INSERT and SELECT statement.

Insert data into table using SELECT statement

INSERT INTO EMP(col1, col2, col3)
SELECT col1, col2, col3 
FROM User  
WHERE id >100


SQL server column encryption using symmetric key


In this article we’ll learn about SQL server Column encryption using Symmetric key. In Financial organizations or banks, we have sensitive data including account numbers and credit card numbers. As the data is stored in the columns and one can read this using simple select statement from that table. So, here instead of encrypting the whole DB or table we can encrypt the given column data and it will become secure. One who will have the certificate can view this data in readable format and for others it will remain as encrypted. So, how to do this let's check out below step by step.

Step 1 - Create a sample table.

USE Test_BankDB;
GO
-- Create Table
CREATE TABLE [dbo].[User](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[AccountNo] [nvarchar](50) NULL,
	[CreditCard] [nvarchar](50) NULL,
	[AccountNo_Encrypted] [varbinary](max) NULL,
	[CreditCard_Encrypted] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--Now insert some data into this sample table 
GO
SET IDENTITY_INSERT [dbo].[User] ON 
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (1, N'Mukesh', N'1256734567', N'985645356432', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (2, N'Rakesh', N'4567239012', N'756460742257', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (3, N'Smith', N'5344843622', N'356801345657', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (4, N'John', N'2345345689', N'235476956701', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (5, N'Peter', N'4597941437', N'354903343577', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (6, N'James', N'3265768999', N'324789976762', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[User] OFF
GO

Step 2 – Create SQL Server' Service Master Key'

Service Master Key is very important for SQL server data encryption; it is by default created when we install MS SQL and create the instance. It must be preset in your DB and if it does not exist we need to manually create it.

USE master;
GO
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';


Step 3 – Create your 'Database Master Key'

In next step we will create Master key for the DB whose data we need to encrypt. The "encrypt by password" argument is required and it will accept password which will get used to encrypt the key. You need to keep the password safe for future usage and you can also take the backup of your master key for future reference.

-- Create database Key
 USE Test_BankDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Codeconfig@123';
GO

You can view your Database master key using below query that it is created successfully or not. Its output will be same as shown in below image.

USE Test_BankDB;
GO
SELECT * FROM sys.symmetric_keys


Step 4 - Create a Self-Signed SQL Server Certificate

Next, we will create a self-signed certificate that is protected by the database master key (which we create in step 3). Our certificate can be protected by password or by database master key. We’ll use database master key on our case.

-- Create self-signed certificate
USE Test_BankDB;
GO
CREATE CERTIFICATE CertBank1
WITH SUBJECT = 'Secure_Data';
GO

Run this certificate creation query again, it will say that it is already created which means that certificate is successfully created in your DB.



Step 5 - Create SQL Server Symmetric Key

Encryption and decryption by using a symmetric key is fast and same key will get used for both encryption and decryption process.

-- Create symmetric Key
USE Test_BankDB;
GO
CREATE SYMMETRIC KEY SymmetricKey1 
 WITH ALGORITHM = AES_256 
 ENCRYPTION BY CERTIFICATE CertBank1;
GO

You can run below query to check “SymetricKey” in DB. It output will be as shown in below image which indicate that your key is created successfully.

USE Test_BankDB;
GO
SELECT * FROM sys.symmetric_keys


Step 6 - Encrypting the SQL column data

Before running below query, our table will look like this.



You can run following query which will update your column with required encrypted data. Output will appear like this.

-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE CertBank1;
GO
UPDATE [dbo].[User]
SET [AccountNo_Encrypted] = EncryptByKey (Key_GUID('SymmetricKey1'),[AccountNo]),
    [CreditCard_Encrypted] = EncryptByKey (Key_GUID('SymmetricKey1'),[CreditCard])
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO
-- select The rows now
Select * from [dbo].[User]


Step 7 – How to Read SQL Server Encrypted Data

The data which you have encrypted can be read using “DecryptByKey” as shown in below query.

---How to Read SQL Server Encrypted Data
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE CertBank1;
GO
    select id, [name],
                 CONVERT(nvarchar(max),DecryptByKey([AccountNo_Encrypted])) as AccountNo_Encrypted ,
                 CONVERT(nvarchar(max),DecryptByKey([CreditCard_Encrypted])) as CreditCard_Encrypted
	 from [dbo].[User]
GO
-- Close the symmetric key once done
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Output of above query will be as shown in below image, and you will be able to read encrypted data only if you’ll pass correct name of Certificate and symmetric key. If you lost Certificate and symmetric key, then data will not be able to decrypt.



In this article you have learned about SQL server Column encryption using Symmetric key. We hope after reading this article you’ll able to use this in your daily development practices.


Aggregate and Summarize Totals with SQL SUM


In this we will discuss about ‘aggregate and summarize totals with SQL SUM function.  Knowing the method to calculate the complete addition of a specified column in a SQL Server database table is crucial. For instance, a sales supervisor may require the overall sales figure of each salesperson per month or a continuous addition of sales of a customer for a particular month. What is the solution to these issues?

Let’s discuss Solution

Microsoft provides assistance to SQL database developers in the form of the SUM function, which can be used to address various issues. In this article, we will focus on three key objectives:

  • Calculating the sum of a single column.
  • Generating a cumulative total, and
  • Substituting a convoluted pivot statement with aggregated sums.

Normally, I consult the internet-based records whenever I encounter new T-SQL functions that I wish to utilize. After that, I investigate the function's capabilities and limitations. Solely numerical values are compatible with the SUM function. To determine which data types generate results with the most frequently used functions, namely SUM, AVG, MIN, and MAX, we will analyze the “Order” table in the “NorthWND” database.

The below SQL commands shows us that only the MIN and MAX functions are supported by the date/time data type

SELECT
      MAX([RequiredDate]) AS RequiredDate,
      MIN([ShippedDate]) AS ShippedDate
     
  FROM [NorthWND].[dbo].[Orders]


Next, we will explore the string data format. The following code indicates that exclusively MIN and MAX functions are recognized, whereas SUM and AVG will result in an error message of an invalid data type.

SELECT
      MAX([ShipCity]) AS ShipCity,
      MIN([ShipRegion]) AS ShipRegion
     
  FROM [NorthWND].[dbo].[Orders]

But if we will use SUM instead of MAX and MIN then it will be following error.



Simple Summation with SQL SUM

The SUM function assumes the ALL keyword as default if not specified. However, if you include the DISTINCT keyword, the function will first calculate a distinct list of values for the column and then perform the summation.

SELECT 
  SUM(DISTINCT UnitPrice) As UnitPrice,
  SUM(ALL SalesAmount) As SAmount
FROM 
  Products
GO

The T-SQL code above demonstrates the difference between ALL and DISTINCT. The image below shows the different totals by the given keyword.



Aggregated Summation

It gets more interesting when we use these functions with the GROUP BY clause. A business user might want to know the total sales by month during 2011. The T-SQL snippet produces that output.

SELECT 
  OrderBYMonth,
  SUM(SalesAmount) AS Sales_Amt
FROM 
  Orders
GROUP BY
  OrderBYMonth
ORDER BY
  OrderBYMonth
GO

Let's get Running Totals

The PARTITION BY clause establishes a segment in a query result set, and the window operation calculates a result for each record. Operations can be utilized to obtain summarized values such as continuous averages, cumulative aggregates, running sums, or the highest N per category results.

The T-SQL query provided below generates a view called “SalesByRegion2023” by using a SELECT statement that computes a running sum per country, year, month, and sales order number. As PARTITION BY only accepts one column, we are combining the country, year, and month to create a single hash key. The ROW_NUMBER function assigns a unique number to each row in the partition.

CREATE OR ALTER VIEW RunningTotal2011
AS
SELECT 
  SalesTerritoryCountry,
  OrderYearMonth,
  SalesOrderNumber,
  ROW_NUMBER() OVER ( PARTITION BY (SalesTerritoryCountry + OrderYearMonth) ORDER BY SalesOrderNumber) AS RegionDateRow,
  SUM(SalesAmount) OVER ( PARTITION BY (SalesTerritoryCountry + OrderYearMonth) ORDER BY 
SalesOrderNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RegionDateTotal
FROM 
  SalesByRegion2023
GO

Since there are over 5,000 rows, we want to filter the data for sales in INDIA in January 2023. The query below performs this filtering and returns the top 100 rows

SELECT 
  TOP 10 * 
FROM 
  SalesByRegion2023

The SQL SUM function was created to total numeric values. We saw that date/time and string values result in errors from the algebraic parser. Today, we looked at both simple and aggregated summations. Remember, by default, you are summing ALL values. You can add the DISTINCT keyword if necessary.


SQL Paging using OFFSET and FETCH


In this article we will learn about SQL Paging using OFFSET and FETCH. In SQL these clauses can be used with ORDER BY to limit the number of rows returned by a query.

OFFSET:  Specifies how many rows to skip over before starting to return rows.

FETCH:   Specifies how many rows to return.

Following query will return 200 records in one go @PageNumber means it will skip 0 records and will start picking from first record. @RowsOfPage will say how many records need to fetch.

DECLARE @PageNumber INT = 0
DECLARE @RowsOfPage INT = 200

SELECT [OrderID] ,[CustomerID]
      ,[EmployeeID] ,[OrderDate]
      ,[RequiredDate] ,[ShippedDate]
      ,[ShipVia],[Freight]
      ,[ShipName],[ShipAddress]
      ,[ShipCity],[ShipRegion]
      ,[ShipPostalCode],[ShipCountry]
  FROM [NorthWND].[dbo].[Orders]
  ORDER BY OrderID ASC 
OFFSET @PageNumber ROWS
FETCH NEXT @RowsOfPage ROWS ONLY;
GO


Changing the OFFSET to 200 will give us the next 200 results. Like show in below query

DECLARE @PageNumber INT= 200
DECLARE @RowsOfPage INT = 200

SELECT [OrderID] ,[CustomerID]
      ,[EmployeeID] ,[OrderDate]
      ,[RequiredDate] ,[ShippedDate]
      ,[ShipVia],[Freight]
      ,[ShipName],[ShipAddress]
      ,[ShipCity],[ShipRegion]
      ,[ShipPostalCode],[ShipCountry]
  FROM [NorthWND].[dbo].[Orders]
  ORDER BY OrderID ASC 
OFFSET @PageNumber ROWS
FETCH NEXT @RowsOfPage ROWS ONLY;
GO

In below query you need to play with @PageNumber and @RowsOfPage. You can pass these parameter form application and SQL query will return the desired set of records in paging.

DECLARE @PageNumber INT= 1  -- this is the page number whose data you want  
DECLARE @RowsOfPage INT = 200  -- this is number of records you want in return.   
DECLARE @MaxPagesInTable FLOAT

Select @MaxPagesInTable=count(1)  FROM [NorthWND].[dbo].[Orders]
SET @MaxPagesInTable = CEILING(@MaxPagesInTable / @RowsOfPage)
print @MaxPagesInTable

SELECT [OrderID] ,[CustomerID]
      ,[EmployeeID] ,[OrderDate]
      ,[RequiredDate] ,[ShippedDate]
      ,[ShipVia],[Freight]
      ,[ShipName],[ShipAddress]
      ,[ShipCity],[ShipRegion]
      ,[ShipPostalCode],[ShipCountry]
  FROM [NorthWND].[dbo].[Orders]
  ORDER BY OrderID ASC 

OFFSET (@PageNumber - 1) * @RowsOfPage ROWS 
FETCH NEXT @RowsOfPage ROWS ONLY

GO

 I hope you have enjoyed reading this article and learned about “SQL Paging using OFFSET and FETCH”. You can use above SQL sample query to implement paging in your application. 


Is SQL NOLOCK bad practice?


In this post we'll read about the use of [Nolock] in SQL Queries and stored procedures. Is this good or bad practice to use this in query. Let's check out below.

  • If you don't care about dirty reads (i.e., application has no sensitive data like no payment details), then NOLOCK is fine.
  • When querying single values/rows it's always bad practice to use NOLOCK -- you probably never want to display and update incorrect information.
  • It is bad practice to use this in payments or banking applications or in transactions. It can be used only in large report generation which means for read only data.
  • When displaying rough statistical information, NOLOCK can be very useful.
  • If you have static data in table and it is used to select and display, then no harm of using nolock.
  • If table data is getting updated very frequently then nolock should not be used.

Additionally, as an alternative to nolock, consider "read committed snapshot", which is meant for databases with heavy read and less write activity. You can turn it on with:

ALTER DATABASE codeConfigDB SET READ_COMMITTED_SNAPSHOT ON;

WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Both of these are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the present connection, so if you only want a [nolock] on one or two tables use that in your query with table otherwise can use TRANSACTION ISOLATION LEVEL.

Hence, both will give you dirty reads. If one is okay with that, then can use this. If you don't want dirty reads, then you can consider snapshot or serializable hints too.

  • NOLOCK is local to the table or views etc.
  • READ UNCOMMITTED is per session/connection.

Conclusion:

So, depending upon the above parameters use of nolock in SQL query can vary from individual to individual and their needs. You can see which option is fit as per your needs and can use that.


Set Transaction Isolation Level in SQL 

In this post we'll discuss SQL server "Set Transaction Isolation Level". These are of 5 types, and we'll discuss all one by one below.

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SNAPSHOT
  • SERIALIZABLE
-- Syntax for SQL Server ISOLATION LEVEL
  
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

READ UNCOMMITTED

Read uncommitted specifies that current SQL statements can read rows data that have been modified by some other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not acquire shared locks to prevent other transactions from altering data read by the current transaction.

READ UNCOMMITTED transactions are also not hindered by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is enabled, it is possible to read uncommitted modifications, which are referred to as dirty reads. Values in the data can be altered and rows can appear or disappear in the data set before the end of the transaction. This option has the same impact as setting NOLOCK on all tables in all SELECT statements within a transaction. This is the least restrictive of the isolation levels.

In SQL Server, you can also minimize contention caused by locking while safeguarding/protecting transactions against dirty reads of uncommitted data modifications by using the following:

  1. The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

READ COMMITTED

This option is the SQL Server default option which specifies that statements cannot access data that has been altered but not yet committed by other transactions. This prevents reading of uncommitted data. Data can be modified by other transactions between separate statements within the ongoing transaction, leading to inconsistent reads or fictional data.

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT options in database. It can be ON or OFF as per the requirement.

a) READ_COMMITTED_SNAPSHOT is set to OFF

It is the default option and in this the database engine utilizes mutual locks to hinder other transactions from altering rows while the ongoing transaction is executing a read operation. The mutual locks also impede the statement from accessing rows modified by other transactions until the other transaction is finished. The mutual lock category determines when it will be relinquished. Row locks are relinquished prior to processing the subsequent row. Page locks are relinquished upon reading the next page, and table locks are relinquished upon completion of the statement.

b) READ_COMMITTED_SNAPSHOT is set to ON

This is the default option for Azure SQL Database. Under this option the Database Engine employs row versioning to provide each statement with a transactionally consistent snapshot of the data as it appeared at the beginning of the statement. Locks are not utilized to safeguard/protect the data from updates by other transactions.

Under this option you can utilize the ReadCommittedLock table which give the hit to demand shared locking instead of row versioning for individual statements in transactions running at the READ COMMITTED isolation level.

REPEATABLE READ

REPEATABLE READ specifies that SQL statements cannot access data that has been altered but not yet committed by other transactions and that no other transactions can alter data that has been accessed by the current transaction until the current transaction is finished.

Shared locks are applied to all data accessed by each statement in the transaction and are maintained until the transaction is completed. This prevents other transactions from modifying any rows that have been accessed by the current transaction. Other transactions can insert new rows that meet the search conditions of statements issued by the current transaction. If the current transaction, then repeats the statement, it will retrieve the new rows, resulting in phantom reads. Because shared locks are held until the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.

SNAPSHOT

Snapshot specifies that information read by any statement in a transaction will be the transactionally consistent version of the information that existed at the beginning of the transaction.

The transaction can only acknowledge information modifications that were committed before the start of the transaction. Information modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The impact is as if the statements in a transaction get a snapshot of the committed information as it existed at the beginning of the transaction.

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading information. SNAPSHOT transactions reading information do not prevent other transactions from writing information. Transactions writing information do not prevent SNAPSHOT transactions from reading information.

During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read information that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.

SERIALIZABLE

Serializable consist of the followings.

  • Statements cannot access data that has been modified but not yet committed by other transactions.
  • No other transactions can modify data that has been read by the current transaction until the current transaction completes.
  • Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This prevents other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed again, they will access the same set of rows. The range locks are held until the transaction finishes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction finishes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

SQL Server SET TRANSACTION isolation level in stored procedure

If you set the isolation level within a procedure, the isolation level will revert to its previous level once the procedure finishes. The available options are: READ UNCOMMITTED – this will Allows dirty reads, meaning data can be read from a transaction that is not yet complete. Hance it will be limited to the scope of stored procedure.

WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Both of these are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the present connection, so if you only want a [nolock] on one or two tables use that in your query with table otherwise can use TRANSACTION ISOLATION LEVEL.

Hence, both will give you dirty reads. If one is okay with that, then can use this. If you don't want dirty reads, then you can consider snapshot or serializable hints too.

  • NOLOCK is local to the table or views etc.
  • READ UNCOMMITTED is per session/connection.


How to enable SQL Server authentication

SQL Server Authentication is a default way to connect to MS SQL Server, but there may be many times that you need to use SQL Server Authentication to connect to the server, like when you have to connect to a MS SQL Server Database on a different network or when you have a SSPI problem. In order to use SQL Server Authentication, you need to create a SQL Server Authentication Login ID first. Let's check this below:

To allow SQL Server Authentication for your instance:

Open SQL Server Management Studio.

Expand the Security item in Object Explorer and right-click Logins and choose New Login.



      Enter an account name in the Login name field and choose SQL Server authentication. Then, enter a password for the account.

      Uncheck the following options to set the password as you want:

      • Enforce password policy
      • Enforce password expiration
      • User must change password at next login

      If the options are checked, you should the password according to security policy in Windows.

      Go to the Server Roles page and select sysadmin.



      Go to the User Mapping page and choose DATABASE and db_owner.



      Go to the Status page and set Permission to connect to database engine to Grant and Login to Enabled.



       Right-click the database server in Object Explorer and choose Properties.





      Go to the Security page and choose SQL Server and Windows Authentication mode



      Right-click the database server and restart it. When a message box shows up, click YES.






      SQL CASE Statement


      Introduction of SQL CASE:

      In this article we'll learn about SQL CASE Statement. Like if-else the CASE statement goes through multiple conditions and returns a value when the first condition is met. When the condition meets, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

      What you'll learn below:

      Syntax:

      CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result 2
         -----------------------
    WHEN conditionN THEN resultN
    ELSE result
END;

      Let’s create a table with data and check SQL CASE statement:

      CREATE TABLE [dbo].[User](
	[Uid] [int] NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Stars] [int],
	[Gander] [nvarchar](1) NULL
) 
GO  

INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (1,  N'Rakesh', N'kumar',  N'5',  N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (2,  N'Mukesh', N'Kumar', N'5', N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (3,  N'Ram', N'Singh',  N'4',  N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (4,  N'Merry', N'smith',  N'3',  N'F')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (5,  N'Sham', N'Kumar', N'2',  N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (6,  N'Thomas', N'Hawkins',  N'1',  N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (7,  N'Garry', N'Smith',  N'4',  N'F')

GO

      SQL CASE with example:

      The following SQL goes through conditions and returns a value when the first condition is met.

      Select FirstName, Lastname,
CASE
    WHEN stars >= 4 THEN 'Excellent Rating'
    WHEN stars <= 2 THEN 'poor'
    ELSE 'Average Rating '
END AS UserRating
,
CASE
    WHEN Gander = 'M' THEN 'Male'
    WHEN Gander = 'F' THEN 'Female'
    ELSE 'NA'
END AS Gander
from [user]

      In above query we have added two case statements. You can add multiple Case statements as per your need in query. First case will check stars and display meaningful user Rating and second CASE will display meaningful Gander rather than M or F.

      Adding multiple conditions in CASE using AND:

      Select FirstName, Lastname,
CASE
    WHEN stars >= 1 and Stars<= 2 THEN 'Poor Rating '
    WHEN stars >= 3  and Stars <=4 THEN 'Average Rating'
    ELSE 'Excellent Rating'
END AS UserRating
from [user]

      We can add multiple AND in CASE statement to meet our requirement. In above example we have shown stars 1 & 2 as “Poor Rating” 3 & 4 as “Average Rating” and 5 as “Excellent Rating”.

      Output:



      Nesting of multiple CASE Statement:

      Now we have changed the requirement with reference to “Male” and “Female” users for displaying their “User ratings”.  Now with in the same SQL query, we’ll nest the case statement and achieve the same. Lest check out below.    

      For Males:

      • 1 & 2 stars will be ‘Poor rating’
      • 3 & 4 stars will be ’Average rating’.
      • 5 will be ‘Excellent rating’.

      For Females: 

      • 1 star will be ‘Poor rating’ only.
      • 2 & 3 stars will be ‘Average rating’.
      • 4 & 5 will be ‘Excellent rating’.
      Select FirstName, Lastname,
 CASE
   when gander ='M' then
 case 
   WHEN stars >= 1 and Stars<= 2 THEN 'Poor Rating '
    WHEN stars >= 3  and Stars <=4 THEN 'Average Rating'
    ELSE 'Excellent Rating'
END
when gander ='F' then
 case 
    WHEN stars = 1 THEN 'Poor Rating '
    WHEN stars >= 2  and Stars <=3 THEN 'Average Rating'
    ELSE 'Excellent Rating'
END
END AS UserRating,Stars,Gander
from [user]

      Check out the below results, stars=4 showing “Average rating” for Male (record number -3) whereas same stars=4 is “Excellent Rating” for female. So, we have achieved the required results by nesting of CASE statement.



      I hope this article has given you deep understanding of CASE statement. Now you will be able it uses this in you daily routine SQL development. 


      Labels:

      No comments:

      Post a Comment

      Subscribe to: Post Comments (Atom)

      Popular Posts

      • All about SQL
         You will learn followings SQL Query Optimization Techniques Fill Factor in SQL Server How is data physically stored in a database: How to S...
      • Python QuickStart
          Python is an interpreted programming language; this means that as a developer you write Python (.py) files in a text editor and then put t...
      • All about windows
          You will learn followings How To Schedule Automatic Shut Down Windows 10 How To Cancel Automatic Shutdown in Windows 10 How To Delete Temp...
      • HTML URL Encoding
         In this post we'll discuss about HTML URL Encoding. Web browsers request pages from web servers by using a URL i.e., Uniform Resource L...
      • Input and Output in Python
          Understanding input and output operations is fundamental to Python programming. With the print() function, you can display output in vario...
      • Dependency Injection in C#
         This article I am going to explain how to implement Dependency Injection in C#. Dependency Injection (DI) is a software design pattern that...
      • Python String, f-string and string slicing
          Python String A string is a sequence of characters. Python treats anything inside quotes when a string. This includes letters, ...