SQL Approach

Free SQL database compare tool

2011-12-13T06:08:00.000-08:00

Hi All, SqlDbAid now comes with a new database compare module.

The comparison uses an offline mode like the previous version,
but now you'll be able to visually analyze the differences.

All you have to do is connect to two databases, select the objects
you want to examine and then create two compare exports.

These files will be the input for the compare module.

MKST Staging Area Template

2011-05-31T15:36:00.000-07:00

MKST provides a standard approach to many of the common data warehousing activity tasks.

The data warehousing theory is out of scope here, but we could introduce some very basic concepts.

Most of the times we need to prepare data for a reporting/analysis tool without accessing directly the production databases:
- We don’t want to load the server interfering with every day user’s activities
- Normally source data is quite normalized (not ready for efficient reporting queries)
- We may need to do some cleansing and checks
- Many times we have to combine data from different sources (ERP, CRM…) and enrich them

MKST will enable you to transfer data from different sources (local or linked databases) to a data warehouse.

The MKST data warehouse flow consists of a process organized in three phases.
- Upload: transfer data from multiple sources to MKST staging
- Validation: check the uploaded data
- Import: transfer data from the staging area to a DWH

After configuring the staging the dbo.full_process stored procedure will do the job and the dbo.last_process_log will provide you the results.

MKST comes with four sql schemas.
- dbo: basic tables and procedures you should not modify
- ext: tables and procedure you may need to extend or add
- stg: tables that will serve as a destination for the original data
- dwh: views that will serve as a source for the DWH tables

You can download the script and the manual MKST.zip

Update your statistics

2011-03-20T10:09:00.000-07:00

If your database suffers of poor performances, before you try any optimization, you should update all statistics. Otherwise SQL Server will produce suboptimal plans.
Out of there a lot of databases have no maintenance plan simply because no one is in charge of it.

This simple query will generete the script for you.

SELECT

 'UPDATE STATISTICS [' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + ']'

FROM

 sys.tables T

WHERE

 NOT EXISTS

  SELECT TOP 1 1

  FROM

   sys.extended_properties EP

  WHERE

   EP.major_id = T.object_id AND

   EP.name = 'microsoft_database_tools_support'

 ) AND

 T.is_ms_shipped = 0

ORDER BY

Find Missing and Unused indexes with SqlDbAid

2010-09-11T03:24:00.000-07:00

SQL Server's Dynamic Management Views (DMV) give you access to a lot of useful information.

The web is full of good scripts about SQL Server indexes, but if you prefer a friendly interface may be you could just use SqlDbAid application.

Lets create a test case.

Create and fill the following table

CREATE TABLE [dbo].[missing_idx]

 [row_id] [bigint] NOT NULL,

 [object_id] [int] NOT NULL,

 [name] [nvarchar](128) NOT NULL,

 [type] [char](2) NOT NULL,

 CONSTRAINT [PK_missing_idx] PRIMARY KEY CLUSTERED ([row_id])

INSERT INTO dbo.missing_idx

SELECT TOP 20000

 ROW_NUMBER() OVER (ORDER BY O.object_id) row_id,

 O.object_id,

 O.name,

 O.type

FROM

 sys.all_objects O CROSS JOIN sys.all_objects O1

then we could try to extract some data accessing the table using object_id column (no index there).

SELECT

 object_id,

 name,

 type

FROM

 dbo.missing_idx

WHERE

 object_id in (SELECT TOP 1 object_id FROM dbo.missing_idx)

Now run SqlDbAid, connect to your server and select the database where you created the table.

Select "Missing Indexes" from Tools --> Indexes menu.

You should see the following report:

Double click the row to get the create script:

CREATE NONCLUSTERED INDEX [IX_AT_dbo_missing_idx_1870364392] ON [dbo].[missing_idx] ([object_id]) INCLUDE ([name], [type])

To find unused indexes from SqlDbAid select "Indexes Status" from Tools --> Indexes menu.

You should see the following report:

The read_vs_write column is the sum of all reads (scan, seek and lookup) minus the writes.

Look also to the date columns to know when was the last read.

SqlDbAid DBA tool

2010-06-29T13:35:00.000-07:00

Latest version (v2.0.2.0 - 2012-01-30) SqlDbAid.zip

SqlDbAid is an intuitive standalone application that will enable you to easily script database items and data.

SqlDbAid is free for personal and commercial usage.

Main Features

