DataType can matter in where condition - a worst case scenario by serverku

We should use proper filter in where conditions as per data type. Like if the data type of filtered column is varchar then use filtered variable or value varchar, if filtered column is an integer then filtered variable/value should be an integer.

Worst case,

 
-- #1. 
-- If OrderId column is VARCHR, this cause table scan
-- because here OrderId column all values convert from varchar to int 
-- even if index created on it 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

Best case,

 
-- #1. 
-- If OrderId column is INT, this will work fine 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

-- #2. 
-- If OrderId column is INT, this will also work fine 
-- because here value '123' convert from varchar to int 
SELECT * FROM OrderDetails 
WHERE OrderId = '123'

Create a table with sample records with different data types as mentioned in above queries and check the execution plan, you may see the difference for both of them.

COUNT(*) with a LEFT JOIN may produce unexpected results - SQL Server by serverku

We should not use count (*) for left outer join two or more tables with group by. Instead we can use right table's column for count, otherwise it will come up with unexpected output.

Way cause for unexpected output

 
SELECT 
    a.id, 
    COUNT(*)
FROM table1 a
LEFT JOIN table2 b ON (a.id=b.id)
GROUP BY a.id
GO

Right way

 
SELECT 
    a.id, 
    COUNT(b.id) 
FROM table1 a
LEFT JOIN table2 b ON (a.id=b.id)
GROUP BY a.id
GO

Create tables with sample records, run above queries for them and see difference. Did you know this earlier?

Trace the query with specific session in SQL Server Profiler by serverku

One more enhancement i found with Query Editor of SQL Server 2012 and later version, we can trace the query with specific current session in SQL Server Profiler. For that just need to right click in Query Editor and you can find the option Trace Query in SQL Server Profiler or press CTRL + ALT + P, which will open SQL Profiler filtered with that current session.

Below is a screen shot for the same.

Did you know this or used ever?

Missing index suggesion by Execution Plan - SQL Server by serverku

The index is a most important factor in the optimization and performance. Whenever we work for the query optimization, we are mostly looking for indexes, then check any other factors. Also scheduling the job for the maintenance of the indexes on periodical basis. From Index usage and statistics report, we can have an idea for the index optimization. But SQL Server query execution plan also recommended and give the suggestion with index hint. We will look here the demonstration for the same with execution plan.

--- Creating tables
IF (OBJECT_ID('ItemTypes','U') > 0 )
DROP TABLE ItemTypes
GO

IF (OBJECT_ID('ItemDetails','U') > 0 )
DROP TABLE ItemDetails
GO

CREATE TABLE ItemTypes
(
 ItemType varchar(100),
 ItemTypeDesc varchar(100)
)
GO

CREATE TABLE ItemDetails
(
 ItemId int not null,
 ItemName varchar(100),
 ItemType varchar(10),
 ItemDescription varchar(200)
)
GO


-- Inserting sample records
INSERT INTO ItemDetails
SELECT 
 a.id,
 a.name,
 a.xtype,
 'ItemDesc' 
FROM sys.sysobjects a
CROSS JOIN sys.sysobjects b
GO

INSERT INTO ItemTypes
SELECT distinct 
 type,
 type_desc 
FROM  sys.objects 
GO

-- Review the execution plan.
SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 it.ItemTypeDesc as ItemTypeDesc,
 id.ItemDescription
FROM ItemDetails id
INNER JOIN ItemTypes it
ON (it.ItemType = id.ItemType)
GO

You can see the index hint with the execution plan, and following is a script for same.

/*
Missing Index Details from SQLQuery.sql - ReportServer (dba (56))
The Query Processor estimates that implementing the following index could improve the query cost by 89.0255%.
*/

/*
USE [ReportServer]
GO
CREATE NONCLUSTERED INDEX []
ON [dbo].[ItemDetails] ([ItemType])
INCLUDE ([ItemId],[ItemName],[ItemDescription])
GO
*/

Apply it and enjoy!

Insert default values for all columns in table - SQL Server by serverku

I was asked by some of the job colleagues as how can insert all the DEFAULT values in table when table have all the columns defined with default property. If the table has at least one column without default and identity column, then we can do insert default with rest columns easily. How can with insert default values for all columns in the table? For that we will see a small demo where we will create one table with all columns defined by default and will see the insertion of default values for the same.

USE DEMO
GO

-- Creating table
IF (OBJECT_ID('DefaultTable','U') > 0)
DROP TABLE DefaultTable
GO

