-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Forrards Group Database Blog

I`m an SQL Server consultant experienced in areas such as database design, T-SQL advanced programming, database administration and business intelligence. I have a good understanding of a few subject domains, for example, finance, trading, telecoms and insurance. Right now I`m running a small database consultancy and a blog, though I'm also an active stock trader.

Performance and using of VARCHAR boxes as filters in SSRS reports

Very few people have thought about this performance killer. It is all about report parameter of text type.

The initial situation

There was a table with about 45 million rows. Some columns in the table were VARCHAR and should have been filtered. As a result of filtering by 10 different… Read more

0 comments, 1,948 reads

Posted in Forrards Group Database Blog on 22 October 2015

SQL Server issue: switching partitions using the parameter with enabled replication

It’s quite an unpleasant thing that we had to face once. There was a working script, which had been functioning for a long time and did not cause any problems until a need to replicate a few directories… But let’s get into the details step by step: First of all,…

Read more

0 comments, 657 reads

Posted in Forrards Group Database Blog on 14 October 2015

How to create a database on a network share

If for any reason you need to place the database on a network share and by default this option is off you can follow the way described below: A database on a network share is created by running T-sql script, graphic mode will not work in this case. So, first… Read more

0 comments, 313 reads

Posted in Forrards Group Database Blog on 12 October 2015

MS SQL Server 2014 Mirroring – thanks for being alive!

I decided to check if there is still database mirroring option in MS SQL Server 2014

Wow! It works!

MIrroring is still alive in SQL Server 2014

However in the screenshot you see a message that new tables “in memory” can not be used together with mirroring.

It’s worth noting… Read more

0 comments, 436 reads

Posted in Forrards Group Database Blog on 8 October 2015

How to convert to Base64 and backwards via T-SQL

I want to share my way to convert a string to  Base64 and back. Let me note that presented method is not the fastest, but it has one important advantage – it works OOB, without CLR functions. We need a test database and a table to use as an example: Read more

0 comments, 1,428 reads

Posted in Forrards Group Database Blog on 30 September 2015

SQL Server SSIS: ReplaceNull

One of widespread problems in SSIS packages is to replace NULL values with some specific value. Unfortunately, before it was not a simple task as Integration Services didn’t have the operator similar to ISNULL () in T-SQL. To be more exact, there was such an operator, but it works in… Read more

0 comments, 836 reads

Posted in Forrards Group Database Blog on 25 September 2015

SQL Server Enterprise Edition Advanced Scanning

It is widely known that SQL Server Enterprise Edition contains a range of improvements, which under certain conditions allow you to perform operations in a more optimal way in comparison with Standard Edition. One of these improvements is Advanced Scanning, which allows multiple scans to share one physical read from… Read more

0 comments, 682 reads

Posted in Forrards Group Database Blog on 24 September 2015

SQL Server 2014: ONLINE operation on a single partition of partitioned object

Here I continue a series of articles devoted to new options in SQL Server 2014. Today let’s review another useful operation – ONLINE rebuilding some partitions of partitioned objects (tables or indexes).

First let me remind you that some versions of SQL Server (Enterprise, Evaluation, Developer) allow you to perform… Read more

0 comments, 563 reads

Posted in Forrards Group Database Blog on 22 September 2015

Define SQL Server database permissions for development team

