SQL SERVER – dbForge Object Search for SQL Server

SSMS is a nice tool for database developers. However, it is far from being ideal, and this is because some routine operations in SSMS are extremely inconvenient to perform.

Very often, developers face the challenge of searching a database for all occurrences of an object, column, variable, or search simultaneously all the objects, in which a search value occurs. If you happen to solve a similar problem, then you must know that this problem is not the most trivial and Ctrl + F will not help here.

Of course, you can write a quick metadata query:

SELECT SCHEMA_NAME(o.[schema_id]), o.name, o.type_desc, m.[definition]
FROM sys.sql_modules m
JOIN sys.objects o ON m.[object_id] = o.[object_id]
WHERE [definition] LIKE '%PersonID%'

However, not all developers know metadata structure … and why waste time writing a query, if you already have an excellent free plug-in that helps to effectively search the wilds of DDL.

Though dbForge Object Search for SQL Server has been released not so long ago, it occupied the place of pride in my gentleman’s set.

This plug-in impresses me with the simplicity of use — type a text in the search box and click Enter:

devsearch01 SQL SERVER   dbForge Object Search for SQL Server

All the search results are displayed in a table that supports filtering. When you select a respective object, its DDL is displayed below… but not as a simple text. The plug-in has a convenient syntax highlighting.

If you need to restrict your search, you can configure the filtering by object type. For example, we can search only within stored procedures and triggers.

devsearch02 SQL SERVER   dbForge Object Search for SQL Server

Additional filters allow you to search much faster.

dbForge Object Search for SQL Server does not cache the information between the searches and directly accesses metadata. For me, it’s definitely a plus, especially when in active development and continuously updating database schema — you don’t need to constantly press Refresh cache to get the proper search results.

If necessary, you can do a search on multiple databases at the same time:

devsearch03 SQL SERVER   dbForge Object Search for SQL Server

The plug-in supports navigation. Simply select the context menu command Find in Database Explorer, and you will automatically jump to the found object:

devsearch04 SQL SERVER   dbForge Object Search for SQL Server

When working with this plug-in, I discovered some pleasant things. For example, previous search queries are saved in the search history:

devsearch05 SQL SERVER   dbForge Object Search for SQL Server

If we talk about the object search, doing it in SQL Server Management Studio is rather inconvenient. Queries dealing with this task are inefficient and require deep knowledge of the SQL Server system objects. By contrast, dbForge Object Search for SQL Server does the task brilliantly.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Dedicated Database Development with SQL Source Control

andrey SQL SERVER   Dedicated Database Development with SQL Source Control

We all make mistake and we all wish that we have not made those mistakes. In the field of the development, there are proper solutions, but in the world of SQL, there are not many solutions. I recently asked the same question to my friend Andrey from Devart and he has provided me a wonderful blog post about how one can do dedicated database development with SQL Source Control.

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server, speaks at SQL Saturdays, contributes to open source projects and writes his own blog: codingsight.com


It is a good practice for database developers to have their own “sandbox” environment, rather than having everyone work in a shared environment. Thus, developers can work separately, unaffected by the changes made by other team members. Once a developer completes and tests a change, they commit the change to VCS and it becomes available to other developers.

In this article, we will discuss how to build such workflow with help of dbForge Source Control for SQL Server.

Database development models

There are two common approaches for teams to develop databases. However, some teams may use combinations of the two.

Dedicated

Developers work with their own copy of the database. The copy might be located on a local PC or on a central server. All developers make changes independently, then test those changes and commit to a source control. As soon as changes are committed, other team members can apply them to their working copies.

Each developer works in their “sandbox”, thus there are no risks to overwrite someone else’s changes.

Shared

Developers share a single copy of the database. All changes are made directly to the database. That is why there is no need to care about the state of local working copy. This approach is not too safe, because one developer may overwrite others’ changes.

Setting up SQL Source Control for dedicated development

  1. Install dbForge Source Control for SQL Server

Download and install the tool. Once it is installed, you will see the Source Control Tasks shortcut menu inside SSMS Object Explorer.

  1. Create version control repository

The tool supports the most popular source control systems: SVN, GIT, Mercurial, TFS and many others. For the demonstration purposes, Visual SVN will be used. You can use one of your favorite source control systems. At this step, you need to set up your source control system and the repository. You can place a database script folder inside the repository or just leave the repository blank. In this particular demo, the repository contains the database script folder. The folder contains DDL scripts for the sales_demo1 database objects.

SVN repo SQL SERVER   Dedicated Database Development with SQL Source Control

  1. Link a database to the source control

To link a database to the source control, you need to create a new database in SSMS. Right-click the database and navigate to the Source Control Tasks shortcut menu. Then, click Link Database to Source Control.

link db to sc SQL SERVER   Dedicated Database Development with SQL Source Control

The next step is to set up the connection to the repository. Click the Source control repository text box and then click Manage.

manage sc repo SQL SERVER   Dedicated Database Development with SQL Source Control

Select a required source control system. It is SVN in our case. Provide the URL to the repository. Click OK.

sc repo prop SQL SERVER   Dedicated Database Development with SQL Source Control

There it is! Note the database icon has changed in the Object Explorer. Also, it contains all required objects.

  1. Get latest

The tool automatically detects that there are some changes in the remote repository. To get the remote changes, select all required checkboxes and click Get Latest.

 get latest SQL SERVER   Dedicated Database Development with SQL Source Control

The process will take a while. Once it is completed, you get the following report:

progress window SQL SERVER   Dedicated Database Development with SQL Source Control

Now, just refresh the sales_demo1 database in SSMS object explorer. You will see that the database contains all required objects.

after get latest SQL SERVER   Dedicated Database Development with SQL Source Control

  1. Working with changes

Assume someone from the team has changed an object in their local sandbox and committed the change into the repository. You need to get the update from the repository. To get remote changes, just refresh the source control tool. The tool automatically detects the remote modifications and provides you a list of all changes. You can see which lines of code have changed. If you agree to the changes, select them and click Get Latest!

remote changes SQL SERVER   Dedicated Database Development with SQL Source Control

All changes will be applied to your local copy of the database.

As you can see, it is very simple to manage database changes with dbForge Source Control for SQL Server. Most database developers, however, relying on a shared database development model. Nevertheless, changes made by other developers can break the code and affect the entire project, causing delays and frustration. Even so, most developers would likely choose the dedicated model.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Best Time to Buy – Devart Christmas Price Fall

Have you missed out the deals you were hoping to grab on Black Friday or Cyber Monday? Good news! You still have time to save. 16 December 2015 — 16 January 2016 is your next big window of opportunity for many software products from Devart.

20% Off Guaranteed

Christmas season is a great time to get 20% off on all Devart licenses until 16 January 2016. You need nothing special. Just go and buy products at the discounted price.

Devart is one of the leading developers of database tools and administration software, ALM solutions, data providers for various database servers, data integration and backup solutions.

SQL Server Tools

dbForge Studio for SQL Server — a comprehensive IDE that incorporates essential tools for working with SQL Server databases: SQL code autocompletion and formatting, database design and synchronization, security management and data reporting.

dbForge Developer Bundle for SQL Server — A massive tools collection including nine separate products for developing SQL databases. dbForge Developer Bundle offers a 50% discount in comparison with purchasing each tool separately.

dbForge Schema Compare for SQL Server — a tried and true instrument that will help save you time and effort when it comes to comparing SQL Server databases.

dbForge Data Compare for SQL Server — a powerful, fast and easy-to-use SQL data comparison tool, with the capability of using native SQL Server backups as a meta-data source.

Data Generator for SQL Server — a powerful GUI tool for a fast generation of large volumes of SQL Server test table data.

