Sunday, 3 December 2017

Difference Between Truncate and Delete Command in SQL Server

I have seen a lot of times, people get confused to differentiate the Truncate and Delete commands in SQL Server. If you are a DB developer or a DBA, then this is a mandate requirement to know exactly on what is being performed when Truncate on a table and delete on a table. Let’s check the difference between Truncate and Delete in SQL Server.

TRUNCATE 

TRUNCATE is a DDL command, which is used to delete all the rows of a table without being to use a WHERE clause. Most of them come up to an assumption that a truncate cannot be rolled back once the TRUNCATE command is executed. But, the assumption is very untrue! The TRUNCATE command can be rolled back with the lopping into a begin transaction and rollback commands. 

Further, in truncation, the pages are completely deallocated and commits to the transaction log as a deallocation. TRUNCATE can be only executed if a login is given with an ALTER permission for a database. Additionally, as the rows are completely removed from the table, there is less utilization in the transaction log, hence performance is high.

Before we proceed with the truncation of table, let’s create a dummy table, insert some values on CusName and CusID and test on it.

Create table dummy
(
CusID int not null,
CusName nvarchar(100) null
)

Insert into dummy values (1, 'Dale'), (2, 'James')


T-SQL command to truncate a table.

begin tran
Truncate table dummy

select * from dummy


rollback 



The above command will completely remove two rows with two unique records from the dummy table.

DELETE

DELETE is a DML command, which is used to either remove all the rows from a table or a specific row from a table using the WHERE clause. As the rows are deleted from the table one by one, every single delete from the table is maintained and recorded in the transaction log. Since such records are maintained, there is a high utilization of transaction log, due to which the performances are low.

Further, there is a need of DELETE permission for a login in SQL Server to carryout the DELETE command. When there is a rollback possibility for Truncate, there is also a similar way to rollback DELETE records of a table by looping to Begin Transaction.

T-SQL command to delete a table.

Let’s use the dummy table to delete CusID record.

begin tran
DELETE from dummy where CusID=1

select * from dummy



The above delete command will just delete the row selected in the WHERE clause with CusID=1.

 rollback 

Saturday, 2 December 2017

Understanding the Importance of Database Schema in SQL Server

A schema is a container for objects such as tables, views, stored procedures, functions, etc., to facilitate management and ownership of a database object. In Microsoft SQL Server, the default schema is dbo, wherein any object created will be referenced to a dbo schema. There can be more than one schema created for a database in SQL Server. When a database is created, by default we will get to see the schemas as shown in the screenshot.


Let’s take a look at the example for differentiating the default schema and the created schema.

CASE 1: Creation of default dbo schema

Say, if we are creating a database with the name SQLArena and create an object say SQLArena_Test, then the object will reference to a dbo schema by default.

Create database [SQLArena]
Go
Create table SQLArena_Test(id int null)

From the created table, the id is the value which will be referenced to the dbo schema by default

CASE 2: Create a new database schema (Schema_Name:Trash)

Say, if we are creating a new database schema in already created database – SQLArena, then we will have to create the table with the prefix as the <Schema_Name> along with the object name to reference to the respective schema created. We can created the same table SQLArena_Test with a new database schema as below.

Create table <Schema_Name>.SQLArena_Test(id int null)

Example:
Create table Trash.SQLArena_Test(id int null)

From the above two cases, don’t be confused while fetching the column of the same table. Now, while fetching for the ‘id’ column in SQLArena database, by default the ‘dbo’ schema will be fetched rather than the ‘Trash’ schema.

GUI to guide you through to create a new schema.

1. Expand the respective database from the object explorer.
2. Expand “Security” under the selected database.


3. Now, we will see an option to expand from “Schema”.
4. Here, all the default schemas will be intact. Once such is the dbo.


5.  Right click on the schema and select “New Schema…”.


Saturday, 4 November 2017

Simple Way to Change Index Fill Factor Settings in SQL Server

The fill factor is basically used for a database only if there is an index created. While checking for any databases, the fill factor is set to zero by default. Depending the table usage, the fill factor is set. In simple terms, when there is an insert, update or delete for a table, there should be some interpretation of data and these data should be stored somewhere ideally. Now, these data store in the leaf-level page and the amount of data to be stored on each page is determined by the Fill Factor.

