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
reconfigurego
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

Get rows just I want

The Oracle latest version optimizers are working too smart. The comparison with oracle 9i and higher viersions.
I have a Query with an ORDER BY clause that returns 10,000 rows, but I'm restricting it to 500 rows with ROWNUM. Does the Oracle database store 10,000 rows in memory or open with 500 rows? For example:
SELECT empno, empnmFROM ( SELECT empno, empnm FROM employee ORDER BY empno )WHERE ROWNUM < 500;

Generally, the Oracle database does not store an entire result "in memory." Instead, the database tries to answer the query and return the first row before it gets the last row.
If the database attempted to store the result set in memory, we would never be able to ask a query like give more that one billion rows. Oracle generally answers the query on the fly whenever possible. In the above case—if EMPNO is indexed and EMPNO is NOT NULL—the optimizer will read the index to process the ORDER BY and stop after reading 500 rows.

Oracle 11g Result:

SQL_ORA11G> create table emp as select object_id empno, object_name ename from all_objects;
Table created.
SQL_ORA11G> select count(*) from emp;
COUNT(*)
----------
68503

SQL_ORA11G> create index emp_idx on emp(empno);
Index created.

SQL_ORA11G> analyze table emp compute statistics;
Table analyzed.

SQL_ORA11G> analyze index emp_idx compute statistics;
Index analyzed.

SQL_ORA11G> set autotrace traceonly
SQL_ORA11G> select empno, ename from ( select empno, ename from emp order by empno ) where rownum < 500;
Execution Plan
----------------------------------------------------------
Plan hash value: 2549860052
----------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------
0 SELECT STATEMENT 499 14970 6 (0) 00:00:01
* 1 COUNT STOPKEY
2 VIEW 499 14970 6 (0) 00:00:01
3 TABLE ACCESS BY INDEX ROWID EMP 68503 1873K 6 (0) 00:00:01
4 INDEX FULL SCAN EMP_IDX 499 3 (0) 00:00:01
---------------------------------------------------------
Oracle 9i Result:

The same script is working differently in the Oracle 9i. it is taking all the records and it is not stopping after it reaches 500. Oracle 9i reads all the data and filtering the 500 records.
SQL> create table emp as select object_id empno, object_name ename from all_objects;
Table created.

SQL> select count(*) from emp;
COUNT(*)
----------
29125

SQL> create index emp_idx on emp(empno);
Index created.

SQL> analyze table emp compute statistics;
Table analyzed.

SQL> analyze index emp_idx compute statistics;
Index analyzed.

SQL> set autotrace traceonly
SQL> select empno, ename from ( select empno, ename from emp order by empno ) where rownum < 500

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=194 Card=499 Bytes=873750) 1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=194 Card=29125 Bytes=873750)
3 2 SORT (ORDER BY STOPKEY) (Cost=194 Card=29125 Bytes=786375)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=15 Card=29125 Bytes=786375)

We try to retrieve the data from table with out order by. So it will not use any index and it goes for the FULL table scan.

Oracle 9i:
In Oracle 9i, it reads the 29125 records and giving the 499 records to outside.
SQL> select empno, ename from emp where rownum < 500;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=499 Bytes=786375)
1 0 COUNT (STOPKEY) 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=15 Card=29125 Bytes=786375)

Oracle 11g:
In Oracle 11g, it is going for the FULL table scan and it reads only 499 records but the actual count of the table is 68503. it is giving rows just I want.

SQL_ORA11G> select empno, ename from emp where rownum < 500;

Execution Plan
----------------------------------------------------
Plan hash value: 1973284518
----------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------
0 SELECT STATEMENT 499 13972 3 (0) 00:00:01
* 1 COUNT STOPKEY
2 TABLE ACCESS FULL EMP 499 13972 3 (0) 00:00:01
----------------------------------------------------

Read More

Count with View in Oracle 9i vs 11g

Oracle 9i Result:
SQL> drop table t;
Table dropped.

SQL> create table t (c number);
Table created.

SQL> create view v1
as select count(1) count from t;
View created.

SQL> create view v2
as select count(*) count from t;
View created.

SQL> select object_name, object_type, status from user_objects
where (object_name in ('V1','V2'))
and object_type = 'VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------- --------
V2 VIEW VALID
V1 VIEW VALID

Add another column in the table.
SQL> alter table t add ( c2 number);
Table altered.

SQL> select object_name, object_type, status
2 from user_objects
3 where (object_name in ('V1','V2'))
4 and object_type = 'VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------ -------------
V2 VIEW INVALID
V1 VIEW INVALID

Both the views are Invalid in Oracle 9i environment.
Oracle 11g Result:
SQL> drop table t;
Table dropped.

SQL> create table t (c number);
Table created.

SQL> create view v1
2 as
3 select count(1) count from t;
View created.

SQL> create view v2
2 as
3 select count(*) count from t;
View created.

SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- -------------- -------------
V1 VIEW VALID
V2 VIEW VALID

Add another column in the table.

SQL> alter table t add(c2 number);
Table altered.

SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
------------ --------------- -------------
V1 VIEW INVALID
V2 VIEW VALID


The V1 became Invalid. but V2 is valid

Read More

Array Size Effects

The array size is the number of rows fetched (or sent, in the case of inserts, updates, and deletes) by the server at a time. It can have a dramatic effect on performance.
SQL> drop table t;

Table dropped.

SQL> create table t as select * from all_objects;

Table created.

SQL> select count(*) from t;
COUNT (*)
----------
29120
SQL> set autotrace traceonly statistics;
SQL> set arraysize 2
SQL> select * from t;

29120 rows selected.

Statistics
----------------------------------------------------------
14779 consistent gets

Note how one half of 29120 (rows fetched) is very close to 14779 , the number of consistent gets. Every row we fetched from the server actually caused it to send two rows back. So, for every two rows of data, we needed to do a logical I/O to get the data. Oracle got a block, took two rows from it, and sent it to SQL*Plus. Then SQL*Plus asked for the next two rows, and Oracle got thatblock again or got the next block, if we had already fetched the data, and returned the next two rows, and so on.
Next, let’s increase the array size:
SQL> set arraysize 5
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
6152 consistent gets

Now, 29120 divided by 5 is about 5824, and that would be the least amount of consistent gets we would be able to achieve (the actual observed number of consistent gets is slightly higher).

All that means is sometimes in order to get two rows, we needed to get two blocks: we got the last row from one block and the first row from the next block.
Let’s increase the array size again:

SQL> set arraysize 10
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
3271 consistent gets

SQL> set arraysize 25
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
1551 consistent gets

SQL> set arraysize 100
SQL> select * from t;

29120 rows selected.


Statistics
----------------------
688 consistent gets

SQL> set arraysize 500
SQL> select * from t;

29120 rows selected.


Statistics
------------------
460 consistent gets
............
As you can see, as the array size goes up, the number of consistent gets goes down. So, does that mean you should set your array size to 5,000, as in this last test? Absolutely not. If you notice, the overall number of consistent gets has not dropped dramatically between array sizes of 100 and 5,000.

It would be better to have more of a stream of information flowing: Ask for 100 rows, get 100 rows, ask for 100, process 100, and so on. That way, both the client and server are more or less continuously processing data, rather than the processing occurring in small bursts.

Read More