Some minor omissions can go unnoticed and produce completely unexpected results.
The following code example has two seemingly similar SELECT statements but they produce different results. All because I missed out a comma in the second SELECT statement! SQL Server did not raise any error and quietly assumed that I have tried to use an alias name for the First Name column!
CREATE TABLE employee
(
employee_id INT,
firstname VARCHAR(50),
middleinitial VARCHAR(1),
lastname VARCHAR(50)
)
INSERT INTO employee
(employee_id, firstname, middleinitial, lastname) VALUES
(1001, 'Timothy', 'B', 'Parker'),
(1002, 'Henry', 'F', 'Williams'),
(1003, 'Jeff', NULL, 'Smith'),
(1004, 'Mary', 'M', 'Marsh'),
(1005, 'Miles', 'V', NULL)
--Correct statement
SELECT employee_id, firstname, middleinitial, lastname
FROM employee
-- Wrong statement with a comma missing
-- between the firstname and middleinitial columns.
-- SQL Server thinks it is an alias for firstname.
SELECT employee_id, firstname middleinitial, lastname
FROM employee
If you try to put an empty string in an INTEGER type (or its cousin) columns, SQL Server converts it implicitly into a zero. This might not be the expected behavior for the application so it is good to be aware.
declare @v char(1) = '' -- this could have been a varchar too with the same results
declare @t table
(
colbigint bigint,
colint int,
colsmallint smallint,
coltinyint tinyint
)
/* The wrong way that will change all blank strings to zero */
insert into @t
(
colbigint,
colint,
colsmallint,
coltinyint
)
values
(
@v,
@v,
@v,
@v
)
/* The correct way to sanitize */
insert into @t
(
colbigint,
colint,
colsmallint,
coltinyint
)
values
(
nullif(ltrim(@v), ''),
nullif(ltrim(@v), ''),
nullif(ltrim(@v), ''),
nullif(ltrim(@v), '')
)
select * from @t
go
You need to delete and then insert data into tables which have parent-child relationships defined via primary and foreign keys. The delete and insert commands have to be executed in proper sequence.
The sequence for DELETE is Child-before-Parent.
The sequence for INSERT is Parent-before-Child.
I created a quick script in response to a forum post. The script is not the best out there because –
It does not handle circular references in relationships
It takes 30 seconds or more if run on a database with hundreds of tables
I’m sharing it for my future reference and probably others with smaller number of tables would find it useful too.
I had a recent project where we planned to re-factor an existing application’s .Net and TSQL code. I wanted to find the complexity of the stored procedures and arrive at an estimate of the effort required to review all them. I had in mind that something like number of lines of code, number of dependencies and parameters in the stored procedure would give a good starting point to create such an estimate.
I patched together a script that produced a report similar to the example below. I used that output and put in some more formulas to assign a complexity level (simple, medium or complex) and the approximate number of minutes required to review that procedure. I have not included those calculations here because they depend entirely on the estimator’s perception of complexity and the developer’s skill level.
The number of lines of code is a subjective metric here because it depends on the developer’s coding style and use of whitespace. Even then, it could be a useful reference point.
In case this seems useful then the script to create that report is available for download at Technet.
CHANGE HISTORY:
2012/Dec/01 – Version 1
2013/Feb/15 – Version 1.1 Added the CASE statement for complexity.
2014/Apr/29 – Version 1.2 Added Database and Schema name to the report.
Included objects that may not be refering to other objects.
Included views, functions and triggers.
Please feel free to share you feedback in the comments.
How to store text in multiple languages in SQL Server.
If you have to store and retrieve characters of any other language besides English in SQL Server, you must do the following –
Use a Unicode compatible data type for the table column. NVACHAR, NCHAR, NTEXT are the datatypes in SQL Server that can be used for storing non-English characters.
Precede the Unicode data values with an N (capital letter) to let the SQL Server know that the following data is from Unicode character set. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.
The N should be used even in the WHERE clause.
REFERENCE: Microsoft Support KB 239530
You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server http://support.microsoft.com/kb/239530
If the correct data-type is not used or the data is not preceded with an N, SQL Server will save the data to the table as ‘?’ or another garbled character.
The following scripts demonstrate saving and retrieving multi-lingual data to and from SQL Server. I have used Google Translate to get the characters of other languages. I left out far-east languages like Japanese and Chinese from the following example on purpose because those languages have a few other considerations that I’ll save for another blog post.
DROP TABLE dbo.unicodeData;
GO
CREATE TABLE dbo.unicodeData
( languageUsed VARCHAR(50)
, unicodeData NVARCHAR(200)
, nonUnicodeData VARCHAR(200) -- same data in a normal VARCHAR column for comparison
, comments VARCHAR(100)
);
GO
INSERT INTO dbo.unicodeData
( languageUsed
, unicodeData
, nonUnicodeData
, comments)
VALUES
('English'
, N'This is an example'
, N'This is an example'
, NULL)
,('Hindi'
, N'यह एक उदाहरण है.'
, N'यह एक उदाहरण है.'
, 'Using the preceding N in both strings but VARCHAR is still a ?')
,('Hindi'
, 'यह एक उदाहरण है.'
, 'यह एक उदाहरण है.'
, 'Not using the preceding N in both strings so both are a ?')
,('Kannada'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, NULL)
,('Arabic'
, N'هذا مثال على ذلك.'
, N'هذا مثال على ذلك.'
, NULL)
,('Czech'
, N'To je příklad.'
, N'To je příklad.'
, NULL);
GO
SELECT *
FROM dbo.unicodeData;
GO
-- Example of using N' in the WHERE clause
SELECT *
FROM dbo.unicodeData
WHERE unicodeData like N'%एक%';
