Saturday, October 18, 2014
DELETE millions of records in batches
Sometimes you need to delete millions of records from table. when you try to delete record all millions of records at same time it will cause log file growing and it will cause excessive locking and eventually log file disk space may get of space. so best way to delete records is in batches. please check below script to delete record in batches.
If you're using simple recovery model for database then use CHECKPOINT at the end of script
and if you are using full recovery model for database then backup log.
Best way , create SQL Job with below script and execute instead of executing in query window. SQL job will notify you once DELETE job is complete
SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP (100000) -- this will change
dbo.salesorderdetail
WHERE salesorderdate < '10/18/2012'
SET @r = @@ROWCOUNT;
COMMIT TRANSACTION;
-- CHECKPOINT; -- if simple
-- BACKUP LOG ... -- if full
END
Option B:- count no. of records against no. of delete record. If no. of records are smaller than no. of records that we are going to delete then I would create Temp new table and move recrods in it and then delete old table/truncate old table and you can either rename temp new table with old table name or you can use INSERT INTO from new temp table to old table.
Thursday, October 16, 2014
script out db object and db level permission
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY perm.permission_name ASC, perm.state_desc ASC
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY perm.permission_name ASC, perm.state_desc ASC
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
script to find out object level permission
USE DatabaseNameHere
GO
DECLARE c1 CURSOR FOR SELECT DISTINCT [name]
FROM sysusers
WHERE [name] NOT IN (
'db_accessadmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_ddladmin',
'db_denydatareader',
'db_denydatawriter',
'db_owner',
'db_securityadmin',
'dbo',
'guest',
'public'
)
AND issqlrole=0
OPEN c1
DECLARE @DatabaseUserName sysname
FETCH c1 INTO @DatabaseUserName
WHILE (@@FETCH_STATUS =0)
BEGIN
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
--'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
--'EXEC [sp_grantdbaccess]' + CHAR(13) +
--CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
--CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
--'CREATE USER [' + @ServerUserName + '] ' + 'FOR LOGIN [' + @DatabaseUserName + ']' + CHAR(13) --+ 'GO' + CHAR(13) +
'CREATE USER [' + @DatabaseUserName + '] ' + 'FOR LOGIN [' + @DatabaseUserName + ']' + CHAR(13) + -- 'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
-------------------------
CLOSE _sysusers
DEALLOCATE _sysusers
-------------------------
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
FETCH c1 INTO @DatabaseUserName
END
CLOSE c1
DEALLOCATE c1
GO
DECLARE c1 CURSOR FOR SELECT DISTINCT [name]
FROM sysusers
WHERE [name] NOT IN (
'db_accessadmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_ddladmin',
'db_denydatareader',
'db_denydatawriter',
'db_owner',
'db_securityadmin',
'dbo',
'guest',
'public'
)
AND issqlrole=0
OPEN c1
DECLARE @DatabaseUserName sysname
FETCH c1 INTO @DatabaseUserName
WHILE (@@FETCH_STATUS =0)
BEGIN
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
--'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
--'EXEC [sp_grantdbaccess]' + CHAR(13) +
--CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
--CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
--'CREATE USER [' + @ServerUserName + '] ' + 'FOR LOGIN [' + @DatabaseUserName + ']' + CHAR(13) --+ 'GO' + CHAR(13) +
'CREATE USER [' + @DatabaseUserName + '] ' + 'FOR LOGIN [' + @DatabaseUserName + ']' + CHAR(13) + -- 'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
-------------------------
CLOSE _sysusers
DEALLOCATE _sysusers
-------------------------
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
FETCH c1 INTO @DatabaseUserName
END
CLOSE c1
DEALLOCATE c1
Sunday, October 12, 2014
Validate sql script before deployment
As a DBA, you've been asked to validate/verify SQL script and seee if it's working as expected before deployment. we can verify into development server but there is one more option which we can use for script validation
Use SET NOEXEC ON before and SET NOEXEC OFF after script. once you'll execute it will only create compilation plan for SQL script but it will not make any update/changes into data table.
--verify SQL script
SET NOEXEC ON
--your SQL script
SET NOEXEC OFF
Use SET NOEXEC ON before and SET NOEXEC OFF after script. once you'll execute it will only create compilation plan for SQL script but it will not make any update/changes into data table.
--verify SQL script
SET NOEXEC ON
--your SQL script
SET NOEXEC OFF
 |
| Add caption |
Saturday, October 11, 2014
Stored Proc VS User Defined Functions(UDF)
Basic Difference:-
Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n
values).
Functions can have only input parameters for it whereas Procedures can have input/output parameters .
Functions can be called from Procedure whereas Procedures cannot be called from Function.
Advance Difference
Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function
allows only SELECT statement in it.
Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT
statement.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
section whereas Function can be.
The most important feature of stored procedures over function is to retention and reuse the execution
plan while in case of function it will be compiled every time.
Functions that return tables can be treated as another rowset. This can be used in JOINs with other
tables.
Inline Function can be though of as views that take parameters and can be used in JOINs and other
Rowset operations.
Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a function.
We can go for Transaction Management in Procedure whereas we can't go in Function.
Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n
values).
Functions can have only input parameters for it whereas Procedures can have input/output parameters .
Functions can be called from Procedure whereas Procedures cannot be called from Function.
Advance Difference
Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function
allows only SELECT statement in it.
Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT
statement.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
section whereas Function can be.
The most important feature of stored procedures over function is to retention and reuse the execution
plan while in case of function it will be compiled every time.
Functions that return tables can be treated as another rowset. This can be used in JOINs with other
tables.
Inline Function can be though of as views that take parameters and can be used in JOINs and other
Rowset operations.
Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a function.
We can go for Transaction Management in Procedure whereas we can't go in Function.
Difference between View vs Materialized View
1) First difference between View and materialized view is that, In Views query result is not stored in the
disk or database but Materialized view allow to store query result in disk or table.
2) Another difference between View vs materialized view is that, when we create view using any table,
rowid of view is same as original table but in case of Materialized view rowid is different.
3) One more difference between View and materialized view in database is that, In case of View we
always get latest data but in case of Materialized view we need to refresh the view for getting latest
data.
4) Performance of View is less than Materialized view.
5) This is continuation of first difference between View and Materialized View, In case of view its only
the logical view of table no separate copy of table but in case of Materialized view we get physically
separate copy of table
6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra
trigger or some automatic method so that we can keep MV refreshed, this is not required for views in
database.
generate script to rebuild index for all tables
we can generate script for rebuild indexes for specific database by using below query
SELECT 'ALTER INDEX ALL ON ' + t.[table_Schema]+'.'+ t.[TABLE_NAME] + ' REBUILD; '
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.[TABLE_TYPE] = 'BASE TABLE'
ORDER BY t.[TABLE_NAME];
after executing query copy output of query result into new window and execute it.
Sunday, October 5, 2014
Run SQL Profiler without Sysadmin permission
many times, developers ask permission to run SQL Profiler/Trace. you can grant below permission to run SQL Trace/ Profiler
USE [MASTER]
GO;
GRANT SHOWPLAN to [AD\User]
GRANT VIEW SERVER STATE to [AD\User]
GRANT ALTER TRACE to [AD\User]
USE [MASTER]
GO;
GRANT SHOWPLAN to [AD\User]
GRANT VIEW SERVER STATE to [AD\User]
GRANT ALTER TRACE to [AD\User]
Subscribe to:
Posts (Atom)