Category: SQL-Tips and Tricks

SQL Server – Stored Procedure with Output Parameters

SQL, SQL-Advanced, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

In SQL Server, there are two ways with we can pass parameters to procedures. INPut parameter OUTPut parameter INPut parameter These types of parameters are used to send values to stored procedures. OUTPut parameter These types of parameters are used to get values from stored procedures. This is similar to a return type in functions. In this post we will try to understand how stored procedure returns the parameter Example — Create a table CREATE TABLE myTable ( ID INT

» Read more
,

SQL Server – Stored Procedure – WITH RECOMPILE

SQL, SQL-Advanced, SQL-Scripts, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

Stored Procedure has ability to execute faster than the same T-SQL statements executed individually because SQL Server recorded the query execution plan for Stored Procedure. In case you need to execute the stored procedure with different execution plan then use WITH RECOMPILE options Different ways to use the WITH RECOMPILE options You can create the stored procedure using WITH RECOMPILE option. This way every time SQL Server uses the recompile the execution plan. CREATE PROCEDURE sp_orders ( @StartDate Datetime, @EndDate

» Read more
, ,

SQL Server – Stop/Close a Server-Side Trace

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

This function “sp_trace_setstatus” modifies the current state of the specified trace. Syntax sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status Example –See the Ruuning trace SELECT * FROM ::fn_trace_getinfo(NULL) — Stop thr Running Trace EXEC sp_trace_setstatus @traceid = 1 , @status = 0 — Delete the Running trace EXEC sp_trace_setstatus @traceid = 1 , @status = 2 Note: 0 = Stops the specified trace. 1 = Starts the specified trace. 2 = Closes the specified trace

» Read more
, ,

SQL Server – System Trace Functions

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

SQL Server provides the following system trace functions that are useful if you are working with trace. fn_trace_getinfo fn_trace_gettable fn_trace_getfilterinfo fn_trace_geteventinfo fn_trace_getinfo This function returns the information of a specified trace or all running traces. Syntax fn_trace_getinfo ( { trace_id | NULL | 0 | DEFAULT } ) Example See all the Running Trace SELECT * FROM fn_trace_getinfo(NULL) fn_trace_gettable This function export the Trace file data into Table. Syntax fn_trace_gettable ( ‘filename’, number_files ) Example See the example here fn_trace_getfilterinfo

» Read more
, , , ,

SQL Server – Find the expensive queries with Server Side Trace

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

With the help of this post sharing with you the procedure to find the expensive queries with Server Side trace. First we see how to create a server side trace script. Run the Profiler Trace as mentioned here Go to the File >> Export >> Script Trace Definition >> For SQL Server 2000 or For SQL Server 2005- 2008R2   You will get a Script for Server Side Trace as /****************************************************/ /* Created by: SQL Profiler */ /* Date: 01/11/2011

» Read more
,

SQL Server – File stats using Fn_virtualfilestats

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

Fn_virtualfilestats() is in-build function in the SQL Server and really useful for understanding the amount of I/O within individual files on your SQL Server system. It returns I/O statistics for database files, including log files. Syntax fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } ) Example 1 File statistical information for a database select * from :: fn_virtualfilestats (1,1)   Example 2 File statistical information for all databases and files select * from :: fn_virtualfilestats

» Read more
,

SQL Server – Capture the Wait Stats to table for analysis

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

We can find the SQL Server wait stats by using DBCC SQLPERF (waitstats)   Now we need to capture all these wait stats to table for analysis –Create a temp table for waitstats CREATE TABLE #waitstats ( wait_type VARCHAR(500), Request REAL, wait_time REAL, signal_wait_time REAL ) — Insert the waitstats to table INSERT INTO #waitstats EXEC('DBCC SQLPERF(waitstats)') –Now you can analysis waitstats SELECT * FROM #waitstats   Hope this will help you to analyze the wait stats.

» Read more
,

SQL Server – Enable / Configure Replication Alerts

SQL, SQL-Advanced, SQL-Replication, SQL-Tips and Tricks, Varinder Sandhu

Replication offers the following predefined alerts, which can be configured to respond to replication events: Replication: agent success Replication: agent failure Replication: agent retry Replication: expired subscription dropped Replication: Subscription reinitialized after validation failure Replication: Subscriber has failed data validation Replication: Subscriber has passed data validation Replication: agent custom shutdown Configure these alerts from the Alerts folder in Microsoft SQL Server Management Studio or the Warnings tab in Replication Monitor Let us take a look how can we enable/Configure Replication Alerts;

» Read more
,

SQL Server – User Defined Group

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

I had requirement to make the User Defined Group in Reports (i.e. Crystal Reports) Example: CREATE TABLE test_custom_group ( id INT, dept VARCHAR(20), detail VARCHAR(30), salary NUMERIC(5,2) ) Created a Function CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20), grp varchar(10) ) AS BEGIN INSERT @temp_table(sno,dept,grp ) SELECT 1,'C','A' UNION all SELECT 2,'A','A' UNION all SELECT 3,'D','B' UNION all SELECT 4,'E','B' UNION all SELECT 5,'B','B' RETURN END Created a Store Procedure USE test_db IF EXISTS

» Read more
, ,

SQL Server – User Defined Sort Order

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

I had requirement to get the values with Use Defined Sort order. Here is simple way to get User Defined Sort order Example: Created a table as : CREATE TABLE sort_order ( id INT, dept VARCHAR(20), detail VARCHAR(30) ) Inserted few rows in table as Requirement: Now we want to change the sort order and new sorted output should be: REQUIRED OUTPUT: For this created a function: CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20)

» Read more
, , ,
1 2 3 4 5 6 … 8