SQL Azure – Reset Primary Key Identity/Index to Zero after Deleting all records

I was faced with a situation where I needed to set up an index for widgets but I wanted those to start at zero. I made some attempts to import information and deleted those records from the table several times and was faced with a situation where users might see the index occasionally. As a result I wanted the index to start from 1 and at least at the start go up to the approximate number of widgets.

Its a quick search using google to get this but wanted to reset the identity on a blank table.

In SSMS / navigate to the database and open a new query window

DBCC CHECKIDENT (dbo.t001table, RESEED,0);
GO

MS SQL Azure – Conditional Computed Column Definition Using CASE

Setting aside for the moment the rights and wrongs of using persisted columns sometimes it is just a great way to add some automation to your database and make it clearer for the user.

But what if you want to add a conditional computed column for rows based on another value within that row. Here’s something I worked out.

ALTER TABLE ProjectManagement
ADD FutureorPast AS CAST
(
CASE
WHEN TargetDate > GetDate() or TargetDate is Null THEN 'FUTURE' WHEN TargetDate <= GetDate() THEN 'PAST'
ELSE ''
END as nvarchar(6)
)
GO

MS SQL Azure – CONCAT_WS Working with addresses and nicely formatting separated fields

I recently came across a very useful function that I am told was introduced in SQL Server 2017 called CONCAT_WS
I have never used it in any other application other than SQL Server I hope it exists in MYSQL and PostgreSQL

This will join together a series of fields with chosen separators and by combining it with NULLIF can be used to remove spaces and format address nicely.
For applications that will at some point need addresses either for post or for information this function allows the display of addresses in a format the most clearly reflects the requirements of most postal systems. Needless to say it is likely that most of my systems will at some point transition to the use of this function.

Here is a link to Microsofts documentation on the function CONCAT_WS

Firstly let me review what I am starting to standardise on with regard to address fields which should be a balance between enough detail to store any address on the planet but not so much that it is overly complicated. I’ve expanded their descriptions somewhat. In my experience someone will have created an excel spreadsheet to start the process of recording information, often they standardise on column names such as address01/02/03 etc. If that breakdown exists then I have indicated the fields that I would normally expect to map those fields too.

flatno-buildingdivision
houseno-buildno
housename-buildname
streetfirst-address01
streetsecond
locality-address02
towncity-address03
stateoradminlocality
postcode-address04
country-address05

And here is an example of the code implemented.

CREATE VIEW v001formattedaddress AS SELECT pkid,
email,
CONCAT_WS(' ',NULLIF(dbo.t001addresstable.firstname,' '), NULLIF(dbo.t001addresstable.surname,' ')) AS fullname,
CONCAT_WS(CHAR(13) + CHAR(10),
NULLIF(dbo.t001addresstable.flatno, ' '), 
NULLIF(dbo.t001addresstable.houseno,' '), 
NULLIF(dbo.t001addresstable.housename,' '),
NULLIF(dbo.t001addresstable.streetfirst,' '),
NULLIF(dbo.t001addresstable.streetsecond,' '),
NULLIF(dbo.t001addresstable.locality,' '),
NULLIF(dbo.t001addresstable.towncity,' '),
NULLIF(dbo.t001addresstable.stateoradminlocality,' '),
NULLIF(dbo.t001addresstable.postcode,' '),
NULLIF(dbo.t001addresstable.country,' '),
REPLICATE('-', 30) ) AS addressconcat FROM dbo.t001addresstable;

Additionally on reflection for a recent project I made up a list of countries that covers most in the world. For my project I put an include field next to them to allow system administrators to include whether these would be visible in the drop down. Clearly while overtime more and more countries may be added there I would expect it to be years or possibly decades before some of the values of the smaller nations are needed. (For my particular application anyway)

I standardised on the following the 2 digit codes are the ISO Country code standard

