How to find incorrect datetime data from «Char» format column

One of my colleges was asked me
about problem finding incorrect data
but hier problem is this column is Char type
and find incorrect rows by the date type

for example hava a one table

 

CREATE TABLE #test (c1 char(8) NULL)
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
in this insert process have a row ‘20150229’ does not match in really datetime
because in really in 2015 year february is 28 dayhow select this incorrect rows with automaticly wihtout diffuclut queries or without cross or inner join

for this problems in Microsoft Sql Server have a  awesome function
this is isdate()

in correct time isdate() give us 1 in incorrect time give us 0

then we are need select our table for give incorrect rows

CREATETABLE#test (c1 char(8) NULL)
INSERT#test VALUES('20150131')
INSERT#test VALUES('20000131')
INSERT#test VALUES('20150229')
INSERT#test VALUES('20150229')
INSERT#test VALUES('19990331')
INSERT#test VALUES('20000331')
INSERT#test VALUES('20150131')
INSERT#test VALUES('20000131')
INSERT#test VALUES('20150229')
INSERT#test VALUES('20150229')
INSERT#test VALUES('19990331')
INSERT#test VALUES('20000331')
INSERT#test VALUES('20150131')
INSERT#test VALUES('20000131')
INSERT#test VALUES('20150229')
INSERT#test VALUES('20150229')
INSERT#test VALUES('19990331')
INSERT#test VALUES('20000331')
SELECT*
FROM#test
WHEREISDATE(c1)=0
c1
20150229
20150229
20150229
20150229
20150229
20150229

result was succes

in table  6 rows  does not match in really datetime

and you can see this articel in my msdn wiki page too

How to find incorrect datetime data from «Char» format column

SQL Server:Find updated row by another user

One of my colleges asked me about interesting problems
the problem that
have a one table and adminstator database want see every updating this table
(for example inserting or updating rows) by another users
solution have a SQL SERVER
but a interesting
we must use «TIMESTAMP» function

have a quession what is a «TIMESTAMP» function this is time function from sql server?

answear one — no  this is no time function

A timestamp is a special
binary(8) value that’s guaranteed to be unique across a database
and give value from @@DBTS system function
and from different databases give us different value

for example

USE tempdb
select @@DBTS as tempdb_timestamp
use master 
select @@DBTS as masterdb_timestamp
use msdb 
select @@DBTS as msddbdb_timestamp
tempdb_timestamp
0x0000000000000887
masterdb_timestamp
0x0000000000000FA0
msddbdb_timestamp
0x0000000000002710
and using «TIMESTAMP» function with inserting time in a different rows will give us  different value

for example

CREATETABLE #test(k1 intidentity,timestamptimestamp)
INSERT #test DEFAULTVALUES
go 5
select* from#test
droptable#test
k1  timestamp
1   0x0000000000000888
2   0x0000000000000889
3   0x000000000000088A
4   0x000000000000088B
5   0x000000000000088C
in this example rows of column timestamp was give in inserting time different value from «TIMESTAMP» function

and in updating time default value from  @@DBTS was give different value from updating process

for find different value we are need «TSEQUAL()» function SQL SERVER

This command compares two timestamp values—returns 1 if they’re identical, raises an error if they’re not

then we can created our query for give message from updating our table by another user

CREATE TABLE #test
(k1 intidentity,
timestamptimestamp)
DECLARE@ts1 timestamp, @ts2 timestamp
SELECT@ts1=@@DBTS, @ts2=@ts1
SELECTCASEWHENTSEQUAL(@ts1, @ts2) THEN'Equal'ELSE'Not Equal'END
INSERT#test DEFAULTVALUES
SET@ts2=@@DBTS
SELECTCASEWHENTSEQUAL(@ts1, @ts2) THEN'Equal'ELSE'Not Equal'END
GO
(1 row(s) affected)
(1 row(s) affected)
Msg 532, Level16, State 2, Line 11
The timestamp(changed to0x0000000000000892) shows that the row has been updated byanother user.
and  result was succes

from inserting process  we was  give message    «the row has been updated by another user"