CREATE TABLE DefaultTable
(
 TransactionId INT IDENTITY(1,1) NOT NULL,
 TransactionName VARCHAR(100) NOT NULL DEFAULT 'Test Transaction',
 TransactionDate DATETIME NOT NULL DEFAULT GETDATE(),
 TransactionType SMALLINT NOT NULL DEFAULT 0
)
GO

-- Insert Default values for all columns

INSERT DefaultTable
DEFAULT VALUES
GO

-- Reviewing records in table
SELECT 
* 
FROM DefaultTable
GO

Let us try more with some more inserts and see again,

INSERT DefaultTable
DEFAULT VALUES
GO 100

-- Reviewing records in table
SELECT 
* 
FROM DefaultTable
GO

You might experience this type of need.

Script to get Database Files detail - SQL Server by serverku

The DBA needs to perform some day to day activities to monitor SQL servers and databases as everything is OK or not. Some of the tasks are based on daily, some are on weekly and some are on a monthly. But database activities and monitoring and its details are important because databases are growing day to day and accordingly we need to check out the disk space.

For that we should have all the database details and statistics like database file current size,growth and maximum size.We can do check the databases properties and file size for each of them manually. We should have the script to check it for all or specific databases Which can ease to get the details and monitoring with same. Here I will write a simple script which will give the specific database files and information for the same. Let us drive with it, here we will create one database and add one filegroup and secondary files in that.

 
USE MASTER
GO

-- Creating database 
CREATE DATABASE DatabASeFileDetails
GO

-- Adding a new filegroup to database 
ALTER DATABASE DatabASeFileDetails
ADD FILEGROUP NewFileGroup;
GO

--Adding a new secondary and log files to a database to above created filegroup
ALTER DATABASE DatabASeFileDetails 
ADD FILE 
(
    -- New secondary files added here
    NAME = FileGroupDB_Data_1,
    FILENAME = 'C:\DatabASeFileDetails_Data_1.ndf',
    SIZE = 15 MB,
    MAXSIZE = 100 MB,
    FILEGROWTH = 5 MB
),
(
    -- New log file added here
    NAME = FileGroupDB_Log_1,
    FILENAME = 'C:\DatabASeFileDetails_Log_1.ldf',
    SIZE = 5 MB,
    MAXSIZE = 100 MB,
    FILEGROWTH = 5 MB
)
TO FILEGROUP NewFileGroup; -- Defining filegroup name here
GO

Now we have created databases and done with new filegroup and database files. We have turned to run the script which we actually want, which returns with database details.

 
-- Using that database
USE DATABASEFILEDETAILS
GO

SELECT  
  DB_NAME(DBID) AS DatabaseName, 
  Name AS LogicalFileName,
  CASE 
    WHEN FILEID = 1 
     THEN 'Primary File'
       WHEN FILEID = 2 THEN 'Log File'
     ELSE 'Secondary File' 
  END AS FileDescription,
  FILEGROUP_NAME(groupid) AS FileGroup,
  CAST( ((CAST (SIZE AS NUMERIC(18,2) )*8)/1024) AS NUMERIC(18,2)) AS [FileSize(MB)],
  CASE status  
    WHEN 0 
     THEN  'No growth'
    WHEN 2 
     THEN CAST(CAST(((CAST (growth AS INT )*8)/1024) AS INT) AS VARCHAR(1000)) + ' [growth(MB)]'
    ELSE CAST(growth AS VARCHAR(1000)) + ' [growth(%)]'
  END AS Growth,
  CASE maxsize
    WHEN 0 
      THEN 'No growth'
    WHEN -1 
      THEN 'File will grow until the disk is full'
    ELSE CAST(CAST(((CAST (maxsize AS NUMERIC(18,2) )*8)/1024) AS NUMERIC(18,2)) AS VARCHAR(1000))
  END AS [MaxFileSize(MB)],
  FileName AS PhysicalFilePath
FROM  SYS.SYSALTFILES
WHERE DBID > 4
AND DB_NAME(DBID) = 'DatabASeFileDetails'
ORDER BY DBID,FileId 

GO

In the above snapshot we have compared database script results and database property, so we can have idea everything are ok and fine. What you are using? Please fill your comments if I missed something.

Cumulative Calculation with some methods - SQL Server by serverku

Sometime we have a need some calculation custom logic like some aggregation, pivoting and unpivoting etc. We have a same need here to do the calculation for the cumulative data from the logic. At that time we are implementing any logic which is in our mind, which are not going to find the best and easy way to do that thing and that cause the performance issue.

