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 or index depending on being linked to a partition scheme.

Partition function:

The partition function defines the boundary values of the initial set of partitions and the data type of the partitioned column:

o It makes no reference to any tables or disk storage.
o It forms the basis for one or more partition schemes.

create PARTITION function PF_dept(int)
AS range right FOR values (10,20,30)

Partition scheme:

The partition scheme maps particular partitions to filegroups:

o A given partition scheme can be used for one or more partitioned tables, indexes, and indexed views.

create partition scheme ps_dept
as partition PF_dept
TO (FG1,FG2,FG3,FG4)

Partitioned table:

The partitioned table or index is tied to a particular partition scheme when it is created:

o The partition table has only an indirect relationship, through the partition scheme, to the partition function.

create table dept
(deptid int,
deptname varchar(10)
)
ON ps_dept(deptid)


CREATE TABLE EMP
(EMPID INT,
EMPNAME VARCHAR(10),
DEPT_ID INT)
ON PS_DEPT(DEPT_ID)

Read More

WITH TIES

WITH TIES in SQL server

Specifies 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 5 * from sales
order by sales_amount desc

customer_name sales_amount
------------- ------------
F 15000
E 13000
D 12000
C 10000
B 6000

(5 row(s) affected)

Top 5 WITH TIES:

The “Top 5 WITH TIES” gives 6 records because the base result set with the same value in the ORDER BY columns appearing as the last of the TOP 5.

select top 5 WITH TIES * from sales
order by sales_amount desc

customer_name sales_amount
------------- ------------
F 15000
E 13000
D 12000
C 10000
A 6000
B 6000

(6 row(s) affected)

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 y

select * from b
C1 C2 C3
----- -- --
1 x y
2 x y
3 x yy


The first rows are the same, but the second and third rows differ. This is how we can find them:

select i.c1,i.c2, i.c3,
count(i.src1) CNT1,
count(i.src2) CNT2
from
( select a.*,
1 src1,
null as src2
from a
union all
select b.*,
null as src1,
2 src2
from b
)i
group by i.c1,i.c2,i.c3
having count(i.src1) <> count(i.src2)

C1 C2 C3 CNT1 CNT2
--- -- -- ---- ----
2 x y 0 1
2 xx y 1 0
3 x y 1 0
3 x yy 0 1

The below link having the script in Oracle:

http://karthikeyanbaskaran.blogspot.com/2010/02/comparing-contents-of-two-tables.html

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 select through ISQL via xp_cmdshell and use the -o parameter to output the results to a file. This example uses the -E parameter to avoild hard-coding a userid.

declare @str varchar(255)
select @str = 'isql -Q"select * from " -E -oc:\file.fil'
exec master..xp_cmdshell @str

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 to enable or disable the xp_cmdshell extenden procedure and releases the SQL Server products with xp_cmdshell is disabled fby default. So if you think you are capable of taking the security risks and prevent those risks you can enable xp_cmdshell by using the sp_configure stored procedure.
The below t-sql code displays how xp_cmdshell can be used to delete a file named delete-me-file.txt in the root folder of C drive

xp_cmdshell 'del c:\delete-me-file.txt'

Since the xp_cmdshell extended procedure has not been enabled yet, the SQL Server will return the following error and warning message:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Actually the warning message is self explaining in details. We can either enable the sys.xp_cmdshell procedure by using sp_configure or by using the SQL Server Surface Area Configuration Tool.Of course in order to make configuration changes on the sql server, you have to have the system administrator rights or permissions on the SQL Server instance.

Enable xp_cmdshell using sp_configure:

exec sp_configure
go
exec sp_configure 'xp_cmdshell', 1
-- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go

When you run the exec sp_configure t-sql command, you will see a returned list of configuration values for the related SQL Server installation.The last row of the configuration list is probably displaying values of xp_cmdshell.config_value and run_value columns are displaying if xp_cmdshell is enabled and if this change is reflected to the running configuration values.exec sp_configure 'xp_cmdshell', 1 sql command sets the configuration value ("config_value") to 1 which means enable.And the last t-sql code or command reconfigure sets the running value to enabled in a way reflects the changes to the running server configurations.

How to delete a file using xp_cmdshell extended procedure:

After enabling xp_cmdshell it is straight forward for deleting a file from the file system if you have the necessary permissions on the file object for the user who is running the xp_cmdshell procedure.

xp_cmdshell 'del c:\delete-me-file.txt'

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 sysname

Read More

Read file names from the folder

Stored procedure to read the filenames:

create procedure spReadFolder @path sysname
as
begin
set nocount on

declare @dirfile table(
id int identity(1,1),
FileName sysname NULL )

declare @cmd nvarchar(512)
set @cmd = 'DIR /b ' + @path

insert into @dirfile
exec master..xp_cmdshell @cmd

select * from @dirfile

end

go

Execute stored procedure:

declare @path sysname
set @path = 'D:\SQL_folder'

exec spReadFolder @path

go

Read More