How to deploy database in production server

Few ways ....
1) If you have all the scripts for all objects.. then create new db run the scripts.
2) Backup database and restore on to production
3) Deattach-reattach to production.

Exclusive lock on a particular table for select * from statement

select * from statement gets an exclusive lock on table which is having less than 10K rows . Activity monitor shows lock by processes for an objects named dbname.dbo.spt_fallback_db.
spt_fallback_db is in master and is a system table.
The issue get solved when reorganized the index in that table.
The optimizer will lock objects if it deems it will yield the best query performance. Out of date statistics and a fragmented index are a prime combo for this type of thing

Find Text in all columns of all tables in a Database

CREATE PROCEDURE GetColumnVal
@TEXT VARCHAR
AS
BEGIN
DECLARE @TABLES TABLE
([id] INT IDENTITY(1,1),
TableName VARCHAR(500),
ColumnName VARCHAR(500)
)
INSERT INTO @TABLES(TableName, ColumnName)
SELECT O.[NAME], C.[NAME]
FROM SYSOBJECTS O
JOIN SYSCOLUMNS C
ON C.ID = O.ID
WHERE O.XTYPE = 'U'
AND C.XTYPE NOT IN
(
127 --bigint
, 173 --binary
, 104 --bit
, 61 --datetime
, 106 --decimal
, 62 --float
, 34 --image
, 56 --int
, 60 --money
, 108 --numeric
, 59 --real
, 58 --smalldatetime
, 52 --smallint
, 122 --smallmoney
, 189 --timestamp
, 48 --tinyint
, 36 --uniqueidentifier
, 165 --varbinary
)
ORDER BY O.[NAME], C.[NAME]
IF EXISTS (SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#TMPREPORT%')
BEGIN
DROP TABLE #TMPREPORT
END
CREATE TABLE #TMPREPORT(COUNTER INT, TABLENAME VARCHAR(500), COLUMNNAME VARCHAR(500))
DECLARE @CNTR INT, @POS INT, @TableName VARCHAR(500), @ColumnName VARCHAR(500), @SQL VARCHAR(8000)
SELECT @POS = 1, @CNTR = MAX([ID]), @TableName = '', @ColumnName = ''
FROM @TABLES
--SELECT @POS, @CNTR, * FROM @TABLES
WHILE @POS <= @CNTR
BEGIN
SELECT @TableName = TableName, @ColumnName = ColumnName
FROM @TABLES
WHERE [ID] = @POS
SELECT @SQL = 'SELECT COUNT(*), ''' + @TABLENAME + ''' [TABLE],''' + @COLUMNNAME + '''[COLUMN] FROM ' + @TableName + ' WHERE CAST(' + @ColumnName + ' AS VARCHAR) LIKE ''%' + @TEXT + '%'''
--PRINT @SQL
BEGIN TRY
INSERT INTO #TMPREPORT(COUNTER, TABLENAME, COLUMNNAME)
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT @@ERROR
PRINT @SQL
END CATCH
SELECT @POS = @POS + 1
END
SELECT * FROM #TMPREPORT WHERE COUNTER > 0
DROP TABLE #TMPREPORT
End

Does the order of columns in an index matter?

A single column index is fairly straightforward to understand. You may have heard it compared to the index in the back of a technical book. When you want to find some information in the book, say DBCC INPUTBUFFER, you turn to the index in the back of the book and look up DBCC INPUTBUFFER. The index doesn't actually contain the information on DBCC INPUTBUFFER, it has a pointer to the page in the book where the command is described. So, what do you do? You turn to that page and read all about how DBCC INPUTBUFFER may be used. This is a good analogy for a single column non-clustered index.

Composite Indexes
In Microsoft SQL Server, you can also create an index that contains more than one column. These are known as composite indexes. A good analogy for a composite index is the telephone book.
A telephone book lists every individual in the area who has a publicly available telephone number. It's organized not by one column, but two: last name, first name. (Ignoring the middle initial that is sometimes listed but most often treated as an extension of the person's first name). If you wanted to look up someone in the telephone book, you'd first navigate to the last name and then the first name. For example to find Jake Smith, you'd first locate the Smiths, then within the Smiths, you'd find Jake. The same holds true for a composite SQL Server index.
This is all good to know, but how does it really affect query resolution?
Well, let's consider an example. Let's assume you have a Customers table as described below.CREATE TABLE Customers ( Customer_ID INT NOT NULL IDENTITY(1,1) ,Last_Name VARCHAR(20) NOT NULL ,First_Name VARCHAR(20) NOT NULL ,Email_Address VARCHAR(50) NULL )
It has a clustered index on Customer_ID and composite index on the Last_Name, First_Name columns as shown below.CREATE CLUSTERED INDEX ix_Customer_ID ON Customers(Customer_ID)CREATE INDEX ix_Customer_Name ON Customers(Last_Name, First_Name)
Finding a specific row
To find a specific row, we could execute the following query.SELECT *FROM CustomersWHERE Last_Name = 'smith' AND First_Name = 'Jake'
It should be pretty obvious that the ix_Customer_Name index would work well to satisfy this query. A quick look at the execution plan confirms our expectations.

