:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • Aug 23 / 2008
  • 2
dbDigger, T-SQL Tips and Tricks

Copy or move table from one Database to another

Suppose we have a table Tab1 in database DB1. And it is required to move the Tab1 to another database DB2. So it is assumed that DB2 already exists with no or some tables in it. First of all use following T-SQL command to copy table in to destination database with all data in it.

 INTO DB2..Tab1  
 FROM DB1..Tab1  

Now if you have to just copy the table then its over but if you are required to move the table then use following command to drop the table from source database.


As you can see that it is done by select statement, so no affiliated objects like triggers, views etc will be copied.

  • Aug 12 / 2008
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization, T-SQL Tips and Tricks, User Defined Stored Procedures

DBA Best Practices for Queries and Stored Procedures

Queries and stored procedures are close companions of a DBA. So make your life easy by following the baseline given below, while writing queries and stored procedures by tomorrow.

  1. Maintain all code in a source control system.
  2. Keep transactions as short as possible. This reduces locking and increases application concurrency, which helps to boost performance.
  3. Avoid using query hints unless you know exactly what you are doing, and you have verified that the hint actually boosts performance.
  4. Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure.
  5. Use the least restrictive transaction isolation level possible for your user connection, instead of always using the default READ COMMITTED.
  6. SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.
  7. Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them.
  8. For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner. object_name or schema_owner.object_ name.
  9. One way to help ensure that stored procedures query plans are reused from execution to execution of the same stored procedure is to ensure that any user connections information, SET options, database options, or SQL Server configuration options don’t change from execution to execution of the same stored procedure. If they do change, then SQL Server may consider these same stored procedures to be different, and not be able to reuse the current query plan stored in cache.
  10. Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. Stored procedures offer many benefits, including:
  11. Reduced network traffic and latency, boosting application performance.
  12. Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
  13. Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.
  14. Stored procedures help promote code reuse. While this does not directly boost an application’s performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
  15. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result sets columns). This saves developer time.
  16. Stored procedures provide better security to your data.

A part chosen from SQL Server DBA best practices By Brad M.mcGehee

  • Aug 06 / 2008
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts, T-SQL Tips and Tricks

Search for a column in all tables of a database

Today i read an interesting question on a blog, that how to search for a column in all tables of a database. Following script was given by author to search for columns with name like employeeID in adventureworks database.

USE AdventureWorks  
 SELECT t.name AS table_name,  
 c.name AS column_name  
 FROM sys.tables AS t  
 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID  
 WHERE c.name LIKE '%EmployeeID%'  
 ORDER BY SCHEMA_NAME, table_name  

It served the purpose but i have a more simple approach for same purpose. My script generates same result in a more simple and flexible way

USE AdventureWorks  
 SELECT Table_Schema, Table_Name, Column_Name, Data_Type  
 FROM information_schema.columns  
 WHERE table_name in ( select name from adventureworks..sysobjects  
 where xtype = 'U' )  
 and column_name like '%EmployeeID%'  
 order by table_schema, table_name  

So both may be used as convenient.

Consult us to explore the Databases. Contact us