SQL Tips and Tricks Archives - Page 134 of 536 - 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,…

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…

SQL SERVER – How to Identify a DB is Using Cross-Database Transactions?

April 22, 2015

Pinal Dave

SQL Tips and Tricks

SQL Function, SQL Scripts, SQL Server, SQL Transactions

Recently, when I was at a conference, one of the attendees came to me to ask a question. The conversation was as below for cross-database transactions.

SQL SERVER – Split Comma Separated List Without Using a Function

April 21, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL Server

The best way to learn something is to revisit some of the older blogs and then look at finding a more efficient way to work on the same problem. Recently I was looking at the forums to find a question come up again and again around making a comma separated list. This is not new over this blog and if you search you will get few. Long time ago I have written below blogs to show the values stored in table as comma separate list – this is not a rehash of what was written before but a newer way to solve the problem again.