AD Andorra
AE United Arab Emirates
AF Afghanistan
AG Antigua and Barbuda
AI Anguilla
AL Albania
AM Armenia
AO Angola
AQ Antarctica
AR Argentina
AT Austria
AU Australia
AW Aruba
AX Aland Islands
AZ Azerbaijan
BA Bosnia and Herzegovina
BB Barbados
BD Bangladesh
BE Belgium
BF Burkina Faso
BG Bulgaria
BH Bahrain
BI Burundi
BJ Benin
BL Saint Barthélemy
BM Bermuda
BN Brunei Darussalam
BO Bolivia, Plurinational State of
BQ Bonaire, Sint Eustatius and Saba
BR Brazil
BS Bahamas
BT Bhutan
BV Bouvet Island
BW Botswana
BY Belarus
BZ Belize
CA Canada
CC Cocos (Keeling) Islands
CD Congo, the Democratic Republic of the
CF Central African Republic
CG Congo
CH Switzerland
CI Cote d’Ivoire
CK Cook Islands
CL Chile
CM Cameroon
CN China*
CO Colombia
CR Costa Rica
CU Cuba
CV Cape Verde
CW Curaçao
CX Christmas Island
CY Cyprus
CZ Czech Republic
DE Germany
DJ Djibouti
DK Denmark
DM Dominica
DO Dominican Republic
DZ Algeria
EC Ecuador
EE Estonia
EG Egypt
EH Western Sahara
ER Eritrea
ES Spain
ET Ethiopia
FI Finland
FJ Fiji
FK Falkland Islands (Malvinas)
FO Faroe Islands
FR France
GA Gabon
GB United Kingdom
GB United Kingdom Northern Ireland
GD Grenada
GE Georgia
GF French Guiana
GG Guernsey
GH Ghana
GI Gibraltar
GL Greenland
GM Gambia
GN Guinea
GP Guadeloupe
GQ Equatorial Guinea
GR Greece
GS South Georgia and the South Sandwich Islands
GT Guatemala
GW Guinea-Bissau
GY Guyana
HK Hong Kong SAR China
HM Heard Island and McDonald Islands
HN Honduras
HR Croatia
HT Haiti
HU Hungary
ID Indonesia
IC Spain Canary Islands
IE Ireland Republic
IL Israel
IM Isle of Man
IN India
IO British Indian Ocean Territory
IQ Iraq
IR Iran, Islamic Republic of
IS Iceland
IT Italy
JE Jersey
JM Jamaica
JO Jordan
JP Japan
KE Kenya
KG Kyrgyzstan
KH Cambodia
KI Kiribati
KM Comoros
KN Saint Kitts and Nevis
KP Korea, Democratic People’s Republic of
KR Korea, Republic of
KW Kuwait
KY Cayman Islands
KZ Kazakhstan
LA Lao People’s Democratic Republic
LB Lebanon
LC Saint Lucia
LI Liechtenstein
LK Sri Lanka
LR Liberia
LS Lesotho
LT Lithuania
LU Luxembourg
LV Latvia
LY Libyan Arab Jamahiriya
MA Morocco
MC Monaco
MD Moldova, Republic of
ME Montenegro
MF Saint Martin (French part)
MG Madagascar
MK Macedonia, the former Yugoslav Republic of
ML Mali
MM Myanmar
MN Mongolia
MO Macau SAR China
MQ Martinique
MR Mauritania
MS Montserrat
MT Malta
MU Mauritius
MV Maldives
MW Malawi
MX Mexico
MY Malaysia
MZ Mozambique
NA Namibia
NC New Caledonia
NE Niger
NF Norfolk Island
NG Nigeria
NI Nicaragua
NL Netherlands
NO Norway
NP Nepal
NR Nauru
NU Niue
NZ New Zealand
OM Oman
PA Panama
PE Peru
PF French Polynesia
PG Papua New Guinea
PH Philippines
PK Pakistan
PL Poland
PM Saint Pierre and Miquelon
PN Pitcairn
PS Palestine
PT Portugal
PY Paraguay
QA Qatar
RE Reunion
RO Romania
RS Serbia
RU Russian Federation
RW Rwanda
SA Saudi Arabia
SB Solomon Islands
SC Seychelles
SD Sudan
SE Sweden
SG Singapore
SH Saint Helena, Ascension and Tristan da Cunha
SI Slovenia
SJ Svalbard and Jan Mayen
SK Slovakia
SL Sierra Leone
SM San Marino
SN Senegal
SO Somalia
SR Suriname
SS South Sudan
ST Sao Tome and Principe
SV El Salvador
SX Sint Maarten (Dutch part)
SY Syrian Arab Republic
SZ Swaziland
TC Turks and Caicos Islands
TD Chad
TF French Southern Territories
TG Togo
TH Thailand
TJ Tajikistan
TK Tokelau
TL Timor-Leste
TM Turkmenistan
TN Tunisia
TO Tonga
TR Turkey
TT Trinidad and Tobago
TV Tuvalu
TW Taiwan
TZ Tanzania United Republic of
UA Ukraine
UG Uganda
US United States
UY Uruguay
UZ Uzbekistan
VA Holy See (Vatican City State)
VC Saint Vincent and the Grenadines
VE Venezuela Bolivarian Republic of
VG Virgin Islands, British
VN Vietnam
VU Vanuatu
WF Wallis and Futuna
WS Samoa
YE Yemen
YT Mayotte
ZA South Africa
ZM Zambia
ZW Zimbabwe

