How to shuffle results - SqlServer?

This is an interesting question asked by Mr. Ram Nath Rao.
The history is:

Mr. Nath wants to shuffle his record(s) every time when the page refreshes, the same has been tried with the use of rand() function by him. Unfortunately, the results were not as expected.

Now, lets elaborate some of interesting points towards this :

When anybody use rand() function what happened [check followings)]:

Select rand()as Random Number --creates random number

The result may be:



When you will repeat above statement, the new result is entirely different from the earlier one.

Now, try another similar query:

Select rand()as Random_Number,* from Employees

The result may be:



In above, result note-down first column Random_Number, this column has the similar value through-out the result.

In another words from above, we can sum-it up that the rand() function, generates a random number which is a new every time we press or execute the query.

Also, it doesn't change with rows when result-set retrieves more than one row(s). So, the problem of Mr. Ram Nath Rao doesn't resolve with the use of rand() function.

I recommended newid() to retrieve the solution of Mr. Ram's problem.

Check the following query:

Select newid()as RowId,* from Employees

Above generates following result(s):-



Note-down first column of above result(s), every row has a new value.

Now, lets try to ad-more stuff in above:

Select newid()as RowId,* from Employees order by newid()



Now, regenerate above result(s) one more time, you can get different resul(s). This is the solution of the problem.

The above is a short-description how we can get random data in SqlServer2000.

Its time to do all above at application-level, I have decided to use Vs2005:

Step(s) to use:

1. Start your Vs2005
2. Create a New Website project named its as 'Shuffle Result'.
3. Rename your default web-page to 'shuffleresults.aspx'
4. Write the following lines



5. Press F7 or choose code-view from Solution-Explorer
6. Add following sort-of-code in 'shuffleresults.aspx.cs'



7. Run the above application by pressing F5.
8. It will generate following result(s):



This is the normal output.

9. Click on 'Shuffle Results' button and check the output it might be s following :



The above is described "How one can shuffle the result-sets".

Database object naming conventions - SQLSERVER

Database object naming conventions

There is no such hard and fast rule to define database names, but I prefer to use some naming conventions which will polish your task(s):

I always usee the following(s):-

1. Tables
Tables represent the instances of an entity. For example, you store all your customer information in a table. Here, 'customer' is an entity and all the rows in the customers table represent the instances of the entity 'customer'. So, why not name your table using the entity it represents, 'Customer'. Since the table is storing 'multiple instances' of customers, make your table name a plural word.

Rules: Pascal notation; end with an ‘s’
Examples: Employees, Customers, Headlines, Groups etc.

This is a more natural way of naming tables, when compared to approaches which name tables as tblCustomers, tbl_Orders. Further, when you look at your queries it's very obvious that a particular name refers to a table, as table names are always preceded by FROM clause of the SELECT statement.

If your database deals with different logical functions and you want to group your tables according to the logical group they belong to, it won't hurt prefixing your table name with a two or three character prefix that can identify the group.

2. Views
A view is nothing but a table, for any application that is accessing it. So, the same naming convention defined above for tables, applies to views as well, but not always. Here are some exceptions:

a) Views not always represent a single entity. A view can be a combination of two tables based on a join condition, thus, effectively representing two entities. In this case, consider combining the names of both the base tables. Here's an example:

If there is a view combining two tables 'Customers' and 'Addresses', name the view as 'CustomersAddresses'. Same naming convention can be used with junction tables that are used to link two many-to-many related base tables. Most popular example is the 'TitleAuthor' table from 'Pubs' database of SQL Server.

b) Views can summarize data from existing base tables in the form of reports. You can see this type of views in the 'Northwind' database that ships with SQL Server 7.0 and above. Here's the convention that database follows. (I prefer this):

'Product Sales for 1997'
'Summary of Sales by Quarter'
'Summary of Sales by Year'

However, try to stay away from spaces within object names.

3. Stored Procedure
Stored procedures always do some work for you, they are action oriented. So, let their name describe the work they do. So, use a verb to describe the work.

This is how I would name a stored procedure that fetches me the customer details given the customer identification number:
'GetCustomerDetails'. Similarly, you could name a procedure that inserts a new customer information as 'InsertCustomerInfo'. Here are some more names based on the same convention: 'WriteAuditRecord', 'ArchiveTransactions', 'AuthorizeUser' etc.