In a previous article I have posted for pivoting and unpivoting usage. Here I would like you to go through all the way which used to calculate cumulative data. The thing is that we can have all the ways to get it and which are the best among them, but it is totally depend our query and table data. We will look all the ways one by one and decide the best way related to logic. Before implementing it, we will first create objects required for the demo.

USE DEMO
GO

-- Creating table
IF (OBJECT_ID('CummulativeData','U') > 0)
DROP TABLE CummulativeData
GO

CREATE TABLE CummulativeData
(
TransactionId Int identity(1,1) not null,
UserAccountId int not null,
UserName varchar(500) not null,
Amount numeric(18,2),
TransactionDate datetime 
)

GO

-- Inserting sample records in table
INSERT CummulativeData
(
UserAccountId,
UserName,
Amount,
TransactionDate
)
SELECT 1234, 'ABCUSER', 250.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 350.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 150.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 100.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 300.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 650.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 50.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 100.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 450.00, GETDATE()

GO

-- Reviewing data from table
SELECT 
TransactionId,
UserAccountId,
UserName,
Amount,
TransactionDate 
FROM CummulativeData
WHERE UserAccountId = 1234
ORDER BY TransactionId

GO

After creating objects and sample records we have a turn to test drive with all the methods.

#1 way - Using temp table with while loop :

 
-- creating temp table
CREATE TABLE #tempCummulative
(
TransactionId Int ,
UserAccountId int not null,
UserName varchar(500) not null,
Amount numeric(18,2),
TransactionDate datetime ,
CummulativeAmount numeric(18,2)
)
GO

-- variables declaration
DECLARE @cnt int, @LastAmmout numeric(18,2)
SET @cnt = 1
SET @LastAmmout = 0.00

-- While loop start
WHILE((SELECT COUNT(1) FROM CummulativeData WHERE UserAccountId = 1234) >= @cnt)
BEGIN

INSERT INTO #tempCummulative
SELECT 
TransactionId,
UserAccountId,
UserName,
Amount,
TransactionDate,
@LastAmmout + Amount
FROM CummulativeData
WHERE UserAccountId = 1234
AND TransactionId = @cnt

