Free SQL database compare tool

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

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

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

 1

Find Missing and Unused indexes with SqlDbAid

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

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

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