MS SQL Azure – Creating contained users – SQL Authentication – DACPAC and BACPAC import

In every database engine it is important to create logins that enforce security around your database and that can be maintained.
Additionally if you are working for a client you may wish to transfer this database at some point in the future to the client.

In SQL Azure users can be created against the master database in the instance and the role can then be transferred to individual databases.

Fine but there may be circumstances where you want to isolate roles to individual databases so that when they are moved the roles move with them and are not left in the master database.
The following sets out an example of how to set up a Contained database in SQL Azure along with something extra you have to think about when re-importing to an SQL Server instance.

Using your sysadmin account connect to the database you wish to add a user to and run;

CREATE USER rocketengineapplication WITH PASSWORD = 'bluedanube';
ALTER ROLE db_owner ADD MEMBER rocketengineapplication;

Now developers could use this password and username to login to the database and do most of what is required without having any privileges to the SQL Server and if you ever transfer the database the role will pass with the database.

Here is a link to built in database roles
SQL Database Roles

Secure a single or pooled database in SQL Azure

and here is a useful query that can be run to identify the users and roles that a particular database has. This allows you to check what users are on a database and what are there roles.

SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName
FROM sys.database_principals AS u
LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
WHERE
    u.type NOT IN('R', 'G')
ORDER BY
      UserName
    , RoleName;

Note that when deploying or importing data tier applications to for instance SQL Express versions by default contained database authentication is deactivated and must be activated.

To do this connect to the local sql express instance and highlight the Databases on the left hand side then run the following code

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

DACPAC (structure only) and BACPAC (data and structure) import should now be possible locally!

This will specifically assist with the following error message which I was getting when I tried to import the database back into a local machine.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 12824, Level 16, State 1, Line 5 The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database. You may need to use RECONFIGURE to set the value_in_use.
Error SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N'$(DatabaseName)')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
CONTAINMENT = PARTIAL
WITH ROLLBACK IMMEDIATE;
END

Error SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.
Error SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N'$(DatabaseName)')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
CONTAINMENT = PARTIAL
WITH ROLLBACK IMMEDIATE;
END

(Microsoft.SqlServer.Dac)

MS SQL Azure – Computed Columns

It can be really nice to create a computed column and add it to the table rather than adding it in a field

This would work well using the function listed in the previous post where I automatically calculate the age of trees.

Add Computed Column to SQL Azure Table

ALTER TABLE dbo.t001trees ADD treeage AS (dbo.functionyearmonthday(dbo.t001trees.plantdate, GETDATE()));

This will appear in the table and look like it is an actual field but it is calculated and will not keep the figures in the table unless you specify persistance

see the above link for further reading on this topic

MS SQL Azure – TSQL to name the age between dates in text

It is relatively easy to calculate the number of either years, months days hours or seconds between two dates using the native DATEDIFF built in function which comes with SQL.

e.g.

SELECT dbo.t001trees.pkid, 
dbo.t001trees.plantdate, 
DATEDIFF(Year, dbo.t001trees.plantdate, GETDATE()) as treeage 
from dbo.t001trees;

But here is a function that will spell it out into a string that reads something like
2 days
1 month 2 days
2 years 1 month 2 days

CREATE OR ALTER FUNCTION dbo.functionyearmonthday
(
@datefrom Date,
@dateto Date
)
RETURNS varchar(100)
as
BEGIN
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT;

SET @date1 = @datefrom
SET @date2 = @dateto

SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)

SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)

SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)

SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years ','')
+ ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months ','')
+ ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days','')

RETURN @result;
END

And if you would like to call the function from another query here is an example

SELECT dbo.functionyearmonthday(dbo.t001trees.plantdate, GETDATE()) as treeage FROM dbo.t001trees

That is enough for most people but it can be expanded to include hours minutes seconds and milliseconds which could be useful if you need more precision it can be seen that the DATEDIFF native function is included extensively within this function.