As explained above in the case of tables, you could use a prefix, to group stored procedures also, depending upon the logical group they belong to. For example, all stored procedures that deal with 'Order processing' could be prefixed with ORD_ as shown below:

ORD_InsertOrder
ORD_InsertOrderDetails
ORD_ValidateOrder

If you are using Microsoft SQL Server, never prefix your stored procedures with 'sp_', unless you are storing the procedure in the master database. If you call a stored procedure prefixed with sp_, SQL Server always looks for this procedure in the master database. Only after checking in the master database (if not found) it searches the current database.

I do not agree with the approach of prefixing stored procedures with prefixes like 'sproc_' just to make it obvious that the object is a stored procedure. Any database developer/DBA can identify stored procedures as the procedures are always preceded by EXEC or EXECUTE keyword

Rules: sp_[_]<table/logical instance>
Examples: spOrders_GetNewOrders, spProducts_UpdateProduct

4. User Defined Functions
In Microsoft SQL Server 2000, User Defined Functions [UDFs] are almost similar to stored procedures, except for the fact that UDFs can be used in SELECT statements. Otherwise, both stored procedures and UDFs are similar. So, the naming conventions discussed above for stored procedures, apply to UDFs as well. You could even use a prefix to logically group your UDFs. For example, you could name all your string manipulation UDFs as shown below:

str_MakeProperCase
str_ParseString

5. Triggers
Though triggers are a special kind of stored procedures, it won't make sense to follow the same naming convention as we do for stored procedures.

While naming triggers we have to extend the stored procedure naming convention in two ways:

a) Triggers always depend on a base table and can't exist on their own. So, it's better to link the base table's name with the trigger name

b) Triggers are associated with one or more of the following operations: Insert, Update, Delete. So, the name of the trigger should reflect it's nature

Rules: TR_<TableName>_
Examples: TR_Orders_UpdateProducts
Notes: The use of triggers is discouraged

6. Indexes
Just like triggers, indexes also can't exist on their own and they are dependent on the underlying base tables. So, again it makes sense to include the 'name of the table' and 'column on which it's built' in the index name. Further, indexes can be of two types, clustered and nonclustered. These two types of indexes could be either unique or non-unique. So, the naming convention should take care of the index types too.
Rules: IX_<TableName>_
Examples: IX_Products_ProductID

7. Columns
Columns are attributes of an entity, that is, columns describe the properties of an entity. So, let the column names be meaningful and natural.

Here's a simplest way of naming the columns of the Customers table:

CustomerID
CustomerFirstName
CustomerAddress

As shown above, it'll be a good idea to prefix the column names with the entity that they are representing.

Here's another idea. Decide on a standard two to four character code for each table in your database and make sure it's unique in the database. For example 'Cust' for Customers table, 'Ord' for Orders tables, 'OrdD' for OrderDetails table, 'Adt' for Audit tables etc. Use this table code to prefix all the column names in that table. Advantage of this convention is that in multi-table queries involving complex joins, you don't have to worry about ambiguous column names, and don't have to use table aliases to prefix the columns. It also makes your queries more readable.

If you have to name the columns in a junction/mapping table, concatenate the table codes of mapped tables, or come up with a new code for that combination of tables.

So, here's how the CustomerID column would appear in Customers table:

Cust_CustomerID

The same CustomerID column appears in the Orders table too, but in Orders table, here's how it's named:

Ord_CustomerID

Some naming conventions even go to the extent of prefixing the column name with it's data type. But I don't like this approach, as I feel, the DBA or the developer dealing with these columns should be familiar with the data types these columns belong to.

If a column references another table’s column, name it <table name>ID
Example: The Customers table has an ID column
The Orders table should have a CustomerID column

8. Uer Defined DataTypes
User defined data types are just a wrapper around the base types provided by the database management system. They are used to maintain consistency of data types across different tables for the same attribute. For example, if the CustomerID column appears half a dozen tables, you must use the same data type for all the occurrences of the CustomerID column. This is where user defined data types come in handy. Just create a user defined data type for CustomerID and use it as the data type for all the occurrences of CustomerID column.

