Microsoft Technologies

Tuesday, August 13, 2013

Bulk Copy Program (BCP) Utility to Import and Export Data in SQL Server

Bulk Copy Program (BCP) is a powerful command line utility to import and export data in SQL Server database. If you do not have much knowledge about database commands then also you can use it easily. BCP utility is very fast in operation which can save lots of time for processing as compared to other method of bulk import and export of data. We will see how to use this utility and different useful switches involved in it.

First of all open windows command prompt and type bcp and press enter button where you can see different arguments which you can use for copying data to and from SQL Server instance and database. [refer Figure-1]

Figure 1: BCP Arguments

C:\Windows\system32>bcp
usage: 
bcp {dbtable | query} {in | out | queryout | format} datafile 
 [-m maxerrors]            [-f formatfile]          [-e errfile]
 [-F firstrow]             [-L lastrow]             [-b batchsize]
 [-n native type]          [-c character type]      [-w wide character type] 
 [-N keep non-text native] [-V file format version] [-q quoted identifier]
 [-C code page specifier]  [-t field terminator]    [-r row terminator]
 [-i inputfile]            [-o outfile]             [-a packetsize]
 [-S server name]          [-U username]            [-P password]
 [-T trusted connection]   [-v version]             [-R regional enable]
 [-k keep null values]     [-E keep identity values] [-h "load hints"] 
 [-x generate xml format file] [-d database name]   [-K application intent]

First line shows syntax for BCP.

bcp {dbtable | query} {in | out | queryout | format} datafile

{dbtable | query} – represents database table name to use as a source.
{in | out | queryout | format} – this arguments represents if you want to import data or export data and type of format file.
datafile – represents the full path of data file been used.

Now second part represents different arguments which can be used with bulk copy program (BCP) operations as shown in Figure-1.

Here, to understand it better, we will take few examples.

bcp AdventureWorks2012.dbo.tbl_Person out C:\BCPDemo\tbl_Person.txt -T -c

As shown above, we are copying data from database table to file on disk. AdventureWorks2012 is the name of database, dbo is schema name and tbl_Person is the name of the table which we are exporting to data file on disk. -T is switch used for trusted connections and -c is used for character data format. As you can see we have used out option which is normally used to export data.

Figure 2: Export Data with Trusted Connection

Figure shows 106 rows copied in 1 ms clock time with an average of 106000 rows per second, you can actually imagine how fast this utility is performing operations.

Below is a scenario of mixed mode authentication where you have to specify user name and server name to export data. -Usa is username and -SManzoor is server name, once you click enter, it will prompt you to enter password for user sa, you can also specify the password by switch -P (e.g. -P123456). After successful login it will copy data to the file. [refer Figure-3]

bcp AdventureWorks2012.dbo.tbl_Person out C:\BCPDemo\tbl_Person.txt -c -Usa -SManzoor

Copying Data with Mixed Mode Authentication

Figure 3: Export Data with Mixed Mode Authentication

If you are passing password along with other switches then it will directly perform the operation without prompting for it as shown below.

Figure 4: Export Data with Mixed Mode without prompting for Password

bcp AdventureWorks2012.dbo.tbl_Person out C:\BCPDemo\tbl_Person.txt -c -Usa -P123456 -SManzoor

we have passed -P switch with password 123456 which indicates password for login sa.

Now, we will take an example of import data from file location to database server, for that we need to understand structure of data file and create the table with same structure in database where you want to insert records.

Here we will copy data file tbl_Person.txt to a new table which we have created as tbl_PersonInfo with same table structure.

bcp AdventureWorks2012.dbo.tbl_PersonInfo in "C:\BCPDemo\tbl_Person.txt" -T -c

We have used ‘in‘ argument here and successfully imported data into tbl_PersonInfo table, you can select the table from database and verify it with data file. [refer Figure-5]

Figure 5: Import Data with Trusted Connection

If you have a requirement to export specific columns then we can do the same with queryoutargument. To use queryout argument you need basic knowledge of SQL commands as we have to write query as per requirements. Let’s see an example, we require two columns FirstName and LastName from table tbl_Person for that we will execute below query on windows command prompt.

Figure 6: Export Data with Specific Columns

bcp "SELECT FirstName, LastName FROM AdventureWorks2012.dbo.tbl_Person" queryout C:\BCPDemo\tbl_Person.txt -T -c

Once all rows copied you can cross check data from table tbl_Person of BCPDemo folder on the disk.

