Question of the month February 2017 – What is the fastest way to transfer data between servers?

Assume that you have two identical tables in different servers. The table in production server has 100 million rows and the table in test server is empty and you want to transfer data from production server into test server to do some analysis based on that data. 

What is your preferred approach to transfer data between servers? 

Understanding Single quotes

Often I see that newbies struggle to understand how single quotes work in SQL Server. The problem happens when you try to add text data which has single quote as part of it to a table.

I have seen newbies worrying why the following doesn’t work

SELECT columns from mytable where col ='Lifco's'

When you specify a value which has single quote, you need to double it

SELECT columns from mytable where col ='Lifco''s'

The following may be helpful (Run and see the result)

SELECT '','''','''''','''''''',''''''''''

When you use a static sql and express a value in a single quote then first and last single quotes specify that the value is a string. Then within those single quotes every double single quotes represent a single single quote

When you use a Dynamic sql then first and last single quotes specify that it is a dynamic sql. Then within those single quotes every double single quotes specify that it is a string.Then within those single quotes every four single quotes represent a single single quote

Execute the following code and see the result

EXEC
(
'SELECT 
'''','''''''','''''''''''','''''''''''''''',
'''''''''''''''''''''
)

Fun with GO

In SQL Server, GO is the default Batch Separator. Here is a Fun when you use GO as Stored Procedure name

Create the following stored Procedure with the name GO

CREATE PROCEDURE GO
(
    @I INT
)
AS
    SELECT @I AS number

The procedure is created and let us try to execute with the following statement

GO 2

What we see is

Command(s) completed successfully

Because it becomes a syntax to execute a batch for two times

EXECUTE GO 2 

Result is 2

Now I let you to try running the following statements to understand what happens

Execution 1

[GO] 2

Execution 2

[GO] 2
GO 2

Execution 3

[GO] 2
GO
EXECUTE GO 2
GO

Execution 4

[GO] 2
GO 2
GO 2

Execution 5

[GO] 2
GO
GO

Execution 6

EXECUTE GO 2
EXECUTE GO 2
GO 2

Execution 7

[GO] 2
GO
GO 2
GO 2
EXECUTE GO 2
GO
GO 2
GO 2
GO 2 
Execution 8
GO;

Execution 9

GO;2

Execution 10

GO 2;

DROP IF EXISTS with multiple tables

SQL Server 2016 has introduced new DDL statement DROP IF EXISTS which will drop the table if exists.

In my earlier post on Dropping multiple tables with one DROP statement I have shown the method on how you can use single DROP statement to drop multiple tables

In the similar way, the new DDL also supports dropping multiple tables if they exists

create table #t1(id int);
create table #t2(id int);
create table #t3(id int);

DROP TABLE IF EXISTS #t1, #t2, #t3