SQL Server Indexes Tutorial

This article gives a real world explanation (and code examples) of indexing along with the tools necessary to be able to choose the right indexing strategy for your tables. Continue reading ...

SQL Server Interview Questions

Real world questions asked to real interviewees. These interview questions cover the different areas of SQL Development, data flow, and Database Administration.

Delete Duplicate Records in SQL

Examples on how to removed duplicate rows from your table even if you do not have a primary key. Continue reading ...

Replace Invalid Characters

Example on how to replace characters within a SQL String. Examples would be to remove all alpha or numeric characters from a string.

MetaData Change Data Capture Solution (CDC)

Change Data Capture, CDC is a feature that is revolutionizing SQL Server in respects to data flow. ┬áPrevious to change data capture, triggers or high water marks needed to be used in order to perform delta transfers of data to data warehouses or disparate systems. This example shows the use of a custom CDC solution that utilizes a definition table which stored table and column names in order to create a meta-data driven CDC subscription. First create your CDC subscription table [cc lang=”sql”] CREATE TABLE [dbo].[Columns_To_Monitor_Sto]( [ColumnID] [int] IDENTITY(1,1) NOT NULL, [DatabaseName] [varchar](40) NULL, [SchemaName] [varchar](20) NULL, [TableName] [varchar](255) NULL, [ColumnName] [varchar](255) NULL, [Ordinal] [int] NULL, [IsActive] [tinyint] NULL, ) [/cc] With the table now created, we have a meta data repository of columns. Anything added or removed from this table will automatically get added or removed from the CDC subscription. [cc lang=”sql”] DECLARE @DebugMode int = 1 DECLARE @source_schema varchar(20) DECLARE @source_name varchar(255) DECLARE @role_name varchar(20) = ‘cdc_manager’ DECLARE @supports_net_changes int = 1 DECLARE @captured_column_list varchar(MAX) = ” DECLARE @TableCount int DECLARE @iOrigCount int DECLARE @capture_instance nvarchar(MAX) DECLARE @TableName varchar(255) DECLARE @IsCDCEnabled int CREATE TABLE #TablesToMonitor ( ID int IDENTITY(1,1) ,SchemaName varchar(20) ,TableName varchar(255) ,ColumnsToMonitor varchar(MAX) ,CaptureInstance varchar(MAX) ,IsCDCEnabled bit ) INSERT INTO #TablesToMonitor ( TableName ,SchemaName ,ColumnsToMonitor ,CaptureInstance ,IsCDCEnabled ) SELECT DISTINCT rcm.TableName ,rcm.SchemaName ,ColumnsToMonitor = SUBSTRING((SELECT ‘,’ + r.ColumnName FROM( SELECT DISTINCT TableName,ColumnName FROM dbo.Columns_To_Monitor_Sto WHERE IsActive = 1 ) r WHERE (r.TableName = rcm.TableName) FOR XML PATH(”) ), 2, 8000) ,CaptureInstance = rcm.SchemaName + ‘_’ + […] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!