Saturday, 17 August 2019

How to Hide Rows Affected Message in SQL Server - SET NOCOUNT

Probably, this could be one of the simplest concepts that most of the developers would be aware of. To connect to our basics of SQL, for any DML statements performed by us, the SSMS would return the number of rows affected in the results table. Most of the times, the number of rows affected are left unnoticed by most of the developers while writing the stored procedure and executing them. Hiding the rows affected message can, in turn, affect the execution time as well. But, for now, let’s get a touch of the basics on how to remove the number of rows affected messages in SQL Server. To be very honest, most of my initial SPs were without NOCOUNT that would have probably hit the query performance.

General syntax of using NOCOUNT for hiding messages:

General syntax of using NOCOUNT for showing messages:

Let’s try with an example of rows affected message in the results tab.
I have created a table as SQLArena_Test and imported the data from SQLArena_Test table to a test temp table. Here, we are seeing the rows affected message as I have not mentioned the SET NOCOUNT statement. 


insert into ##Test
select * from SQLArena_Test

Here, the  SET NOCOUNT ON is not added, so the number of rows affected message will be displayed while importing the data.



insert into ##Test
select * from SQLArena_Test

Here, the  SET NOCOUNT ON is added, so the number of rows affected message will not be displayed while importing the data.

Thursday, 15 August 2019

NOT IN Not Working in SQL Server

This may sound a little silly for SQL experts, but can really help for someone working newly on SQL queries. The NOT IN query is used when there is a need to select the data on the first table, but not the matching records on the second. While selecting such data, there are chances of getting just the NULL in the output though there should be records for display.  

Let’s now take a simple example as below for a demonstration:

select ID from Table1
where ID not in (select ID from table2)

From the above query, we have an empty result. Most of the times there is a confusion when there are millions of records and the NULLS are left unnoticed. Having NULLs in the columns for comparison can lead the results to be NULL.

Let’s try the same query by just adding IS NOT NULL for table2 in the WHERE clause.

select ID from Table1
where ID not in (select ID from table2 where ID is not null)

Here, we can see that the results are coming up for us. There are chances that we may forget or miss that thought that NULLs are present of either of the tables. So, always use the IS NOT NULL statement to fix the confusion of NOT IN not working.

Thursday, 19 July 2018

How To Rename A Column Name Or Table Name In SQL Server

I have seen a lot of times beginners seeking for a request to rename either the column name or table name in SQL Server. For some reason, the tables and columns created in the production servers cannot be dropped and recreated with the custom structure as the prior table could be consisted of data. In this case, we can either use the stored procedure or use the GUI to rename either the Table name or Column name.

Let’s create the tables and insert some data into the table.

CREATE TABLE [dbo].[SQLArena](
       [Column1] [nvarchar](300) NULL,
       [Column2] [nvarchar](300) NULL

INSERT INTO SQLArena(column1, column2) values(1,'value1')


Case 1: Using the GUI to rename the Table Name or Column Name

Open the object explorer, and expand the respective database. Under the respective database, expand once again the table and columns that need to be edited. To rename the table name or column name, a single left mouse key button on the table name or the column name turns to an edit box. Here, you will be able to rename to the desired name needed. Let’s look at the same. 
TableName Change:

ColumnName Change:

Case 2 : Using the system stored procedure to rename the Table Name or Column Name

Format for TableName Change:  EXEC sp_RENAME '[OldTabName]' , '[NewTabName]'

EXEC sp_RENAME '[SQLArena]' , '[SQLArena_TEST]'

Format for ColumnName Change: EXEC sp_RENAME 'TableName.OldColName' , 'NewColName', 'COLUMN'

EXEC sp_RENAME 'SQLArena.Column1' , 'ID', 'COLUMN'
EXEC sp_RENAME 'SQLArena.Column2' , 'Value', 'COLUMN'

Doing the system stored procedure to change the Table Name or Column Name can pop a warning message in the result window, which is not to be considered a major concern.

Warning message: “Caution: Changing any part of an object name could break scripts and stored procedures.

Saturday, 19 May 2018

Difference Between Shared Lock, Exclusive Lock And Update Lock In SQL Sever

Difference Between Shared Lock, Exclusive Lock And Update Lock In SQL Sever

Most often I have come across these locks while experiencing deadlocks. And there had to be a lot of times to understand what is happening exactly at the row-level while fetching the data. Let’s look at the concept and difference between a Shared Lock, an Exclusive Lock and an Update Lock in SQL Server.

Shared Lock (S)

A Shared Lock is basically a read-only lock for a row-level. Any number of resources can fetch the data to read when the shared lock is present on the resource. That means that many process IDs can have a shared lock on the same resource to read the respective data.

Exclusive Lock (X)

The Exclusive Lock is used and valid on a single transaction, that locks either row or a page depending on the data. The mechanism for understanding is simple, where an exclusive lock can be applied only on a single resource. There cannot be more than one exclusive lock on the same resource. Either Insert, Update or Delete commands happen over with the Exclusive lock and these commands will not be in effect until the exclusive lock is released from the resource.

Update Lock (U)

An Update Lock is used and valid when there is a shared lock applicable for a resource. In other words, the update lock cannot be placed until there are no other offending exclusive or update locks for the fetching resource. Additionally, the update lock happens to be acquiring an exclusive when all other locks are released from a resource. 

Sunday, 18 March 2018

How to Find Particular Column from All Tables in SQL Server

It is always too hard to use the Alt + F1 to check and review if there is a column present in the created table in SQL Server. Further, it would be much easier if there was a query to find the required column was present in all tables are not. An easy query for you to find a particular column from all tables in a database.

USE SQLArena_Test
FROM sys.tables AS t
WHERE LIKE 'Date' --Replace Date with your own column name
ORDER BY schema_name, table_name;

Now some of them would seek for the same column whether present or not across all tables and across the database from a server. We can do that as well by using stored procedure sp_msforeachdb.

EXEC sp_MSForeachdb
' USE [?]
IF ''?'' <> ''master''
   AND ''?'' <> ''model''
   AND ''?'' <> ''msdb''
   AND ''?'' <> ''tempdb''
   AND ''?'' like ''database name''    
   SELECT ''?''

 Use the above SELECT statement here to fetch across all your databases