A Software Engineer and data professional's blog on SQL, data, databases, data architectures, data management, programming, Software Engineering, Project Management, ERP implementation and other IT related topics.
Pages
- 🏠Home
- 🗃️Posts
- 🗃️Definitions
- 🔢SQL Server
- 🎞️SQL Server: VoD
- 🏭Fabric
- 🎞️Fabric: VoD
- ⚡Power BI
- 🎞️Power BI: VoD
- 📚Data
- 📚Engineering
- 📚Management
- 📚SQL Server
- 🎞️D365: VoD
- 📚Systems Thinking
- ✂...Quotes
- 🧾D365: GL
- 💸D365: AP
- 💰D365: AR
- 👥D365: HR
- ⛓️D365: SCM
- 🔤Acronyms
- 🪢Experts
- 🗃️Quotes
- 🔠Dataviz & BI
- 🔠D365
- 🔠Fabric
- 🔠Engineering
- 🔠Management
- 🔡Glossary
- 🌐Resources
- 🏺Dataviz
- 🗺️Social
- 📅Events
- ℹ️ About
30 January 2011
💠🛠️ SQL Server: Learning Resources
For those interest to upgrade their knowledge to SQL Server 2008 R2 Edition check the Microsoft SQL Server 2008 R2 Update for Developers Training Course videos on Channel9. They cover topics like Location Awareness, CLR Integration, StreamInsight, Reporting Services, Application and Multi-Server Management, improvements on BLOB, T-SQL and tools for Excel 2010 or SharePoint 2010.
The ER diagrams of SQL Server’s DMVs or DMFs are available for download as PDF at SQL Server 2008 Systems Views Map, and the same for SQL Server 2005, respectively SQL Server 2008 R2. Even if not all objects and possible relations are shown, they could still save you from lot of effort as the diagram depicts the relations between the most important entities and the cardinality existing between them, plus the additional constraints participating in the joins. The entities represented: Objects, Types and Indexes, Linked Servers, CLR, Database Mirroring, Service Broker, Resource Governor, Transactions, Databases and Storage, Traces and Events, Execution Environment, Server information.
There are several free SQL Server books on SQLServerCentral.com coming from Redgate: A. Kuznetsov’s Defensive Database Programming, B. McGehee’s SQL Server Maintenance Plans, How to become an Exceptional DBA, Mastering SQL Server Profiler and Brad’s Sure Guide to SQL Server 2008, J. Magnabosco’s Protecting SQL Server Data, G. Fritchey’s SQL Server Execution Plans and SQL Server Crib Sheet Compendium (coauthor with A. Prasanna), R. Landrum’s SQL Server Tacklebox, plus several volumes of ‘Best of SQL Server Central’ authors in Vol. 1-4, Vol. 5, Vol. 6 and Vol. 7, and several other books to come.
It seems there are a few new books also on Scribd website: R. Colledge’s SQL Server Administration in Action, R. Vieira’s Professional Microsoft SQL Server 2008 Programming, M. Lee and M. Mansfield’s SQL Server 2008 Administration Instant Reference, plus many other books that can be retrieved by searching SQL Server 2008 on the respective site.
There are several papers coming from Microsoft and several professionals. It worth to check them:
- K. Lassen’s paper on Using Management Data Warehouse for Performance Monitoring, in which he provides several best practices for performance management of SQL Server 2008. Except the introduction in Management Data Warehouse features, he provides also an extension of the built-in functionality for indexes, plus several functions and queries.
- D. Kiely's paper on SQL Server 2008 R2 Security Overview for Database Administrators covers some of the most important security features in SQL Server 2008. There is a similar paper targeting SQL Server 2005.
- B. Beauchemin’s paper on SQL Server 2005 Security Best Practices - Operational and Administrative Tasks describes the best practices for setting up and maintaining security in SQL Server 2005.
- SQL Server 2008 Compliance Guide a paper written to help organizations and individuals understand how to use the features of the Microsoft® SQL Server® 2008 database software to address their compliance need.
- SQL Server 2008 Upgrade Technical Reference Guide essential phases, steps and best practices to upgrade existing instances of SQL Server 2000 and 2005 to SQL Server 2008.
- Technet Wiki Checklists on Database Engine Security, Database Engine Security, Database Engine Connections and Data Access. (probably more to come)
- Also Microsoft maintains a list of White Papers on SQL Server 2008, respectively SQL Server 2005.
07 January 2011
💎🏭SQL Reloaded: Pulling the Strings of SQL Server IV (Spaces, Trimming, Length and Comparisons)
Trimming
Trimming is the operation of removing the empty spaces found at the endings of a string. Unlike other programming languages which use only one function for this purpose (e.g. Trim function in VB or Oracle), SQL Server makes use of two functions used for this purpose, LTrim used to trim the spaces found at the left ending of the string, respectively RTrim, used to trim the spaces found at the right ending of the string.
-- trimming a string SELECT LTrim(' this is a string ') Length1 -- left trimming , RTrim(' this is a string ') Length2 --right trimming , LTrim(RTrim(' this is a string ')) Length2 --left & right trimming
As can be seen it’s not so easy to identify the differences, maybe the next function will help to see that there is actually a difference.
Note:
1) If it looks like the two trimming functions are not working with strings having leading or trailing spaces, then maybe you are not dealing with an empty character but rather with other characters like CR, LF, CRLF or other similar characters, rendered sometimes like an empty character.
Length
Before approaching other operations with strings, it’s maybe useful (actually necessary as we will see) to get a glimpse of the way we can determine the length of a string value, in other words how many characters it has, this being possible by using the Len function:
-- length of a string SELECT Len('this is a string') Length1 -- simple string , Len('this is a string ') Length2 --ending in space , Len(' this is a string') Length3 --starting with a space , Len(' this is a string ') Length4 --starting & ending with a space , Len(LTrim(' this is a string ')) Length5 --length & left trimming
,Len(RTrim(' this is a string ')) Length5 --length & right trimming
,Len(LTrim(RTrim(' this is a string '))) Length5 --length, left & right trimming
In order to understand the above results, one observation is necessary: if a strings ends in with one or more empty characters, the Len function ignores them, though this doesn’t happen with the leading empty characters, they needing to be removed explicitly if needed.
Comparisons
The comparison operation points the differences or similarities existing between two data types, involving at minimum two expressions that reduce at runtime to a data type and the comparison operator. This means that each member of comparison could include any valid combinations of functions as long they are reduced to compatible data types. In what concerns the comparison of strings, things are relatively simple, the comparison being allowed independently on whether they have fix or varying length. Relatively simple because if we’d have to go into details, then we’d need to talk about character sets (also called character encoding or character maps) and other string goodies the ANSI SQL standard(s) are coming with, including a set of rules that dictate the behavior of comparisons. So, let’s keep things as simple as possible. As per above attempt of definition, a comparison implies typically an equality, respectively difference, based on equal (“=”), respectively not equal (“<>” or “!=”). Here are some simple examples:
-- sample comparisons SELECT CASE WHEN 'abc' != 'abc ' THEN 1 ELSE 0 END Example1 , CASE WHEN ' abc' != 'abc' THEN 1 ELSE 0 END Example2 , CASE WHEN ' ' != '' THEN 1 ELSE 0 END Example3 -- error comparison , CASE WHEN 'abc' != NULL THEN 1 ELSE 0 END Example4 , CASE WHEN 'abc' = NULL THEN 1 ELSE 0 END Example5 -- adequate NULL comparison , CASE WHEN 'abc' IS NOT NULL THEN 1 ELSE 0 END Example6 , CASE WHEN 'abc' IS NULL THEN 1 ELSE 0 END Example7
Example1 | Example2 | Example3 | Example5 | Example7 |
0 | 1 | 0 | 0 | 0 |
The first three examples are demonstrating again the behavior of leading, respectively trailing spaces. The next two examples, even if they seem quite logical in terms of natural language semantics, they are wrong from the point of view of SQL semantics, and this because the comparison of values in which one of them is NULL equates to a NULL, thus resulting the above behavior in which both expressions from the 4th and 5th example equate to false. The next two examples show how the NULLs should be handled in comparisons with the help of IS operator, respectively it’s negation – IS NOT.
Like in the case of numeric values, the comparison between two strings could be expressed by using the “less than” (“<;”) and “greater than” (“?”) operators, alone or in combination with the equality operator (“<=”, “>=”) or the negation operator (“!>”, “<!”) (see comparison operators in MDSN). Typically an SQL Server database is case insensitive, so there will be no difference between the following strings: “ABC”, “abc”, “Abc”, etc. Here are some examples:
-- sample comparisons (case sensitive) SELECT CASE WHEN 'abc' < 'ABC' THEN 1 ELSE 0 END Example1 , CASE WHEN 'abc' > 'abc' THEN 1 ELSE 0 END Example2 , CASE WHEN 'abc' >= 'abc ' THEN 1 ELSE 0 END Example3 , CASE WHEN 'abc' <> 'ABC' THEN 1 ELSE 0 END Example4 , CASE WHEN 'abc' > '' THEN 1 ELSE 0 END Example5 , CASE WHEN ' ' > '' THEN 1 ELSE 0 END Example6
Example1 | Example2 | Example3 | Example4 | Example5 | Example6 |
0 | 0 | 1 | 0 | 1 | 0 |
The case sensitivity could be changed at attribute, table or database level. As we don’t deal with a table and the don’t want to complicate too much the queries, let’s consider changing the sensitivity at database level. So if you are using a non-production database, try the following script in order to enable, respectively to disable the case sensitivity:
--enabling case sensitivity for a database ALTER DATABASE <database name> COLLATE Latin1_General_CS_AS --disabling case sensitivity for a database ALTER DATABASE <database name> COLLATE Latin1_General_CI_AS
In order to test the behavior of case sensitivity, enable first the sensitivity and then rerun the previous set of example (involving case sensitivity).
Output:
Example1 | Example2 | Example3 | Example4 | Example5 | Example6 |
1 | 0 | 1 | 1 | 1 | 0 |
The queries work also in SQL databases in Microsoft Fabric.
03 January 2011
💎SQL Reloaded: Pulling the Strings of SQL Server V (Character Indexes)
-- searching a character into a string DECLARE @string varchar(50) SET @string = 'this is a string' SELECT CharIndex(' ', @string) Example1 , CharIndex(NULL, @string) Example2 , CharIndex(NULL, @string, NULL) Example3 , CharIndex(NULL, NULL, NULL) Example4
Example1 | Example2 | Example3 | Example4 |
5 | NULL | NULL | NULL |
If in the first example the function is used to retrieve the first occurrence of a space in the given string, in the last three examples is highlighted the behavior of the function in case one of the parameters is NULL, in such cases the function returning a NULL – this means that when other behavior is expected, it falls in your attributions to handle the NULL values accordingly.
Here’s a second set of examples based on the search of a string within another string. As can be seen there isn’t a difference at all in how the function is applied:
-- searching a string into a string DECLARE @string varchar(50) DECLARE @search varchar(50) SET @string = 'this_is_another_string' SET @search = 'is' SELECT @string Example1 -- simple string , CharIndex(@search, @string) Example2 -- occurring position , CharIndex(@search, @string, 5) Example3 -- second occurring position
Example1 | Example2 | Example3 |
this_is_another_string | 3 | 6 |
From the three examples, the third deserves some special attention because it attempts to retrieve the second occurrence of the string by using a hardcoded starting value. In fact the search could start a position further from the first occurrence, something like in the below example:
-- searching a string into a string in foward/reverse order DECLARE @string varchar(50) DECLARE @search varchar(50) SET @string = 'this_was_a_string' SET @search = '_' SELECT @string Example1 -- simple string , CharIndex(@search, @string) Example2 -- 1st occuring position (forward) , CharIndex(@search, @string, CharIndex(@search, @string)+1) Example3 -- 2nd occuring position (forward) , CharIndex(@search, @string, CharIndex(@search, @string, CharIndex(@search, @string)+1)+1) Example4 -- 3rd occuring position (forward)
Example1 | Example2 | Example3 | Example4 |
this_was_a_string | 5 | 9 | 11 |
The logic could be repeated over and over again in order to retrieve the n-th position, list all the positions of occurrence or the total number of occurrences.
Even if SQL Server doesn’t provide a function for retrieving the first occurrence in backward order, in other words starting from the end to the start, there is actually a simple trick that might do it. As SQL Server provides a Reverse function, which reverses the value of the string provided as parameter.
-- 1st occurring position backwards DECLARE @string varchar(50) DECLARE @search varchar(50) SET @string = 'this_was_a_string' SET @search = '_' SELECT Len(@string) - CharIndex(@search, Reverse(@string))+1 Example
Unfortunately the logic needed to retrieve the second and following occurrences starting from a given position it’s not so easy readable. Therefore, for the sake of “simplicity”, here’s the logic for reverse processing incorporated in CharIndexRev function:
-- reverse CharIndex CREATE FUNCTION dbo.CharIndexRev( @search varchar(50) , @string varchar(500) , @start int = 0) RETURNS int AS BEGIN RETURN Len(@string) - CharIndex(@search, Reverse(@string), CASE WHEN IsNull(@start, 0) != 0 THEN Len(@string) - @start+1 ELSE 0 END)+1 END
-- searching a string into a string in reverse order DECLARE @string varchar(50) DECLARE @search varchar(50) SET @string = 'this_was_a_string' SET @search = '_' SELECT dbo.CharIndexRev(NULL, @string, 0) Example1 , dbo.CharIndexRev(@search, NULL, 0) Example2 , dbo.CharIndexRev(NULL, NULL, 0) Example3 , dbo.CharIndexRev(@search, @string, 0) Example4 , dbo.CharIndexRev(@search, @string, dbo.CharIndexRev(@search, @string, 0)-1) Example5 , dbo.CharIndexRev(@search, @string, dbo.CharIndexRev(@search, @string, dbo.CharIndexRev(@search, @string, 0)-1)-1) Example6
Example1 | Example2 | Example3 | Example4 | Example5 | Example6 |
NULL | NULL | NULL | 11 | 9 | 5 |
As can be seen, the output of the last three examples matches the reversed output of the previous set (11, 9, 5) vs. (5, 9, 11), while the first three examples exemplify the behavior of the function when one of the parameters is Null.
The queries work also in SQL databases in Microsoft Fabric.
01 January 2011
🔖Book Review: Lynn Beighley's "Head First SQL" (2011)
Coming back to the current title, Head First SQL, it covers almost everything somebody has to know about database design, DML and DDL statements, database objects and security in a nutshell. It focuses only on the SQL Standard (I wish I know which one as there are several versions/revisions, see SQL 1999), so it could be used for all type of RDBMS that implements the standard, even if the book is built around MySQL. A consequence of this aspect is that any vendor specific flavors won’t be found in here, so the book should be used in combination with the documentation or the books that target the vendor-specific functionality. There are also several topics not covered but mentioned roughly: reserved words and special characters, temporary tables, data conversion, some numeric functions and operators, indexes, etc.
I must remark that some of the definitions are somehow ambiguous, and I feel that in some cases additional clarifications are necessary. Some examples in this direction could be considered the very first important definitions, the ones of the database seen as "a container that holds tables and SQL structures related to those tables", the one of column seen as piece of data stored by a table, or of a row seen as a single set of columns that describe attributes of a single thing. The first definition is quite acceptable if it’s ignored the existence of non-relational databases (it’s also true that the book targets RDBMS even if not evident), however the other two definitions are too vague, at least from my point of view.
Notes:
1. As I learned on the hard way, not all book formats and writing styles are matching everybody’s taste and expectations. The simplistic and rich visually format of the book could annoy as well, so might be a good idea to browse first the book on Scribd before buying it.
2. The source code is available here.
31 December 2010
Meta-Blogging: Past, Present and Future
Introduction
Even if I started to blog 3-4 years ago, only this year (still 2010) I started to allocate more time for blogging, having two blogs on which I try to post something periodically: SQL Troubles and The Web of Knowledge plus a homonym Facebook supporting group (for the later blog). As a parenthesis, the two blogs are approaching related topics from different perspective, the first focusing on data related topics, while the second approaching data from knowledge and web perspective; because several posts qualify for both blogs, I was thinking to merge the two blogs, though given the different perspectives and types of domains that deal with them, at least for the moment I’ll keep them apart. Closing the parenthesis, I would like to point out that I would love to allocate more time though I have to balance between blogging, my professional and personal life, and even if the three have many points in common, some delimitation it’s necessary. Because it’s the end of a year, I was thinking that it’s maybe the best time to draw the line and analyze the achievements of the previous year and the expectations for the next year(s), for each of the two blogs. So here are my thoughts:
Past and Present
There are already more than 10 years since I started to work with the various database systems, my work ranging from data modeling to database development, reporting, ERP systems, etc. I can’t consider myself an expert, though I’ve accumulated experience in a whole range of areas, fact that I think entitles me to say that I have something to write about, even if the respective themes are not rocket science. In addition, it’s the human endeavor of learning something new each day, and in IT that’s quite an imperative, the evolvement of various technologies requesting those who are working in this domain to spend extra hours in learning new things or of consolidating or reusing knowledge in new ways. I considered at that time, and I still do, that blogging helps the learning process, allowing me to externalize the old or new knowledge, clear my thoughts, have also some kind of testimony of what I know or at least a repository of information I could reuse when needed, and eventually receive some feedback. These are few of the reasons for which this blog was born, and I hope the information presented in here are useful also for other people.
During the past year I made it to post on my blog more than 100 entries on various topics, the thematic revolving around strings, hierarchical queries, CLR functionality, Data Quality, SSIS, ERPs, Reports, troubleshooting, best practices, joins, etc. Not all the posts rose to my expectations, though that’s a start, hoping that I will find a personal style and the quality of the posts will increase. I can’t say I received lot of feedback, however based on the user access’ statistics provided by Clustrmaps and Google the number of visitors this year was somewhere around 8500, close to my expectations. Talking about the number of visitors, it’s nice to have also some visualization, so for this year’s statistics I’ll use Clustrmaps visualization, which provides a more detailed geographical overview than Google’s Stats, while for trending I show below Google’s Stats (contains data from May until today):
What I find great about Google’s Stats is that it provides also an overview of the most accessed posts and the traffic sources. There are also some statistics of the audience per browsers and OS, though they are less important for my blogging requirements, at least for the moment.
What I find interesting is that most visited posts and searched keywords were targeting SSIS and Oracle vs. SQL Server-related topics. So, if for the future I want more traffic than maybe I should diversify my topics in this direction.
Future
I realize that I started many topics, having in the next year to continue posting on the same, but also targeting new topics like Relational Theory, Business Intelligence, Data Mining, Data Management, Statistics, SQL Server internals, data technologies, etc. Many of the posts will be an extension of my research on the above topics, and I was thinking to post also my learning notes with the hope that I will receive more feedback. I realized that I need to be more active and provide more feedback to other blogs, using the respective comments as gateways to my blog and try to build a network around it. I was thinking also to start a Facebook “support group”, posting the links I discovered, quotes or impressions in a more condensed form, but again this will take me more time, so I’m not sure if it makes sense to do that. Maybe I should post them directly on the blog, however I wanted my posts to be a little more consistent than that. Anyway I know also that I won’t manage to post more than an average of one post per week though per current expectations is ok.
Right now all the posts are following a push model, in other words I push the content independently of whether there is a demand or not for it. It’s actually natural because the blog is having a personal note. In the future I’m expecting to move in the direction of a pull model, in other words to write on topics requested by readers, however for this I need more feedback from you, the reader. So please let me know what topics you’d like to read!
I close here, hoping that the coming year (2011) will be much better than the current one. I wish to all of you, a Happy New Year!
14 December 2010
💎🏭SQL Reloaded: Pulling the Strings of SQL Server III: (Concatenation)
Concatenation is the operation of joining two or more string values within an expression. In SQL Server the “+” operator is used for concatenation, and it could be used to concatenate two or more members. In order to concatenate two members from which one of them is a string, the second term needs to be explicitly converted to a string data type, otherwise an error will occur. For readability or post-processing purposes, the strings are concatenated using a delimiter in order to delimit the boundaries of the initial value, it could be used a space, a comma, a semicolon, a pipe, a tab or any other character that could be used typically in delimiting columns.
-- concatenation of strings SELECT 'a string' + ' and ' + 'a second string' Example1 , 'a string' + ',' + 'a second string' Example2 , '1245' + '67890' Example3 , '1245' + '.' + '67890' Example4
The concatenation of string variables or columns functions based on the same principles:
-- concatenating string variables DECLARE @string1 varchar(50) DECLARE @string2 varchar(50) DECLARE @string3 varchar(50) SET @string1 = 'this is a string' SET @string2 = 'this is another string' SELECT @string1 + ' and ' + @string2 Example1 , @string1 + char(31) + @string2 Example2 , @string1 + ', ' + @string2 Example3 , @string1 + ' ' + @string3 Example4 , @string1 + IsNull(@string3, '!') Example5
Here’s another example based on the concatenation of columns coming from two joined tables from AdventureWorks database:
-- concatenating columns of joined tables SELECT PAD.AddressID , IsNull(PAD.AddressLine1, '') + IsNull(', ' + PAD.AddressLine2, '') + IsNull(', ' + PAD.City, '') + IsNull(', ' + PAD.PostalCode, '') + IsNull(', ' + PSP.Name, '') Address FROM Person.Address PAD JOIN Person.StateProvince PSP ON PAD.StateProvinceID = PSP.StateProvinceID
As stressed in the previous post, the NULL values need to be adequately handled either by initializing values or by using the IsNull or COALESCE functions. The concatenation of strings combined with IsNull function could be used creatively in order to add a comma only when a value is not null, as in the above example.
There are scenarios in which is needed to concatenate the values belonging to the same column but from different records, for example concatenating the email values in order to send a single email to all the users in one single action. Before the introduction of common table expressions (CTE), wasn’t possible to concatenate the string values belonging to different records, at least not in a query, this functionality being achieved by using cursors or loops, or simply performed on client or intermediate layers. As I already gave an example on how to use cursor in order to loop through the values of a table and concatenate them (see “Cursors and Lists” post), I will focus on the use of loops and simple CTEs.
Loops are one of the basic functionality in programming languages, no matter of their complexity or type. Either if are talking about WHILE, FOR, foreach or do … until loops, the principles are the same: perform a list of actions until one or more conditions are met. In this case the actions performed is reduced to a set of concatenations based on the letters of the (English) alphabet:
-- concatenation within a loop DECLARE @list varchar(max) DECLARE @index int SET @list = '' SET @index = ASCII('a') WHILE (@index<ASCII('z')) BEGIN SET @list = @list + ', ' + Char(@index) SET @index = @index + 1 END SELECT @list Result
There is more work that needs to be performed in order to remove the leading comma from the output, but that’s a topic for the next post, when discussing about decomposition of strings.
CTEs are a little more complex to use than the loops, though the concatenation could be achieved across records and this in one query and not in procedural language as in the above example. In order delimit the two components of a CTE, I made use of a second CTE which simulates the existence of a given table:
-- concatenation across records ;WITH Data(Column1, Ranking) AS ( -- preparing test data SELECT 'A' Column1, 0 Ranking UNION ALL SELECT 'B' Column1, 1 Ranking UNION ALL SELECT 'C' Column1, 2 Ranking ) , Result(Column1, Ranking) AS ( -- performing the actual concatenation SELECT Cast(Column1 as nvarchar(max)) Column1 , Ranking
FROM Data
WHERE Ranking = 0 UNION ALL SELECT Cast(B.Column1 + ',' + A.Column1 as nvarchar(max)) Column1 , A.Ranking FROM Data A JOIN Result B ON A.Ranking - 1 = B.Ranking ) SELECT Column1 FROM Result WHERE Ranking IN (SELECT MAX(Ranking) FROM Result)
The logic for doing a simple concatenation seems maybe complicated, though the volume of work is not so big if we ignore the first CTE. On the other side I introduced an “index” within the Ranking column, fact that allows processing easier the records. When dealing with the records coming from a table it’s probably much easier to use one of the ranking functions that suits best.
The queries work also in SQL databases in Microsoft Fabric
11 December 2010
💎🏭SQL Reloaded: Pulling the Strings of SQL Server II (Creation and Selection)
-- selecting a string value SELECT 'this is a string' -- string 1 , 'and this is a second string' --string2 -- selecting a string value (named columns) SELECT 'this is a string' String1 , 'and this is a second string' String2
Pretty simple, isn’t it? This kind of scripts could be useful especially when debugging logic or testing an expression (e.g. functions at work), the advantage residing in the fact that is not necessary to built a table in order to test simple things. When performing multiple operations with the same values could be handy to store the values in declared variables:
-- selecting string variables DECLARE @string1 varchar(50)
DECLARE @string2 char(50) DECLARE @string3 char(50) = 'this is a string' SET @string1 = 'this is a string' SELECT @string1 String1 , @string2 String2 , @string3 String3
As can be seen a not initialized variable has by default the value NULL, fact that could lead to unexpected behavior problems when used in expressions involving multiple variables. Therefore it’s recommended to initialize the values with the empty string or at least to handle the nulls in expressions. Starting with SQL Server 2008 it’s possible to declare and initialize variables within the same statement, so the above code won’t work in previous versions, unless the third declaration is broken into two pieces as per definition of first string.
It looks like that’s all, at least in what concerns the direct declaration of a string, be it implicit or explicit. However a basic introduction into strings’ creation is incomplete without mentioning the creation of strings from other data types and the various functions that could be used for this purpose. Ignoring the Cast and Convert functions used to explicitly convert the other data types to string data types, there are several other functions for this purpose, namely Char, Space and Replicate.
Probably some of you are already familiar with the ASCII (American Standard Code for Information Interchange) character-encoding scheme (vs. binary encoding) used to encode files. ASCII code represents a mapping between number and characters, SQL Server supporting the transformation between two sets through the ASCII and Char functions. If ASCII translates a non-unicode character into an integer, the Char function translates an integer value into a non-unicode character. The integer values range between 0 and 255, they encompassing the 0-9 digits, the characters of English together with the diacritics of other important languages, punctuation signs and several graphical characters. The mapping between the two sets is unique, and as can be seen from the below example based on a common table expression, the functions are inverse:
-- ASCII character values WITH CTE AS ( SELECT 0 [Index] UNION ALL SELECT [Index]+1 [Index] FROM CTE WHERE [Index]<255 ) SELECT [Index] , CHAR([Index]) [Character] , ASCII(CHAR([Index])) [ASCII] FROM CTE OPTION (MAXRECURSION 0)
There is not much to say about the Space and Replicate functions, the Space function returns a string of repeated spaces, while the Replicate function forms a string as a repeated sequence of values. The definition of the Space function could be considered as redundant as long the same output could be obtained by using the space as repeating sequence.
-- Space & Replicate at work SELECT Space(1) Example1 , Space(5) Example2 , Replicate(' ', 1) Example3 , Replicate(' ', 5) Example4 , Replicate('10', 1) Example5 , Replicate('10', 2) Example6 , Replicate('10', 3) Example7
As per the last statement, the first and third examples, respectively the second and fourth example will return the same values, unfortunately the output of the respective examples it’s not so easy to see. For exemplification, it could have been enriched by comparing or concatenating the strings, though that’s a topic for the next post.
The queries work also in SQL databases in Microsoft Fabric
About Me

- Adrian
- Koeln, NRW, Germany
- IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.