Define Keyboard Shortcuts in SQL Server Management Studio to Simplfy T-SQL Commands

Did you get tired of coding the same statement like "SELECT * FROM ...", or "sp_helptext ...", or "sp_help ...", etc ?
Why don't you use SQL Server Management Studio Query Window keyboard shortcuts?
Just highlight the SQL Server object name on Query Editor window and press shortcut keys.
Follow the following menu options in Microsoft SQL Server Management Studio IDE.
Tools > Options...
When Options configuration dialog screen is displayed drill-through Environment > Keyboard
SQL Server professionals will see a screen similar to shown below. The default keyboard shortcuts defined are :
Alt+F1 for sp_help,
Ctrl+1 for sp_who, and
Ctrl+2 for sp_lock

Note : One important thing about this keyboard shortcuts is if you make a change on the list you must open a new Query Editor window to see the changes affected.

SQL Query Performance Optimization using SET Statistics in SQL Server

SQL query performance optimization in SQL Server is a vital task for SQL developers and SQL Server database administrators. Especially if the database application is showing poor performance, for sql performance optimization database professionals should use helping tools to identify poor code, bottlenecks and missconfigured objects in the SQL database.
Configuring database statistics using "SET STATISTICS" is one of the easiest methods and database tools for developers trying to seek what is causing slow performance of a T-SQL code. Either a t-sql script, or a SQL Server object like an SQL view or SQL Server stored procedure the following script will give starting tips for sql query performance optimization task.



Enalbe time, I/O and profile statistics using the following Set Statistics commands.
Then execute the target sql script or sql procedure which you want to optimize.
Then revert back the configuration settings back to original values.

set statistics time on
go
set statistics io on
go
set statistics profile on
go

--- Execute SQL Query or SQL Stored Procedure
EXEC sp_SQLQueryOptimizationSample
go

set statistics time off
go
set statistics io off
go
set statistics profile off
go

SQL Assistant SQL Formatter tool for SQL Server Database Developers

Using SQL formatter tool for formatting sql code is very important to make sql code reading easier. I believe many sql developers searched for a formatting sql code tool. I ended to suffer from unformatted sql codes within hundreds of lines of t-sql code in stored procedures with SQL Assistant SQL Server tool.
SQL Assistant is a SQL Server tool from SoftTree Technologies. In fact database developers can this database development tool not only for SQL Server but for Oracle, DB2, MS Access, MySQL, etc. Although I gave sample screenshots from SQL Server, I'm also using this sql tool on Oracle 10g Express with PL/SQL Developer from Allround Automations. So SQL Assistant tool for SQL is supporting all well known database products, and is a simple to use and yet powerful database tool.
The installation process is short and simple. During the installation you choose Add-in options to add this sql tool as an add-in to other database development applications and management tools like SQL Server Management Studio, Microsoft Visual Studio, MS Access, etc. If you want to download SQL Assistant, you can download it from Brothersoft

SQL Tools for SQL Server

T-SQL developers can have an idea about the SQL Assistant by just looking at the menu selections in the SQL Server Management Studio IDE.
Code snippets, SQL syntax check, format code, refactoring, test data generation, unit test, compare code are all very useful features of this SQL Server tool. All these features will definitely add to sql development quality and speed for a rapid database application development.

SQL Intellisense tool for SQL Server

The first feature of this SQL Server tool is the intellisense support while you develop your t-sql code in the SQL Server Management Studio IDE. Although, SQL Server 2008 and later has build-in intellisense support for transact-sql development but SQL Assistant impressed me with its features. For example, when you write "SELECT" then a list of tables is displayed and when you click on the table name the table columns are listed. You mark the checkboxes beside the column names then that is all. You have a sql SELECT statement with column names and tables are completed by this SQL Server intellisense tool.

SQL Formatter for Readable SQL Code

The most important feature for me in this SQL tool for SQL Server, is sql formatting function of the SQL Assistance. Because as I said I had experienced many times situations where I had to understand how a sql script is build before understanding what is it doing. I remember I formatted every field in the select part and formatted every table in the from clause, join columns, where clauses, etc to make the sql code readable. I'm happy to find this SQL Server tool for sql format within my t-sql codes. Just pressing Ctrl+F11 key combination on the SSMS query editor screen or following the menu options "SQL Assistant > Format Code > Format", sql developers can easily make sql code more readable so understandable.
To format sql code will also bring a sql code standardization to development teams building applications on SQL Server databases.

Rename Stored Procedures or any SQL Object using SQL Server Refactoring Tool