dbForge Query Builder for SQL Server — a brand-new solution for quick query creation and extended data management. It allows you to create complex queries, which you could not create before.

SSMS Add-ins

dbForge SQL Complete — a powerful and handy add-in for SQL Server Management Studio and Visual Studio. Its basic free and more advanced commercial editions provide powerful autocompletion and formatting of T-SQL code feature, replacing native Microsoft T-SQL Intellisense.

dbForge Source Control for SQL Server —  a powerful SSMS add-in for managing SQL Server database changes in source control.

dbForge Unit Test for SQL Server — an intuitive and convenient GUI for implementing automated unit testing based on the open-source tSQLt framework in SQL Server Management Studio.

dbForge Data Pump for SQL Server — an SSMS add-in for filling SQL databases with external source data and migrating data between systems.

dbForge Index Manager for SQL Server — a handy SSMS add-in for analyzing the status of SQL indexes and fixing issues with index fragmentation.

I wish you a Merry Christmas and all the best in the New Year! May the Holidays bring you plenty of cheer and joy!

Have a nice shopping!

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix SQL Server Index Fragmentation with dbForge Index Manager

You need to know SQL Server basics to keep database performance at the highest level. This knowledge will also help you to be prepared to any potential problems. When working with files, you may discover that there is not enough free space to store the required data in the file. By default, in such situations, SQL Server locks the file and extends it (it’s called – autogrow).

All autogrow events are stored in SQL Server log:

SELECT
DatabaseName
, [FileName]
, CONVERT(VARCHAR(20), EndTime - StartTime, 114)
,
StartTime
, EndTime
, FileType =
CASE EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END
FROM
(
SELECT pt = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1
) p
CROSS APPLY sys.fn_trace_gettable(pt, DEFAULT)
WHERE EventClass IN (92, 93)
ORDER BY StartTime DESC

Frequent call of autogrow files may significantly reduce productivity and may also lead to the fragmentation of files on the disk. The following settings (that differ from the recommended) may affect this call:

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
GO
CREATE TABLE #temp (
db SYSNAME DEFAULT DB_NAME(),
flname SYSNAME,
size_after_growth DECIMAL(18,2),
size DECIMAL(18,2),
space_used DECIMAL(18,2),
growth INT,
is_percent_growth BIT,
PRIMARY KEY CLUSTERED (db, flname)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
SELECT '
USE ['
+ name + ']
INSERT INTO #temp (flname, size_after_growth, space_used, size, growth, is_percent_growth)
SELECT
name
, CASE WHEN is_percent_growth = 1 THEN size * (1 + (growth / 100.)) ELSE size + growth END * 8. / 1024
, space_used = FILEPROPERTY(name, ''SpaceUsed'') * 8. / 1024
, size = size * 8. / 1024
, CASE WHEN is_percent_growth = 1 THEN growth ELSE growth * 8. / 1024 END
, is_percent_growth
FROM sys.database_files'
FROM sys.databases
WHERE [state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
EXEC sys.sp_executesql @SQL
SELECT
db
, flname
, size_after_growth
, size
, space_used
, CAST(growth AS VARCHAR(10)) + CASE WHEN is_percent_growth = 1 THEN ' %' ELSE ' MB' END
FROM
#temp
WHERE (is_percent_growth = 0 AND growth < 50)
OR (
is_percent_growth = 1 AND growth < 5)
OR (
size - space_used < 20)

If this query will return the 1 MB in the last column for some files, then think about the situation when we need to insert 100 MB of data. Each time SQL Server will block file and increase it by 1 MB, and then paste the data into it. I would advise you to keep enough space for log and database files.

There are also two types of files fragmentation:

Logical fragmentation (also called external fragmentation or extent fragmentation) — the logical order of the pages does not correspond their physical order. As a result, SQL Server increases the number of physical (random) reads from the hard drive, making the read-ahead mechanism less efficient. This directly affects to the query execution time, because random reading from the hard drive is far less efficient comparing to sequential reading.

Internal fragmentation — the data pages in the index contain free space. This lead to an increase in the number of logical reads during the query execution, because the index utilizes more data pages to store data.

To manage index fragmentation issues, SQL Server provides two statements: ALTER INDEX REBUILD / REORGANIZE.

The REBUILD operation creates a new structure for the index. The REORGANIZE operation is more lightweight. It runs through the leaf level of the index, and as it goes, it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings.

Do not ignore high index fragmentation level. You can get the information about the fragmentation by executing the following query:

SELECT
SCHEMA_NAME(o.[schema_id]) AS [schema_name]
, o.name AS parent_name
, i.name
, s.avg_fragmentation_in_percent
, s.avg_page_space_used_in_percent
, i.type_desc
, s.page_count
, size = s.page_count * 8. / 1024
, p.partition_number
, p.[rows]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s
JOIN sys.partitions p ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id
AND s.partition_number = p.partition_number
JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
JOIN sys.objects o ON o.[object_id] = i.[object_id]
WHERE i.is_disabled = 0
AND i.is_hypothetical = 0
AND s.index_level = 0
AND s.page_count > 0
AND i.[type] > 0
AND s.avg_fragmentation_in_percent > 15
AND s.alloc_unit_type_desc = 'IN_ROW_DATA'
AND o.[type] IN ('U', 'V')
ORDER BY s. avg_fragmentation_in_percent DESC

Depending on the level of fragmentation, you can generate scripts to automatically rebuild or reorganize indexes:

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT '
ALTER INDEX ['
+ i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
CASE WHEN s.avg_fragmentation_in_percent > 30
THEN 'REBUILD'
ELSE 'REORGANIZE'
END + ';
'
FROM (
SELECT
s.[object_id]
, s.index_id
, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
WHERE s.page_count > 128 -- > 1 MB
AND s.index_id > 0 -- <> HEAP
AND s.avg_fragmentation_in_percent > 5
GROUP BY s.[object_id], s.index_id
) s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.objects o ON o.[object_id] = s.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
EXEC sys.sp_executesql @SQL

However, it may be too tough task for the novice users. Moreover, I prefer simple solutions. Recently, Devart was announced a new add-in for SSMS – dbForge Index Manager for SQL Server. The new tool will be used to analyze and fix index fragmentation for databases.

Let’s create a test database and populate it with test data. For this, we can use dbForge Data Pump.

USE [master]
GO
IF DB_ID('BigData') IS NOT NULL DROP DATABASE [BigData]
GO
CREATE DATABASE [BigData]
GO
USE [BigData]
GO
CREATE PARTITION FUNCTION [TypeID_PF](tinyint) AS RANGE LEFT FOR VALUES (0x14, 0x96, 0x97, 0xAE, 0xAF)
GO
CREATE PARTITION SCHEME [PD_PS] AS PARTITION [TypeID_PF] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GO
CREATE TABLE [dbo].[ProductData] (
[ProductID] [int] NOT NULL,
[TypeID] [tinyint] NOT NULL,
[Specification] [nvarchar](MAX) NOT NULL,
CONSTRAINT [PK_ProductData] PRIMARY KEY CLUSTERED
(
[ProductID] ASC,
[TypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PD_PS]([TypeID])
)
ON [PD_PS]([TypeID])
GO

Open the Index Manager:

indexde 1 SQL SERVER   Fix SQL Server Index Fragmentation with dbForge Index Manager

The Index Manager will search for fragmented indexes and offer options to fix them:

indexde 2 SQL SERVER   Fix SQL Server Index Fragmentation with dbForge Index Manager

Click Fix to remove index fragmentation. Alternatively, you can automatically generate a script:

USE BigData
GO
ALTER INDEX [PK_ProductData] ON [dbo].[ProductData] REBUILD PARTITION = 1
WITH (SORT_IN_TEMPDB = ON)
GO
ALTER INDEX [PK_ProductData] ON [dbo].[ProductData] REBUILD PARTITION = 2
WITH (SORT_IN_TEMPDB = ON)
GO
ALTER INDEX [PK_ProductData] ON [dbo].[ProductData] REBUILD PARTITION = 3
WITH (SORT_IN_TEMPDB = ON)
GO
ALTER INDEX [PK_ProductData] ON [dbo].[ProductData] REBUILD PARTITION = 4
WITH (SORT_IN_TEMPDB = ON)
GO
ALTER INDEX [PK_ProductData] ON [dbo].[ProductData] REBUILD PARTITION = 6
WITH (SORT_IN_TEMPDB = ON)

You can change the settings, if necessary:

indexde 3 SQL SERVER   Fix SQL Server Index Fragmentation with dbForge Index Manager

Now we can use a free tool — dbForge Event Profiler for SQL Server (as we did in previous post), and see what queries does Index Manager send.

For this, we can have a look at the trace, and see that there is not too much queries:

-- SQL Server version and edition
SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('EngineEdition')
-- list of user databases
SELECT database_id, name
FROM [master].sys.databases
WHERE is_in_standby = 0
AND state_desc = 'ONLINE'
AND source_database_id IS NULL

А query that returns fragmented indexes is quite complex:

DECLARE @db_id INT
SET
@db_id = DB_ID()
SELECT
SCHEMA_NAME(o.[schema_id]) AS [schema_name]
, o.name AS parent_name
, i.name
, s.avg_fragmentation_in_percent
, i.type_desc
, o.[type] AS object_type
, s.page_count
, p.partition_number
, p.[rows]
, ISNULL(lob.is_lob_legacy, 0) AS is_lob_legacy
, ISNULL(lob.is_lob, 0) AS is_lob
, CASE WHEN ds.[type] = 'PS' THEN 1 ELSE 0 END AS is_partitioned
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL) s
JOIN sys.partitions p ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id AND s.partition_number = p.partition_number
JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
LEFT JOIN (
SELECT
c.[object_id]
, index_id = ISNULL(i.index_id, 1)
,
is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END)
,
is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END)
FROM sys.columns c
LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0
WHERE c.system_type_id IN (34, 35, 99)
OR
c.max_length = -1
GROUP BY c.[object_id], i.index_id
) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id
JOIN sys.objects o ON o.[object_id] = i.[object_id]
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE i.[type] IN (1, 2)
AND
i.is_disabled = 0
AND i.is_hypothetical = 0
AND s.index_level = 0
AND s.alloc_unit_type_desc = 'IN_ROW_DATA'
AND o.[type] IN ('U', 'V')

I will tell you what it does. We can get fragmented indexes with help of the DMV (dm_db_index_physical_stats.) We can do this for the entire database (how it was shown earlier) or for specified table.

USE AdventureWorks2012
GO
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Person.Person'), NULL, NULL, NULL)

If the compatibility level for your database is set to 80 (SQL Server 2000), the following query fails:

I would recommend writing such queries as follows:

USE AdventureWorks2012
GO
DECLARE @db_id INT, @obj_id INT
SELECT
@db_id = DB_ID(), @obj_id = OBJECT_ID('Person.Person')
SELECT *
FROM sys.dm_db_index_physical_stats(@db_id, @obj_id, NULL, NULL, NULL)

Query result displays a lot of useful information:

indexde 4 SQL SERVER   Fix SQL Server Index Fragmentation with dbForge Index Manager

As you can see, only IN_ROW_DATA is fragmented.

IN_ROW_DATA – pages that store data columns in fixed-length columns. Variable-length data (no more than 8060 bytes) does not fit the IN_ROW_DATA page is stored as ROW_OVERFLOW_DATA page that has a link to IN_ROW_DATA. The data, which are larger than 8060 bytes, such as varchar(max), varchar(max), XML, TEXT, IMAGE is stored on the LOB_DATA pages.

The more free space on the page, the more internal fragmentation at IN_ROW_DATA pages. If the fragmentation level is high, SQL Server has to read a huge number of pages. This can increase the number of logical reads and decrease performance.

Now back to our query. The part of the query that may seems unclear:

SELECT
c.[object_id]
, index_id = ISNULL(i.index_id, 1)
,
is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END)
,
is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END)
FROM sys.columns c
LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0
WHERE c.system_type_id IN (34, 35, 99)
OR
c.max_length = -1
GROUP BY c.[object_id], i.index_id