So, the simplest way of naming these user defined data types would be:
Column_Name + '_type'.
So, I would name the CustoerID type as:

CustomerID_type

9. Primary Keys
Primary key is the column(s) that can uniquely identify each row in a table. So, just use the column name prefixed with 'pk_' + 'Table name' for naming primary keys.

Rules: PK_<TableName>
Examples: PK_Products

10. Foreign Keys
Foreign key are used to represent the relationships between tables which are related. So, a foreign key can be considered as a link between the 'column of a referencing table' and the 'primary key column of the referenced table'.

I prefer the following naming convention for foreign keys:

fk_referencing table + referencing column_referenced table + referenced column.

Based on the above convention, I would name the foreign key which references the CustomerID column of the Customers table from the Order's tables CustomerID column as:

fk_OrdersCustomerID_CustomersCustomerID

Foreign key can be composite too, in that case, consider concatenating the column names of referencing and referenced tables while naming the foreign key. This might make the name of the foreign key lengthy, but you shouldn't be worried about it, as you will never reference this name from your code, except while creating/dropping these constraints.

Rules: FK_<TableName1>_<TableName2>
Example: FK_Products_Orderss

11. Defaults and Check Constrains
Use the column name to which these defaults/check constraints are bound to and prefix it with 'def' and 'chk' prefixes respectively for Default and Check constraints.
I would name the default constraint for OrderDate Column as def_OrderDate and the check constraint for OrderDate column as chk_OrderDate.

Rules: DF_<TableName>_
Example: DF_Products_Quantity

12. Variable
For variables that store the contents of columns, you could use the same naming convention that we used for Column names.

13. General Rules
a)Do not use spaces in the name of database objects.
b)Do not use SQL keywords as the name of database objects. In cases where this is necessary, surround the object name with brackets, such as [Year]
c)Do not prefix stored procedures with ‘sp_’ Prefix table names with the owner name.

13. Strucure
a) Each table must have a primary key
o In most cases it should be an IDENTITY column named ID
b) Normalize data to third normal form
o Do not compromise on performance to reach third normal form. Sometimes, a little denormalization results in better performance.
c) Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead
d) In VARCHAR data columns, do not default to NULL; use an empty string instead
e) Columns with default values should not allow NULLs
f) As much as possible, create stored procedures on the same database as the main tables they will be accessing.

14. Formatting
· Use upper case for all SQL keywords
o SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.
· Indent code to improve readability
· Comment code blocks that are not easily understandable
o Use single-line comment markers(--)
o Reserve multi-line comments (/*.. ..*/) for blocking out sections of code
· Use single quote characters to delimit strings.
o Nest single quotes to express a single quote or apostrophe within a string

For example, SET @sExample = 'SQL''s Authority'
· Use parentheses to increase readability
o WHERE (color=’red’ AND (size = 1 OR size = 2))
· Use BEGIN..END blocks only when multiple statements are present within a conditional code segment.
· Use one blank line to separate code sections.
· Use spaces so that expressions read like sentences.
o fillfactor = 25, not fillfactor=25
· Format JOIN operations using indents
o Also, use ANSI Joins instead of old style joins4
· Place SET statements before any executing code in the procedure.