SET @LastAmmout = (select CummulativeAmount from #tempCummulative where TransactionId = @cnt)
SET @cnt = @cnt + 1

END
-- While loop end

-- Viewing cummulative data from temp table 
SELECT 
* 
FROM #tempCummulative
GO;

#2 way - Adding column in table and while loop :

 

-- Adding column for CummulativeAmount
ALTER TABLE CummulativeData
ADD CummulativeAmount numeric(18,2)
GO

-- declaring variables
DECLARE @cnt int, @LastAmmout numeric(18,2)
SET @cnt = 1
SET @LastAmmout = 0.00

-- while loop start
WHILE((SELECT COUNT(1) FROM CummulativeData WHERE UserAccountId = 1234) >= @cnt)
BEGIN

UPDATE CummulativeData
SET @LastAmmout = @LastAmmout + ISNULL(Amount,0.00),
CummulativeAmount = @LastAmmout
WHERE UserAccountId = 1234
AND TransactionId = @cnt

SET @cnt = @cnt + 1

END
-- while loop end


-- Viewing cumulative data from table 
SELECT 
* 
FROM CummulativeData
GO

#3 way - Common Table Expression (CTE):

 
-- CTE 
;WITH cteCummulative
AS
(

SELECT TOP 1 
TransactionId,
UserAccountId,
UserName,
Amount,
TransactionDate,
Amount as CummulativeAmount  
FROM CummulativeData
WHERE UserAccountId = 1234
ORDER BY TransactionId

UNION ALL

SELECT 
c1.TransactionId,
c1.UserAccountId,
c1.UserName,
c1.Amount,
c1.TransactionDate,
CAST(c2.CummulativeAmount + c1.Amount AS numeric(18,2))
FROM CummulativeData c1
INNER JOIN cteCummulative c2
ON (c1.UserAccountId = c2.UserAccountId and c1.TransactionId = c2.TransactionId + 1 )

)

-- Viewing cummulative data from CTE 
SELECT
* 
FROM cteCummulative 

Stay tuned for more.

PERSISTED Columns with HierarchyId Datatype - SQL Server 2008 by serverku

I think you have already read all the articles related to HierarchyId in the past And hope you understood the concept and usage of it. You also experienced with performance by using HierarchyId datatype as I have posted the performance review in earlier posts as well.

With earlier posts you got some basic methods or functions as how we get the hierarchy data levels, root node, string path. Also, some other methods like as how can we get up-line & down-line modes. Here I am going to present the same, but as Persisted column. So we do not need to write those functions in query level every time. Let us see the workaround for that.

How can ?
We need to create one table and define those columns as a function call as a PERSISTED. We will look the methods to get the hierarchical data without defining columns as Persisted columns and we will use those function calls at the query level. The scripts for the same are as follows.

-- Creating objects
IF (OBJECT_ID('TblHierarchyStructure','U') > 0)
DROP TABLE TblHierarchyStructure
GO

CREATE TABLE TblHierarchyStructure
  (
     ItemId        INT,
     ParentItemId  INT,
     ItemOrder     INT,
     ItemName      VARCHAR(100),
     HierarchyNode HIERARCHYID
  )

GO

-- Inseting records in tables for the demo
INSERT INTO TblHierarchyStructure
            (ItemId,
             ParentItemId,
             ItemOrder,
             ItemName,
             HierarchyNode)
SELECT 1,
       NULL,
       1,
       'RootItem',
       HierarchyId::Parse('/')
UNION ALL
SELECT 2,
       1,
       1,
       'FirstItem',
       HierarchyId::Parse('/1/')
UNION ALL
SELECT 3,
       1,
       2,
       'SecondItem',
       HierarchyId::Parse('/2/')
UNION ALL
SELECT 4,
       1,
       3,
       'ThirdItem',
       HierarchyId::Parse('/3/')
UNION ALL
SELECT 5,
       2,
       1,
       'FourthItem',
       HierarchyId::Parse('/1/1/')
UNION ALL
SELECT 6,
       4,
       1,
       'FifthItem',
       HierarchyId::Parse('/3/1/')
UNION ALL
SELECT 7,
       5,
       1,
       'SixthItem',
       HierarchyId::Parse('/1/1/1/')
UNION ALL
SELECT 8,
       5,
       2,
       'SeventhItem',
       HierarchyId::Parse('/1/1/2/')
UNION ALL
SELECT 9,
       5,
       3,
       'NinthItem',
       HierarchyId::Parse('/1/1/3/')
UNION ALL
SELECT 10,
       8,
       1,
       'TenthItem',
       HierarchyId::Parse('/1/1/2/1/')

GO

-- Usinf HierarchyId functions at query level and see output.
SELECT *,
       HierarchyNode.ToString()                AS ItemNodeString,
       HierarchyNode.GetLevel()                AS ItemNodeLevel,
       HierarchyNode.GetAncestor(1)            AS ParentNode,
       HierarchyNode.GetAncestor(1).ToString() AS ParentNodeString
FROM   TblHierarchyStructure
GO

Now we will look the methods to get the hierarchical data with defining columns as Persisted columns and we will use those functions call at the column level. The scripts for the same are as follows.

-- Creating objects
IF (OBJECT_ID('TblHierarchyStructure','U') > 0)
DROP TABLE TblHierarchyStructure
GO

CREATE TABLE TblHierarchyStructure
  (
     ItemId        INT,
     ParentItemId  INT,
     ItemOrder     INT,
     ItemName      VARCHAR(100),
     HierarchyNode HIERARCHYID NOT NULL PRIMARY KEY,
     ItemNodeString AS HierarchyNode.ToString() PERSISTED,
     ItemNodeLevel AS HierarchyNode.GetLevel() PERSISTED,
     ParentNode AS HierarchyNode.GetAncestor(1) PERSISTED,
     ParentNodeString AS HierarchyNode.GetAncestor(1).ToString()
  )

GO

-- Inserting sample records here 
INSERT INTO TblHierarchyStructure
            (ItemId,
             ParentItemId,
             ItemOrder,
             ItemName,
             HierarchyNode)
SELECT 1,
       NULL,
       1,
       'RootItem',
       HierarchyId::Parse('/')
UNION ALL
SELECT 2,
       1,
       1,
       'FirstItem',
       HierarchyId::Parse('/1/')
UNION ALL
SELECT 3,
       1,
       2,
       'SecondItem',
       HierarchyId::Parse('/2/')
UNION ALL
SELECT 4,
       1,
       3,
       'ThirdItem',
       HierarchyId::Parse('/3/')
UNION ALL
SELECT 5,
       2,
       1,
       'FourthItem',
       HierarchyId::Parse('/1/1/')
UNION ALL
SELECT 6,
       4,
       1,
       'FifthItem',
       HierarchyId::Parse('/3/1/')
UNION ALL
SELECT 7,
       5,
       1,
       'SixthItem',
       HierarchyId::Parse('/1/1/1/')
UNION ALL
SELECT 8,
       5,
       2,
       'SeventhItem',
       HierarchyId::Parse('/1/1/2/')
UNION ALL
SELECT 9,
       5,
       3,
       'NinthItem',
       HierarchyId::Parse('/1/1/3/')
UNION ALL
SELECT 10,
       8,
       1,
       'TenthItem',
       HierarchyId::Parse('/1/1/2/1/')

GO

-- We have not using HierarchyId functions at query level
-- and using them at columns level as Persisted
SELECT 
*
FROM   TblHierarchyStructure 

GO

I hope you liked this post about Persisted columns with HierarchyID new datatype. Share your experience if you know this type of the usage.

Example of SET XACT_ABORT ON in SQL Server by serverku

We have seen one error The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION and seen workaround too. There is related to transaction mismatch and now I am writing further same with different error and an issue.

In that post, if I remove one column of used tables in those SPs. What will happen? It will raise an error and keep the transaction open. Let us check.

-- Creating table which will be used in SPs.
CREATE TABLE tbl_Tran
  (
     TranId    INT NOT NULL PRIMARY KEY
     ,TranName VARCHAR(10)
  )
 
GO
 
-- Altering first stored procedure here
CREATE PROCEDURE Firttranproc
AS
  BEGIN
	SET NOCOUNT ON
    
   -- Here we have specified Tran1 as transaction name
  
   BEGIN TRY
          BEGIN TRANSACTION
 
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
    
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran1'
 
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Altering second stored procedure here
CREATE PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON
 
      BEGIN TRY
          BEGIN TRANSACTION
 
    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'
 
          -- Calling first stored procedure here
          EXEC Firttranproc
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran2'
 
     
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Executing second stored procedure which will also call SP1 
EXEC Secondtranproc
GO

 -- Droping one column to raise an complite type error 
ALTER TABLE tbl_Tran
DROP COLUMN TranName
GO
 
-- Executing second stored procedure which will also call SP1 
EXEC Secondtranproc
GO
Msg 207, Level 16, State 1, Procedure Secondtranproc, Line 13
Invalid column name 'TranName'.
Msg 266, Level 16, State 2, Procedure Secondtranproc, Line 13
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

This will raise a compiled type error and keep transaction open which you can get details using the following script,

USE master
GO

SELECT 
       est.session_id as [Session ID],
       est.transaction_id as [Transaction ID],
       tas.name as [Transaction Name],
       tds.database_id as [Database ID]
FROM sys.dm_tran_active_transactions tas 
INNER JOIN sys.dm_tran_database_transactions tds 
       ON (tas.transaction_id = tds.transaction_id )
INNER JOIN sys.dm_tran_session_transactions est 
       ON (est.transaction_id=tas.transaction_id)
       WHERE est.is_user_transaction = 1 -- user
       AND tas.transaction_state = 2 -- active
       AND tas.transaction_begin_time IS NOT NULL
GO    
/*
Output :

Session ID  Transaction ID   Transaction Name  Database ID
----------- ---------------- ----------------- -----------
54          176426           user_transaction  19

*/

You can see the open transaction details above. For a fox for such issue we should use SET XACT_ABORT ON in the beginning of the stored procedures. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. So let's change stored procedures,

-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
  BEGIN
	SET NOCOUNT ON
        SET XACT_ABORT ON
   -- Here we have specified Tran1 as transaction name
  
   BEGIN TRY
          BEGIN TRANSACTION
 
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
    
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran1'
 
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
  BEGIN
          SET NOCOUNT ON
	  SET XACT_ABORT ON
 
      BEGIN TRY
          BEGIN TRANSACTION
 
    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'
 
          -- Calling first stored procedure here
          EXEC Firttranproc
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran2'
 
     
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Executing second stored procedure which will also call SP1 
EXEC Secondtranproc
GO

It will raise a run-time error, but does not keep the transaction open. Please execute stored procedures and check for open transaction using query provided above. This is just what I want to share with you. I would like to put your comments.

Run query against all the databases without MSFOREACHDB and WHILE/CURSOR by serverku

I have learned one more thing today, for small queries when we need to run it for all the databases of instance, then we are mostly using MSFOREACHDB and WHILE loop or CURSOR to get the data. But with COALESCE I can I do with very small code here, which collect count of the objects for each database.

DECLARE @ObjectSQL NVARCHAR(MAX)
SET @ObjectSQL = ''
SELECT @ObjectSQL = COALESCE(@ObjectSQL,'') + CHAR(13) + CHAR(10) 
    + 'SELECT ' + QUOTENAME([Name],'''') + ' as DbName, 
    COUNT(1) AS CntObject 
    FROM ' + QUOTENAME([Name],'') + '.DBO.SYSOBJECTS;'  
FROM SYS.DATABASES 
PRINT (@ObjectSQL)
EXECUTE (@ObjectSQL)

Hope you like it.

Script to find Assembly registered in SQL Server by serverku

Sometime we need to know registered an assembly in our SQL Server instance and Today I would like to share a script to find the same. Here is a script for same.

USE <DBName>
GO

SELECT  
 a.name as AssemblyName, 
 af.name as AssemblyPath, 
 a.create_date as CreateDate,
 am.assembly_class as AssemblyClass,
 am.assembly_method as AssemblyMethod,
 a.is_user_defined as IsUserDefined
FROM sys.assemblies AS a
INNER JOIN sys.assembly_files AS af
 ON a.assembly_id = af.assembly_id
LEFT JOIN sys.assembly_modules am
 ON a.assembly_id = am.assembly_id
GO

This script return assembly name, path, created date and object associated with it. I would like to you share, if any, other information can be received for registered assembly.

How to add filtered table in replication - SQL Server by serverku

I wrote some of the articles related to replication e.g. adding tables, stored procedures, views and functions in transactional replication. Now I am sharing one another script to add a filtered table in the transactions table. In script I have applied filters on CreatedDate column SampleTable table. It helps to fit the need of only required rows at subscriber and reduce the overhead of data transfer.

Script :

USE [PublisherDB]
GO

-- Adding the transactional articles
EXEC sp_addarticle 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @source_owner = N'dbo', 
 @source_object = N'SampleTable', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x000000000803509F, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'SampleTable', 
 @destination_owner = N'dbo', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_dboSampleTable]', 
 @del_cmd = N'CALL [sp_MSdel_dboSampleTable]', 
 @upd_cmd = N'SCALL [sp_MSupd_dboSampleTable]', 
 @filter_clause = N'[CreatedDate] between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000'''

-- Adding the article filter
EXEC sp_articlefilter 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @filter_name = N'FLTR_SampleTable_1__51', 
 @filter_clause = N'[CreatedDate] between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000''', 
 @force_invalidate_snapshot = 1, 
 @force_reinit_subscription = 1

-- Adding the article synchronization object
EXEC sp_articleview 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @view_name = N'SYNC_SampleTable_1__51', 
 @filter_clause = N'[CreatedDate]  between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000''', 
 @force_invalidate_snapshot = 1, 
 @force_reinit_subscription = 1
GO

UI :

Hope you like this post. Have a great day!

Script to generate ADD COLUMN statements for existing table - SQL Server by serverku

As we have seen some posts about replication, today I want to share a script which generates a tsql statement to add columns in an existing table. It helps when we need to add columns in an existing table from source and need to add in the destination. So let me share a query here,

SELECT 
'ALTER TABLE '+QUOTENAME(TABLE_NAME)
+' ADD '+QUOTENAME(COLUMN_NAME)+' ' 
+ QUOTENAME(DATA_TYPE)
+ CASE 
  WHEN DATA_TYPE LIKE '%CHAR%' THEN '('+REPLACE(CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)),'-1','MAX') +')' 
  ELSE '' 
 END 