A smallest unit page is 8K. When a page of 8k size is filled with data of more than 8K, then the data will be stored in a new page, which is basically called as page split. Let’s check on a simple way to change index fill factor settings in SQL Server.

Steps to change index fill factor settings in SSMS

1.   Open SSMS and right click on the server name to click the “Properties”.
2.  A new window pops with more info about the server, Click on “Database Settings”

Take a look at the screenshot of the same below:


Here is the T-SQL script for the changes from the screenshot.

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'fill factor (%)', N'80'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

Tuesday, 17 October 2017

Fixing CTRL+R Show/Hide Control Pane in SQL Server

To be straight forward, I experienced the issue of CTRL+R not working all of a sudden. As per my personal experience, this happens when there are multiple instances opened with different servers connected in respective instances. Additionally, the same issue of results not getting vanished using CTRL+R could be due to computer formatting. Once the computer is formatted or if there is a windows patching update, the settings may get changed. Not to panic about it. Here is a simple fix for the show/hide control pane result tab using the CTRL+R option in SQL Server.

Steps for Fixing Show/Hide pane in SSMS

1. Go to option “Tools”..
2. Click on “Options” to get a new window popped with more options.
3. On the left side tab, click on the option “Keyboard” to populate more options.
4. Set the mapping scheme keyboard to default (Default)
5. Tap the “Reset” button for the fix.

Take a look at the screenshot:

Saturday, 14 October 2017

SQL Server - How to Shrink Big Log File Size of a Database

I had previously written an article on how to shrink the logfile size of a database. But, while working intensely on databases that are pretty huge with respect to transactions, I felt it really challenging to shrink the log file. As I had previously mentioned, the log file size increases with increase in transactions such as bulk data insertions, Index ReOrg, Update Statistics, or Full backups and Log backups running for quite a long time. There are two ways through which a big log file size can be shrinked. 

Method 1:

Say for example, if the log file size is utilized to 90 GB of 100 GB. Then, for sure there will be high difficulty in shrinking the log file. Here we can shrink the log file by giving the user databases to 10GB and shrink the log file.

Before to shrink the log file, check the log file size and log percentage with the below DBCC command: 

DBCC SQLPERF(logspace)



Now, let’s take a database for example – SQLArena_TEST
First take the log backup of respective database

BACKUP LOG <Database_Name> TO  DISK = N'<Drive_Path>.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

Example:
BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

Shrink the file by giving the user database to 10 GB, as said the example here was taken to be as 90 GB utilized log. The user database needs to be reduced in 10 GB recursively to completely shink the log file. 

USE SQLArena_TEST
GO
DBCC SHRINKFILE ('SQLArena_TEST_Log', 80328) 




Note: Take the log backup twice or thrice with a different filename for the path consecutively to shink the database to 10 GB reduction successfully.

Method 2:

As per method 1, where the shrinking happens at 10 GB reduction for the user database, here we will be shrinking the log file size at a single shot. This method as far I have seen seems to be something pretty faster than any other shrinking methods used in SQL Server. 

When the log file sizes are too high, we tend to make a mistake of giving just one path while taking the log backup and shrinking. Instead, let’s try to increase the drive path in the log backup script to finish the log backup and shrinking the data file of the user by giving zero and truncate. Take a look at the command mentioned:

– Given just one path for log backup and this fails in shrinking
BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP 

– Give 7-8 drive paths to shrink at a single shot, Take this log backup twice
BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path1.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path2.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path3.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path4.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path5.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path6.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path7.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path8.trn'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Once the log backup with multiple path files are created, shrink the user data by zero with TRUNCATEONLY command to completely shrink the big log file size. 

USE SQLArena_TEST
GO
DBCC SHRINKFILE (N'SQLArena_TEST_Log' , 0, TRUNCATEONLY)
GO

Thursday, 12 October 2017

Advantages of Using UNION ALL Over UNION in SQL Server

Most of them working in development or SQL environment would have come across UNION and UNION ALL. But, when we look into the cause of using UNION and UNION ALL, it’s pretty interesting not just in terms of results, but also in terms of performance.

