In SQL Server 2005, Microsoft introduced a new command line utility, SQLCMD, as a replacement for osql and isql.In this article you will find some advantage you will gain when you use SQLCMD instead of OSQL.Migration from OSQL to SQLCMD will be very simple because most of the command line switches are similar,the only difference is that SQLCMD support additional switches.To see the difference between then type OSQL /? and SQLCMD /? at the command prompt.
1) SQLCMD supports parameterized variablesSQLCMD can be useful for running some SQL scripts which require you to pass different parameters specific to a certain environment.Environment variables and declared variables can be used in various places in the script The following is an example: -- c:\sqlvariable.sql
DECLARE @myVar varchar(255) SET @myVar = '$(myVar)' SET @myVar = @myVariable + ' ' + suser_sname() PRINT @myVar
You can run it from a command prompt:
SQLCMD -E -i"c:\sqlvariable.sql" -v myVar="You are connected as "
Result:
You are connected as ABOUHMEID02\Administrator
2) !! This command allows you to execute an Operating System Command on the client without having to connect and use xp_cmdshell. For example while connected to SQLCMD type !!dir
3) Management Studio's query editor in the SQLCMD modeSQLCMD can be run both in the DOS screen as well as in the MS SQL Server 2005 Management Studio's query editor in the SQLCMD mode. If you want to use scripting, which brings in features like color coding, parsing and executing scripts, and others, this must be enabled. Enabling SQLCMD allows you to write SQL Commands as well as T-SQL Statements. You can set this in Tools Options or click the button with the Red
4) Performance It allows you to connect to any instance of SQL Server via OLE DB (rather than via older technologies such as DB-library and ODBC, used by OSQL and ISQL, respectively) which is a much better performer
5) Remote Dedicated Admin Connection (DAC) If you use the -A switch, you can get into a server that might otherwise be hung. You can kill an offending process and restore order to the universe - without a reboot!
6) SQLCMD support Customized editorIn SQLCMD, if you type ed, it will invoke a text editor and put the last command you run in the editor buffer. The default editor is Edit.com You can change the default editor to your own preferred editor,to do this: Open a DOS prompt; Type set sqlcmdeditor=notepad Go to sqlcmd, enter and execute a sql statement, then type ed; The notepad opens.Do the modifcations you want to do, save and close.
7) SQLCMDINIThis allows you to set default parameters and even create a default query to execute on the server as soon as you connect. This helps you to automate your connection properties and to some degree your environment in command line mode. For example: Create a script file named : -- c:\Init.SQL -- Begin script set nocount on go
print 'You are connected to ' + rtrim(CONVERT(char(20), SERVERPROPERTY('servername'))) + ' (' + rtrim(CONVERT(char(20), SERVERPROPERTY('productversion'))) + ')' + ' ' + rtrim(CONVERT(char(30), SERVERPROPERTY('Edition'))) + ' ' + rtrim(CONVERT(char(20), SERVERPROPERTY('ProductLevel'))) + char(10)
set nocount off go
--End script
In Command prompt, type: set sqlcmdini=c:\Init.SQL Here are some sample results: C:\DOCUME~1\ADMINI~1>sqlcmd You are connected to ABOUHMEID02 (8.00.760) Developer Edition SP3 1>
8) :XML [ONOFF] Specifies if XML output resulting from FOR XML clause will be output as a continuous stream.
9) :Connect server_name[ \instance_name][timeout] [ user_name[ password]] From within a single script, you can connect to multiple SQL Server instances and execute commands. This allows for a great deal of automation possibilities, such as backup from one server, restore to another server. Also, you could execute a configuration change script on all servers in production.
10) :Error STDERRSTDOUT
There's pretty much all you need in some combination of these to gracefully deal with run-time problems. That is so much better than piping out and digging through log files! It Redirects all error output to the file specified by filename, to stderr or to stdout. The Error option can appear multiple times in a script. Error output is sent to stderr by default.
11) :Perftrace STDERRSTDOUT
Redirect all performance trace information to the file specified by filename, to stderr or to stdout. Performance trace output is sent to stdout by default. It lets you put your statistics (IO, TIME, ShowPlan) out to a separate file for later viewing as so not to clutter the results file output.
Basically partitions help in improving the performance, scalability and managing of the large
tables. As the table grows larger and larger the performance in accessing the data is affected, scalability and managing issues arises. With
the help of partitioning a table we can achieve a great level of performance and managing of tables.
Let us see in how we can create partitioned tables in detail. The procedure for creating a partitioned table is as follows.
1) Creation of Filegroups
Beginning with this we have to have various filegroups for the database if we need to place the partitioned tables on different filegroups. To
create a filegroup, there are different ways one is using the Alter command and the second is using the Interface. Let us see each of them
a) Using the Alter Command
The syntax is as follows
ALTER DATABASE Database Name ADD FILEGROUP Filegroup name
After adding a filegroup we need to add files to the filegroup. We can add one or more files. The syntax is as follows
ALTER DATABASE Database Name
ADD FILE
(
NAME = File Name,
FILENAME = Path,
SIZE = Size,
MAXSIZE = Size,
FILEGROWTH = Size
)
TO FILEGROUP Filegroup Name;
b) Using the Interface
The following steps show how we can create using the interface
a) Login to the Microsoft SQL SERVER Management Studio
b) Select the Database and right click and select properties.
c) Select the Filegroup section and add the necessary details and click the add button.
d) Next we will click on the Files section and add a new file and associate this file to the FG2 filegroup .
e) Then finally click on the OK button.
This is procedure for create a filegroup using the interface.
2) Creation of Partition Function
The partition function is created for setting the range partitions. The ranges can be set for a lower or upper threshold. The syntax is as follows
CREATE PARTITION FUNCTION Function Name (Data Type)
AS
RANGE LEFT/RIGHT FOR VALUES (value1,value2,…)
3) Creation of Partition Scheme
The next step is to create the partition scheme after the partition function is created. This is needed for associating the partitions to a specific
filegroups.
The syntax is as follows
CREATE PARTITION SCHEME Partition Scheme Name
AS
PARTITION Partition Function Name
TO (Filegroup1,Filegroup2,….)
4) Creation of Partition Table
The next step is to create the partitioned table which would be associated with the defined partition scheme.
The syntax is as follows
CREATE TABLE Table Name
(
Column Name1 Datatype,
Column Name2 Datatype,
……..
)
ON Partition Scheme Name ( Column Name)
The above four steps clearly states how to create a partitioned table.
Querying a Partitioned Table
After the creation of a partitioned table, now let us see how to query the data from the partitioned tables.
Let us see the various cases in querying
a) Querying the data from a particular partition
Syntax
SELECT Column Name1…/* FROM Table Name WHERE $PARTITION.Partition Function Name(Column Name) =
Partition Number
The Partition number refers to first partition range or second partition range and so on, The first partition range is referred as 1 , second as 2
and so on.
b) Querying for Knowing the Partition Number
Syntax
SELECT $PARTITION.Partition Function Name(Column Name) = Partition Range Value
c) Querying to find the count of records in each partition
Syntax
SELECT $PARTITION.Partition Function Name(Column Name), COUNT(*) FROM Table Name GROUP BY $PARTITION.
Partition Function Name(Column Name)
SPLITTING OF PARTITION
The partitions can be split by splitting the partition ranges. The splitting is done by using the alter partition command. We have to note that
before we split the partition there should be a additional filegroup already associated in the partition scheme. If there is no unused filegroup
available then we cannot split. So before splitting we have to ensure that a filegroup is added to the partition scheme. This is as shown below.
SYNTAX
ALTER PARTITION FUNCTION Partition Function Name () SPLIT RANGE (PARITITION RANGE VALUE)
MERGING OF PARTITION
The partitions can be merged by merging the partition ranges. The partition range value mentioned will merge that to the next greater
partition range value into a singe partition. This is as shown below.
SYNTAX
ALTER PARTITION FUNCTION Partition Function Name () MERGE RANGE (PARITITION RANGE VALUE)
ALTERING PARTITION SCHEME
The partition scheme is altered to add new filegroup which may be required when partitions are splitted.
SYNTAX
ALTER PARTITION SCHEME Partition Scheme Name NEXT USED Filegroup Name
DROPPING OF PARTITION
Let us see now how to drop partitions .
a) Drop a Partition Function
To drop a partition function it should not have any partition scheme associated with it.
Syntax
DROP PARTITION FUNCTION Partition Function Name
b) Drop a Partition Scheme
To drop a partition scheme it should not have any table associated with it.
Syntax
DROP PARTITION SCHEME Partition Scheme Name
Partition Function Information
The Partition Function Information is obtained from the sys.partition_functions table as shown below
SELECT * FROM SYS.PARTITION_FUNCTIONS
Partition Range Information
The Partition Range information is obtained from the sys.partition_range_values as shown below
SELECT * FROM SYS.PARTITION_RANGE_VALUES
Partition Scheme Information
The Partition Scheme information is obtained from the sys.partition_scheme as shown below
SELECT * FROM SYS.PARTITION_SCHEME
Partitions Information
The Partition information can be obtained from the sys.partitions table as shown below.
SELECT * FROM SYS.PARTITIONS
Click here to download an example : partition.sql