+ CASE 
  WHEN DATA_TYPE IN ('NUMERIC','DECIMAL') THEN '('+CAST(NUMERIC_PRECISION AS VARCHAR(50)) + ',' + CAST(NUMERIC_SCALE AS VARCHAR(50)) + ')' 
  ELSE '' 
 END 
 + CASE 
  WHEN IS_NULLABLE = 'YES' THEN ' NULL' 
  ELSE ' NOT NULL' 
 END 
 + CASE 
  WHEN COLUMN_DEFAULT IS NULL THEN '' 
  ELSE ' DEFAULT '+ COLUMN_DEFAULT 
 END
 FROM INFORMATION_SCHEMA.COLUMNS  
 WHERE TABLE_NAME = '<TableName>' AND TABLE_SCHEMA = '<SchemaName>'
 GO

Note that this is a script about to generates add columns statements without primary key, foreign key ,check constraint and identity. Hope you like this and i request you to share any correction, enhanced script or any different script with full details for the same.

Example of SQL CLR in SQL Server by serverku

After writing some previous posts of replication and mirroring, today i am writing about SQL CLR (common language runtime) server user defined function. As per msdn it is a SQL user-defined function by adding a User-Defined Function to a SQL Server project. After successful deployment, the user-defined function can be called and executed. So let us implement it with one example.