It returns information about which tables use LOB columns. Why it is so important? SQL Server 2005 does not support online index rebuild for indexes which contain LOB columns. If you do not specify the ONLINE option, the index is unavailable while rebuilding. Here is an example that demonstrates why the LOB check is important:

IF OBJECT_ID('dbo.test1', 'U') IS NOT NULL DROP TABLE dbo.test1
GO
CREATE TABLE dbo.test1 (ID INT PRIMARY KEY, [Text] NVARCHAR(MAX))
GO
ALTER INDEX ALL ON dbo.test1 REBUILD WITH(ONLINE=ON)
GO

SQL Server 2012 does not have such limitations for all data types (except deprecated IMAGE, TEXT, NTEXT).

IF OBJECT_ID('dbo.test1', 'U') IS NOT NULL DROP TABLE dbo.test1
GO
CREATE TABLE dbo.test1 (ID INT PRIMARY KEY, [Text] NVARCHAR(MAX))
GO
ALTER INDEX ALL ON dbo.test1 REBUILD WITH(ONLINE=ON)
GO
IF OBJECT_ID('dbo.test2', 'U') IS NOT NULL DROP TABLE dbo.test2
GO
CREATE TABLE dbo.test2 (ID INT PRIMARY KEY, [Text] TEXT)
GO
ALTER INDEX ALL ON dbo.test2 REBUILD WITH(ONLINE=ON)
GO

Do not use deprecated data types. You can check it with the following query:

SELECT DISTINCT
OBJECT_SCHEMA_NAME(c.[object_id])
,
OBJECT_NAME(c.[object_id])
FROM sys.columns c
WHERE c.system_type_id IN (34, 35, 99)

I did not discover any problems, while exploring dbForge Index Manager for SQL Server. The tool is stable and performs a great job for me.

indexde 5 SQL SERVER   Fix SQL Server Index Fragmentation with dbForge Index Manager

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Easy Way to Import and Export SQL Server Data

I know from my personal experience that many SQL Server novice users have issues while uploading and downloading data in various formats. In my practice, this kind of tasks arise very often. I would prefer to automate the process for the recurrent tasks, if the file structure is not going to be changed. I can create a SSIS package, deploy it on a server and then schedule one through the SQL Agent.

In practice, clients operate with numerous data formats (excel and xml). In some cases, Import and Export Wizard included in Microsoft SQL Server Management Studio helps greatly. However, I prefer dbForge Data Pump for SQL Server a new SSMS add-in from Devart. The tool allows me to quickly and easily upload and download data in a variety of formats.

Recently, I needed to upload data from a large XML file with quite simple structure:

<users>
<user Email="joe.smith@google.com" FullName="Joe Smith" Title="QA" Company="Area51" City="London" />
</users>

Let’s compare three approaches to resolving the problem: SSIS, XQuery, and Data Pump. We cannot use the SSMS Import and Export Wizard as it does not work with XML.

  1. SSIS

In Integration Service, create a new Data Flow Task:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

On the Data Flow tab, select the XML data source. Then we need to specify the XML file to load data from and generate the XSD schema:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

Since all XML values are stored in the Unicode, I need to convert them to ANSI for the following insertion:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

Then we need to specify where to insert data:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

We need to specify the table to insert data to. In this example, I created a new table.

 SQL SERVER   Easy Way to Import and Export SQL Server Data