CREATE OR ALTER FUNCTION dbo.functiontimeperiodmoreprecision
(
@datefrom Date,
@dateto Date
)
RETURNS varchar(100)
as
BEGIN
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT,
@hours INT, @minutes INT, @seconds INT, @milliseconds INT;

SET @date1 = @datefrom
SET @date2 = @dateto

SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)

SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)

SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)

SELECT @hours=DATEDIFF(hh, @date1, @date2)
IF DATEADD(hh, -@hours, @date2) < @date1
SELECT @hours=@hours-1
SET @date2= DATEADD(hh, -@hours, @date2)

SELECT @minutes=DATEDIFF(mi, @date1, @date2)
IF DATEADD(mi, -@minutes, @date2) < @date1
SELECT @minutes=@minutes-1
SET @date2= DATEADD(mi, -@minutes, @date2)

SELECT @seconds=DATEDIFF(s, @date1, @date2)
IF DATEADD(s, -@seconds, @date2) < @date1
SELECT @seconds=@seconds-1
SET @date2= DATEADD(s, -@seconds, @date2)

SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)

SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
+ ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')
+ ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
+ ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
+ ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
+ ISNULL(' ' + CAST(@seconds AS VARCHAR(10))
+ CASE
WHEN @milliseconds > 0
THEN '.' + CAST(@milliseconds AS VARCHAR(10))
ELSE ''
END
+ ' seconds','')

RETURN @result
END

MS Access Function : Function to create SQL union queries

Another small function that can speed up the text required to be written for large union queries.

Typically this can be used with
MS Access Function : Scan through a directory and write list of files to a table.

There are a number of data providers that provide data files broken down into different geographical areas. In previous posts I have outlined how we can get these all into Postgis. But once they are in postgis (or any other database) you may wish to get these separate tables into one single global table. Clearly a union query will do this, however it can be time consuming to write the union query out as it simply has so many tables in it.

I used the code in the link to scan a directory and get all the filenames (in this case shape files of the UK road network) into a table that I called UKRoadLinks which had two fields PKID (primary long integer autonumber) and Filen text field where Filen were the filenames.

I then wrote the following function to write a text file that on completion will contain an sql union of all the tables listed in your recordset – I then copied and pasted this into Postgis database within which I had already imported all the sub tables to union the tables into a single copy. Alter the recordset source if for instance if you wish to use only a subset. The nice thing about this is if you have hundreds of tables to amalgamate there should be less likelyhood of you accidentally missing or misspelling table names.

Public Function createunionsqllinks()

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("UKRoadLinks")

Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("c:\data\sqlmerge.txt", True)
TextFile.WriteLine ("CREATE TABLE sqltomergetables AS ")
Do Until rst.EOF = True
TextFile.WriteLine (Chr$(40) & "select * from " & rst!Filen & Chr$(41) & " UNION ")
rst.MoveNext
Loop
rst.Close
TextFile.WriteLine (";")
TextFile.Close

MsgBox "Created"

End Function

SQL SERVER – Create TSQL Trigger to add date to existing record after INSERT or UPDATE

I came across an interesting issue with a web application I have been trying to create. DEFAULTS in SQL Server didn’t seem to be registering when new records were inserted through the web application.

How come? Surely the web application is not bypassing requirements set out by the database.

What I believe is happening is that the web application is explicitly supplying a null value to the dateupdated field and because of this the default value is NOT applied by SQL Server. You are therefore in the strange position of creating a trigger on a column after insert. Normally you would never really do this as that’s specifically what default is for.

Here’s code to create the trigger on the SQL Server that will do just this.

CREATE TRIGGER TR_moddate
On T0001PersonData
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE T0001PersonData SET DATEUPDATED=GETDATE()
WHERE PKID IN (SELECT PKID FROM INSERTED)
END

Some explanation – This updates the field DateUpdated within the table T0001PersonData after an update or an insert. If you have default on a column this may mean that it updates to default then to null then to trigger so you may wish to remove the default on the column. Alternatively you may wish to get the front end to update to the current date. I guess this could have performance issues at scale but my application is pretty small beer so I am happy with this at the moment. I think I prefer to have it at database level as well. It should be noted that INSERTED is actually a ALIAS table created by SQL Server and held in memory that allows for reference to pull out items before they are registered in the database. Leaving out the where clause may have resulted in the application updating the dateupdated column for all records anytime an update or insert was performed. BE WARNED