SQL Server 2005+ support
Text search inside code definition with highlitghting
DBA reports (missing indexes, missing foreign key indexes, indexes status, top queries, table MBytes)
One file per object or single file scripts (tables, views, triggers, procedures, functions, indexes...)
Select, Insert and Update scripts
Table data insert script creation
Table/View data export
Basic database compare via sql script generation

Requirements

.Net Framework 2.0+

Screenshots

Screenshot1

Screenshot2

Screenshot3

For questions and suggestions write to Miken.

Table Rows Concatenation

2010-06-01T05:54:00.001-07:00

Whenever you need to concatenate strings stored inside a table column you have several alternatives.

Depending on the specific situation you may try one of the following approaches.

First lets create a test table filled with some sample data.

--test table

CREATE TABLE dbo.string_row

    row_id INT NOT NULL PRIMARY KEY,

    string VARCHAR(50),

    group_id INT NOT NULL

GO

--add two row groups leaving some row_id holes

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(1, 'AA', 1)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(2, 'BB', 1)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(4, 'CC', 1)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(8, 'EE', 2)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(12, 'FF', 2)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(13, 'GG', 2)

When the situation is somewhat "static" we could use variables.

--1 variables
DECLARE @concatenation1 VARCHAR(MAX)

DECLARE @concatenation2 VARCHAR(MAX)

SET @concatenation1 = ''

SET @concatenation2 = ''

SELECT

    @concatenation1 = @concatenation1 +

        CASE group_id WHEN 1 THEN string ELSE '' END,

    @concatenation2 = @concatenation2 +

        CASE group_id WHEN 2 THEN string ELSE '' END

FROM dbo.string_row

ORDER BY row_id

SELECT @concatenation1 full_string, 1 group_id

UNION ALL

SELECT @concatenation2 full_string, 2 group_id

All other cases require one ore more queries.

Iteration using updates on a temporary table.

--2 iteration
CREATE TABLE #TMP

    row_id INT NOT NULL,

    full_string VARCHAR(MAX),

    group_id INT NOT NULL

INSERT INTO #TMP (row_id, full_string, group_id)

SELECT DISTINCT 0, '', group_id FROM dbo.string_row

WHILE @@ROWCOUNT > 0

BEGIN

    UPDATE T SET

        row_id = R.row_id,

        full_string = full_string + R.string

    FROM

        #TMP T

            INNER JOIN

        dbo.string_row R ON

            R.group_id = T.group_id AND

            R.row_id > T.row_id

            LEFT JOIN

        dbo.string_row R1 ON

            R1.group_id = T.group_id AND

            R1.row_id > R.row_id AND

            R1.row_id < T.row_id

    WHERE

        R1.row_id IS NULL

END

SELECT full_string, group_id FROM #TMP

Recursion using common table expressions.

--3 recursion
BEGIN

    WITH ORDROWS (new_row_id, full_string, group_id)

AS

        --remove row_id holes

        SELECT

            ROW_NUMBER()

                OVER (PARTITION BY group_id ORDER BY row_id) new_row_id,

            string full_string, group_id FROM dbo.string_row

),

    CROWS (new_row_id, full_string, group_id)

AS

        --get the last row in each group

        SELECT

            new_row_id, CONVERT(VARCHAR(MAX), full_string) full_string, group_id

        FROM

            ORDROWS R

        WHERE

            NOT EXISTS

                SELECT TOP 1 1

                FROM ORDROWS R1

                WHERE

                    R1.group_id = R.group_id AND

                    R1.new_row_id > R.new_row_id

        UNION ALL

        --add the previous row using the sequence obtained using ORDROWS CTE

        SELECT

            R.new_row_id,

            CONVERT(VARCHAR(MAX), R.full_string + C.full_string) full_string,

            R.group_id

        FROM

            ORDROWS R

                INNER JOIN

            CROWS C ON

                C.group_id = R.group_id AND

                C.new_row_id = R.new_row_id + 1

    SELECT full_string, group_id FROM CROWS WHERE new_row_id = 1

END

SQL Server xml capabilities.

--4 xml
SELECT

        SELECT

            R.string + ''

        FROM

            dbo.string_row R

        WHERE

            R.group_id = G.group_id

        ORDER BY R.row_id

        FOR XML PATH('')

    ) full_string,

    G.group_id

FROM

(SELECT DISTINCT group_id FROM dbo.string_row) G

You could download the complete script.

StringConcatenation.zip

Search a String Inside a Database

2010-05-21T02:24:00.000-07:00

May be you need to know where a string shown by an application you don't know much about has been stored in the related database.