Then just press the Execute Results and check the package for errors:

 SQL SERVER   Easy Way to Import and Export SQL Server Data

The SSIS package creation process took 10 minutes. The package ran for 1 minute.

  1. XQuery

Often, writing a query is much faster than creating a SSIS package:

IF OBJECT_ID('dbo.mail', 'U') IS NOT NULL
DROP TABLE dbo.mail
GO
DECLARE @xml XML
SELECT
@xml = BulkColumn
FROM OPENROWSET(BULK 'D:\users.xml', SINGLE_BLOB) x
SELECT
Email = t.c.value('@Email', 'VARCHAR(255)')
,
FullName = t.c.value('@FullName', 'VARCHAR(255)')
,
Title = t.c.value('@Title', 'VARCHAR(255)')
,
Company = t.c.value('@Company', 'VARCHAR(255)')
,
City = t.c.value('@City', 'VARCHAR(255)')
INTO dbo.mail
FROM @xml.nodes('users/user') t(c)

It took me 3 minutes to create the query. However, the execution time exceeded 9 minutes. The reason is that parsing server-side values is quite expensive.

  1. dbForge Data Pump for SQL Server

Now’s the time to try Devart’s Data Pump.

In the Database Explorer shortcut menu, select Import Data.

easytable 07 SQL SERVER   Easy Way to Import and Export SQL Server Data

Then we need to specify the file type and the file path.

easytable 08 SQL SERVER   Easy Way to Import and Export SQL Server Data

Then we can select a table to insert data. The table can be created automatically.

easytable 09 SQL SERVER   Easy Way to Import and Export SQL Server Data

Select the XML tag for parsing:

easytable 10 SQL SERVER   Easy Way to Import and Export SQL Server Data

Now we can select what columns we need (from the selected tag).

easytable 11 SQL SERVER   Easy Way to Import and Export SQL Server Data

Now we need to check the Use bulk insert option to reduce the load on the log file when inserting data.

easytable 12 SQL SERVER   Easy Way to Import and Export SQL Server Data

Click Import and that’s it! The whole process took 2 minutes. This approach does not require any special qualification. The tool allows me to quickly and easily upload data in any formats convenient for my clients.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Practical Tips to Reduce SQL Server Database Table Size – Experts Opinion

I am one of those fortunate people who has friends everywhere in the world. Syrovatchenko Sergey (LinkedIn) is one of my good friends who is an expert on SQL Server and works at Devart. When we met a few days ago in one of the event, we end up discussing about practical tips to reduce database table size. Upon returning home, I found Sergey has sent me an amazing article on this subject. It is beautifully written article and I enjoyed it very much. Let us read what Sergey has to say about this subject in his own words.


In this article, I’d like to elaborate on the subject of boosting productivity while working with database tables. Chances are you already know this from multiple resources on database development process.

experts1 SQL SERVER   Practical Tips to Reduce SQL Server Database Table Size   Experts Opinion

However, the topic seems to become a front-burner issue when there are continuous data growths — tables become too large that leads to the performance loss.

It happens due to an ill-designed database schema that was not originally designed for handling large volumes of data.

To avoid the performance loss in the context of continuous data growth, you should stick to certain rules when designing a database schema.

Rule # 1 — Minimum Redundancy of Data Types

The fundamental unit of SQL Server data storage is the page. The disk space intended for a data file in a database is logically divided into pages numbered contiguously from 0 to n. In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte.

Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages. The more compact data types is used, the less pages for storing that data are required, and as a result, less I/O operations needed.

Introduced in SQL Server, buffer pool significantly improves I/O throughput. The primary purpose of the SQL buffer pool is to reduce database file I/O and improve the response time for data retrieval.

Thus, when compact data types are used, the buffer pool stores larger amount of data on the same amount of pages. As a result, you will not waste the memory and reduce a number of logical operations.

Consider the following example — a table that stores working days of employees.

CREATE TABLE dbo.WorkOut1 (
DateOut DATETIME
, EmployeeID BIGINT
, WorkShiftCD NVARCHAR(10)
,
WorkHours DECIMAL(24,2)
,
CONSTRAINT PK_WorkOut1 PRIMARY KEY (DateOut, EmployeeID)
)

Are the selected data types correct? The most probable answer is no. It is unlikely that an enterprise has (2^63-1) employees. Therefore, the BIGINT is an unsuitable data type in this case.

We can remove this redundancy and estimate the query execution time.

CREATE TABLE dbo.WorkOut2 (
DateOut SMALLDATETIME
, EmployeeID INT
, WorkShiftCD VARCHAR(10)
,
WorkHours DECIMAL(8,2)
,
CONSTRAINT PK_WorkOut2 PRIMARY KEY (DateOut, EmployeeID)
)

The following execution plan demonstrates the cost difference which depends on a row size and expected number of rows.

experts2 SQL SERVER   Practical Tips to Reduce SQL Server Database Table Size   Experts Opinion

The less data you need to retrieve, the faster query will run.

(3492294 row(s) affected)
SQL Server Execution Times:
CPU time = 1919 ms, elapsed time = 33606 ms.
(3492294 row(s) affected)
SQL Server Execution Times:
CPU time = 1420 ms, elapsed time = 29694 ms.

As you can see, the usage of non-redundant data types is a keystone for the best query performance. It also allows reducing the size of problem tables. By the way, you can execute the following query for measuring a table size:

SELECT
table_name = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
, data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(8,4))
FROM sys.objects o
JOIN (
SELECT
p.[object_id]
, total_rows = SUM(p.[rows])
,
total_pages = SUM(a.total_pages)
,
usedpages = SUM(a.used_pages)
,
pages = SUM(
CASE
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0
WHEN a.[type]! = 1 AND p.index_id < 2 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages ELSE 0
END
)
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id]
GROUP BY p.[object_id]
) do ON o.[object_id] = do.[object_id]
WHERE o.[type] = 'U'

For the above-considered tables, the query returns the following results:

table_name           data_size_mb
——————– ——————————-
dbo.WorkOut1         167.2578
dbo.WorkOut2         97.1250

Rule # 2 — Use Database Normalization and Avoid Data Duplication

Recently, I have analyzed a database of a free web service that allows formatting T-SQL code. The server part is quite simple over there and consists of a single table:

CREATE TABLE dbo.format_history (
session_id BIGINT
, format_date DATETIME
, format_options XML
)

Every time when formatting SQL code, the following parameters were saved to the database: current session ID, server time, and the settings that were applied while formatting user’s SQL code.

This data subsequently were used for determining of most popular formatting styles. There were plans to add these styles to SQL Complete default formatting styles.

However, the service popularity rise led to a significant table rows increase, and profiles processing became slow. The settings had the following XML structure:

&lt;FormatProfile&gt;
&lt;FormatOptions&gt;
&lt;PropertyValue Name="Select_SelectList_IndentColumnList"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="Select_SelectList_SingleLineColumns"&gt;false&lt;/PropertyValue&gt;
&lt;PropertyValue Name="Select_SelectList_StackColumns"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="Select_SelectList_StackColumnsMode"&gt;1&lt;/PropertyValue&gt;
&lt;PropertyValue Name="Select_Into_LineBreakBeforeInto"&gt;true&lt;/PropertyValue&gt;
...
&lt;PropertyValue Name="UnionExceptIntersect_LineBreakBeforeUnion"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="UnionExceptIntersect_LineBreakAfterUnion"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="UnionExceptIntersect_IndentKeyword"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="UnionExceptIntersect_IndentSubquery"&gt;false&lt;/PropertyValue&gt;
...
&lt;/FormatOptions&gt;
&lt;/FormatProfile&gt;