15. Coding
· Optimize queries using the tools provided by SQL Server5
· Do not use SELECT *
· Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server
· Avoid unnecessary use of temporary tables
o Use 'Derived tables' or CTE (Common Table Expressions) wherever possible, as they
perform better.
· Avoid using <> as a comparison operator
o Use ID IN(1,3,4,5) instead of ID <> 2
· Use SET NOCOUNT ON at the beginning of stored procedures7
· Do not use cursors or application loops to do inserts8
o Instead, use INSERT INTO
· Fully qualify tables and column names in JOINs
· Fully qualify all stored procedure and table references in stored procedures.
· Do not define default values for parameters.
o If a default is needed, the front end will supply the value.
· Do not use the RECOMPILE option for stored procedures.
· Place all DECLARE statements before any other code in the procedure.
· Do not use column numbers in the ORDER BY clause.
· Do not use GOTO.
· Check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction if an error occurs
o Or use TRY/CATCH
· Do basic validations in the front-end itself during data entry
· Off-load tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server
· Always use a column list in your INSERT statements.
o This helps avoid problems when the table structure changes (like adding or dropping a column).
· Minimize the use of NULLs, as they often confuse front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form.
o Any expression that deals with NULL results in a NULL output.
o The ISNULL and COALESCE functions are helpful in dealing with NULL values.
· Do not use the identitycol or rowguidcol.
· Avoid the use of cross joins, if possible.
· When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.
· Avoid using TEXT or NTEXT datatypes for storing large textual data.9
o Use the maximum allowed characters of VARCHAR instead
· Avoid dynamic SQL statements as much as possible.10
· Access tables in the same order in your stored procedures and triggers consistently.
· Do not call functions repeatedly within your stored procedures, triggers, functions and batches.
· Default constraints must be defined at the column level.
· Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword, as these results in an index scan, which defeats the purpose of an index.
· Define all constraints, other than defaults, at the table level.
· When a result set is not needed, use syntax that does not return a result set.
· Avoid rules, database level defaults that must be bound or user-defined data types. While these are legitimate database constructs, opt for constraints and column defaults to hold the database consistent for development and conversion coding.
· Constraints that apply to more than one column must be defined at the table level.
· Use the CHAR data type for a column only when the column is non-nullable.14
· Do not use white space in identifiers.
· The RETURN statement is meant for returning the execution status only, but not data.

What is the result when comparing two nulls in SQL?

The answer is quite interesting with my stuff. Let me clear it in more writting:

1. When we compare two nulls then the result always 'false'. The main reason is the null is not a value its neither an empty nor a empty space, so the actual result is null which places as null.
2. When we compare a null with another which has some value like some int value then the result is false. The actual result is false and not null.

Consider the following examples:

--null = null is null which is false
Declare @intNull1 int
Set @intNull1 =null
Declare @intNull2 int
Set @intNull2=null
If @intNull1=@intNull2
Print 'null = null is true'
Else
Print 'null = null is false'

--Now assign some value
Set @intNull1 = 1
If @intNull1=@intNull2
Print 'null = int value is true'
Else
Print 'null = int value is false'

What is difference between Union and Union All?

I have a got a comment and a question from an anonymous for my post How to Insert multiple rows?. I must say thanks to you all to encourage my stuffs.

Union vs. Union All
In simple we can say that
1. union is used to select distinct values from two tables,where as union all is used to select all values including duplicates from the tables.
2. The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets combined using UNION must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types.By default, the UNION operator removes duplicate rows from the result set. If you use UNION ALL, all rows are included in the results and duplicates are not removed.

Lets consider following examples:

1. UNION
Select * from dbo.checkDuplicate
Union --it will leave the duplicate rows
Select * from dbo.checkDuplicate

The above querry will retrieve all rows from checkduplicate table except duplicate entries.

2. UNION ALL
Select * from dbo.checkDuplicate
Union --it will select all rows including duplicates
Select * from dbo.checkDuplicate

The above querry will select all rows from checkduplicate table including duplicate entries.

Note: One can count the number of rows using following statement:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('checkDuplicate') AND indid < 2

How to Insert Multiple Records Using Single Insert - SQL SERVER

This is very interesting question, I have received from one of my colleague - Neeraj Tomar. How can I insert multiple values in table using only one insert? .

To insert values in a table, there are many ways like :

Use HrnPayroll --Change database name with yours

--Here you can try with any table available under above chosen database

INSERT INTO dbo.employees VALUES('0001', 'Gaurav','Arora',38)
INSERT INTO dbo.employees VALUES('0005', 'Shuby','Arora',28)
INSERT INTO dbo.employees VALUES('0007', 'Shweta','Arora',29)

Go

With the help of above lines, one can achieve the task but think for numerous insert statements to do the same one should repeat the Insert multiple times.

--One can achieve the multiple insertion with the following statement:
Insert Into dbo.employees (ID, FirstName, LastName, Age)
Select '0008','Arun', 'Kumar',39
Union All
Select '0009','Vibha', 'Arora',19
Union All
Select '0018','Neeraj', 'Tomar',23
Union All
Select '0118','Laxmi', 'Farswan',24

Go

With the help of above line one can insert multiple data using single Insert statement. The above both statements are working fine when using SQLSerevr 2000/2005.

The SQLServer2008 provides more stuff to add multiple values using single Insert statement.