Basic difference between UNION and UNION ALL is that, UNION ALL results in almost all the duplicates between the tables, whereas UNION omits from being resulting the duplicate values.

Secondary difference and advantage would be getting the results at faster rate. The results will be generated at a pretty faster rate for UNION ALL when compared to UNION. However, the UNION ALL generating the duplicate results can eventually lead to long time querying.

For example, let’s take count of a table name CUS from two databases, one without duplicates and the other with duplicate counts. Take a look at the script below:

FOR UNION:

SELECT '-----CUSName',0
UNION
      SELECT 'Customer1',COUNT(*) FROM CUS
UNION  
      SELECT 'Customer1',COUNT(*) FROM CUS



Another scenario is to use UNION ALL instead of UNION in the statements to see a different result in the output set. 

UNION ALL:

SELECT '-----CUSName',0
UNION ALL
      SELECT 'Customer1',COUNT(*) FROM CUS
UNION ALL
      SELECT 'Customer1',COUNT(*) FROM CUS
From the above outputs, it is clear that UNION ALL functions in selecting even the duplicate counts. Seeing the example, Customer2 consists of 10 duplicate records, which was put forward along as a redundancy.

You may also need: "10 New Features SQL Server 2016 Does Better than SQL Server 2014"

Tuesday, 3 October 2017

How to resolve error “The local device name is already in use” in SQL server?

This happened very recently to me when I set up a job to fetch resource from a local machine, which caused the error as “The local device name is already in use”. Now, looking at the error we may find it very straight-forward to know that that there is a resource which is using the local device path. Further, if we are setting up two jobs or running a command, to fetch the same resource at a time, then there are high chances to get the error message.

 A simple way to fix the error could be either to change the schedule in the job or run the session/process in odd timings. Here are the steps to schedule the job timings.
  1. Right click on the job and select “Properties”.
  2. Under properties select “Schedules” option.
  3. Double click the step name under“Name” option to open the schedules.
  4. Now setup the schedules accordingly.
Now, let’s see a sample schedule setup for a job in the screenshot below.



Saturday, 30 September 2017

Rename Physical Database File names of an Existing Database in SQL Server

Usually, Physical database names are not changed after the database creation, unless there is a requirement for name change for the created database. Say, a database is created and it is renamed with a new name. The new database name will still point to the same physical paths of the originally created database and hence cause confusion to rectify the right database file names.

Unlike, the logical file names which can be altered using the ALTER command for the database, the physical database file names cannot be changed when the database is online. Very straightforward to know that these physical paths are already being used when the database is ONLINE. Hence, we will have to take the database offline and then proceed to alter the physical database file names. 

Before moving on to the methods of renaming the physical database files, let’s check the information of the database by using a simple stored proc.

EXEC sp_helpdb <Database_Name>

Example:

USE SQLArena_Test
EXEC sp_helpdb SQLArena_Test

From the above stored proc, if we have to gather the physical data filenames and logical filenames of the database, proceed with the query as:

select file_id, NAME as [LOGICAL_FILE_NAME], PHYSICAL_NAME
from sys.database_files


Take database offline and then rename the Physical Database Files

In order to change the physical filename from the screenshot, let’s first disconnect all the opened session in the SQL Server and take the database OFFLINE.

USE SQLARENA_TEST
GO
ALTER DATABASE SQLARENA_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE SQLARENA_TEST SET OFFLINE

Check if the database is offline or online with the command as:

SELECT NAME, STATE_DESC FROM SYS.DATABASES WHERE NAME='SQLARENA_TEST'



Now, all the sessions and process connected to SQLArena_TEST database is disconnected and taken the database to offline mode. Let’s ALTER the physical filenames by "_CHANGED" :

ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_CHANGED.mdf')
GO
ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_1', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_1_CHANGED.ndf')
GO
ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_2', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_2_CHANGED.ndf')
GO
ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_log', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_log_CHANGED.ldf')
GO

Set the database to online and now you can now check the database information and the status of the databases as:

EXEC sp_helpdb SQLArena_Test
GO
USE SQLARENA_TEST
GO
ALTER DATABASE SQLARENA_TEST SET SINGLE_USER
GO
ALTER DATABASE SQLARENA_TEST SET OFFLINE

