Saturday, 17 August 2019

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

Probably, this is one of the simplest concepts for most of the SQL developers. If you are very frequent in performing the DML statements, then you would have seen “rows affected” message in the result tab. In most cases, the rows affected message help us in rectifying the actual rows that are either inserted or updated. But, just hope that the stored procedure written by you doesn’t require a count of the rows being affected. In such cases, we can use a command as SET NOCOUNT to just hide them. Doing so can as well increase the performance of the query. To be very straight, most of my initial Stored Procedures were without the SET NOCOUNT command.

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