1. Enable clr in SQL Server.

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

2.  This is a sample C# code which have a logic to show up data.

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void HelloWorld_CS(
        SqlDateTime ParaDateTime, SqlString ParaVarchar, SqlInt64 ParaInt)
    {

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            
            conn.Open();

            SqlCommand command = new SqlCommand("SELECT @ParaVarchar + ' Hello World', @ParaInt + 1, dateadd(dd,1,@ParaDateTime)" , conn);
            command.Parameters.AddWithValue("@ParaVarchar", ParaVarchar );
            command.Parameters.AddWithValue("@ParaInt", ParaInt);
            command.Parameters.AddWithValue("@ParaDateTime", ParaDateTime);

            SqlContext.Pipe.ExecuteAndSend(command);

            conn.Close();
        }
    }
}

3. Register a DLL.

4. Create a assembly with registered DLL.

CREATE ASSEMBLY HelloWorldCLR 
FROM 'C:\SQLCLR\HelloWorldTest.dll'
WITH PERMISSION_SET=SAFE;
GO 

5. Create a stored procedure which calls an assembly.

CREATE PROCEDURE Proc_CLR_HelloWorld
(
 @DateTest DATETIME,
 @VarcharTest NVARCHAR(100),
 @IntTest BIGINT
)
AS 
EXTERNAL NAME
    HelloWorldCLR.StoredProcedures.HelloWorld_CS