--The following querry will happen only with SQLServer2008:
Insert Into dbo.employees (ID, FirstName, LastName, Age)
Values('1018','Neeraj', 'Shivasam',18)
Values('1118','Neeraj', 'Huda',38)
Values('1028','Gaurav', 'Malhotra',30)
Values('1128','Abhishek', 'Prasad',30)
Values('2128','Pankaj', 'Nautiyal',36)
Values('3128','Ritesh', 'Kashyap',33)

How to delete duplicate rows from a SQL table, which has no Primary Key?

Sql provide RowCount to Delete a Duplicate rows from a table which has no Primary key:
1. In Sql2000 one can use rowcount while deleting duplicate rows
2. In Sql2005 one can use rowcount and top while deleting duplicate rows
3. But according to MicroSoft:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

---

The following statement(s) have better way to resolve above:-
CREATE TABLE dbo.checkDuplicate
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.checkDuplicate VALUES(1, 'Gaurav','Arora')
INSERT INTO dbo.checkDuplicate VALUES(2, 'Shuby','Arora')
INSERT INTO dbo.checkDuplicate VALUES(3, 'Amit','Gupta')
INSERT INTO dbo.checkDuplicate VALUES(1, 'Gaurav','Arora')
INSERT INTO dbo.checkDuplicate VALUES(5, 'Neelima','Malhotra')
INSERT INTO dbo.checkDuplicate VALUES(4, 'Shweta','Arora')
INSERT INTO dbo.checkDuplicate VALUES(4, 'Shweta','Arora')
INSERT INTO dbo.checkDuplicate VALUES(2, 'Meghna','Arora')

SELECT * FROM dbo.checkDuplicate
SELECT * FROM dbo.checkDuplicate WHERE ID = 1 AND FirstName = 'Gaurav' AND LastName = 'Arora'

--To Delete duplicates, one should use rowcont as follows[for SQL2000/2005]:
SELECT * FROM dbo.checkDuplicate

SET ROWCOUNT 1
DELETE FROM dbo.checkDuplicate WHERE ID = 1
SET ROWCOUNT 0

SELECT * FROM dbo.checkDuplicate

--In SQL2005, one should use Top as follows:
SELECT * FROM dbo.checkDuplicate
DELETE TOP(1) FROM dbo.checkDuplicate WHERE ID = 4
SELECT * FROM dbo.checkDuplicate

--Following is an update method to delete row(s)-For SQL2005:

--If you use the following command this will get a count of how many rows
--there are and delete this minus one record.

SELECT * FROM dbo.checkDuplicate

DELETE TOP (SELECT COUNT(*) -1 FROM dbo.checkDuplicate WHERE ID = 1)
FROM dbo.Emptest
WHERE ID = 1

SELECT * FROM dbo.checkDuplicate

---

Copy and Paste above snippet and try.

How to find-out a Leap Year in SQL Server?

The only solution is to check the 29days of Feb for the year. Also, there are some rules which tells a year as 'Leap year'

1. It should divisible by 4
2. It should have 29days fo February

To do achieve the same I prefer inbuilt function 'datepart', may be there are many other ways to achieve the same.

But I prefer following one :

---

Create Function dbo.fnCheckLeapYear (@year int)
returns char(3) --This will retun Yes-if Leap year, No-If doesn't
As
Begin
return(Select Case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))
when 2 then 'Yes'
else 'No'
end)
end
Go

---

The above functiona fnCheckLeapYear accepts one parameter which is year, one wants to know?

Example:Some example to check this function
1.Select dbo.fnCheckLeapYear(1986) as 'IsLeapYear?'
2.Select dbo.fnCheckLeapYear(2000) as 'IsLeapYear?'
3.Select dbo.fnCheckLeapYear(2013) as 'IsLeapYear?'
4.Select dbo.fnCheckLeapYear(2020) as 'IsLeapYear?'


Just copy and paste above function in SQL Query Analyzer and try the above examples.

Small but unforgettable Questions of SQL Server

