Advertisements

Archive

Archive for the ‘Sql Server’ Category

SQL: Condition in Count()

July 7, 2015 1 comment

Condition in count()

SELECT
count(case when EventTypeID =1 then 1 else null end) as TotalTournamentsAttended,
count(case when EventTypeID =2 then 1 else null end) as TotalLeaguesAttended

FROM

[Tables]

 

 

Advertisements

Difference Between Primary Key and Unique Key


PRIMARY KEY UNIQUE KEY
NULL It doesn’t allow Null values.
Because of this we refer
PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT
Allows Null value. But only one Null value.
INDEX By default it adds a clustered index By default it adds a UNIQUE non-clustered index
LIMIT A table can have only one PRIMARY KEY Column[s] A table can have more than one UNIQUE Key Column[s]
CREATE SYNTAX Below is the sample example for defining a single column as a PRIMARY KEY column while creating a table:CREATE TABLE dbo.Customer
(
Id INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(50)
)Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY:

CREATE TABLE dbo.Customer
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT PK_CUSTOMER PRIMARY KEY(Id,FirstName)
)

Below is the sample example for defining a single column as a UNIQUE KEY column while creating a table:CREATE TABLE dbo.Customer
(
Id INT NOT NULL UNIQUE,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(50)
)Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY:

CREATE TABLE dbo.Customer
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT UK_CUSTOMER UNIQUE(Id,FirstName)
)

ALTER SYNTAX Below is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created and doesn’t have any primary key:ALTER TABLE dbo.Customer
ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id)
Below is the Syntax for adding UNIQUE KEY CONSTRAINT on a column when the table is already created:ALTER TABLE dbo.Customer
ADD CONSTRAINT UK_CUSTOMER UNIQUE(Id)
DROP SYNTAX Below is the Syntax for dropping a PRIMARY KEY:ALTER TABLE dbo.Customer
DROP CONSTRAINT PK_CUSTOMER
Below is the Syntax for dropping a UNIQUE KEY:ALTER TABLE dbo.Customer
DROP CONSTRAINT UK_CUSTOMER

MySQL : coalesce in mysql


DESCRIPTION

The MySQL COALESCE function returns the first non-null expression in the list.

SYNTAX

The syntax for the MySQL COALESCE function is:

COALESCE( expression1, expression2, ... expression_n )

e.g.

SELECT COALESCE(t.col,1) FROM Table t

Above query will return 1 if t.col is null.

Hope this help !

Categories: Sql Server Tags: , , ,

Comma separated values in mysql


MY SQL:

GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.

Syntax GROUP_CONCAT([DISTINCT] exp [ORDER BY sorting] [SEPARATOR ‘sep’])
Quick Example SELECT GROUP_CONCAT(city) FROM cities GROUP BY state;
Separator Comma (,) by default, ” eliminates separator
NULL Values Skipped
Max Length 1024 by default, specified by group_concat_max_len system variable

Sample:

CREATE TABLE cities
   (
     city VARCHAR(70),
     state CHAR(2)
   );
 
   INSERT INTO cities VALUES ('San Francisco', 'CA');
   INSERT INTO cities VALUES ('San Diego', 'CA');
   INSERT INTO cities VALUES ('Los Angeles', 'CA');
 
   INSERT INTO cities VALUES ('Austin', 'TX');
   INSERT INTO cities VALUES ('Houston', 'TX');

   SELECT state, GROUP_CONCAT(city)
   FROM cities
   GROUP BY state;
The result:
CA San Francisco,San Diego,Los Angeles
TX Austin,Houston
SQL Server:
SELECT state,
STUFF((SELECT ',' + city FROM cities WHERE state = c.state FOR XML PATH('')),1 ,1 ,'')
FROM cities c
GROUP BY state

Hope this help !
Rahul Bhatia

SQL: Alter script to change the Primarykey Column datatype

February 23, 2015 Leave a comment

–This will drop the primary key constraint
ALTER TABLE [pTARGET_TYPE] DROP CONSTRAINT PK_TARGET_TYPE

–Alter datatype
ALTER TABLE [TARGET_TYPE] ALTER COLUMN [TARGET_TYPE_ID] INT NOT NULL

–Add Primary key constraint
ALTER TABLE [TARGET_TYPE] ADD CONSTRAINT PK_TARGET_TYPE PRIMARY KEY ([TARGET_TYPE_ID])

SQL Server Setup Issue : How to resolve Reporting Services Catalog Database File Existence Failed or Temporary Database File Existence failed

January 28, 2015 Leave a comment

When you Uninstall SQl server some of the traces will be left behind one of them are Reporting serivces catalogs in order to fix this issue follow the screen shots below

You can delete those file from following location

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

  • ReportServer.mdf
  • ReportServer_log.LDF
  • ReportServerTempDB.mdf
  • ReportServerTempDB_log.LDF.

Hope this help !

 

Categories: Sql Server

SQL Error : Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

November 26, 2014 1 comment

Error :

Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Following setting required for the above problem

[1] ALTER DATABASE [$(DatabaseName)] SET ALLOW_SNAPSHOT_ISOLATION ON;

[2]  ALTER DATABASE [$(DatabaseName)]SET READ_COMMITTED_SNAPSHOT ON;

 

Hope this help !