One of the most important features of SQL Assistant tool for SQL Server is its refactoring capabilities in SQL Server. SQL developers and database administrators can easily rename a database object like a stored procedure, a user defined function using SQL Assistant refactoring tool. Using this sql tool for refactoring, database professionals can set a new name for the database object in the first step of refactoring process. And in the second step, the refactoring tool will list the database objects that will be affected by the name change and with your approval alters the dependent objects to use the new object name for reference.
For example, in the following screenshot I just change the input parameter name of a SQL Server split function for demonstrating refactoring in SQL Server with SQL Assistant tool

In the second step of refactoring process, the code places where will be changed by refactoring tool is displayed as follows.

It is easy for database developers to rename a database object without any risk of losing reference in other dependent objects using SQL Assistant refactoring tool

SQL LEAD() Function in SQL Server 2011 for Calculating Next Value

 http://www.kodyaz.com/t-sql/lead-function-in-sql-server-2011-for-next-value.aspx

SQL LAG() Function in SQL Server 2011 for Calculating Preivous Value

SQL LAG() function is one of the recent enhancements among SQL Analytic Functions introduced with Denali, CTP3 for SQL Server 2011 developers. What SQL Server Lag() function does is returning simply the previousnext Nth row value in an order. The order of the rows can be grouped by using the Partition By clause for powering the SQL Lag() function.
Here is the SQL Analytic LAG() function syntax for T-SQL developers

LAG (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)

The Lag() function input offset argument determines the number of previous rows in order that the SQL Engine will read from. Offset input parameter is optional. If nothing is provided, then the default value 1 will be used in LAG() function
The default argument sets the value which will be returned if SQL Lag() function returns nothing.


Here is our first SQL Server Lag() function example. If you look at the returned result set of the below SQL query, you will realize that all data from AnalyticFunctions table is listed. Besides the value of the previous row when ordered by Id column with the same Category value is displayed in PreviousValue column.
You see, the Lag() function offset argument is 1 as default value. This returns the previous row value. And the Lag() function default argument is NULL. Since the first row for each category does not have a previous row, the SQL LAG() function cannot calculate the PreviousValue column, and returns the default NULL value.

SELECT
 *,
 LAG(Value, 1, NULL) OVER (Partition By Category ORDER BY Id) AS PreviousValue
FROM AnalyticFunctions

how to configure sp_send_dbmail

sp_configure
go
reconfigure
go with overridesp_configure
go
reconfigure

Configuration Steps of SQL Server 2005 Database Mail

Right click on the Database Mail in order to display the context menu and select the Configure Database Mail menu item.


Database Mail Configuration Wizard runs and displays a welcome message.
Database Mail Configuration Wizard is used to enable Database Mail, manage security, and configure Database Mail system parameters

When you click Next

TITLE: Microsoft SQL Server Management Studio
------------------------------

The Database Mail feature is not available. Would you like to enable this feature?

You can enable the Database Mail feature by pressing the "Yes" button or by running the below sql sp_configure mail script
USE Master
GO
sp_configure 'show advanced options', 1
GO
reconfigure with override
GO
sp_configure 'Database Mail XPs', 1
GO
reconfigure
GO
sp_configure 'show advanced options', 0
GO


Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Database Mail XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

If you run sp_configure command on the master database, you will see that the run_value for the "Database Mail XPs" is changed from 0 to 1 to enable this feature.
sp_configure

First step is creating a new profile. Enter a profile name and a description for the new profile. Then click the Add... button to complete the task.







Click Next button

Use this page to configure a public profile.

Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.

A profile may be a default profile. In this case, users or roles can send e-mail using the profile without explicitly specifying the profile. If the user or role sending the e-mail message has a default private profile, Database Mail uses that profile. If the user or role has no default private profile, sp_send_dbmail uses the default public profile for the msdb database. If there is no default private profile for the user or role and no default public profile for the database, sp_send_dbmail returns an error. Only one profile can be marked as the default profile

To send Database Mail users must also be a added to the DatabaseMailUsersRole database role in the msdb database using Management Studio or sp_addrolemember.


Use this page to configure a private profile.

Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.

A profile may be a default profile. In this case, users or roles can send e-mail using the profile without explicitly specifying the profile. If the user or role sending the e-mail message has a default private profile, Database Mail uses that profile. If the user or role has no default private profile, sp_send_dbmail uses the default public profile for the msdb database. If there is no default private profile for the user or role and no default public profile for the database, sp_send_dbmail returns an error.

Note:
To send Database Mail users must also be a added to the DatabaseMailUsersRole database role in the msdb database using Management Studio or sp_addrolemember.


Use this page to specify Database Mail system parameters. View the system parameters and the current value of each parameter. Select a parameter to view a short description in the information pane.

Options
Account Retry Attempts
The number of times that the external mail process attempts to send the e-mail message using each account in the specified profile.

