Monday, November 28, 2016

Tempdb Sizing

Many DBA’s Prefer to set 100-200 GB size for  database tempdb,  out of this space 90 % for data file(.mdf) and 10% for Log file(.ldf), this sizing help to prevent uncontrolled size growth & reduce contention.

Friday, October 14, 2016

MAX Value From Multiple Columns



CREATE TABLE #MAXCOLUMNVALUES
(ID INT IDENTITY(1,1)
,INFYSalary INT, WIPROSalary INT, SYNESalary INT, TCSSalary INT
)


INSERT INTO #MAXCOLUMNVALUES(InfySalary,WiproSalary,SyneSalary,TCSSalary) VALUES
(10000,20000,33000,25000),
(20000,55000,35000,45000),
(40000,24000,66000,50000),
(50000,51000,53000,77000),
(44000,32000,38000,30000)

SELECT * FROM #MAXCOLUMNVALUES
-- Select Max value from Multiple columns as column name MAXSalary

SELECT
*, (SELECT MAX(MaxSalary) FROM (VALUES (InfySalary),(WiproSalary),(SyneSalary),(TCSSalary)) AS Salary(MaxSalary)) AS MAXSalary
 FROM #MAXCOLUMNVALUES


Thursday, September 8, 2016

QUERY TO GET REVERSE DUPLICATE


Create Table with Reverse Duplicate Data

CREATE TABLE GETREVERSEDUPLICATE
(COL1 VARCHAR(10),COL2 VARCHAR(10) ,COL3 INT)

INSERT INTO GETREVERSEDUPLICATE
SELECT 'A','B',10
UNION
SELECT 'C','D',20
UNION
SELECT 'B','A',10
UNION
SELECT 'D','C',20



SELECT * FROM GETREVERSEDUPLICATE
OUTPUT:
COL1 COL2 COL3
A B 10
B A 10
C D 20
D C 20

GET REVERSE DUPLICATE WITH EXPECTED OUTPUT
EXPECTED RESULT:
COL1 COL2 COL3
B A 10
D C 20



Query:
SELECT * FROM GETREVERSEDUPLICATE T
 WHERE Col1 > Col2
       AND EXISTS (
                   SELECT *
                     FROM GETREVERSEDUPLICATE  AS T1
                    WHERE T1.Col1 = T.Col2
                          AND T1.Col2 = T.Col1
                  );

OUTPUT:
COL1 COL2 COL3
B A 10
D C 20


Please try in Test environment only.



Thursday, August 4, 2016

SQL Server Logins vs Database Users




SQL Server Login is used to Access SQL Server means for Authentication but Database users
are specific to a database to perform database operation based on database role membership and the permission under the tab securables means for Authorization.



After creation of login named SQLLogin as per above figure it will create the user in database Capital with user name SQLLogin with default schema [dbo]
SQL script for SQL Login as per above figure.
CREATE LOGIN [SQLLogin] WITH PASSWORD='random encrypted password', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO



We can see in above image SQLLogin is marked as user in database Capital thus it will
Create database user in database Capital using below SQL Script.

CREATE USER [SQLLogin] FOR LOGIN [SQLLogin] WITH DEFAULT_SCHEMA=[dbo]

Once SQL server login and respective database user is created we cannot delete the SQL Server logins if said SQL Login is used to access the SQL Server or database user is still associated with said SQL Login.
Database Role members.


We can create Database user with different types, shown in below figure



As per above image from securables tab we can give required permissions/authorization to database users for database objects (tables/view/sp’s etc.)
SQL Script to create user without SQL Login:
CREATE USER [sqlwologin] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]



Tuesday, April 5, 2016

TableDiff.exe Utility

Tablediff.exe  command line utility
SQL server has command line utility tablediff.exe, using this we can easily compare source and destination tables. This is generally used in replication topology.
We can use it from command prompt or by using batch file.
We can perform following
1.       Row by row comparison
2.       Row count or schema comparison
3.       Column level comparison
4.       Generate t-SQL script to have data in sync with source and destination
5.       Log comparison output into file or in destination database table.
Utility required list of parameters but some rudimentary parameters are below
1.       -sourceserver 
2.       -sourcedatabase 
3.       -sourcetable
4.       -destinationserver 
5.       -destinationdatabase 
6.       -destinationtable 
Let us create tables for source and destination comparison
Source Table:
CREATE TABLE TableDiffSource
(TDID INT PRIMARY KEY, TDDescription VARCHAR(100))

INSERT INTO TableDiffSource
SELECT 1,'A'
UNION
SELECT 2,'B'
UNION
Select 4,'D'

Destination Table:
CREATE TABLE TableDiffDestination
(TDID INT PRIMARY KEY, TDDescription VARCHAR(100))

INSERT INTO TableDiffDestination
SELECT 1,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,'C'

Open the command prompt and execute below command by changing your Server & Database names.
In command I have used. 
SourceServcer: SQLSourceServerName & SourceDatabase= Capital
DestinationServer= SQLDestinationServerName & DestinationDatabase=  Capital

C:\Program Files\Microsoft SQL Server\120\COM>tablediff.exe -sourceserver SQLSourceServerName -sourcedatabase Capital -sourcetable tablediffsource -destinationserver
 SQLDestinationServerName -destinationdatabase Capital -destinationtable tablediffdestinat
ion -et tableDiffoutput

It will look like below after execution of tablediff.exe utility, in image i have hidden soruceserver & destinationServer names, please change them as per your SQL Server details














Let us check the difference, in output table
select * FROM tablediffoutput
output
TDID
MSdifftool_ErrorCode
MSdifftool_ErrorDescription
my Comments
3
1
Dest. Only
Present in Destination but not in source table
4
2
Src. Only
Present in source but not in destination table

Note: in above output, column myComments I have added for more clarification marked in color olive green.

for more details, please visit below link .

https://msdn.microsoft.com/en-IN/library/ms162843.aspx