1. How to run a query on a remote SQL Server?
Ans: To do the same use OPENROWSET:
Syntax: SELECT * FROM OPENROWSET('SQLOLEDB',REMOTE_SERVER_NAME';'sa';'password','SQL STATEMENT')
Example: Select * from OPENROWSET('SQLOLEDB','local';'sa';'','Select * from employees')

2.How can execute operating system comman from within SQL Server?
Ans: The xp_cmdshell stored procedure helps to do the same.
Example:EXEC MASTER..xp_cmdshell 'Dir C:\'

3. If we have two triggers of same type on a table, then which one will fire first?
Ans: They will fire as they are created. However we can set the trigger order by using stored procedure sp_settriggerorder, but the first and last triggers have of different types. Also define first and last triggers rest will fire as they have created.

SQL Server Script to Create Windows Directories

The script is originally written by : Tim Ford
One can attain the same with the use of two inbuild stored procedure master.sys.xp_dirtree and master.sys.xp_create_subdir

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @DBName sysname
DECLARE @DataPath nvarchar(500)
DECLARE @LogPath nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

-- 2 - Initialize variables
SET @DBName = 'Foo'
SET @DataPath = 'C:\zTest1\' + @DBName
SET @LogPath = 'C:\zTest2\' + @DBName

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @DataPath

-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree

-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath

-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath

SET NOCOUNT OFF
GO

Brief Code Overview
The core functionality in this script is based on two extended system stored procedures: master.sys.xp_dirtree and master.sys.xp_create_subdir. Let's take a look at each, individually:

• master.sys.xp_dirtree - This extended stored procedure returns all the folders within the folder that is passed into it as a parameter. It also returns the nested level of each folder found. By inserting the values returned from xp_dirtree into the temp table you can then query against it to test the existence of the folder you are attempting to create.
• master.sys.xp_create_subdir - Use this stored procedure to create a folder on either a local server or network share.

How to export data from SQL Server to Excel

Exporting data from SQL Server to Excel can be achieved in a variety of ways:

• Data Transformation Services [DTS]
• SQL Server Integration Services [SSIS]
• Bulk Copy [BCP]

Rest of above GUI operation there is another option available via the T-SQL language is the OPENROWSET command.This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL. Below outlines the full syntax available:

OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
'provider_string' }
, { [ catalog. ] [ schema. ] object
'query'
}
BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
SINGLE_BLOB SINGLE_CLOB SINGLE_NCLOB }
} )
::=
[ , CODEPAGE = { 'ACP' 'OEM' 'RAW' 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]

With the following example you can write a simple job for the operation:-
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO

How to Read the SQL Server log files using T-SQL

The system stored procedure sp_readerrorlog allows to read the contents of the SQL Server error log files directly from a query window and also allows to search for certain keywords when reading directly from the error file.
Lets check following sample: -

CREATE PROC [sys].[sp_readerrorlog](
@p1 INT = 0,
@p2 INT = NULL,
@p3 VARCHAR(255) = NULL,
@p4 VARCHAR(255) = NULL)
AS
BEGIN

IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
BEGIN
RAISERROR(15003,-1,-1, N'securityadmin')
RETURN (1)
END

IF (@p2 IS NULL)
EXEC sys.xp_readerrorlog @p1
ELSE
EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
END

This procedure takes four parameters:
Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to further refine the results
If you do not pass any parameters this will return the contents of the current error log.

Some examples:

• EXEC sp_readerrorlog 6 -returns all of the rows from the 6th archived error log
• EXEC sp_readerrorlog 6, 1, '2005' - returns just 8 rows wherever the value 2005 appears
• EXEC sp_readerrorlog 6, 1, '2005', 'exec' - returns only rows where the value '2005' and 'exec' exist

SET SHOWPLAN_TEXT

Syntax: SET SHOWPLAN_TEXT { ON OFF }
Remarks: The setting of SET SHOWPLAN_TEXT is set at execute or run time and not at parse time.

SET SHOWPLAN_TEXT ON
GO
USE pubs
SELECT * FROM roysched
WHERE title_id = 'PS1372'
GO
SET SHOWPLAN_TEXT OFF
GO

SQL Server script to rebuild all indexes for all tables and all databases

A very good and useful script for DBA

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

-- create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

-- SQL 2000 command
--DBCC DBREINDEX(@Table,' ',@fillfactor)

-- SQL 2005 command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server

A very useful script

SET NOCOUNT ON
DECLARE @operation VARCHAR(10)
DECLARE @tableName sysname
DECLARE @schemaName sysname
SET @operation = 'DROP' --ENABLE, DISABLE, DROP
SET @tableName = 'SpecialOfferProduct'
SET @schemaName = 'Sales'
DECLARE @cmd NVARCHAR(1000)
DECLARE
@FK_NAME sysname,
@FK_OBJECTID INT,
@FK_DISABLED INT,
@FK_NOT_FOR_REPLICATION INT,
@DELETE_RULE smallint,
@UPDATE_RULE smallint,
@FKTABLE_NAME sysname,
@FKTABLE_OWNER sysname,
@PKTABLE_NAME sysname,
@PKTABLE_OWNER sysname,
@FKCOLUMN_NAME sysname,
@PKCOLUMN_NAME sysname,
@CONSTRAINT_COLID INT

DECLARE cursor_fkeys CURSOR FOR
SELECT Fk.name,
Fk.OBJECT_ID,
Fk.is_disabled,
Fk.is_not_for_replication,
Fk.delete_referential_action,
Fk.update_referential_action,
OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,
schema_name(Fk.schema_id) AS Fk_table_schema,
TbR.name AS Pk_table_name,
schema_name(TbR.schema_id) Pk_table_schema
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join
WHERE TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName
OPEN cursor_fkeys
FETCH NEXT FROM cursor_fkeys

INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER

WHILE @@FETCH_STATUS = 0
BEGIN
-- create statement for enabling FK
IF @operation = 'ENABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] CHECK CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
END

-- create statement for disabling FK
IF @operation = 'DISABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] NOCHECK CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
END
-- create statement for dropping FK and also for recreating FK
IF @operation = 'DROP'
BEGIN
-- drop statement
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] DROP CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd

