:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • Oct 02 / 2017
  • 0
T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Exec vs Sp_ExecuteSQL for dynamic SQL execution

Exec and sp_executeSQL both are used for executing the dynamic sql statements. There are some differences between the both

EXEC

Exec is available since early versions before the sp_executesql. It has no direct support of using or storing the local variables. Also using Exec makes the setup vulnerable to sql injection attacks as parameters appear as part of code.

SP_ExecuteSQL

sp_executesql is improved way to execute dynamic sql statements. It provides mechanism to use and store values in local variables. This mechanism is not vulnerable to sql injection as variables do not appear as part of code but as an operand. Also it enables the sql engine to use the cached execution plan of the statement like a stored procedure.

Conclusion

sp_executesql has upper hand as compared to exec method of executing dynamic sql. Keeping in view the flexibility, efficiency and security of sp_executesql we should opt it for executing dynamic SQL.

  • Aug 25 / 2017
  • 0
Columnstore Indexes, T-SQL Scripts, T-SQL Tips and Tricks

Row count for all tables with clustered columnstore index in a database

Consider a scenario that you require to get number of rows for tables in a database with clustered column store index (CCI). It would require to access three tables to get the required info. Following script will get the row count for all tables with CCI

-- Get row count for all tables with CCI in a DB
SELECT schema_name(o.schema_id)+'.'+o.name AS CCITables,
sum(si.rowcnt) AS NoOfRows
FROM sys.objects o
INNER JOIN sysindexes si ON o.object_id = si.id
INNER JOIN sys.indexes i ON o.object_id = i.object_id
AND i.type = 5
GROUP BY schema_name(o.schema_id)+'.'+o.name
ORDER BY NoOfRows DESC
GO

  • Apr 11 / 2017
  • 0
System Stored Procedures, T-SQL Tips and Tricks

Considerations while using SP_Rename to change name of object

Before going to concerned point that i want to share through this post i.e. considerations while using sp_rename,  i would like to summarize the scope and features of Sp_Rename system stored procedure.

Scope and Features of Sp_Rename

Sp_Rename is used the change the name of any user object in a SQL Server database or even the database itself. It accepts three parameters for current name, new name and object type. Generally object type parameter (@objtype) can be skipped unless there is a name conflict for concerned object.

Required Permissions

Following are the permissions required for different type of objects

Permissions for using sp_rename in SQL Server T-SQL

Issues while using sp_rename

There are some associated issues while using sp_rename. These should be known and taken care of

Using schema name in new name parameter

You can use schema name along with object name in current name of object but DO NOT use it in new name provided to SP as parameter. It would also take the schema name as object name. Consider following example for this scenario.

 

-- Use any test DB
Use Test
GO
-- Create table
Create table TableName_Old (id int identity(1,1), FName varchar(50))
GO
-- Verify Table name
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like 'TableName_Old'

-- Rename table
exec sp_rename 'dbo.TableName_Old', 'dbo.TableName_New'
GO

And when we tried to verify with table with new name the result is empty.

-- Verify table with new name
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like 'TableName_new'

Let us run a generic search to include more tables.

-- Verify table with generic search
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like '%TableName%'

Now we have following result that shows that schema dbo is also prefixed in name of table.
Result of sp_rename T-SQL

So we have to execute another sp_rename command to set the required name of this table.


-- Rename and remove the schema prefix
exec sp_rename 'dbo.[dbo.TableName_New]', 'TableName_New'
GO

This fixed out the issue and we have table name as TableName_New.

SP_rename for name correction T-SQL

Effect on Associated Indexes

Associated index will be automatically renamed whenever a PRIMARY KEY or UNIQUE constraint is renamed.
In same way if a renamed index is associated to a PRIMARY KEY constraint,
the PRIMARY KEY constraint will be automatically renamed.

References to Object

Renaming an object will not update its references to that object. This task should be done manually to avoid code failure after rename.

Code definition

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view.

Conclusion

Sp_rename should be avoided if you have option to drop and recreate an object with a different name. Analysis of code base is imperative to make sure that code will not break after rename is performed.

  • Oct 01 / 2014
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Increase or shrink file size for SQL Server database files

Following handy scripts help to manage the SQL Server database files. You may increase the max size and current size as well.

-- Increase file max size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', maxsIZE = 1500MB)  
 GO  
 -- Increase file size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', sIZE = 1500MB)  
 GO  

Shrink operation may be up to a specific extent or just shrink by removing the space available ay the end of file without manipulating data in file.If file shrink operation is cancelled during execution the current status is not rolled back. TRUNCATEONLY operation is fast.

-- Shrink to specific limit  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 200)  
 GO  
 -- Shrink to space available at the end of file  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 0, TRUNCATEONLY)  
 GO  
  • Oct 01 / 2014
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Get all file groups and file details in a SQL Server database

Following script will provide information about the filegroups and files in a given database. It includes names, location, allocated size, used size and percent free.

-- get data file space and locations  
 Use DatabaseNameHere;  
 SELECT b.groupname AS 'File Group'  
   ,a.NAME  
   ,physical_name  
   ,CONVERT(INT, a.Size / 128.000, 2) AS [Currently Allocated Space (MB)]  
   ,CONVERT(INT, FILEPROPERTY(a.NAME, 'SpaceUsed') / 128.000, 2) AS [Space Used (MB)]  
   ,CONVERT(INT, a.max_Size / 128.000, 2) [Maximum Space (MB)]  
   ,CASE   
     WHEN a.IS_PERCENT_GROWTH = 0  
       THEN CONVERT(VARCHAR, CONVERT(DECIMAL(15, 2), ROUND(a.growth / 128.000, 2))) + ' MB'  
     ELSE CONVERT(VARCHAR, a.growth) + ' PERCENT'  
     END [Growth]  
   ,CONVERT(INT, (a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2) AS [Available Space (MB)]  
   ,(CONVERT(INT, ROUND((a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2)) * 100) / (CONVERT(INT, ROUND(a.Size / 128.000, 2))) AS PercentFree  
 FROM sys.database_files a(NOLOCK)  
 LEFT OUTER JOIN sysfilegroups b(NOLOCK) ON a.data_space_id = b.groupid  
 ORDER BY PercentFree  
  • Oct 11 / 2013
  • 0
Backup and Restore, Consultancy, dbDigger, T-SQL Tips and Tricks

Create directories through T-SQL

In a recent task i was connected to SQL Server through SSMS and had no OS access. To configure the automatic backups i was required to create couple of directories on the disk. So without having OS access i had to create directories by using T-SQL. Following is the command that i used to create ‘D:DBBackupsDBDigger’

EXEC master.dbo.xp_create_subdir 'D:DBBackups_DBDIgger'
GO
Consult us to explore the Databases. Contact us