SQLLOCALDB.exe

 

SQLLOCALDB is a utility to install the instance of SQL Server express LocalDB, it’s a command line tool which allow the developers to install and manage the instance of SQL Server express LocalDB

SqlLocalDB.exe have multiple arguments to pass i.e Create,Delete,Start, Stop etc.

Example:

sqlLocalDB.exe create "Organization" 

Above command will create the SQL server instance named Organization

for more details, please visit link

https://msdn.microsoft.com/en-us/library/hh212961.aspx

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.

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

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.

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]

ISNULL vs COALESCE vs NULLIF

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

Announcing SQL Server on Linux

https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

Memo Structure in SQL Server

Query optimizer generate alternate execution plans through transformation rules for optimal plan, these alternate execution plans are stored in a structure called MEMO for the duration of optimization process.

MEMO provides efficient way to store alternate plan through groups, each group in memo initially contain one entry.

Optimizer initially copy the query tree logical expression in memo structure based on the operator it copies into equivalent groups and optimization process start through transformation.

Transformation process starts from initial logical expression and generate alternative plan, if required it add new logical expression and generate new group and new alternative plan and adding them to equivalent group.

We have some undocumented trace flag which help us to see the memo structure, please do not try these in production environment.

Scenarios/Examples in test environment only:

 CREATE TABLE TempMemo

( id INT )

CREATE TABLE TempMemo1

( id INT )

INSERT INTO TempMemo

SELECT 1

UNION

SELECT 2

INSERT INTO TempMemo1

SELECT 1

UNION

SELECT 3

-- Check the data in tables

SELECT *  FROM   TempMemo1

SELECT *  FROM   TempMemo

-- start trace flag 3604 to return output info to client

DBCC traceon(3604);

-- clear buffer/cache

DBCC freeproccache

DBCC dropcleanbuffers

-- Start Trace flag 8608, which will show Initial memo strucute, marked in color Yellow

SELECT     *

FROM       TempMemo tm  INNER JOIN TempMemo tm1

       ON         tm.id =tm1.id

OPTION(QueryTraceON 8608);

/*

--- Initial Memo Structure ---

Root Group 5: Card=2 (Max=1e+008, Min=0)

   0 LogOp_Join 3 4 2 (Distance = 0)

Group 4: Card=2 (Max=10000, Min=0)

   0 LogOp_Get (Distance = 0)

Group 3: Card=2 (Max=10000, Min=0)

   0 LogOp_Get (Distance = 0)

Group 2:

   0 ScaOp_Comp  0 1 (Distance = 0)

Group 1:

   0 ScaOp_Identifier  (Distance = 0)

Group 0:

   0 ScaOp_Identifier  (Distance = 0)

------------------------------

(2 row(s) affected)

*/

DBCC freeproccache

DBCC dropcleanbuffers

-- Start Trace flag 8615, which will show Final memo strucute, marked in color Green

SELECT     * FROM       TempMemo tm

       INNER JOIN TempMemo tm1 ON         tm.id =tm1.id

OPTION (QueryTraceON 8615);

/*

--- Final Memo Structure ---

Group 7: Card=2 (Max=10000, Min=0)

   1 PhyOp_Range 0 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0032842(Distance = 2)

   0 LogOp_GetIdx (Distance = 1)

Group 6: Card=2 (Max=10000, Min=0)

   1 PhyOp_Range 0 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0032842(Distance = 2)

   0 LogOp_GetIdx (Distance = 1)

Root Group 5: Card=2 (Max=1e+008, Min=0)

   3 PhyOp_HashJoinx_jtInner 4.2 3.2 2.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0243645(Distance = 2)

   1 LogOp_Join 4 3 2 (Distance = 1)

   0 LogOp_Join 3 4 2 (Distance = 0)

Group 4: Card=2 (Max=10000, Min=0)

   6 PhyOp_Sort 4.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0146486(Distance = 0)

   2 PhyOp_Range 0 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0032842(Distance = 1)

   0 LogOp_Get (Distance = 0)

Group 3: Card=2 (Max=10000, Min=0)

   4 PhyOp_Sort 3.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0146486(Distance = 0)

   2 PhyOp_Range 0 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0032842(Distance = 1)

   0 LogOp_Get (Distance = 0)

Group 2:

   0 ScaOp_Comp  0.0 1.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 3(Distance = 0)

Group 1:

   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1(Distance = 0)

Group 0:

   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1(Distance = 0)

----------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(2 row(s) affected)

*/

HASHING & HASH COLLISSION in SQL SERVER

HASHING:

Hashing is the mechanism/algorithm to generate hash key values for provided inputs, there is different SQL server algorithms/in build functions to generate hash values.

HASHBYTES is the function to generate hash values using algorithms (MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512)

We also use CHECKSUM & BINARY_CHECKSUM to generate hash values.

 

HASH COLLISSION:  When hashing mechanism generate same Hash values for not very similar inputs.

Execute below queries for examples and watch result marked in particular fashion.

Examples:

DECLARE @V Varchar(88) ='AAADB'

DECLARE @V1 Varchar(88) = 'AAAAAAAAAAAAAAAAAAADB'

SELECT CHECKSUM(@V) AS HashValue,@V AS InputString

UNION ALL

SELECT CHECKSUM(@V1) AS HashValue,@V1 AS ChangedInputString

SET @V = 'DBAAADB'

SET @V1 = 'DBAAAAAAAAAAAAAAAAAAADB'

SELECT  CHECKSUM(@V) AS CHECKSUM_HashValue, @V AS  InputString

UNION ALL

SELECT  CHECKSUM(@V1) AS CHECKSUM_HashValue, @V1 AS ChangedInputString

SET @V = 'DBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADB'

SET @V1 = 'DBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADB'

SELECT  HASHBYTES('SHA', @V) AS SHA_HASHBYTES_HashValue, @V AS InputString

UNION ALL

SELECT  HASHBYTES('SHA', @V1) AS SHA_HASHBYTES_HashValue, @V1 AS ChangedInputString

SELECT  HASHBYTES('SHA1', @V) AS SHA1_HASHBYTES_HashValue, @V AS InputString

UNION ALL

SELECT  HASHBYTES('SHA1', @V1) AS SHA1_HASHBYTES_HashValue, @V1 AS ChangedInputString

SELECT HASHBYTES('SHA2_256', @V) AS SHA2_HASHBYTES_HashValue, @V AS InputString

UNION ALL

SELECT HASHBYTES('SHA2_256', @V1) AS SHA2_HASHBYTES_HashValue, @V1 AS ChangedInputString

SELECT HASHBYTES('SHA2_512', @V) AS SHA2_512_HASHBYTES_HashValue, @V AS InputString

UNION ALL

SELECT HASHBYTES('SHA2_512', @V1) AS SHA2_512_HASHBYTES_HashValue , @V1 AS ChangedInputString 

Queries Output:

From above result set it is very much clear hashing functions/algorithms generate same hash values for different input strings which result to hash collision.