Reverse engeneering purposes, reporting, optimization or simply curiosity.

The following query generates a search script.

SELECT 'USE [' + db_name() + '] --' cmd, -2 rn

UNION ALL

SELECT 'DECLARE @search VARCHAR(100) --' cmd, -1 rn

UNION ALL

SELECT 'SET @search = ''%search this%'' --' cmd, 0 rn

UNION ALL

SELECT

    'IF EXISTS(SELECT TOP 1 1 FROM [' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + '] WITH(NOLOCK) WHERE ' +

    CASE

        WHEN C.collation_name IS NOT NULL THEN '[' + C.name + ']'

        WHEN CT.name IN ('image') THEN 'CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), [' + C.name + ']))'

        ELSE 'CONVERT(VARCHAR(MAX), [' + C.name + '])'

    END +

    ' LIKE @search) PRINT ''[' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + '].[' + C.name + ']'' --' cmd,

    ROW_NUMBER() OVER (ORDER BY SCHEMA_NAME(T.schema_id), T.name, C.name) rn

FROM

    sys.tables T

        INNER JOIN

    sys.columns C ON

        C.object_id = T.object_id

        INNER JOIN

    sys.types CT ON

        CT.user_type_id = C.user_type_id

WHERE

    NOT EXISTS (SELECT TOP 1 1 FROM sys.extended_properties EP WHERE EP.major_id = T.object_id AND EP.name = 'microsoft_database_tools_support') AND

    T.is_ms_shipped = 0 AND

    (C.collation_name IS NOT NULL OR CT.name IN ('image','varbinary','binary','xml')) AND

    C.is_computed = 0

ORDER BY

Run it on the database you want to scan, copy and paste the result in a new window then set the search variable (eventually remove one or both % characters).

USE [MyDatabase] --

DECLARE @search VARCHAR(100) --

SET @search = 'Best%' --

IF EXISTS(SELECT TOP 1 1 FROM [dbo].[Accounts] WITH(NOLOCK) WHERE [AccountName] LIKE @search) PRINT '[dbo].[Accounts].[AccountName]'

...

The results will be printed on the output window.

On large databases you'd better run only a group of search queries then proceed with the next one.

SearchString.zip

Sample Sales Data Warehouse

2010-04-02T04:16:00.000-07:00

Sometimes you may need a simple small data warehouse only for test or learning purposes.

Having this goal in mind I wrote a short script that create a simple star schema:

Products (3 products with price attribute)

Customers (3 customers with city attribute)

Calendar (year-quarter-month with 6 years starting 3 years back)

Sales (random combinations product-customer-date and quantity/value)

Due to the small amount of data, a single query generates all combinations and then filter out some of them using a random number.

You'll obtain a different set of combinations and quantities each time you run the script.

SELECT

    T.CalendarDate,

    T.Customer,

    T.Product,

    T.Quantity,

    T.Quantity * P.ProductPrice Amount

FROM

    dbo.Products P

        INNER JOIN

    (--GENERATE ALL COMBINATIONS

        SELECT

            CalendarDate,

            C.Customer,

            P.Product,

            ROUND(RAND(CHECKSUM(NEWID())) * 9, 0) + 1 Quantity,

            RAND(CHECKSUM(NEWID())) Pick

        FROM

            dbo.Customers C

                CROSS JOIN

            dbo.Products P

                CROSS JOIN

            dbo.Calendar

        WHERE

            CalendarDateName < GETDATE()

    ) T ON

        T.Product = P.Product

WHERE

    T.Pick < .3 --REMOVE SOME COMBINATIONS

The RAND function generates random numbers. To initialize it with different integer values we can get the CHECKSUM of NEWID.

You could use the following script as a starting point for your experiments.

SampleDataWarehouse.zip

Comparing data

2009-11-22T05:11:00.000-08:00

Suppose you need to compare data coming from two tables.

ProductionServer

USE SampleDatabase

CREATE TABLE dbo.SameTable

 ID1 INT NOT NULL,

 ID2 INT NOT NULL,

 COL VARCHAR(50) NOT NULL,

 PRIMARY KEY (ID1, ID2)

INSERT INTO dbo.SameTable VALUES(1, 1, 'A')

INSERT INTO dbo.SameTable VALUES(1, 2, 'B')

INSERT INTO dbo.SameTable VALUES(1, 3, 'C')

TestServer

USE SampleDatabase

CREATE TABLE dbo.SameTable

 ID1 INT NOT NULL,

 ID2 INT NOT NULL,

 COL VARCHAR(50) NOT NULL,

 PRIMARY KEY (ID1, ID2)