While using these scripts in production should be with caution, since backups may fail if the logical filenames are incorrectly assigned. 

Wednesday, 27 September 2017

TRIM Function to Remove Blank Spaces From a String in SQL Server

TRIM Function to Remove Blank Spaces From a String in SQL Server
Often while entering data in the tables or during bulk insertions, there are high chances of accidently adding white space either in the trailing or leading end of the string. Such instances can cause very annoying for the users to see the results in a shabby format. To overcome the problem of blank space in the string, we can create the TRIM() function and pass the string as parameter to the function.

Before moving to create the trim function, let’s see the two types of Trim functions that are available in SQL Server

1.  LTRIM - This function helps in removing the blank spaces from the leading end of the string
2.  RTRIM - This function helps in removing the blank spaces from the trailing end of the string

Using the above function in SQL Server to trim the string of an existing table in a database. The initial step is to create the table and insert some values to it with blank spaces. For example, In the CusName column insert names with the blank spaces both front and back of the string.

USE SQLArena_Test
GO
Create table TestDB
(
EID int null,
CusID nvarchar(100) null,
CusName Varchar(100) null
)

From the above table created, in case the names in “CusName” consists of leading and trailing spaces, then use the below command:

UPDATE TestDB SET CusName= LTRIM(RTRIM(CusName))

In the update statement, both LTRIM() and RTRIM() functions are applied while passing the CusName as the parameter. Both the functions are needed to apply in SSMS or Oracle to remove unwanted white spaces. In case if we want to create a function named “Trim” then we can create the function as show:

use SQLArena_Test

GO

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END

GO
SELECT dbo.TRIM('SQLArena')
GO

Additionally, using these function in JOINT and WHERE clause can reduce the performance drastically and using these in production should be into consideration as the query run-time can increase very high. 

Tuesday, 26 September 2017

How to Add or Remove Identity Property on Column from a table in SQL server

I got stuck at a point on removing the identity column from a table in a database, thinking much about T-SQL scripts to remove the identity property. However, there is no direct DDL scripts like ALTER or MODIFY tables to remove the identity property.

Steps to remove the Identity property through GUI in SSMS:

1. Before dropping the identity, please check the table from the database that you are referring to
2. From the “Object Explorer”, expand the database referring to and right click on the table where the identity property needs to be changed.
3.  Select “Design” option from the list, which will open the design of the table as shown below.
4. Under Result section, the design is popped with the details of the table with respect to structure.
5. Now, expand the “Identity Specification” and opt as “Yes/No” in the “Is Identity” option.
6. Click on the “Save” option to effect the changes made

When you click on the "Design option", the below layout pops up:


Take a look at the result section to opt the "Is Identity" changes:
















Additionally, there is an option to generate T-SQL script by the action performed using the GUI in SSMS. Since there is no ready-made script for changing the identity property, a feature in SSMS could be used to generate the T-SQL script.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO

/*creating a DumpTable*/
CREATE TABLE dbo.Tmp_DumpTable
       (
[COUNT] int not null identity(1,1),
fruits nvarchar not null,
vegetables nvarchar not null
       )  ON UserData
       TEXTIMAGE_ON UserData
GO
ALTER TABLE dbo.Tmp_DumpTable SET (LOCK_ESCALATION = TABLE)

GO
/  /*Setting the Identity_Insert to ON*/
SET IDENTITY_INSERT dbo.Tmp_DumpTable ON
GO
IF EXISTS(SELECT * FROM dbo.DumpTable)
       EXEC('INSERT INTO dbo.Tmp_DumpTable (Count,Fruits,Vegetables)
              SELECT Count,Fruits,Vegetables FROM dbo.DumpTable WITH (HOLDLOCK TABLOCKX)')
GO
/  /*Setting the Identity_Insert to OFF*/
SET IDENTITY_INSERT dbo.Tmp_DumpTable OFF
GO

/*Dropping the created DumpTable*/
DROP TABLE dbo.DumpTable
GO
EXECUTE sp_rename N'dbo.Tmp_DumpTable', N'DumpTable', 'OBJECT'
GO

COMMIT