Finding a last name
Now, let's broaden our search a bit to retrieve all customers whose last name is Smith. The following query may be executed for this. SELECT *FROM CustomersWHERE Last_Name = 'smith'
Looking at the query execution plan, we can see that SQL Server did indeed use the ix_Customer_Name composite index; it performed an index seek to find the rows that satisfied the query, then it used a Key Lookup to retrieve the non-indexed column information. You'll notice that this time, however, more work was expended in the Key Lookup than in the Index Seek.

Returning to our telephone book analogy, we can see why this index was deemed efficient by the Query Optimizer. To find all of the Smiths in the telephone book, we'd navigate to the page that contains the first Smith and keep moving forward until we found something other than Smith.
Finding a first name
Now, let's see what happens if we need to find all people who have a first name of Jake. Let's execute the following query.SELECT *FROM CustomersWHERE First_Name = 'Jake'
This yields the following query execution plan.

Notice that this time, SQL Server used a Clustered Index Scan to resolve the query. This is tantamount to a complete table scan. It did not use our ix_Customer_Name index.
Once again, returning to the telephone book example, we can see why. Think about find all of the Jakes in the telephone book. You'd have to start on the first page of the book and look through every entry. Why? Because it's not organized by first name; it's organized by last name, first name.
Does this mean that composite indexes are worthless? No. They have great value to SQL Server. In our first example we were able to use the ix_Customer_Name index to navigate directly to Smith, Jake. We just need to give considerable forethought to the kinds of queries our applications will be submitting to our server and create the appropriate indexes to handle those queries.
Cheers!

Nested Stored Procedure with Begin Transaction and Rollback

Nested Stored Procedure with Begin Transaction and Rollback: It is mentionned in the SQL Documentation that a Rollback is full rollback (all levels) are rolled back, in contrast to the commit that only commits the current level. you can't execute a rollback anywhere other than the outermost transaction if you want to avoid errors. BOL says:Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback are in fact rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back.

When you use nested sp with transactions, that is you using nested transaction. When use nested transactions without name it, when you call rollback tran, it will rollback the outer most transaction. So it set @@TranCount to 0. You can try the code below to test:

begin tran
print @@trancount
begin tran
print @@trancount
begin tran
print @@trancount
commit tran
print @@trancount
begin tran
print @@trancount
rollback tran
print @@trancount

You can't rollback (but commit OK) nested (inner) transaction with name. Because only the first (outermost) transaction is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error.
Test this code:begin tran T1
print @@trancount
begin tran T2
print @@trancount
begin tran T3print @@trancount
commit tran T3 --OK
print @@trancount
rollback tran T2 --Error
print @@trancount
So, if you want to rollback at inner trans, use :
SAVE TRANSACTION MyName IF (@@error <> 0)BEGIN ROLLBACK TRANSACTION MyNameEND