INSERT INTO dbo.SameTable VALUES(1, 1, 'A')

INSERT INTO dbo.SameTable VALUES(1, 2, 'D')

INSERT INTO dbo.SameTable VALUES(1, 4, 'E')

From TestServer using the LinkProd linked server pointing to ProductionServer

we could use a full join query to spot the differences:

USE SampleDatabase

SELECT

 A.*,B.*

FROM

 dbo.SameTable A

  FULL JOIN

 LinkProd.SampleDatabase.dbo.SameTable B ON

  B.ID1 = A.ID1 AND

  B.ID2 = A.ID2

WHERE

 A.ID1 IS NULL OR

 B.ID1 IS NULL OR

 A.COL <> B.COL

You should obtain the follwing result:

ID1	ID2	COL	ID1	ID2	COL
1	2	D	1	2	B
NULL	NULL	NULL	1	3	C
1	4	E	NULL	NULL	NULL

Here you'll find the code to automatically create comparing scripts.

CompareData.zip

Reporting Services Rdl Align

2009-10-21T05:09:00.000-07:00

RdlAlign is a small standalone utility that will allow you to change your rdl report grid spacing.

All sizes will be recomputed accordingly and, if needed, converted from cm to inches.

The program creates a new file OriginalReport.rdl.xml

Requirements

- .Net Framework

RdlAlign.zip

Extract MOSS 2007 list info via SQL

2009-09-18T01:23:00.000-07:00

I want to share a short script developed to extract information and data regarding MOSS 2007 lists.

List settings are available quering dbo.Lists view:

SELECT tp_ID, tp_Fields FROM dbo.lists WHERE tp_Title = 'My List Name'

tp_ID list unique identifier

tp_Fields XML fragment containing list fields info

tp_Title list name

Using SQL Server xml capabilities, the script can extract fields info, relationships and other things.

You can also view the list content because the script queries dbo.UserData view.

You only have to set two variables.

SET @LIST_NAME = ''

SET @MODE = 'I' --[I]=Info; [C]=Choice; [F]=Fields info; [R]=FieldRef info; [D]=Data

--Ex. all lists: LIST_NAME = '' and MODE = 'I'

--Ex. info about lists where name contains "abc": LIST_NAME = 'abc' and MODE = 'I'

--Ex. "My List" Fields Info + FieldRef + Data: LIST_NAME = 'My List' and MODE = 'FRD'

Of course you could turn the script into a stored procedure.

MossListInfo.zip

Access SQL objects using the right order

2009-09-17T05:06:00.000-07:00

Sometimes you may want to obtain the correct order to access tables.

You may want, for example, to create a script that empty a group af tables following the order imposed by their foreign key constraints.

In SQL Server, relations between tables are available using sys.foreign_keys system view (parent_object_id, referenced_object_id columns).

Relations between procedures, views and functions are available using sys.sql_dependencies (object_id, referenced_major_id) and mantained by SQL Server using object_id values (SQL Server 2008 provides a new approach).

If you create a table T1 and then a procedure P1 that refers to T1 you'll find in the system view something like:

P1 (object_id), T1 (referenced_major_id).

If you drop and re-create T1 you'll loose this relation until ALTER P1.

The following script provides object depth. It not deals with circular references.

DECLARE @DEPTH INT

DECLARE @COUNT INT

IF OBJECT_ID('tempdb..#OBJ') IS NOT NULL

    DROP TABLE #OBJ

SELECT

    O.object_id,

    0 depth

INTO #OBJ

FROM

    sys.objects O

WHERE

    NOT EXISTS (SELECT TOP 1 1 FROM sys.extended_properties EP WHERE EP.major_id = O.object_id AND EP.name = 'microsoft_database_tools_support') AND

    O.is_ms_shipped = 0 AND

    O.type IN

        'U', --tables

        'P', --procedures

        'V', --views

        'TR', --triggers

        'SN', --synonyms

        'FN','IF','TF' --functions

SET @COUNT = @@ROWCOUNT

SET @DEPTH = 0

WHILE @COUNT > 0 AND @DEPTH <>--stop circular references