450 formatting options in total. Each row takes 33 KB in the table. The daily data growth exceeds 100 MB. As an obvius outcome, the database has been expanding day by day, thus making data analysis yet more complicated .

Surprisingly, the salvation turned out to be quite easy: all unique profiles were placed into a separate table, where a hash was defined for every set of options. As of SQL Server 2008, you can use the sys.fn_repl_hash_binary function for this.

So the DB schema has been normalized:

CREATE TABLE dbo.format_profile (
format_hash BINARY(16) PRIMARY KEY
, format_profile XML NOT NULL
)
CREATE TABLE dbo.format_history (
session_id BIGINT
, format_date SMALLDATETIME
, format_hash BINARY(16) NOT NULL
,
CONSTRAINT PK_format_history PRIMARY KEY CLUSTERED (session_id, format_date)
)

And if the previous query looked like this:

SELECT fh.session_id, fh.format_date, fh.format_options
FROM SQLF.dbo.format_history fh

The new schema required the JOIN usage to retrieve the same data:

SELECT fh.session_id, fh.format_date, fp.format_profile
FROM SQLF_v2.dbo.format_history fh
JOIN SQLF_v2.dbo.format_profile fp ON fh.format_hash = fp.format_hash

If we compare the execution time for two queries, we can hardly see the advantages of the schema changes.

(3090 row(s) affected)
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 4698 ms.
(3090 row(s) affected)
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 4479 ms.

But in this case, the goal was to decrease time for analysis. Before we had to write an intricate query for getting the list of popular formatting profiles:

;WITH cte AS (
SELECT
fh.format_options
, hsh = sys.fn_repl_hash_binary(CAST(fh.format_options AS VARBINARY(MAX)))
,
rn = ROW_NUMBER() OVER (ORDER BY 1/0)
FROM SQLF.dbo.format_history fh
)
SELECT c2.format_options, c1.cnt
FROM (
SELECT TOP (10) hsh, rn = MIN(rn), cnt = COUNT(1)
FROM cte
GROUP BY hsh
ORDER BY cnt DESC
) c1
JOIN cte c2 ON c1.rn = c2.rn
ORDER BY c1.cnt DESC

Now due to the data normalization, we managed to simplify the query:

SELECT
fp.format_profile
, t.cnt
FROM (
SELECT TOP (10)
fh.format_hash
, cnt = COUNT(1)
FROM SQLF_v2.dbo.format_history fh
GROUP BY fh.format_hash
ORDER BY cnt DESC
) t
JOIN SQLF_v2.dbo.format_profile fp ON t.format_hash = fp.format_hash

As well as to decrease the query execution time:

(10 row(s) affected)
SQL Server Execution Times:
CPU time = 2684 ms, elapsed time = 2774 ms.
(10 row(s) affected)
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 379 ms.

In addition, the database size has decreased:

database_name    row_size_mb
—————- —————
SQLF             123.50
SQLF_v2          7.88

To retrieve a file size, the following query can be used:

SELECT
database_name = DB_NAME(database_id)
,
row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files
WHERE database_id IN (DB_ID('SQLF'), DB_ID('SQLF_v2'))
GROUP BY database_id

Hope I managed to demonstrate the importance of data normalization.

Rule # 3 — Be careful while selecting indexed columns.

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from a table or a view. Indexes are stored on pages, thus, the less pages is required to store indexes, the faster search process is. It is extremely important to be careful while selecting clustered indexed columns, because all the clustered index columns are included in every non-clustered index. Due to this fact, a database size can increase dramatically.

Rule # 4 — Use Consolidated Tables.

You do not need to execute a complex query on a large table. Instead, you can execute a simple query on a small table.

For instance, we have the following consolidation query

SELECT
WorkOutID
, CE = SUM(CASE WHEN WorkKeyCD = 'CE' THEN Value END)
,
DE = SUM(CASE WHEN WorkKeyCD = 'DE' THEN Value END)
,
RE = SUM(CASE WHEN WorkKeyCD = 'RE' THEN Value END)
,
FD = SUM(CASE WHEN WorkKeyCD = 'FD' THEN Value END)
,
TR = SUM(CASE WHEN WorkKeyCD = 'TR' THEN Value END)
,
FF = SUM(CASE WHEN WorkKeyCD = 'FF' THEN Value END)
,
PF = SUM(CASE WHEN WorkKeyCD = 'PF' THEN Value END)
,
QW = SUM(CASE WHEN WorkKeyCD = 'QW' THEN Value END)
,
FH = SUM(CASE WHEN WorkKeyCD = 'FH' THEN Value END)
,
UH = SUM(CASE WHEN WorkKeyCD = 'UH' THEN Value END)
,
NU = SUM(CASE WHEN WorkKeyCD = 'NU' THEN Value END)
,
CS = SUM(CASE WHEN WorkKeyCD = 'CS' THEN Value END)
FROM dbo.WorkOutFactor
WHERE Value > 0
GROUP BY WorkOutID

If there is no need to often change the table data, we can create a separate table

SELECT *
FROM dbo.WorkOutFactorCache

The data retrieval from such consolidated table will be much faster:

(185916 row(s) affected)
SQL Server Execution Times:
CPU time = 3448 ms, elapsed time = 3116 ms.
(185916 row(s) affected)
SQL Server Execution Times:
CPU time = 1410 ms, elapsed time = 1202 ms.

Rule # 5 — Every rule has an exception

I’ve shown a couple of examples that demonstrated how to eliminate redundant data types and shorten queries execution time. But it does not always happen.

For instance, the BIT data type has a peculiarity —  SQL Server optimizes the storage of such columns group on a disk. If a table contains 8 (or less) columns of the BIT type, they are stored in the page as 1 byte. And if the table contains 16 columns of the BIT type, they are stored in the page as 2 bytes etc. The good news is that the table will take up little space and reduce disc I/O.

The bad news is that an implicit decoding will take place while retrieving data,  and the process is very demanding in terms of CPU resources.

Here is the example. Assume we have 3 identical tables containing information about employees work schedule (31 + 2 PK columns).  The only difference between tables is the data type for consolidated values (1– presence, 2 – absence)

SELECT * FROM dbo.E_51_INT
SELECT * FROM dbo.E_52_TINYINT
SELECT * FROM dbo.E_53_BIT

When using less redundant data types, the table size decreases considerably (especially the last table)

table_name           data_size_mb
——————– ————–
dbo.E31_INT          150.2578
dbo.E32_TINYINT      50.4141
dbo.E33_BIT          24.1953

However, there is no significant speed gain from using the BIT type

(1000000 row(s) affected)
Table ‘E31_INT’. Scan count 1, logical reads 19296, physical reads 1, read-ahead reads 19260, …
SQL Server Execution Times:
CPU time = 1607 ms,  elapsed time = 19962 ms.
(1000000 row(s) affected)
Table ‘E32_TINYINT’. Scan count 1, logical reads 6471, physical reads 1, read-ahead reads 6477, …
SQL Server Execution Times:
CPU time = 1029 ms,  elapsed time = 16533 ms.
(1000000 row(s) affected)
Table ‘E33_BIT’. Scan count 1, logical reads 3109, physical reads 1, read-ahead reads 3096, …
SQL Server Execution Times:
CPU time = 1820 ms,  elapsed time = 17121 ms.

But the execution plan will show the opposite.

experts3 SQL SERVER   Practical Tips to Reduce SQL Server Database Table Size   Experts Opinion

So the negative effect from the decoding will not appear if a table contains less than 8 BIT columns. One must note that the BIT data type is hardly used in SQL Server metadata. More often the BINARY data type is used, however it requires manual manipulations for obtaining  specific values.

Rule # 6 — Delete data that no longer required.