and you can see this article my msdn wiki page too
SQL Server:Find updated row by another user

SQL SERVER : Creating Pivot Table With sign() Function

First we will created #test table and will inserting data this table

 

CREATE TABLE #test
(SalesYear smalldatetime,
 Sales money)
INSERT #test VALUES ('20150101',100.09)
INSERT #test VALUES ('20150101',200.12)
INSERT #test VALUES ('20140202',300.56)
INSERT #test VALUES ('20140306',420.50)
INSERT #test VALUES ('20120505',600.55)
INSERT #test VALUES ('20120505',610.45)
 
 
SalesYear           Sales
 
2015-01-01 00:00:00 100,09
2015-01-01 00:00:00 200,12
2014-02-02 00:00:00 300,56
2014-03-06 00:00:00 420,50
2012-05-05 00:00:00 600,55
2012-05-05 00:00:00 610,45
and then we will created simple pivot table for sum years
but for understanding logic creation pivot table
i will use in this example
with «Case » querying
SELECT
 "2015"=SUM(CASEWHENYEAR(SalesYear)=2015 THENSales ELSENULLEND),
 "2014"=SUM(CASEWHENYEAR(SalesYear)=2014 THENSales ELSENULLEND),
 "2012"=SUM(CASEWHENYEAR(SalesYear)=2012 THENSales ELSENULLEND)
FROM#test
 
 
2015    2014    2012
300,21  721,06  1211,00
and query results give us sum sales for every years
and have a one quession hier
what is a logic this query and what we need update this query by using without case or pivot queries?
in this example
the logic one
we will summarized all Sales by categories years «2015» «2014» «2012»
and another logic
if salesyear is 2015 then we will sum sales in 2015 year but in another year we will update this sal to  0
CASE WHEN YEAR(SalesYear)=2015 THENSales ELSENULLEND
and how we will update this logic to another ?

Power of Microsoft Sql Server give us awesome function
this is «SIGN»

characteristic of  «SIGN» function that return 1,-1,0 from the sql formula

for example

select sign(5+10) as"return value sign"
 
return value sign
1
 
-------------------------------------------------
select sign(5-10)  as"return value sign"
 
retur nvalue sign
-1
 
------------------------------------------------
select sign(5-5)   as"return value sign"
 
return value sign
0
then power of this function we will created our pivot table without pivot or case query
SELECT
 "2015"=SUM(Sales*(1-ABS(SIGN(YEAR(SalesYear)-2015)))),
 "2014"=SUM(Sales*(1-ABS(SIGN(YEAR(SalesYear)-2014)))),
 "2012"=SUM(Sales*(1-ABS(SIGN(YEAR(SalesYear)-2012))))
FROM #test
2015    2014    2012
300,21  721,06  1211,00
and you can see this article in my msdn wiki page too:
SQL SERVER : Creating Pivot Table With sign() Function

SQL SERVER Troubleshooting: Select from NULL rows

Preparation and test

First we will create #test and #test_link tables and will inserted data in this table

create table #test (cities char(30),countries char(30))
insert #test values('Tokyo','Japan')
insert #test values('Moscow','Russian')
insert #test values('New York','USA')
insert #test values('London','GB')
create table #test_link (city char(30),companies char(30))
insert #test_link values('Tokyo','Toyota')
insert #test_link values('Moscow','Lada')
insert #test_link values('New York','HP')
insert #test_link values('London','Central Bank')
insert #test_link values('Berlin','BMW')
select* from #test
select* from #test_link
cities  countries
---------  -----------
Tokyo  Japan 
Moscow  Russian 
New York  USA 
London  GB 
city  companies
---------  -----------
Tokyo  Toyota 
Moscow  Lada 
New York  HP 
London  Central Bank 
Berlin  BMW 

and with simple scripts we will select wich city not in the #test  table

 