Account Retry Delay (seconds)
The amount of time, in seconds, for the external mail process to wait after it tries to deliver a message using all accounts in the profile before it attempts all accounts again.

Maximum File Size (Bytes)
The maximum size of an attachment, in bytes.

Prohibited Attachment File Extensions
A comma-separated list of extensions which cannot be sent as an attachment to an e-mail message. Click the browse button (...) to add additional extensions.

Database Mail Executable Minimum Lifetime (seconds)
The minimum amount of time, in seconds, that the external mail process remains active. The process remains active as long as there are e-mails in the Database Mail queue. This parameter specifies the time the process remains active if there are no messages to process.

Logging level
Specify which messages are recorded in the Database Mail log. Possible values are:

Normal - logs only errors
Extended - logs errors, warnings, and informational messages
Verbose - logs errors, warnings, informational messages, success messages, and additional internal messages. Use verbose logging for troubleshooting.

Default value is Extended.

Reset All
Select this option to reset the values on the page to the default values.

Additional Information about the Retry Options
The sequence number in the Database Mail profile determines the order in which Database Mail uses accounts in the profile. This is configured by using the Move Up and Move Down buttons when configuring a profile in the Database Mail Configuration Wizard. For a new e-mail message, Database Mail starts with the account that has the lowest sequence number. Should that account fail, Database Mail uses the account with the next highest sequence number, and so on until either Database Mail sends the message successfully, or the account with the highest sequence number fails. If the account with the highest sequence number fails, the Database Mail pauses attempts to send the mail for the amount of time configured in the Account Retry Delay parameter of sysmail_configure_sp, then starts the process of attempting to send the mail again, starting with the lowest sequence number. Use the Account Retry Attempts parameter of sysmail_configure_sp, to configure the number of times that the external mail process attempts to send the e-mail message using each account in the specified profile.

If more than one account exists with the same sequence number, Database Mail will only use one of those accounts for a given e-mail message. In this case, Database Mail makes no guarantees as to which of the accounts is used for that sequence number or that the same account is used from message to message.






Run the below sql command from a query editor.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMailProfile', @recipients = 'recipient@mail.com', @body = 'This is a sample email sent via Database Mail from a t-sql code script', @subject = 'Subject: Database Mail'
"Mail queued." response will be returned.




If you run the sp_send_dbmail stored procedure before enabling the Database Mail feature, you will get a response similar to below.
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

In such a case use sp_configure to set Database Mail XPs properties or enable the Database Mail feature using the Configure Database Mail Wizard.
If you enabled the database mail but getting the below error message, you might be using a wrong profile name in the sp_send_dbmail command.
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid

In this case, run the Configure Database Mail Wizard. Then select "Manage Database Mail accounts and profiles" option in the Select Configuration

EXEC msdb.dbo.sp_send_dbmail @recipients='suruthikrishna@gmail.com',@body='yu', @subject ='Test Email from sandbox 94',@profile_name ='dbmail',@body_format = 'HTML' 'Database Mail XPs',1 'show advanced options',1

Gmail SMTP Server Name and Port Details

Gmail SMTP Server Name: ‘smtp.gmail.com’
Gmail SMTP Port for SSL: ‘465’
Gmail SMTP Port for TLS/STARTTLS: ‘587’
Uses Authentication: Yes

DBCC

DBCC DBINFO (s)DBCC DBINFO (master)DBCC DBTABLE (st)DBCC HELP ( 'CHECKIDENT')DBCC IND ('xlis_ods', 'SBSB', 0)EXEC master..sp_MSgetversionSELECT@@versionEXEC master..xp_subdirs 'C:\Documents and Settings\skeerthi\Desktop'go
EXEC master..xp_dirtree 'C:\Documents and Settings\skeerthi\Desktop'EXEC master..xp_getnetnameEXEC master..xp_fileexist 'C:\Documents and Settings\skeerthi\Desktop\Create script for Sproc from VSS dwh\sp_Build_Member_Span_Step1.sql'EXEC master..xp_fileexist 'C:\Documents and Settings\skeerthi\Desktop\Create script for Sproc from VSS dwh\sp_Build_Member_Span_Step9.sql'EXEC master..xp_enumgroupsEXEC master..xp_enumerrorlogs
EXEC master..xp_enum_oledb_providerssp_MSforeachdb
sp_MSforeachtableexec sp_MShelpcolumns 'Sbsb'exec sp_MShelpindex 'Sbsb'execsp_MSdependencies 'CLM_HOSP_COND_CD'EXEC sp_MShelptype -- returns data typessp_MSindexspace 'CLM_HOSP_COND_CD'sp_MStablespace 'chkpoints1'