Let’s take one more example where we will copy specific rows from table to a data file. [refer Figure-7]

Figure 7: Export Data with Specific Rows

bcp "SELECT * FROM AdventureWorks2012.dbo.tbl_Person WHERE FirstName='Gigi' AND LastName='Matthew'" queryout C:\BCPDemo\tbl_Person_Rows.txt -T -c

I have given you few examples of bulk copy program with txt file format, you can also use different file format like .dat, .fmt .xml etc by using -f and -x switches.

How to create xml file format, here is a simple line of commands.

Figure 8: Create XML File Format

bcp AdventureWorks2012.dbo.tbl_Person format nul -T -c -x -f C:\BCPDemo\tbl_Person.xml

You have to specify nul with format which creates format file including -x which indicates general xml format file along with -f.

Let’s open xml file which we have created just now, we will find as below.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
 <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
 <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
 <COLUMN SOURCE="1" NAME="BusinessID" xsi:type="SQLINT"/>
 <COLUMN SOURCE="2" NAME="Title" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="4" NAME="MiddleName" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="5" NAME="LastName" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="6" NAME="Country" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

Case Sensitive SQL Query Search and Joining

This article gives you a total overall idea related to collation. It not only contains definition but also contains SQL comparisons and joins with different type collation table in different database.

So let's start

Collation is defined by a set of rules that determines how the data is stored and compare. There are for types of collation and they are

1. Case sensitive: if "A" and "a" is treated as same way, then it is case in-sensitive. And id "A" and "a" are treated as different way than it is case sensitive. As the ASCII code of both letters are different. Capital "A" takes 65 and small "a" takes 97.

2. Accent sensitivity: if "a" and "á" treated as same way then it is accent in-sensitive and if treated differently than it is accent sensitive. The ASCII code of "a" is 97 and "á" is 225.

3. Kana Sensitivity: When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

4. Width sensitivity: When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently than it is width sensitive.

Example of database creation, with different collations is mentioned bellow

USE MASTER

CREATE DATABASE BIN COLLATE LATIN1_GENERAL_BIN

CREATE DATABASE CI_AI_KS COLLATE LATIN1_GENERAL_CI_AI_KS

CREATE DATABASE CS_AS_KS_WS COLLATE LATIN1_GENERAL_CS_AS_KS_WS

A table objects creation example with different collation

CREATE TABLE Mytable