BEGIN

    SET @DEPTH = @DEPTH + 1

    UPDATE T1 SET

        DEPTH = @DEPTH

    FROM

        #OBJ T1

            INNER JOIN

            SELECT parent_object_id, referenced_object_id FROM sys.foreign_keys

            UNION

            SELECT object_id, referenced_major_id FROM sys.sql_dependencies

            UNION

            SELECT object_id, parent_id FROM sys.triggers

        ) F ON

            F.parent_object_id = T1.object_id

            INNER JOIN

        #OBJ T2 ON

            T2.object_id = F.referenced_object_id

    WHERE

        T1.object_id <> T2.object_id AND --avoid self references

        T2.depth = @DEPTH-1

    SET @COUNT = @@ROWCOUNT

END

SELECT

    T.depth,

    O.type,

    T.object_id,

    SCHEMA_NAME(O.schema_id) + '.' + O.name obj_name

FROM

    #OBJ T

        INNER JOIN

    sys.objects O ON

        O.object_id = T.object_id

 ORDER BY

    T.DEPTH DESC

You can download the full script.

ObjectDepth.zip

SakScript

2009-08-29T13:31:00.000-07:00

SakScript application has changed its name to SqlDbAid

How to script SQL Server database objects

2009-08-22T15:17:00.000-07:00

Sometimes you may need to script SQL objects using T-Sql instead of rely on SSMS or thirdy part tools.

SQL Server 2005 and 2008 offer you the chance to query their system views to obtain what you need.

Note that you could use a dll from SQL Server SDK, but here we are trying the sql approach.

For objects like procedures, triggers, functions and views you may use the OBJECT_DEFINITION function and something like

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.MyStoredProcedure'))

but combining the properties coming from sys.objects and sys.sql_modules views we will extract all the data we need.

SELECT
CASE

  WHEN O.type = 'V' THEN 'View'

  WHEN O.type = 'P' THEN 'Procedure'

  WHEN O.type = 'TR' THEN 'Trigger'

  WHEN O.type IN ('FN','IF','TF') THEN 'Function'

 END [type],

 SCHEMA_NAME(O.schema_id) obj_schema,

 O.name obj_name,

 CONVERT(VARCHAR, O.modify_date, 120) modify_date,

 SM.definition code

FROM

 sys.objects O

  INNER JOIN

 sys.sql_modules SM ON

  SM.object_id = O.object_id

WHERE

 O.is_ms_shipped = 0 AND

 O.type IN ('P','V','FN','IF','TF','TR')

ORDER BY

 type,

 obj_schema,

 obj_name

Table property definitions are spread across many system views. The most important are sys.tables and sys.columns.

SELECT
SCHEMA_NAME(TB.schema_id) tb_schema,

 TB.name tb_name,

 CL.name cl_name

FROM

 sys.tables TB

  INNER JOIN

 sys.columns CL ON

  CL.object_id = TB.object_id

ORDER BY

 tb_schema,

 tb_name,

 CL.column_id

I don't want to bother you with all the details, so you can download the full script.

ScriptSqlObjects.zip

It allows to generete the code for tables, views, procedures, triggers, functions and synonyms.

CRM 4.0 bulk delete performance degradation

2009-05-05T01:56:00.000-07:00

After investigating the progressive performance degradation during a bulkdelete I think I found the problem.

It's the intence use of table variables and Ad Hoc queries (see dbo.fn_CollectForCascadeDelete CRM function) fills the cache with unuseful data.

To free the cache without restarting the server you can use the following command:
DBCC FREESYSTEMCACHE ('ALL').

Beware this command causes a temporary SQL Server general perfomance degradation, so you should use it to solve this specific problem avoiding it during user activities.

In other words you'd better run it during the bulk delete batch activity.

In a real case deletion starts with 3600 records/second and ends with 12 records/second.

During the bulk delete, after empting the cache, the speed go back to 3600 then degradation starts again.

A solution could be periodically empty the cache only if tempdb contains the bulk delete table variable.

Here is the script to search for the specific CRM variable and run the cache cleancing only when we found it and if it was created at least two minutes ago.

select

 t.[object_id]

from

 tempdb.sys.tables t with(nolock)

  inner join

  select

   [object_id]

  from

   tempdb.sys.columns with(nolock)

  where

   [name] in ('o','t'/*,'r'*/,'p')

  group by

   [object_id]

  having

   count([object_id]) = 3

 ) c on

  c.[object_id] = t.[object_id]

where

 datediff(mi, t.create_date, getdate()) >= 2

if @@rowcount > 0

 DBCC FREESYSTEMCACHE ('ALL')

We could for example include this command in a SQL Job scheduled during the bulk delete every 3 minutes.

Another slowing down cause could be missing foreign key indexes (ex. AsyncOperationId column, WorkFlowLogBase table).