GO 

6. Finally, run that stored procedure with parameters.

EXEC Proc_CLR_HelloWorld 
 @DateTest ='01/01/2014',
 @VarcharTest = 'Test',
 @IntTest = 1
GO

You might work with SQL CLR and I would like to share your inputs and ideas the way you are using SQL CLR. Hope you like this post.

Script to Database Mirroring in SQL Server by serverku

Apart from replication articles which I have written earlier, today i want to share for database mirroring configure with windows authentication without automatic failover. So let me share steps and required scripts for the same.

1. Make sure principal database has FULL recovery model.

2. Make sure Windows User on which mirror configured has enough access of both servers and databases.

3. Make sure TCPIP port open in firewall for both SQL Server instances on both server accordingly if firewall enabled.

4. Make sure database service running on windows user for which mirroring endpoint will be created.

5. Take a full backup of principal database and restore at partner server with norecovery.

6. Take a transaction backup of principal database and restore at partner server with norecovery and make sure all log backups restored with norecovery created after full backup of principal database.

7. Create a mirroring endpoint.

USE master
go

CREATE ENDPOINT [DatabaseMirroring]
AUTHORIZATION [<DomainName>\<UserName>]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = DISABLED)

8.  Get mirroring role and status details for confirmation.

USE master
go

SELECT 
 state_desc,
 type_desc 
FROM sys.database_mirroring_endpoints

9. Make sure 5022 port or whatever port used should be open in firewall if firewall enabled.

10. Run following command at partner server.

USE master
go

ALTER DATABASE <DatabaseName>
SET PARTNER ='TCP://<PrincipalHostName>.<DomainName>.local:5022'

11. Run following command at principal server.

USE master
go

ALTER DATABASE <DatabaseName>
SET PARTNER ='TCP://<PartnerHostName>.<DomainName>.local:5022'

12.Run following command in one of server if want to change high performance or high safety as per need and if it is supported.

USE master
go

-- OFF : High performance
-- FULL : High Safety
ALTER DATABASE <DatabaseName>
SET SAFETY OFF 

Hope you like this post and might be useful to you.

Move Publisher database to another drive without affecting replication - SQL Server by serverku

A month ago, I wrote some articles related to replication configuration which are following,

Skip distributor agent error in sql server transactional replication - How to
Configuration Replication failure and retry alert-SQL Server

Hope you read and liked it. Now moving one another article and it is about to move the publisher database to another drive in the same server without breaking whole replication. So let me share some steps and continue with same as below,

1. Go to Replication Monitor from Replication tab under default instance the option with right click.

2. Make sure all undistributed command processed and should be 0 for all subscriber.

3. Go to agent tab (to disable)
         a. Go to snapshot agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.
         b. Go to Log reader agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.  
         c. Go to Queue reader agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.

4. Making database restricted and offline it.

 ALTER DATABASE PublisherDB SET restricted_user with rollback immediate;
 ALTER DATABASE PublisherDB SET OFFLINE;

5. Mapping database with new file location.

ALTER DATABASE PublisherDB MODIFY FILE ( NAME = pdb_Data , FILENAME = 'D:\data\pdb_data.mdf' );
ALTER DATABASE PublisherDB MODIFY FILE ( NAME = pdb_log , FILENAME = 'D:\data\pdb_log.ldf' );