([colu] CHAR(10) COLLATE Albanian_CI_AI_KS_WS NULL,

[Maydate] CHAR(8) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOTNULL ,

[Risk_Rating] CHAR(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL)

As a SQL server developer you have to face a situation to compare the two columns of different table having different collation. For Example you are joining two table which support different collations. If you are not careful it gives you an error msg.

To illustrate my points here I give u a simple example of case sensitive collation.

CREATE TABLE Mytable

(Sname CHAR(100) NOT NULL)

INSERT INTO Mytable (Sname)

VALUES ('JOYDEEP'),('TUHIN'),('PALASH'),('SANGRAM')

-- Get the result

SELECT * FROM Mytable WHERE Sname='joydeep'

To make the query case sensitive uses this.

-- To make the query case sensitive

SELECT * FROM Mytable WHERE Sname COLLATELatin1_General_CS_AS='joydeep'

-- Now

SELECT * FROM Mytable WHERE Sname COLLATELatin1_General_CS_AS='JOYDEEP'

To find the collation of any table use this

EXEC sp_help Mytable

Now I am giving an example to join two tables from different database with different collations.

If we perform this join it gives an error

SELECT ProductCode, ProductResalePrice

FROM [database1].[dbo].[PRODUCTS]

INNER JOIN [database2].[dbo].[items] ON ProductCode =[items].itemCode

Error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

We have to first know what type of collation both of the databases used to further precede the joining operations.

SELECT DATABASEPROPERTYEX('database1', 'Collation') SQLCollation;

SELECT DATABASEPROPERTYEX('database2', 'Collation') SQLCollation;

Now we can

SELECT ProductCode, ProductResalePrice

FROM [database1].[dbo].[PRODUCTS]

INNER JOIN [database2].[dbo].[items]

ON ProductCode COLLATE SQL_Latin1_General_CP1_CI_AS =[items].itemCode

I think that the article is quite informative and thanking you to provide your valuable time on it.

Failure to insert in Table costs Space Loss and Decrease Performance

Regarding the space problem of SQL server, I have collected some fact that you might be interested. It's again one things related to performance issue.

SQL Server has an issue when an INSERT fails that includes LOB data, SQL Server allocates space for the insert but does not reclaim the space on the failure therefore taking up more space than necessary. In this tip I walk through an example of this issue and ways to resolve it.

For the test, let's create a sample test table in my TEST database.

CREATE TABLE MYSQLTEST

(MYPKKEY CHAR(10),

TESTVALUE NVARCHAR(MAX)

CONSTRAINT [PK_MYPKKEY] PRIMARY KEY CLUSTERED

(

[MYPKKEY] ASC

)

Before we do any inserts, let's check the space used by the table by issuing the following command:

sp_spaceused 'MYSQLTEST', true

name rows reserved data index_size unused

MYSQLTEST 0 0 KB 0 KB 0 KB 0 KB

Let's insert the first record.

DECLARE @TMP NVARCHAR(MAX)

SET @TMP=REPLICATE('THIS IS A TEST',4000)

INSERT MYSQLTEST(MYPKKEY,TESTVALUE) VALUES ('KEY1',@TMP)

And then check the space used again. Here we can see that reserved is 40KB, data is 32KB for 1 row.

sp_spaceused 'MYSQLTEST', true

name rows reserved data index_size unused

MYSQLTEST 1 40 KB 32 KB 8 KB 0 KB

Now, let's try to insert the same row again which should fail because of the PK constraint.

DECLARE @TMP NVARCHAR(MAX)

SET @TMP=REPLICATE('THIS IS A TEST',4000)

INSERT MYSQLTEST(MYPKKEY,TESTVALUE) VALUES ('KEY1',@TMP)

As expected, the insert fails because we tried to insert a duplicate primary key value. Let's check the space used again. We can now see that reserved is 56KB, data is 48KB for 1 row.

sp_spaceused 'MYSQLTEST', true

name rows reserved data index_size unused

MYSQLTEST 1 56 KB 48 KB 8 KB 0 KB

Now, let's run the same insert statement 100 times.

Lets check the space used again. Now we can see reserved is 6232KB, data is 848KB for 1 row

sp_spaceused 'MYSQLTEST', true

name rows reserved data index_size unused

MYSQLTEST 1 6232 KB 848 KB 8 KB 5376 KB

If we want to reclaim the allocated space we can run the following CLEANTABLE command where TEST is the name of my database and MYSQLTEST is my table.

DBCC CLEANTABLE(TEST,MYSQLTEST,0)

Please refer to the Microsoft Article

http://support.microsoft.com/kb/2295878.

To Fix the Issue

SQL server receives a user query

SQL server receives a user query; it follows well-defined steps to produce a result for user.

Step-1

First, it creates a compiled plan, which is a set of logical instructions such as how to join rows.

Step-2

Next, it creates an execution plan based on the compiled plan. This execution plan contains instructions with all logical references translated to actual objects, and tracking mechanisms for query execution.

Step-3

Finally, the server starts execution from the top of instruction tree.

Creating a compiled plan is expensive because the server needs to find the optimal plan out of hundreds of potential candidates. Distinguishing between compile and execution helps overall server performance because each compiled plan can be cached and shared among multiple execution plans. The memory grant estimate follows the same overall sequence. It has parameters saved in compiled plan, and a mechanism to calculate actual grant size at execution time.

A successful query execution involves 3 major memory consumers: compile, cache, and memory grant.

Compile: Building and searching the most optimal plan out of hundreds candidates typically requires significant amount of memory. The lifetime of this usage is typically short because optimizer releases memory as soon as optimal plan is found. Lack of available memory would cause delays in compile, and potentially inefficient (slow) plans.
Cache: Finding optimal plan is costly in terms of CPU and memory usage. SQL server tries to store compiled plans in caches for later reuse. Lifetime of this memory usage is long-term. Lack of cache memory would cause more unnecessary re-compiles.
Memory grant: This memory is used to store temporary rows for sort and hash join. The lifetime of memory grant is the same as the lifetime of query. Lack of available memory grant causes a query to use hard disk, which affects query performance.

SQL Server maintains the balance between these 3 consumers with internal facility called "memory broker". Based on the usage and physical memory available, the memory broker sets the usage limit and tells each component to trim its memory usage if it anticipates a shortage. Generally, a well behaving server would have about the same contributions from these consumers.

Global variables

Global variables play a very important role in SQL server. Which are very effective to use in our regular Transact-SQL. In my article I am trying to describe it in a simple architecture.

Global variables represent a special type of variable. The server always maintain the values of these variables. All the global variables represent information specific to the server or a current user session.

Global variable names begin with a @@ prefix. You do not need to declare them, since the server constantly maintains them. They are system-defined functions and you cannot declare them.

Some example of commonly used global variable are mentioned bellow.

SELECT @@VERSION [SQL SERVER VERSION]

For better understanding of each global variable please refer to MSDN or MS Related document.

I think the information is quite informative and thanking you to provide your valuable time on it.

Row version

Rowversion

Here in this article I am trying to discuss related to rowversion.

Rowversion is generally used as mechanism for version-stamping table row. It automatically generates 8 bytes unique binary numbers. The rowversion data type is an incrementing numbers and not preserves any date and time on it.

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database.

A table can have only one rowversion columns. Every time the rowversion columns increments the value if any kind on Insert or Update operation occurs. Please never use the rowversion data type as a primary key as it change the state every time when an insert or update occurs.

Timestamp is the synonym for the rowversion data type.

CREATE TABLE test_tab

(Roll INT NOT NULL PRIMARY KEY,

SName VARCHAR(50) NULL,

Ver_Stamp ROWVERSION)

INSERT INTO test_tab

(Roll, SName)

VALUES (1, 'Raja Ram'),(2, 'Bijay Anand'),(3, 'Jobe Clone')

SELECT Roll, SName, Ver_Stamp FROM test_tab

/* -- Output--

Roll SName Ver_Stamp

1 Raja Ram 0x0000000000014C09

2 Bijay Anand 0x0000000000014C0A <---

3 Jobe Clone 0x0000000000014C0B

UPDATE test_tab

SET SName='Tony Hillary'

WHERE Roll=2

SELECT Roll, SName, Ver_Stamp FROM test_tab

/* -- Output--

Roll SName Ver_Stamp

1 Raja Ram 0x0000000000014C09

2 Tony Hillary 0x0000000000014C0C <---

3 Jobe Clone 0x0000000000014C0B

Look here in this example the marked area (ß) showing the changing of versions.

Hope the article is informative and thanking you to prove your valuable time on it.

Calculating number of Weekdays

In this article there is a simple SQL statement that shows the number of week days between two supplied dates.

You can make it a function and supply 3 parameters like starts date, end dates and Number of Holiday. If you have a holiday list (or you know the number of holiday in years) you can calculates the total number of working dates in a year.

Here in this example I am taking 0 holidays.

I am trying to make it a function, which you can directly use.

IF OBJECT_ID (N'dbo.Ifn_WeekDays',N'FN') IS NOT NULL

DROP FUNCTION dbo.fn_WeekDays

-- Function

CREATE FUNCTION dbo.fn_WeekDays

(

@param_StartDate DATETIME,

@param_EndtDate DATETIME,

@param_NoofHoliday INT = 0

)

RETURNS INT

BEGIN

DECLARE @start As DateTime,

@end As DateTime,

@holidy As Int,

@v_Retval As Int

SET @start=CONVERT(datetime, @param_StartDate)

SET @end =CONVERT(datetime, @param_EndtDate)

SET @holidy=@param_NoofHoliday

SELECT @v_Retval =DATEDIFF(d,@start,@end)+1

- (DATEDIFF(wk,@start,@end) + CASE WHENDATEPART(dw,@start)=1

THEN 1 ELSE 0 END )

- (DATEDIFF(wk,@start,@end) + CASE WHENDATEPART(dw,@end)=7

THEN 1 ELSE 0 END )-@holidy

RETURN(@v_Retval)

END

-- Function execution

SET DATEFORMAT DMY

SELECT dbo.fn_WeekDays(CONVERT(DATETIME, '01-03-2012'),

CONVERT(DATETIME, '31-03-2012'),

0) AS [No of Week Days]

Hope you like it.

Microsoft Technologies

Pages

Tuesday, August 13, 2013

Bulk Copy Program (BCP) Utility to Import and Export Data in SQL Server

CLICK HERE to watch live practical.

Case Sensitive SQL Query Search and Joining

Case Sensitive SQL Query Search and Joining

Failure to insert in Table costs Space Loss and Decrease Performance

Failure to insert in Table costs Space Loss and Decrease Performance

SQL server receives a user query

SQL server receives a user query

Global variables

Global variables

Row version

Rowversion

Calculating number of Weekdays

Calculating number of Weekdays

About Me