System Configuration,Files and paths of Database,OS information

1) System Configuration
SELECT *
FROM sys.configurations
ORDER BY name OPTION (RECOMPILE);

2) Filename and Paths of Database
SELECT DB_NAME([database_id])AS [DBName],
name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) [SizeinMB]
FROM sys.master_files
ORDER BY DB_NAME([database_id])

3)OS level information
SELECT *
FROM sys.dm_os_sys_info

Xp_cmdshell For OS commands

Introduced in sql server 2005
xp_cmdshall option is a server configuration option that enables system administrator.


"xp_cmdshell" is an extended stored procedure provided by Microsoft and stored
in the master database. This procedure allows you to issue operating system commands
 directly to the Windows command shell via T-SQL code


-- To allow advanced options to be changed.


EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
________________________________________________________________
exec master.dbo.xp_cmdshell 'dir c:\temp\*.sql'
________________________________________________________________
exec master.dbo.xp_cmdshell 'mkdir "c:\temp\SQL Agent Output\new_job\"'
________________________________________________________________
DECLARE @rc int
EXEC @rc = master.dbo.xp_cmdshell 'copy c:\temp\doesnotexist.txt c:\temp\workfile.txt'
print @rc
IF @rc <> 0
BEGIN
  PRINT 'Copy Failure Skip work'
END
ELSE
BEGIN
  Print 'Copy worked now we can do some more stuff'
  ....
END
________________________________________________________________

Server-side paging with Row_number()

If you are a programmer working with SQL Server, you must have found it little embarrassing to display information which spans across multiple pages (web pages). SQL Server 2000 did not allow you to retrieve a specific range of records, say, records 51 to 100 ordered by a certain column.

For example, assume that you are working on a web page which lists the names of all the cities in different countries. Assume that you need to display 25 records in a page. The database has 50,000 records consisting all the cities/towns across the globe. In the above scenario, it really makes sense to retrieve only the required records. for example, in the first page, retrieve 1 to 25 records. When the user clicks on "next" button, retrieve records 26 to 50 and so on. at this stage the user might click on another column to change the sort order. Earlier it was ordered by city name but now the display is based on Zip code.

With SQL Server 2000, it was not very easy to achieve this. Some times people used temp tables achieve this. Others put the paging responsibility to the application which retrieved all the records and then displayed the information needed for the current page. (this approach will not only overload server resources, but also degrades performance of the application as well as the database server.)

SQL Server 2005 introduces a helpful function ROW_NUMBER() which helps in this scenario. Using ROW_NUMBER()  you can generate a sequence number based on a given sort order and then select specific records from the results. Here is an example:

ROW_NUMBER() OVER (ORDER BY City) as Seq
The syntax ideally says the following. "Order the records by City, and then assign a serial number to each record". You can use it in a query as follows.

SELECT * FROM
(
    SELECT  ROW_NUMBER() OVER (ORDER BY City) AS row, *
    FROM Cities
) AS a WHERE row BETWEEN 101 AND 125

SQL Server CE 4.0 introduced a new TSQL extension that makes paging queries much easier. For example, to fetch rows 21 to 30, a query can be written like this.

SELECT * FROM Orders ORDER BY OrderID
OFFSET 20 ROWS
FETCH NEXT 10 ROW

Trigger with example

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.

Syntex:
CREATE TRIGGER [owner.]trigger_name

ON[owner.] table_name

FOR[INSERT/UPDATE/DELETE] AS

IF UPDATE(column_name)

[{AND/OR} UPDATE(COLUMN_NAME)...]

{ sql_statements }

INSERT trigger

When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time. The inserted table is a logical table that holds a copy of rows that have been inserted. The inserted table can be examined by the trigger ,to determine whether or how the trigger action are carried out.

The inserted table allows to compare the INSERTED rows in the table to the rows in the inserted table.The inserted table are always duplicates of one or more rows in the trigger table.With the inserted table ,inserted data can be referenced without having to store the information to the variables.

DELETE trigger

When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table.

UPDATE trigger

When an UPDATE statement is executed on a table that has an UPDATE trigger,the original rows are moved into deleted table,While the update row is inserted into inserted table and the table is being updated.

For e.g
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2008R2 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

Simple Example of Cursor

Using cursor Back up of all database

DECLARE @DataBaseName VARCHAR(50)
DECLARE @Path VARCHAR(200)
DECLARE @DataBaseFileName VARCHAR(200)

SET @Path = 'E:\Backup\'

DECLARE DataBase_Cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('form')

OPEN DataBase_Cursor

FETCH NEXT FROM DataBase_Cursor INTO @DataBaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @DataBaseFileName = @Path + @DataBaseName + '.BAK'
BACKUP DATABASE @DataBaseName TO DISK = @DataBaseFileName

FETCH NEXT FROM DataBase_Cursor INTO @DataBaseName
END

CLOSE DataBase_Cursor
DEALLOCATE DataBase_Cursor

CSV Data (Comma Seperated value)

Declare @table table (CityName varchar(50))

insert into @table values ('London')
insert into @table values ('Birmingham')
insert into @table values ('Manchester')
insert into @table values ('Glasgow')

1._________________________________________________________________
declare @csv as varchar(500)
select @csv = case  when @csv is null then ''
else @csv + ',' end + CityName from @table
select @csv as csv

2.__________________________________________________________________
declare @csv as varchar(500)
SELECT @CSV = COALESCE(@csv + ', ','') + CityName
FROM @table
select @csv as csv

3.__________________________________________________________________
declare @csv as varchar(500)
set @csv = (select CityName + ',' from @table for xml path(''))
select @csv as csv