SQL Server supports a performance optimization mechanism called read-ahead. This mechanizm anticipates the data and index pages needed to fulfill a query execution plan and brings pages into the buffer cache before they are actually used by the query.

So if the table contains a lot of needless data, it may lead to unnecessary disk I/O. Besides, getting rid of needless data allows you to reduce the number of logical operations while reading from the Buffer Pool.

In conclusion, my advice is to be extremely careful while selecting data types for columns and try predicting future data loads.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Looking Inside SQL Complete – Advantages of Intellisense Features

Recently SQL Complete add-in was updated, which I use when writing SQL queries in SSMS. In the product history I saw that a several features were added, such as code refactoring or automatic semicolon insertion. However, in this post I do not want to talk about them. I have noticed a new logic for metadata queries, which significantly accelerated my work with database objects. So I decided to look inside SQL Complete… to understand how it works and what changed…

We can use the free dbForge Event Profiler for SQL Server tool to see what queries SQL Complete sends. We can apply filter to eliminate custom queries from the tracing.

sqlcomp 1 SQL SERVER   Looking Inside SQL Complete   Advantages of Intellisense Features

When we first open an SQL document in SSMS, we get the following trace.

sqlcomp 2 SQL SERVER   Looking Inside SQL Complete   Advantages of Intellisense Features

I wrote in comments what each of these queries return.


-- 1. instance version

SELECT SERVERPROPERTY('EngineEdition'), PATINDEX('%64%', CONVERT(VARCHAR, SERVERPROPERTY('Edition')))

GO

-- 2. default schema (dbo by default)

SELECT ISNULL(NULLIF(SCHEMA_NAME(), 'guest'), 'dbo')

GO

SELECT ORIGINAL_LOGIN()

GO

-- 3. don’t know why SQL Complete needs a default backup path

DECLARE @dir NVARCHAR(4000)

IF 1 = ISNULL(CAST(SERVERPROPERTY('IsLocalDB') AS BIT), 0)

SELECT @dir=CAST(SERVERPROPERTY('instancedefaultdatapath') AS NVARCHAR(512))

ELSE

EXEC [master].dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 'BackupDirectory', @dir OUTPUT, 'no_output'

SELECT @dir

GO

-- 4. available databases

SELECT

[dbid]

, name

, CASE WHEN [dbid] >= 1 AND [dbid] <= 4 THEN 1 ELSE 0 END AS is_fixed_database

, CASE WHEN [status] & 32768 != 0 THEN 0 ELSE CONVERT(BIT, [status] & (32 | 64 | 128 | 256 | 512)) END AS is_unavailable

FROM [master].dbo.sysdatabases WITH(NOLOCK)

ORDER BY name;

GO

-- 5. system languages

SELECT alias

FROM [master].sys.syslanguages WITH(NOLOCK)

ORDER BY alias;

GO

-- 6. system collations (SQL_Latin1_General_CP1_CI_AI, ....)

SELECT name

FROM ::fn_helpcollations()

ORDER BY name;

GO

-- 7. linked servers

SELECT srvname AS name

FROM [master].dbo.sysservers

WHERE srvid != 0

ORDER BY srvid;

GO

-- 8. server triggers

SELECT

t.name

, t.[object_id]

, CASE t.[type] WHEN 'TR' THEN 1 ELSE 0 END AS is_sql_trigger

, CASE WHEN ssm.[definition] IS NULL THEN 1 ELSE 0 END AS is_encrypted

FROM [master].sys.server_triggers t WITH(NOLOCK)

LEFT JOIN [master].sys.server_sql_modules ssm WITH(NOLOCK) ON ssm.[object_id] = t.[object_id]

WHERE is_ms_shipped = 0;

GO

-- 9. system objects (like sys.views, sys. ...)

SELECT

o.[object_id] AS id

, o.name

, o.[type]

, o.[schema_id]

FROM sys.system_objects o WITH(NOLOCK)

WHERE o.[type] in ('U', 'V', 'IF', 'TF', 'FT', 'FN', 'AF', 'FS', 'P', 'RF', 'PC', 'X')

ORDER BY o.[object_id];

GO

-- 10. extended properties for current database

SELECT value

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 0 AND name = 'MS_Description';

GO

-- 11. available schemas

SELECT

s.[schema_id]

, s.name

, CASE WHEN s.[schema_id] BETWEEN 16384 AND 16399 OR s.name IN ('guest', 'INFORMATION_SCHEMA', 'sys')

THEN 1

ELSE 0

END AS is_fixed_role

, CASE WHEN ISNULL(NULLIF(SCHEMA_NAME(), 'guest'), 'dbo') = s.name

THEN 1

ELSE 0

END AS is_default_schema

, ep.value AS [description]

FROM sys.schemas s WITH(NOLOCK)

LEFT JOIN (

SELECT value, major_id

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 3 AND name = 'MS_Description'

) ep ON s.[schema_id] = ep.major_id;

GO

-- 12. user objects (tables, views, procedures, triggers, etc)

SELECT

o.[object_id] AS id

, o.name

, o.[type]

, o.[schema_id]

, o.is_ms_shipped AS is_system

, COALESCE(OBJECTPROPERTY(o.[object_id], 'IsEncrypted'), 0) AS is_encrypted

, ep.value AS [description]

FROM sys.objects o WITH(NOLOCK)

LEFT JOIN (

SELECT value, major_id, minor_id

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 1 AND name = 'MS_Description'

) ep ON o.[object_id] = ep.major_id AND ep.minor_id = 0

WHERE o.[type] in ('U', 'V', 'IF', 'TF', 'FT', 'FN', 'AF', 'FS', 'P', 'RF', 'PC', 'X')

ORDER BY o.[object_id];

GO

-- 13. foreign keys between tables

SELECT

fk.parent_object_id

, fk.[object_id]

, fk.name

, o.name AS referenced_table_name

, SCHEMA_NAME(o.[schema_id]) AS referenced_table_schema

FROM sys.foreign_keys fk WITH(NOLOCK)

JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = fk.referenced_object_id

WHERE o.[type] = 'U'

ORDER BY

fk.parent_object_id

, fk.[object_id];

GO

-- 14. columns for foreign keys

SELECT

fc.parent_object_id as owner_object_id

, fc.constraint_object_id AS constraint_id

, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS column_name

, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name

FROM sys.foreign_key_columns AS fc WITH (NOLOCK)

ORDER BY

fc.parent_object_id

, fc.constraint_object_id

, fc.constraint_column_id;

GO

-- 15. user types

SELECT

t.name

, t.[schema_id]

, t.is_assembly_type AS is_clr

, st.name AS base_type

FROM sys.types t WITH(NOLOCK)

LEFT JOIN sys.types st WITH(NOLOCK) ON st.is_user_defined = 0

AND st.is_assembly_type = 0

AND st.is_table_type = 0

AND st.user_type_id = st.system_type_id

AND st.system_type_id = t.system_type_id

WHERE t.is_user_defined = 1 AND t.is_table_type = 0

ORDER BY t.user_type_id;

GO

-- 16. table types

SELECT

tt.type_table_object_id AS id

, t.name

, t.[schema_id]

FROM sys.types t WITH(NOLOCK)

LEFT JOIN sys.table_types tt WITH(NOLOCK) ON t.user_type_id = tt.user_type_id

WHERE t.is_user_defined = 1

AND t.is_table_type = 1

ORDER BY id;

GO

-- 17. database triggers

SELECT

t.name

, t.[object_id]

, o.[schema_id]

, CASE t.type WHEN 'TR' THEN 1 ELSE 0 END as is_sql_trigger

, COALESCE(OBJECTPROPERTY(t.[object_id], 'IsEncrypted'), 0) AS is_encrypted

, o.name AS parent_name