The dba routine includes developer’s accounts management. Such as creating sql logins for new developers. There is usually a  standard permissions list and database environment for code writing and testing. Let`s try to simplify the process.

Assume there are two developers.

-- USE master GO CREATE LOGIN [JuniorDeveloper] WITH PASSWORD=N'Qwerty12345'…

Read more

0 comments, 1,131 reads

Posted in Forrards Group Database Blog on 14 September 2015

SQL Server 2014: protection against Database Administrator

Such a topic related to SQL Server 2014 CTP1 may seem a bit strange, but it was chosen not randomly. The questions like “How can I protect my data against the DBA?” appeared on forums regularly.

Until recently this problem was not solved, but thanks to the emergence… Read more

2 comments, 1,172 reads

Posted in Forrards Group Database Blog on 10 September 2015

SQL Server 2014: parallel execution of SELECT INTO statements

In Microsoft SQL Server 2014 new in-memory options are presented. One of them is online transaction processing (OLTP) that complementing the existing business analytics and data storage options to create the most feature-rich solution for in-memory database. SQL Server 2014 also offers new cloud opportunities to simplify the process of… Read more

1 comments, 2,853 reads

Posted in Forrards Group Database Blog on 9 September 2015

Buffer Pool Extension in SQL Server 2014 part 4: benchmark testing for update operations

One of my previous articles was devoted to carrying out a range of tests to compare speed of data read operations using BPE. But in real life we need not only read but also update data, that’s why I decided to check whether BPE has influence on performing update operations.… Read more

0 comments, 1,575 reads

Posted in Forrards Group Database Blog on 4 September 2015

SQL Server 2014 Buffer Pool Extension part 3: system monitoring

In this article I’d like to tell about monitoring tools available in SQL Server 2014 to evaluate the performance of Buffer Pool Extension.

First of all there is a system view sys.dm_os_buffer_pool_extension_configuration that provides information on the configuration of BPE. It shows whether BPE is enabled or not, the location… Read more

0 comments, 855 reads

Posted in Forrards Group Database Blog on 3 September 2015

Monitoring of free space in database files

Monitoring of the size of database files is one of the important DBA tasks and this process should be automated. This article will show you how you can achieve this. Why is it important? Most likely you keep an eye of the size of the database and its files to… Read more

0 comments, 461 reads

Posted in Forrards Group Database Blog on 2 September 2015

CONTEXT_INFO and efficient mass update on a large table with a trigger

Let`s assume we have following task – make changes to the data in a large table that has consistency checker in a trigger. Let`s look at the ways to do it in a most efficient way.

Use test database in a simple recovery mode. Create two tables and fill them… Read more

0 comments, 610 reads

Posted in Forrards Group Database Blog on 1 September 2015

Buffer Pool Extension in SQL Server 2014 part 2: benchmark testing

Let’s take a closer look at how we can speed up the system’s performance using the Buffer Pool Extension. For testing I will use a virtual machine with 4 GB RAM. I created a separate database with one table. The table has 2 columns [id] and [n]. The first column… Read more

1 comments, 1,218 reads

Posted in Forrards Group Database Blog on 31 August 2015

Data corruption in SQL Server 2012 and 2014 when rebuilding indexes online

When I hear the discussion about causes of data corruption in SQL Server, I always mention bugs in the operating system and the software itself. Fortunately, the last mentioned reason is an extremely rare case, but people are bound to make mistakes and SQL Server is also developed by humans.… Read more

0 comments, 1,511 reads

Posted in Forrards Group Database Blog on 17 August 2015

Buffer Pool Extension in SQL Server 2014

I guess many have heard the phrase «640K ought to be enough for anybody», which Bill Gates is mistakenly thought to be the author of. Although RAM is constantly becoming cheaper, still its capacity is always not enough for database server. SQL Server features intelligent caching of frequently requested data,… Read more

3 comments, 1,316 reads

Posted in Forrards Group Database Blog on 17 August 2015

System databases maintenance in SQL Server

Let’s talk about maintenance  of system databases in MS SQL Server. There are a few differences from user databases.

At first let`s remember what system databases we have and what do they stay for.

Master – this database keeps all system-level information of SQL Server instance.

Model – this databases… Read more

0 comments, 1,170 reads

Posted in Forrards Group Database Blog on 12 August 2015

Step by Step Guide to SQL Server AlwaysOn

AlwaysOn technology in MS SQL Server is designed to increase the availability of your database, it is another step toward the fail-safe system. For the 1st time it appeared in MS SQL Server 2012 and was going to replace mirroring in future. But now we see both technologies in MS… Read more

1 comments, 12,925 reads

Posted in Forrards Group Database Blog on 6 August 2015

Older posts