IRohitable | April 2012

Tool Pick #6: SQL Server Tools

by Rohit 28. April 2012 22:49

This week I have SQL Server tools for you.

SQL Scripter: Powerful tool for Microsoft SQL Server 7.0/2000/2005/2008 database administrators and developers to generate data scripts in a readable and executable T-SQL format.


NetWrix SQL Server Change Reporter: Monitor and review administrative changes on SQL servers and at database levels. Detect early all unauthorized and unwanted changes that can lead to server and database downtime. The Freeware Edition has few limitations.


SQL Sentry Plan Explorer: A FREE tool that builds upon the graphical plan view in SQL Server Management Studio (SSMS) to make query plan analysis more efficient. It is a lightweight standalone app that contains many of the plan analysis features introduced in SQL Sentry v6. It does not require a collector service or database.


FlySpeed SQL Query: Free SQL query tool for any database. It facilitates the process of building SQL by letting you quickly drag and drop tables to create queries, from simple to complex, and combine visual query building with direct SQL text editing.

Tags: , ,

Database | SQL Server | Tool Pick

Iterating through all the SQL Server databases and tables to execute any command.

by Rohit 25. April 2012 16:04
-

Sometimes you may want to execute a command against all the databases in an instance or all the tables in any database. SQL Server (2005 and above) contains two undocumented stored procedures, viz. sp_MSforeachdb and sp_MSforeacheable. To illustrate their use, I am taking a simple example to display all the databases and tables with the total space occupied.

What is the job of sp_MSforeachdb and sp_MSforeachtable?

Both the stored procedures are designed for iteration. With each iteration you can execute a command.

sp_MSforeachdb iterates through all the databases in the current instance, while sp_MSforeachtable iterates through all the tables in a database to perform a command.

You can view the details of each stored procedure by executing:

sp_help <name of system stored procedure>

from the query window. Below is the output of each:

 

Parameter Details
@command1 nvarchar(2000) field specifying the first command to run against each database. Can not be blank.
@replacechar nchar(1) field specifying the character in the command parameters to replace with the database name. Defaults: ?.
@command2 nvarchar(2000) field specifying the 2nd command to run against each database.
@command3 nvarchar(2000) field specifying the 3rd command to run against each database.
@precommand nvarchar(2000) field specifying a command to be run prior any commands against any database.
@postcommand nvarchar(2000) field specifying a command to be run after running all commands.

 

 

Script to display all databases with sizes in an instance using: sp_MSforeachdb

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

An alternate approach is by ignoring the system databases from the list. In that case:

EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
BACKUP DATABASE ? TO DISK = ''D:?.bak, WITH INIT'''

Script to display all the tables with total number of rows in a database using: sp_MSforeachtable

CREATE TABLE #t 
	( 
		Table_Name VARCHAR(80),
		Total_Rows CHAR(11),
		Reserved_Space VARCHAR(18), 
		Data_Size VARCHAR(18), 
		Index_Size VARCHAR(18),
		Unused_Space VARCHAR(18)
	) 

	INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

	SELECT * FROM #t ORDER BY Data_Size DESC

	DROP TABLE #t /* Drop Temp Table */

Note the ? used in all the code snippets above. It represents the name of the database or table returned at each iteration of the loop.

Disadvantages:

Both the stored procedures discussed above have a number of disadvantages. 

  1. Iteration uses Loop which is not recommended because Loops are harder to manage in databases and are slower than SET-based constructs.
  2. It is found that sp_MSforeachdb skips few databases from the list. Aaron Bertrand (Blog | twitter) writes in his blog about these issues. He also suggests a more reliable stored procedure to use instead of sp_MSforeachdb discussed above.

Tags: , ,

Database | Technology

Column Store Indexes in SQL Server 2012 - An Insight.

by Rohit 15. April 2012 18:58

SQL Server 2012 introduces a new index type known as "Column Store Index". This feature has been introduced with BI and OLAP in mind. Before diving into how Column Index works and how to use it, we need to take our focus back to the basics of Index in SQL Server.

What are Indexes?

Indexes form the most important element in the performance enhancement list. Even though performance depends on other factors as well, but Indexing is one of the most important but often neglected factor.

The index provides a fast way to look up data based on the values in the column(s) on which index is created.Traditional row-based indexes, as the name suggests, stores rows of data on a page. A Page is the fundamental unit of data storage in SQL Server. An index is made up of various pages that are organized in a B-Tree structure. This structure is hierarchical in nature where the root node is at the top with leaf nodes branched below.

 

 

Typical row-based indexes in SQL Server are of two types:

  1. Clustered Indexes
  2. Non-clustered Indexes

 Clustered Index:

A Clustered index determines the physical order of data in a table and therefore a table can have only one Clustered Index. A clustered index stores data rows at the leaf level of the index structure.Clustered Indexes are arranged either in ascending or descending order.

Non-Clustered Index:

The leaf node in a non-clustered index structure stores the location of the actual data row. These act as pointers to the actual row and therefore the query engine has one step more to do while locating a row.

Both the traditional indexes, Clustered and Non-Clustered, are based on B-Tree. B-Tree Indexes are fast when you need to scan the entire table and filter out rows.

How Column Store Index differs from Row Store Index?

A Column Store Index stores a single column in a separate SQL Server Page. As opposed to Row Store Index mentioned above where collection of rows reside in a Page, in a Column Store Index, a column is allocated a separate Page.

 More...

About Rohit Prakash

Software Craftsman @ Fortune 100 Company. Technical Reviewer of few programming books.

A Single, Writer, Reader and Adventurist.

You can reach me at:

rohit [at] irohitable.com

-----------------------------------------

LinkedIn:

in.linkedin.com/in/rohitpkhare

Twitter:

@BuzzRohit

-----------------------------------------

Month List

Protected by Copyscape Web Plagiarism Software