select * from  #test_link
 where  city  not  in  ( select cities from #test )
city  companies
------------------------------ ------------------------------
Berlin  BMW 

and for testing  selecting problems NULL rows we first need add NULL data to #test table

and then will selecting  this query again

insert#test values(NULL,NULL)
select* from#test
cities  countries
------------------------------ ------------------------------
Tokyo  Japan 
Moscow  Russian 
New York  USA 
London    GB 
NULL     NULL
----the NULL data was add
 ----and now we will run  our working query again
select * from #test_link
 where  city  not  in  ( select cities from #test )
city  companies
------------------------------ ------------------------------

Results show nothing was selected.

What happened with our query ?

Everytime working with NULL rows gives bad results.

Root cause

For this problems in SQL SERVER have standard from ANSI SQL  «ANSI_NULLS»

For working with NULL rows we need to use SET ANSI_NULLS OFF  and run our query. And then again SET ANSI_NULLS ON, then for  testing select NULL rows  we are need again return our query

 SET ANSI_NULLS OFF  -----------------  first we are need  disable  ANSI_NULLS
 select * from #test_link
 where  city  not  in  ( select cities from #test )
SET ANSI_NULLS ON  -----------------  end we are need enable ANSI_NULLS  
city  companies
------------------------------ ------------------------------
Berlin  BMW 

Result was success, column was selected.

 

and you can see this article in msdn wiki page too
SQL SERVER Troubleshooting: Select from NULL rows

SQL Server: How to automatically stop unnecessary data at insert

Introduction

This article is about how to automatically stop unnecessary data when inserting data in a SQL Server Table.

Problem

One of the problematic aspects of Sql Server is to stop the unnecessary data at the time inserting automatically in Sql Server

First we will created #test table and will inserted data in this table

 

CREATE TABLE #test
(Name varchar(30)  ,
Weight float)
INSERT #test VALUES ('DR.Joe Glass', 112)
INSERT #test VALUES ('Glass Joe', 112)
INSERT #test VALUES ('Piston Hurricane', 176)
INSERT #test VALUES ('DR.Bald Bull', 298)
INSERT #test VALUES ('Sugar Ray Ali', 151)
INSERT #test VALUES ('Leon Holmes', 119)
INSERT #test VALUES ('George Liston', 139)
INSERT #test VALUES ('DR.Larry Leonard', 115)
INSERT #test VALUES ('Mike Mooncalf', 134)
INSERT #test VALUES ('Joe Glass', 112)
select  * from #test
Name                           Weight
------------------------------ ----------------------
DR.Joe Glass                   112
Glass Joe                      112
Piston Hurricane               176
DR.Bald Bull                   298
Sugar Ray Ali                  151
Leon Holmes                    119
George Liston                  139
DR.Larry Leonard               115
Mike Mooncalf                  134
Joe Glass                      112
(10 row(s) affected)

and after inserting have a 10 rows but 3 rows begins with characters «DR»

 

select  * from #test where  SUBSTRING(Name,1,2) like 'DR%'

 

Name                                                         Weight
------------------------------ ----------------------
DR.Joe Glass                   112
DR.Bald Bull                   298
DR.Larry Leonard               115
(3 row(s) affected)
If we do not want to load the data begins  «DR»  and count is not 10 rows but million rows and we will inserted with bulk insert then we have great problem in this data.

Solution

For these problems use the power of Microsoft SQL Server language: «Check» Constraint.

But have a problem how can right use this constant in «bulk loading» or «inserting» in table without unnecessary data

First we will created #test table but in column Name we will add constaint for stopping inserting unnecessary data

CREATE TABLE #test
(Namevarchar(30)  CHECK(LEFT(Name,2)<>'DR'),  --- Establish a check constraint  for stopping uncessary data
Weight float)
------   Now  insert data starts with the 'DR'
INSERT#test VALUES('DR.Joe Glass', 112)
INSERT#test VALUES('Glass Joe', 112)
INSERT#test VALUES('Piston Hurricane', 176)
INSERT#test VALUES('DR.Bald Bull', 298)
INSERT#test VALUES('Sugar Ray Ali', 151)
INSERT#test VALUES('Leon Holmes', 119)
INSERT#test VALUES('George Liston', 139)
INSERT#test VALUES('DR.Larry Leonard', 115)
INSERT#test VALUES('Mike Mooncalf', 134)
INSERT#test VALUES('Joe Glass', 112)
Msg 547, Level 16, State 0, Line 29
The INSERT statement conflicted with the CHECK constraint "CK__#test_______Name__4222D4EF". The conflict occurred indatabase "tempdb", table"dbo.#test_______________________________________________________________________________________________________________00000000003D",column 'Name'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 32
The INSERT statement conflicted with the CHECK constraint "CK__#test_______Name__4222D4EF". The conflict occurred indatabase "tempdb", table"dbo.#test_______________________________________________________________________________________________________________00000000003D",column 'Name'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 36
The INSERT statement conflicted with the CHECK constraint "CK__#test_______Name__4222D4EF". The conflict occurred indatabase "tempdb", table"dbo.#test_______________________________________________________________________________________________________________00000000003D",column 'Name'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)

and Sql Server  give us message

"INSERT statement conflicted withthe CHECK constraint"CK__#test_______Name__4222D4EF"

then with select testing resulst our query

select* from #test
select* from #test where SUBSTRING(Name,1,2) like'DR%'
Name                         Weight
------------------------------ ----------------------
Glass Joe                      112
Piston Hurricane               176
Sugar Ray Ali                  151
Leon Holmes                    119
George Liston                  139
Mike Mooncalf                  134
Joe Glass                      112
(7 row(s) affected)
Name     Weight
------------------------------ ----------------------
(0 row(s) affected)
and result was succes, inserting data was without unnecessary data «DR».
and you can see this article in msdn wiki page too
SQL Server: How to automatically stop unnecessary data at insert

SQL Server: INSERT to Remove Duplicate Rows

First we will created #test table with unique index

and will inserted dublicate rows

CREATE TABLE #test
(name varchar(15) UNIQUE, -- Define a UNIQUE constraint
 profession varchar(25),
 salary int DEFAULT 0,
 )
INSERT #test VALUES ('Paul Raj','Actor',-0045)
INSERT #test VALUES ('Julio Sezar','Teacher',-0055)
-- Now attempt to insert a duplicate value
INSERT #test VALUES ('Julio Sezar','Teacher',-0055)
(1 row(s) affected)
(1 row(s) affected)
Msg 2627, Level14, State 1, Line 12
Violation of UNIQUE KEY constraint'UQ__#test_____72E12F1B117F9D94'. Cannot insert duplicate key in object 'dbo.#test'. The duplicate key value is(Julio Sezar).
The statement has been terminated.

SQL server will give an error message

'Cannot insert duplicate keyinobject'

and eats request

how we need to add data ?

delete dublicate rows?

and if will million rows in inserting data?

it was great problem in our query

for these problems

power Microsoft Sql Server language

give us awesome option

IGNORE_DUP_KEY  for deleting dublicate rows with inserting process

for testing this option

first we will created our #test table  again   with unique index for inserted

and insert this table dublicate rows

but hier we will use

UNIQUE INDEX WITH IGNORE_DUP_KEY
CREATE TABLE #test
(name varchar(15) UNIQUE, -- Define a UNIQUE constraint
 profession varchar(25),
 salary int DEFAULT 0,
)
CREATE UNIQUE INDEX removedups ON #test (name,profession,salary)
WITH IGNORE_DUP_KEY
INSERT #test VALUES ('Paul Raj','Actor',-0045)
INSERT #test VALUES ('Julio Sezar','Teacher',-0055)
-- Now attempt to insert a duplicate value
INSERT #test VALUES ('Julio Sezar','Teacher',-0055)
select *from #test
drop table #test
Duplicate key was ignored.
namep           rofession                salary
--------------- ------------------------- -----------
Julio Sezar     Teacher                   -55
Paul Raj        Actor                     -45
(2 row(s) affected)

and inserting was working success

and in result message give us   ‘ Duplicate keywas ignored'.

 

and you can see this article in msdn wiki page this too:

http://social.technet.microsoft.com/wiki/contents/articles/32648.sql-server-insert-to-remove-duplicate-rows.aspx

SQL Server: INSERT to Remove Duplicate Rows