6. Stop SQL Database service and copy database files to new mapped location.

7. Making database online and make available with multi user.

ALTER DATABASE PublisherDB SET ONLINE;
ALTER DATABASE PublisherDB SET multi_user;

8. Repeat step 3 with “Go to agent tab (to enable)” and finish this task.

Please note these scripts run against publisher server. These are just steps and hope you found it useful. Thanks for reading.

Add new articles in existing publications without Reinitialize All Subscriptions - SQL Server Replication by serverku

The last time we saw a script to add tables, stored procedures, functions and indexed views in a publication and hope you may like that post. Let us continue here one more addition to post and see how can we add a new article in existing publication without reinitialize all subscriptions in transaction replication. Following are the steps which drive to finish this post,
 
Step  1 :
First add articles through the scripts provided in an earlier post, Here for samples we will add tables in existing publication and run on publisher database.

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'TableName', 
 @source_owner = N'SchemaName', 
 @source_object = N'TableName', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x00000000080350DF, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'TableName', 
 @destination_owner = N'SchemaName', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', 
 @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName]', 
 @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]',
 @force_invalidate_snapshot = 1
GO

Step 2 :
After running above script in publisher database, run following script in the publisher database too.

USE PublisherDB
GO
EXEC sp_refreshsubscriptions '<Publication Name>'
GO

Step 3 :
Final completion both above steps we will just start snapshot agent for that publication from Replication Monitor.

Go to Replication Monitor
Select publication,
Move to Agents tab,
Right click on snapshot agent and start agent. 

You will see number of added articles in last action message there after completion of snapshot agent. This is just I want to share with you and maybe help you a lot. Thanks for reading this post and may you like to.

Script to add articles in publication - SQL Server transactional replication by serverku

Recently I wrote a post which having a script to get articles details added in replication for all publications. Today I am sharing a script to add articles in the existing publication of transactional replication, which we can also add through user interface of publication property. Here I am sharing a query to add tables, views, stored procedures, indexed views and functions.

1. Table :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'TableName', 
 @source_owner = N'SchemaName', 
 @source_object = N'TableName', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x00000000080350DF, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'TableName', 
 @destination_owner = N'SchemaName', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', 
 @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName]', 
 @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]',
 @force_invalidate_snapshot = 1
GO

2. View :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'ViewName', 
 @source_owner = N'schemaName', 
 @source_object = N'ViewName', 
 @type = N'view schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'ViewName', 
 @destination_owner = N'schemaName', 
 @status = 16
GO

3. Stored Procedure :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'ProcedureName', 
 @source_owner = N'SchemaName', 
 @source_object = N'ProcedureName', 
 @type = N'proc schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'ProcedureName', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

4. Indexed View:

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'Indexed View Name', 
 @source_owner = N'SchemaName', 
 @source_object = N'Indexed View Name', 
 @type = N'indexed view schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'Indexed View Name', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

5. Function :

USE PublisherDB
GO
exec sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'FunctionName', 
 @source_owner = N'SchemaName', 
 @source_object = N'FunctionName', 
 @type = N'func schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'FunctionName', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

Please note these queries run against publisher server and database. I will add one more post next to add articles in existing publications without initialize whole subscription. So stay tuned for more!

Delete files using Ole Automation Procedures in SQL Server by serverku

To delete the files using Ole Automation Procedures in SQL Server we need to first enable 'Ole Automation Procedures' using sp_configure as following,

exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

After enabling it, we can delete the files with Ole Automation Procedures which using FSO (File System Object) from SQL Server and we need to pass method name 'DeleteFile' in the tsql script. You can delete all the files or specific files as well.

DECLARE @Result int
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'D:\TestFolder\*.txt'
EXEC @Result = sp_OADestroy @FSO_Token

Find SSIS package details scheduled in jobs - SQL Server by serverku

I would like to share the script to find out the scheduled jobs which call SSIS packages in SQL Server.

USE MSDB
GO
SELECT 
 sj.job_id as JobId,
 sj.name as JobName,
 sjs.step_name as StepName,
 sjs.Command as Command
FROM sysjobs sj 
INNER JOIN sysjobsteps sjs
 ON(sj.job_id = sjs.job_id)
 WHERE sjs.subsystem = 'SSIS'
GO

The script return job name and the SSIS package full path as a command like "/FILE " here SSIS package full path" /CHECKPOINTING OFF /REPORTING E"