Partitioning in SQL server

Partitioning in SQL server with small sample: Summary: Table partitioning can make very large tables and indexes easier to manage, and improve the performance of appropriately filtered queries.Table Partitioning Components:The partition function, partition scheme, and partitioned table or index form a dependency tree, with the partition function at the top, the partition scheme depending on the partition function, and then the partitioned table...

Read More

WITH TIES

WITH TIES in SQL serverSpecifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.create table sales( customer_name varchar(10), sales_amount int)insert into sales values('A',6000),('B',6000),('C',10000),('D',12000),('E',13000),('F',15000),('G',5000),('H',4000)Top 5:The “Top 5” gives first 5 records based on the condition in query.select top...

Read More

Comparing the Contents of Two Tables

Comparing the Contents of Two Tables: create table a(c1 int,c2 varchar(3),c3 varchar(3)) create table b(c1 int,c2 varchar(3),c3 varchar(3))insert into a values(1,'x','y')insert into a values(2,'xx','y')insert into a values(3,'x','y')insert into b values(1,'x','y')insert into b values(2,'x','y')insert into b values(3,'x','yy')select * from a C1 C2 C3----- -- -- 1 x y 2 xx y 3 x yselect * from b C1 C2 C3----- -- -- 1 x y 2 x y 3...

Read More

Spool operation in SQL server

SQL Server doesn't have a SPOOL command like Oracle does for writing to files,But there are other ways of doing what we want.1. For reading, use xp_cmdshell and the ECHO command. Use the > or >> redirection symbols to either create or append to a file.xp_cmdshell "@ECHO test message >> C:\file.fil"2. Put the information you want into a table (note this can't be an ordinary temporary table, but it can be a global temporary table) and then bcp it out to a file via xp_cmdshell.xp_cmdshell "bcp .. out c:\file.fil -Usa -P -c"3. Run the...

Read More

Enable xp_cmdshell on a MS SQL Server Instance

Enable xp_cmdshell on a MS SQL Server Instance :xp_cmdshell has the power to make modifications on the outer world of SQL Server. So this power has to be controlled in the security concepts and be manageable. In the early versions of Microsoft SQL Server the xp_cmdshell extended procedure was enabled default. This caused some security gaps for SQL Server owners. Although some administrators do not use xp_cmdshell functionality, it was out of control and can be used in an unsecure way by a sql server developer. Microsoft now enables SQL Server administrators...

Read More

Sysname

About Sysname:The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers. Therefore, it can vary between instances of SQL Server. sysname is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, sysname is defined as varchar(30).Example:declare @path sysn...

Read More

Read file names from the folder

Stored procedure to read the filenames:create procedure spReadFolder @path sysnameasbeginset nocount ondeclare @dirfile table(id int identity(1,1),FileName sysname NULL )declare @cmd nvarchar(512)set @cmd = 'DIR /b ' + @pathinsert into @dirfileexec master..xp_cmdshell @cmdselect * from @dirfileendgoExecute stored procedure: declare @path sysnameset @path = 'D:\SQL_folder'exec spReadFolder @pat...

Read More