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...

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...

RECOVER DROP DATABASE OBJECTS

For SQL Server 2008 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...

Table Wise Rows Count

Here SQL developer need to know how grow the table size and  table wise raw pages in Database. you can easily find the total rows of specific table or all table in db. 1 - SQL Script SELECT obj.name TableName , ind.rows TotalRows FROM sysobjects obj INNER JOIN sysindexes ind ON (obj.id = ind.id) WHERE obj.xtype...

Encryption Methodology in SQL Server.

Encryption and Decryption methodology in SQL Server