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

Posted on by madhivanan

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

Posted on by madhivanan

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

Posted on by madhivanan

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;