SET ROWCOUNT (Transact-SQL)

SQL Server to stop processing the query after the specified number of rows are returned. To set this option off so that all rows are returned, specify SET ROWCOUNT 0. Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes...

Manually set a new current identity value for the identity column.

Resetting the current identity value, if it is needed The following example resets the current identity value, if it is needed, of the specified table in the database. DBCC CHECKIDENT ('dbo.CityMaster'); GO Reporting the current identity value The following example reports the current identity value in the specified table in database, and does not correct...

Six Different ways to find table rows.

You can write below Queries into SQL query window. And you can select a database, on which you need to get row count of specific table. You can use COUNT(*) function in sql statement Select count(*) from dbo.UserMaster You can use COUNT(1) function in sql statement Select count(1) from dbo.UserMaster You can use aggregate function,...

Non-breaking Space in String Value

Developer tries to truncate the space in string but there is a problem found and told me about the space is not remove from string even a using LTRIM & RTRIM functions. First is how to find which character used in string instead of space. Here I have lookup the SQL query result below. SELECT...

Make comma separated value to Columns in SQL

One of my colleague asked me this question, Is there a way to generalize a function to spilt comma separated value and make columns if the column doesn’t has a fixed length.  Yes it can be as below Syntax: LEFT (ColumnName,LEN(ColumnName) – n) The function calculates the length of the string and removes last n characters using LEFT() function. Here is...

ALWAYS BE AWARE OF COLUMN DATATYPES

I’ve got a database of statistics with data like this for players. I downloaded a CSV and loaded it into SQL Server. I would be going to analysis of players for the year wise. I have been used the database PlayerDB. I decided to play with the data a bit and at one point wanted to...

Recompile Database Stored Proedures

----- /* Recompile Database Stored Proedures */ -------- PRINT @@ServerName; DECLARE @name sysname; -- database name DECLARE @dbname sysname; -- database name DECLARE @cmd NVARCHAR(4000); DECLARE @cmd2 NVARCHAR(4000); DECLARE @SQL VARCHAR(MAX); DECLARE @TblTable TABLE ( DBName sysname , RecompileStmt NVARCHAR(4000) ); IF OBJECT_ID('tempdb..#ProcTable') IS NOT NULL BEGIN DROP TABLE #ProcTable; END; CREATE TABLE #ProcTable (...

How to Identify Unused Indexes?

You can find unused indexes on table,  you can use system view SYS.dm_db_index_usage_stats. Here query for mentioned the to find the unused indexes. SELECT OBJECT_NAME(IUS.OBJECT_ID), IUS.* FROM SYS.dm_db_index_usage_stats IUS JOIN SYS.indexes I ON IUS.object_id = I.object_id and IUS.index_id = I.index_id where database_id = DB_ID() database_id - ID of the database on which the table or view...

Recover Drop Objects in SQL

In SQL Server 2018 and 2014, Accidents still may happen. During the sql scripting drop objects like – views, stored procedures, functions, and triggers. And it undocumented how can be recover the deleted script. Beginning the way to recover a sql object using SQL Server fn_dblog function. Which read the database transactional log file. Finally...