SQL Archives - SQL Authority with Pinal Dave

SQL SERVER – Finding Tables with Primary or Foreign Keys

April 29, 2015

If you want to know if a table has a primary key or foreign key, you can find out this in many ways. Let us create these tables CREATE TABLE product_master ( prod_id INT PRIMARY KEY, prod_name VARCHAR(100), price DECIMAL(12,2) ) GO CREATE TABLE product_details ( prod_id INT, sales_date DATETIME,…

SQL SERVER – Collect and Analyze SQL Server Data Efficiently

April 28, 2015

Pinal Dave

SQL

Devart

dbForge Event Profiler is one of the most useful SQL Server “build-in” tools. The Profiler records data associated with various SQL Server events for further analysis. This data is stored in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose SQL Server relates problems. The tool allows you to view communications between a client and SQL Server, and gives you an insight into its internal performance. To take full advantage of its potential, download dbForge Event Profiler for SQL Server for free now. Let us learn about Analyze SQL Server Data Efficiently.

Interview Question of the Week #017 – Performance Comparison of Union vs Union All

April 26, 2015

Pinal Dave

SQL, SQL Interview Questions and Answers, SQL Server, SQL Tips and Tricks

SQL Union clause, Union

Here is what I just learned from email. One of the very prestigious organization asks one of their candidate following questions – Question: If you have options to use Union or Union All – which one of the option will use keeping performance as a top most requirement for the query. Answer: Though…

SQL SERVER – Generate Different Random Number for Each Group Using RAND() Function

April 25, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Random

You know that RAND() function in SQL Server generates a random float value 0 through 1 (excluding 0 and 1). Let us create the following dataset CREATE TABLE #random(no INT) INSERT INTO #random(no) SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT…

SQL Server – Error While Enabling CDC on a Table – The specified ‘@server’ is invalid (valid values are returned by sp_helpserver)

April 24, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

CDC

Every environment is different and it is no doubt some of these environment changes can give you results that are not expected. Recently one of my blog reader followed below blogs: SQL SERVER – Introduction to Change Data Capture (CDC) in SQL Server 2008 SQL SERVER – Download Script of…

SQL SERVER – Different Methods to Know Parameters of Stored Procedure

April 24, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Stored Procedure

Suppose you have a stored procedure with some input parameters. There are two methods to know the list of parameters defined in a stored procedure. Let us create the following stored procedure CREATE PROCEDURE TEST_PROCEDURE ( @CUST_ID INT, @YEAR INT ) AS SELECT @CUST_ID,@YEAR Now to know the parameters used…

Hey DBA – Watch Your Vendors! Hey Vendors – Try a Little Harder! – Notes from the Field #077

April 23, 2015

Pinal Dave

SQL

Notes from the Field, SQL Server

[Note from Pinal]: This is a 77th episode of Notes from the Field series. Every week, I personally wait for the notes from the fields from Mike because it contains lessons of life which directly impacts DBA and Developer’s life positively. Mike Walsh is an amazing person to meet and know. I am fortunate to be his friend. Every single time I meet him, I learn something as a human. He is always looking for an opportunity to improve the life of DBAs and Developers.