-- create process
DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT
-- create cursor to get FK columns
DECLARE cursor_fkeyCols CURSOR FOR
SELECT COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,
COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN
sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
WHERE TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName
ORDER BY Fk_Cl.constraint_column_id
OPEN cursor_fkeyCols
FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
SET @COUNTER = 1
SET @FKCOLUMNS = ''
SET @PKCOLUMNS = ''
WHILE @@FETCH_STATUS = 0
BEGIN

IF @COUNTER > 1
BEGIN
SET @FKCOLUMNS = @FKCOLUMNS + ','
SET @PKCOLUMNS = @PKCOLUMNS + ','
END

SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'
SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'
SET @COUNTER = @COUNTER + 1
FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
END
CLOSE cursor_fkeyCols
DEALLOCATE cursor_fkeyCols
-- generate create FK statement
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] WITH ' +
CASE @FK_DISABLED
WHEN 0 THEN ' CHECK '
WHEN 1 THEN ' NOCHECK '
END + ' ADD CONSTRAINT [' + @FK_NAME
+ '] FOREIGN KEY (' + @FKCOLUMNS
+ ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('
+ @PKCOLUMNS + ') ON UPDATE ' +
CASE @UPDATE_RULE
WHEN 0 THEN ' NO ACTION '
WHEN 1 THEN ' CASCADE '
WHEN 2 THEN ' SET_NULL '
END + ' ON DELETE ' +
CASE @DELETE_RULE
WHEN 0 THEN ' NO ACTION '
WHEN 1 THEN ' CASCADE '
WHEN 2 THEN ' SET_NULL '
END + '' +
CASE @FK_NOT_FOR_REPLICATION
WHEN 0 THEN ''
WHEN 1 THEN ' NOT FOR REPLICATION '
END

PRINT @cmd
END
FETCH NEXT FROM cursor_fkeys
INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER
END
CLOSE cursor_fkeys
DEALLOCATE cursor_fkeys

Running the script

• Table: SpecialOfferProduct
• Schema: Sales
• Operation: DROP

Example:

ALTER TABLE [Sales].[SalesOrderDetail] DROP CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
ALTER TABLE [Sales].[SalesOrderDetail] WITH NOCHECK ADD CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID] FOREIGN KEY ([SpecialOfferID],[ProductID]) REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID],[ProductID]) ON UPDATE NO ACTION ON DELETE NO ACTION

To check the powe of script and make it more useful, you can convert it into stored procedure