FROM sys.triggers t WITH(NOLOCK)

LEFT JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = t.parent_id

WHERE t.is_ms_shipped = 0 AND t.parent_class = 1

ORDER BY

o.[schema_id]

, t.[object_id];

GO

-- 18. synonyms

SELECT

s.[object_id]

, s.name

, s.[schema_id]

, s.base_object_name

, OBJECTPROPERTYEX(s.[object_id], N'BaseType') AS base_object_type

, ep.value AS [description]

FROM sys.synonyms s WITH(NOLOCK)

LEFT JOIN (

SELECT value, major_id, minor_id

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 1 AND name = 'MS_Description'

) ep ON s.[object_id] = ep.major_id AND ep.minor_id = 0;

GO

-- 19. xml collections

SELECT c.name AS name, c.[schema_id]

FROM sys.xml_schema_collections c WITH(NOLOCK)

WHERE c.[schema_id] <> 4

ORDER BY c.xml_collection_id;

GO

Then I switched to another database and noticed that only a part of queries (10-19) has been executed. This is a way of optimization, when we retrieve names of system objects only once. You need to understand that names of system objects do not change between databases. However, the data returned by these objects is subject to change.

Another interesting optimization is hidden in the settings. If such option «Detect changes on a server…»

sqlcomp 3 SQL SERVER   Looking Inside SQL Complete   Advantages of Intellisense Features

If you turn it on, then while updating the custom objects prompt, the following query is executed:

</pre>
SELECT

[type]

, MAX(modify_date) AS max_date

, COUNT(1) AS num

FROM sys.objects WITH(NOLOCK)

WHERE [type] IN ('U', 'V', 'IF', 'TF', 'FT', 'FN', 'AF', 'FS', 'P', 'RF', 'PC', 'X', 'SN', 'TR', 'TA')

GROUP BY [type];
<pre>

The optimization goal is to define what types of objects have changed since the last update of metadata and get changes only for changed types.

When profiling, I noticed another interesting optimization. When writing code, additional information about the objects is loaded when they are selected. For example, we choose a table.

sqlcomp 4 SQL SERVER   Looking Inside SQL Complete   Advantages of Intellisense Features

And we have information about this table immediately:

</pre>
-- table/view columns

SELECT

c.name

, c.[object_id] AS parent_id

, c.column_id

, c.is_rowguidcol

, c.is_identity

, c.is_nullable

, c.is_computed

, CASE WHEN c.default_object_id <> 0 THEN 1 ELSE 0 END AS default_exist

, c.[precision]

, c.scale

, CAST(

CASE WHEN t.base_type_name IN ('nchar', 'nvarchar') AND c.max_length <> -1

THEN c.max_length / 2

ELSE c.max_length

END AS INT) AS [length]

, t.name AS [type_name]

, t.base_type_name

, t.is_user_defined

, t.type_schema_name

, ep.value AS [description]

FROM sys.all_columns c WITH(NOLOCK)

LEFT JOIN (

SELECT

*

, SCHEMA_NAME([schema_id]) AS type_schema_name

, COALESCE(TYPE_NAME(system_type_id), name) as base_type_name

FROM sys.types WITH(NOLOCK)

) t ON c.user_type_id = t.user_type_id

LEFT JOIN (

SELECT value, major_id, minor_id

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 1 AND name = 'MS_Description'

) ep ON ep.major_id = c.[object_id] AND ep.minor_id = c.column_id

WHERE c.[object_id] = {0}

ORDER BY c.column_id;

GO

-- index for selected table/view

SELECT

i.[object_id]

, i.index_id

, i.name

, i.is_unique_constraint

, i.is_primary_key

, i.[type]

FROM sys.indexes i WITH(NOLOCK)

WHERE i.[object_id] = {0}

AND i.index_id > 0

AND i.is_hypothetical = 0

ORDER BY i.index_id;

GO

-- index columns for selected table/view

SELECT

ic.[object_id]

, ic.index_id

, c.name

FROM sys.index_columns ic WITH(NOLOCK)

JOIN sys.columns c WITH(NOLOCK) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id

WHERE ic.[object_id] = {0}

AND ic.index_id > 0

ORDER BY

ic.index_id

, ic.index_column_id;

GO
<pre>

This same approach works while prompting another database objects (e.g. stored routines, functions and synonyms).

The described optimization improves usability when working. You do not need to wait a second to write a query.

In the future, I plan to continue to look under the hood of other SSMS add-ins. It is more interesting to work, when you know how it works.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Generating Meaningful Test Data with dbForge Data Generator for SQL Server

Recently I faced with necessity of generating meaningful SQL data for testing purposes. During my search for a proper application for the task, I found out that Devart, a recognized developer of database solutions, released dbForge Data Generator for SQL Server, a GUI tool for fast generation of meaningful SQL data for development and testing purposes. Devart stated that with the tool, I will be able to:

  • Populate SQL Server tables with millions of rows of test data
  • Generate meaningful data that looks just like real data
  • Use 100+ predefined generators with sensible configuration options
  • Customize built-in generators
  • Create user-defined generators
  • Preview generation results
  • Save data population script for future reference
  • Automate data generation with the command-line interface

It sounded great, so I decided to give a quick test for the tool. Moreover, Devart offers a 30-day free trial for the most of its products, including Data Generator for SQL Server.

The first thing I noticed after running SQL Data Generator was clear user-friendly interface. The start page of the tool includes quick access to the main features and allows to quickly run recent projects, if any.

dbforge 1 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

After running new data generation, I was prompted to select connection and database as well as to set default data generation options. I decided to generate meaningful data for few columns of the AdventrureWokrsTest database.

dbforge 2 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

On the Options page, I selected to generate 2000 rows, and cleared the Include Null values option.

dbforge 3 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

After that, Data Generator showed the generation document. I selected the following columns of the Person.Contact table for data population:

  • ContactID
  • Title
  • First Name
  • LatName
  • EmailAddress

Now, the time has come for selecting generators for each column. In dbForge Data Generator for SQL Server, all generators are split into several categories for easier look-up.

dbforge 4 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

I was really impressed with the collection of generators the application offers. The tool automatically detects and offers appropriate generators for a given column.  Also, Data Generator for SQL Server includes multiple ways to make generated data more customized. For instance, you can:

  • Set the range of generated values for different SQL data types
  • Define the percentage of NULLs to be generated
  • Set the value distribution mode
  • Immediately preview data that will be generated

Data Generator suggested the following generators:

  • IDs for the ContactID Additionally, I selected the Random by timestamp value distribution mode for getting more complex numbers.
  • Honirific Prefix (Title) for the Title column
  • First Name (male) for the FirstName column
  • Last Name for the LastName column
  • Email for the EmailAddress This generator is based on the following regular expression \c{3,8}(\d{0,3}|.\c{3,10})@nowhere.com that can be customized right in the Fill settings section of Column generation settings. For instance, you can modify the regular expression, so that the domain name would be “mailservice.com” instead of “nowhere.com”.

dbforge 5 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

Data Generator also allows to modify existing generators and create your own ones, so in future you won’t need to modify the same generator over and over again. I opened the Email.xml file, that is located at C:\Users\Public\Documents\Devart\dbForge Data Generator for SQL Server\Data Generators,  modified regular expression so that the domain name would consist of combination of letters, updated description, name, and saved the file as MyEmail.xml:

dbforge 6 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

The following screenshot shows the data generation document with the result of my manipulations:

I was quite satisfied with the results showed in Preview, so I was ready to populate the target database with data. Data Generator offers several ways to do that, namely:

  • Open the data population script in the internal editor
  • Save the SQL script to a file
  • Run the script automatically

dbforge 7 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

I chose the first option and in few seconds, I got the following population script:

dbforge 8 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

To sum up, I would like to say that the tool has done a fantastic job for me. In my opinion, the most strong points of Devart’s Data Generator are:

  1. Simple work-flow in a convenient interface. With dbForge Data Generator, the process takes just couple of minutes and basically, it is simplified to three simple steps:
  • Selecting required tables
  • Selecting and customizing a required generator
  • Populating tables with test data
  1. Impressive collection of generators. The tool includes over 100 generators that allow to populate tables with realistic-looking test data.
  2. Flexible Customization Options. Developers put really much effort for making data more customized and suiting specific needs.

I strongly recommend to give dbForge Data Generator for SQL Server a try and to evaluate its functionality on your own by downloading a 30-day free trial.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Using Power of Web to Query Databases / Sources With Skyvia

Web technologies are moving at a pace that we can hardly keep up in pace. More and more of investments are going on in the space of web that even database developers like me find it interesting. In the same context, recently I was exploring on Skyvia (SQL SERVER – Integrate Your Data with Skyvia – Cloud ETL Solution) from ETL to how one might query and was surprised to see how some of these can be made useful.

This blog stems from a simple idea that was in my mind for a long time and I was struggling for a suitable solution till I saw what was available here. Think that you are a DBA and are on a vacation to a far-away land. You didn’t take your laptop along because you wanted to really enjoy your vacation times. Everything looks normal till you get an SMS from your management that something is wrong on one of your servers and they need 10-15 mins of your time to quickly resolve. Now with no laptop and all the cool things that you would have normally installed on your machine, how can you help your management with a few query support? Is it even possible? Assume you get to an internet kiosk and you want to do some amount of troubleshooting. How will you do? Well, there are a number of solutions.

One of the many available including the use of Skyvia to do query on your servers once you have done the simple step setup. In this example, I have used an SQL Server 2014 version available on a Cloud provider so that we can see what is going wrong.

Let us start by connecting to Skyvia and setting up our connections.

 Using Power of Web to Query Databases / Sources With Skyvia

In my example, I am going to use the “SQL Server” source and will give all the credentials. Once done and tested, we must see this in our connections tab.

 Using Power of Web to Query Databases / Sources With Skyvia

Once the connection setting seem to be in place, we are all set to run our queries on the backend. We can then head to the Query tab as shown below:

 Using Power of Web to Query Databases / Sources With Skyvia

At this stage, I just linked the Query window to my connection that we created in our previous step. This started to list our all the tables that were available on my server. This was really a cool moment because I was able to work on my database in less than 5 mins via the web.

 Using Power of Web to Query Databases / Sources With Skyvia

Obviously based on the permissions that I used to connect, the tables and objects are displayed. If there are 100’s of tables to work, then we can surely use the search to find the objects.

The next stage or table is our awesome query window. It is here that we can type in any SQL-92 query that needs to be fired against our database. Since I was wanting to know what was happening in my SQL Server system and what operators were functioning of few long running query, I used a DMV from SQL Server 2014 to check if it works. For my surprise, I was not disappointed.

 Using Power of Web to Query Databases / Sources With Skyvia

This is the query result output based on the query that I was running in the backend.

SELECT
node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS FLOAT)/SUM(estimate_row_count)
FROM sys.dm_exec_query_profiles
GROUP BY node_id,physical_operator_name
ORDER BY node_id;

To know more about sys.dm_exec_query_profiles you can read from MSDN. I also talk about this DMV in my course at Pluralsight – SQL Server 2014 Administration New Features. The course is exhaustive in understanding some of these new capabilities of SQL 2014.

Coming back to our query, based on the output I was able to find out what was the query that was running behind the scene. As you can see this is a Dummy query without any filters or restriction on the columns selected. Hence we are seeing a number of Clustered Index Scan in our query output via the web.

SELECT      *
FROM DimProduct
INNER JOIN DimProductSubcategory
ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
INNER JOIN FactInternetSales
ON DimProduct.ProductKey = FactInternetSales.ProductKey

Once the query was identified, you can start looking at what is firing up the query and take corrective actions. Any normal SQL syntax can be fired like this via the web. It is simple and quite powerful to say the least.

The results from Skyvia is powerful that you can also use them to export to CSV format for later offline consumption. As part of simple testing, I also fired few DDL statements and was surprised to have almost everything I do via SQL Server Management Script window here via the web, anytime and anywhere I like.

And another thing I’d like to mention is that Skyvia Query is not just for SQL Server and other databases. Skyvia Query supports SQL for cloud CRMs, such as Salesforce, Dynamics CRM, etc, and allows using SELECT, INSERT, UPDATE and  DELETE statements against them. This enables admins, knowing SQL, work with cloud data in the same way as with SQL Server data.

Currently Skyvia is free for use. You can find more information on how to use their online SQL editor on the following page: Online SQL Query Editor for Cloud and Relational Data.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Collect and Analyze SQL Server Data Efficiently

dbForge Event Profiler is one of the most useful SQL Server “build-in” tools. The Profiler records data associated with various SQL Server events for further analysis. This data is stored in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose SQL Server relates problems. The tool allows you to view communications between a client and SQL Server, and gives you an insight into its internal performance. To take full advantage of its potential, download dbForge Event Profiler for SQL Server for free now.

The tool offers a large spectrum of features that can assist you in:

  • analyzing trace results by grouping or aggregating them;
  • auditing user activity;
  • creating your own custom traces and save them for future use;
  • debugging T-SQL code and stored procedures;
  • executing quality assurance check;
  • identifying performance-related problems with front-end applications, queries, T-SQL, transactions, and so forth;
  • performing stress testing;
  • performing query analysis of execution plans;
  • viewing SQL Server performance when interacting with a client.

Essentially, the Event Profiler is designed to quickly and efficiently track down and fix many SQL Server related problems, such as poorly-performing queries, locking and blocking, excessive table/index scanning, and a lot more. For example, you can monitor the execution of a stored procedure to see whether it hampers SQL Server performance.

Using the Profiler, you can monitor the events that you are interested in. For example, you may want to capture events from a specific user or a given database. Smart filters allow you to collect only the events that you want, filtering out those of no interest. This reduces the amount of data that is stored in your trace.

dbForge Event Profiler provides a rich graphical user interface that can be used to create, analyze, and replay trace results. As the trace data is being collected, you can stop or pause the trace at a certain point and store the trace results to a physical file on a local hard disc. The saved SQL Server Profiler document has the “.*ssp” extension. This file may then be viewed to analyze data captured, share it with others, or compare the trace results to traces performed later.

Powerful Tool in Action

Below is an example of how you create a new trace using dbForge Event Profiler.

To create a new trace, you follow these steps:

  1. On the Start page, click Profile Server Events. The Profile Server Events wizard appears.
  2. Specify the connection.
  3. Optionally, select a profiling template, modify the trace file settings and the data storage settings. Click Next.

devarteventprofiler1 SQL SERVER   Collect and Analyze SQL Server Data Efficiently

  1. On the Events to Capture page, select the events you want to capture. Click Next.
  2. Optionally, on the Actions page, select the actions you want to capture in the current events session. Click Next.
  3. Optionally, on the Event Filters page, specify filter options to limit the tracing data.
  4. Click Execute. The Trace will start and the Server Event Profiler Document opens.

devarteventprofiler2 SQL SERVER   Collect and Analyze SQL Server Data Efficiently

Once you have collected enough data, stop the trace by clicking the Stop Trace button on the toolbar. At this point, you may review the collected data or save it to a file for future use.

Now that you have learned about many advantages of this smart tool, you can start mastering and making practical use of the dbForge Event Profiler for SQL Server by downloading it now for FREE.

Reference: Pinal Dave (http://blog.sqlauthority.com)