When dealing with NULLs, most beginners think it has some value, but it means unknown.

This article will explore what's NULL is and SQL's NULL conditional operators.

These operators are IS NULL and IS NOT NULL.

What is NULL?
It is used to specify that a particular data value does not exist.

That's why you'll hear from other SQL developers or administrators; NULL represents a missing or unknown value. And from that statement, I think it can be easily remembered and understood.

Moreover, the ANSI behavior for NULL values any expression containing a NULL value is NULL.

Let's see some examples below.
Examples

1. Let's try to add a number to a NULL value.
SELECT (NULL + 1) [Sum of NULL and 1] -- NULL

Output

2. Let's try to concatenate a string to a NULL value.
SELECT (NULL + 'C# Corner Rocks') [Concat NULL to a word] -- NULL

IS NULL and IS NOT NULL
I still remember learning TSQL when I wanted to check a column for NULL. I used the equal (=) or the not (<>) operator.

Let's see an example below.
SELECT * FROM [tblSomething] WHERE name = NULL
SELECT * FROM [tblSomething] WHERE name <> NULL

Note: the example code above won't be behaving as expected. You won't see any results!

Checking for NULL values is impossible with comparison operators such as =, < or <>.

It is because this is not the default behavior of it.

That's why instead of using these equal and or not operators, we can use IS NULL or IS NOT NULL instead.

Let's see an example below.

DROP TABLE IF EXISTS temp.dbo.#tempFruits;

CREATE TABLE #tempFruits
(Id INT IDENTITY,
 [Name] NVARCHAR(10) NOT NULL,
 [Description] NVARCHAR(50) NULL);

 INSERT INTO #tempFruits (Name, Description)
 VALUES
 ('Apple', 'The domesticated tree Malus'),
 ('Kiwifruit', NULL),
 ('Pineapple', NULL),
 ('Papaya', 'Also called papaw or pawpaw'),
 ('Cherry', NULL)

 SELECT * FROM #tempFruits WHERE [Description] = NULL -- 0 result
 SELECT * FROM #tempFruits WHERE [Description] IS NULL -- 3 results

 SELECT * FROM #tempFruits WHERE [Description] <> NULL -- 0 result
 SELECT * FROM #tempFruits WHERE [Description] IS NOT NULL -- 2 results

As you can see, in our code sample above, we have combined the equal and the not operator with their NULL operators' counterparts.

Let's try to see the differences.

First, instead of = operator, we used the IS NULL operator to check whether a column is NULL.
SELECT * FROM #tempFruits WHERE [Description] = NULL -- 0 result
SELECT * FROM #tempFruits WHERE [Description] IS NULL -- 3 results

Output

Second, instead of the <> operator, we used the IS NOT NULL operator to check whether a column is not NULL.
SELECT * FROM #tempFruits WHERE [Description] <> NULL -- 0 result
SELECT * FROM #tempFruits WHERE [Description] IS NOT NULL -- 2 results

Output

SET ANSI_NULLS ON or OFF
Hopefully, now you understand that by default, you can't use the equality operators when dealing with NULLs and instead use IS NULL or IS NOT NULL operators.

However, this default ANSI behavior can be turned off by adding this line of instruction SET ANSI_NULLS OFF before executing the previous entire code.

Let's add the SET ANSI_NULLS OFF on our previous example and see the output.

SET ANSI_NULLS OFF; -- LET'S TURN OFF THE DEFAULT ANSI BEHAVIOR!

DROP TABLE IF EXISTS temp.dbo.#tempFruits;

CREATE TABLE #tempFruits
(Id INT IDENTITY,
 [Name] NVARCHAR(10) NOT NULL,
 [Description] NVARCHAR(50) NULL);

 INSERT INTO #tempFruits (Name, Description)
 VALUES
 ('Apple', 'The domesticated tree Malus'),
 ('Kiwifruit', NULL),
 ('Pineapple', NULL),
 ('Papaya', 'Also called papaw or pawpaw'),
 ('Cherry', NULL)

 SELECT * FROM #tempFruits WHERE [Description] = NULL -- 3 result
 SELECT * FROM #tempFruits WHERE [Description] IS NULL -- 3 result

 SELECT * FROM #tempFruits WHERE [Description] <> NULL -- 2 result
 SELECT * FROM #tempFruits WHERE [Description] IS NOT NULL -- 2 result

Output

As you can see from the output, when you turn set ANSI_DEFAULT off, the equality operators will eventually be acceptable and work as expected.

So, in incase you experienced this, there's a higher chance that the ANSI_DEFAULT is turned off.
Avoid Changing Default ANSI_NULLS

Turning off the ANSI_NULLS option is not recommended unless you're just trying something or making some quick fixes.

Moreover, it is safer to use the IS NULL and IS NOT NULL operators for the NULL value comparison because it will work in all cases irrespective of the ANSI_NULLS option setting.

HostForLIFEASP.NET SQL Server 2019 Hosting

If you're one of those confused about the difference between the CAST and CONVERT functions, you have come to the right place.

That's why in this article, we'll explore the CAST and CONVERT functions of SQL Server and see their difference.

OK, let's get started then.

What is the SQL CAST function?
Basically, this function converts data from one data type to another. For example, it can convert numeric data into character or string data.
Things to Remember about SQL Cast Function

    Transforms the expression data from one data type to another.
    The transformation lasts only for the life of the query.
    It is an ANSI standard (ANSI SQL-92 compliant), portable to other DBMS.
    So if you're comfortable with the CAST function, you'll be able to use it on other SQL-related databases.
    If in case the conversion fails, it will return an error. Otherwise, it returns the converted value.
    Doesn't have a parameter that accepts optional style for formatting. That's why you can use the SQL FORMAT function.

Syntax
CAST([Expression] as DataType (length))

    [Expression] – defines the valid expression argument.
    DataType – the target data type for conversion.
    Length (optional) – the specified length of the target data type.

Examples

Example 1
Let's try to convert a string to DateTime data type.
--Let's declare a type
DECLARE  @YOUR_BIRTHDAY NVARCHAR(10) = '01/29/2000';

--SYNTAX: CAST([Expression] AS DataType)
SELECT @YOUR_BIRTHDAY AS [Data To Convert],
CAST(@YOUR_BIRTHDAY AS DATETIME) AS [BirthDate - Data Converted];

Output

Example 2
Let's add two or more random numbers and convert them into money data type.
--Let's declare two numbers
DECLARE @NUM1 INT = 100, @NUM2 BIGINT = 854775808

--Let's add them together and convert to money and format a bit so it would look like a genuine $. :-)
SELECT FORMAT(CAST ((@NUM1 + @NUM2) AS MONEY), '$ #,##0.00') [Sum];

Example 3
Let's convert two random char data type to int data type and multiply it together.

--Let's declare 2 char with random number assigned to it
DECLARE @char1 CHAR(3) = '100', @char2 CHAR(3) = '569';

--Let's multiply the two char data-type and see the product of those two numbers.
SELECT CAST(@char1 AS INT) * CAST(@char2  AS INT) [Product of Two Numbers]

Output

What is SQL CONVERT function?
This function converts an expression from one data type to another, just like the CAST function. Another good thing about the SQL CONVERT function is that it gives you the option to format your converted data. That's why we can say that it is more powerful compared to the CAST function.
Things to Remember about SQL Convert Function

    A non-ANSI SQL-92 compliant function converts the expression data type to another data type with a formatting option.
    This function is specific to Microsoft's TSQL and will not be portable to other DBMS.

Syntax
CONVERT (DataType, (length), expression, style)

    DataType – target data type to which you want to convert.
    Length – the specified length of the target type.
    Expression – the target data type for conversion.
    Style – optional integer value to specify the style format of the output. Cool right?

Example
Let's create a temp table, insert the data of this year's Chinese New Year, convert it into VARCHAR, and pass some formatting options.
DROP  TABLE IF EXISTS temp.dbo.#CONVERT_FUNCTION_STYLES

CREATE TABLE #CONVERT_FUNCTION_STYLES
(Id int,
 [Name] varchar(10),
 [ConvertedDate] varchar(20))

 DECLARE @CHINISE_NEWYEAR DATETIME = '02/01/2022 12:00:00 AM';

 INSERT INTO #CONVERT_FUNCTION_STYLES
 (Id, Name, [ConvertedDate])
 VALUES
 (1, 'Default', CONVERT(VARCHAR,@CHINISE_NEWYEAR, 100)),
 (2, 'US',      CONVERT(VARCHAR, @CHINISE_NEWYEAR, 101)),
 (3, 'ANSI',    CONVERT(VARCHAR, @CHINISE_NEWYEAR, 102)),
 (4, 'German',  CONVERT(VARCHAR, @CHINISE_NEWYEAR, 104)),
 (4, 'Japan',  CONVERT(VARCHAR, @CHINISE_NEWYEAR, 111));

 SELECT @CHINISE_NEWYEAR [Date and Time To Be Converted],
       Name [Country],
       ConvertedDate [Country Standard Converted Date]

 FROM #CONVERT_FUNCTION_STYLES;

Output

HostForLIFEASP.NET SQL Server 2019 Hosting

If you are a developer then surely you must have used JSON (JavaScript Object Notation) but if not, then don’t worry, you'll use it sooner rather than later. JSON is a kind of ecosystem that is most popular in various areas for exchanging data. If you talk about charting solutions, AJAX, Mobile services, or any 3rd party integration then generally JSON is the first choice of the developers.

Nowadays most of the NoSQL databases like Microsoft Azure Document DB, MongoDB, etc. also use JSON ecosystem and some of them are based on JSON.

As it is such a popular growing system, why not in SQL SERVER?

In SQL SERVER 2016 JSON was introduced. This was step or bridge between NON-relational databases and relational databases by Microsoft SQL SERVER.

SQL Server 2016 provides the following capabilities when you are using JSON

    Parse JSON by relation query
    Insert & update JSON using the query
    Store JSON in the database

If you see it then conceptually it is similar to an XML data type which you might use in SQL SERVER.

The good thing in SQL SERVER 2016 for JSON is there is no Native data type. This will help in migration from any NoSQL to SQL SERVER.

SQL Server provides bidirectional JSON formatting which you can utilize in various ways. Suppose data is coming from the external source in the JSON format then you can parse it and store it in table structure (if required) in another case external source requires data in JSON format while data in SQL SERVER in tabular format so both the purpose can easily solve with SQL SERVER’s JSON feature.

Now, let’s jump directly to the practical to check JSON capabilities in SQL SERVER

1) FOR JSON AUTO
It is similar to FOR XML AUTO. It will return JSON object of selected column where column name is treated as a Key or in other words we can say it will format the query result in JSON.

when you run the above command the result will be like as shown in the below figure.

2) FOR JSON PATH
It’s exactly like JSON auto, the only difference is instead of SQL SERVER we have full control over the format. JSON Auto takes predefined column schema while with JSON path we can create a complex object.

For example, we are using the AdventureWorks Sales order table and joining that with the product table to get a sub-node. As you see below the image we have added a Root node as well. This root Node can be added in JSON auto as well if required.

Now, when you run the above query we can get complex JSON object as follows

3) IsJSON function
By the name, it is clear that this is a validating function.
To cross-check whether the provided string is a valid JSON or not we can run ISJSON.

4) JSON_VALUE
By the name, it is clear that if you want to get the value of the particular key of JSON then you can use this beautiful function which is JSON_VALUE.

5) OPENJSON function
This is a very beautiful function that you can use to parse external schema. Suppose, you got a JSON string from a mobile service that you will directly pass to SQL Server, and the SQL SERVER stored procedure will do the rest of the operation to parse it. The parsing and other operation can be easily handled by OPENJSON. The only tweak here is that it required database compatibility level 130 which you need to do (if not compatible with level 130)

There are many other interesting things which we will cover later.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will discuss how to send email notifications when our SQL job fails. The purpose to configure is to intimate the user to take care of failed jobs. We will cover how to configure Database Mail, Email profile, SMTP Configuration, Operator to send email, notification, and SQL Server Agent configuration.

We know to perform a series of actions automatically on SQL server side, we need to configure jobs. A task that is configured will be run one or more times and monitored for success or failure. Some examples of SQL jobs are daily, weekly, monthly and quarterly reports, sending auto emails, newsletters, writing log files, and audit logs to be sent automatically on local or remote locations.

Why Configure Email Configuration?
When our job is running and we can face any issue while executing the script. Such issues occurred when working with files, simultaneously data insertion, working with SSDT (SQL Server Data Tools), and many more scenarios. SQL job get stuck and take more time as expected and finally fails. On job failure, we need to know on an immediate basis to get rid of the problem.

Steps to Configure Email Notification on Failure
These are steps to be performed to configure email notification on SQL job failure:

Step 1
Under Management, Configure Database Main. Right click on Database Mail and select the first option (Configure Database Mail)

Step 2
We are creating a new profile, so select the first option and click on Next.

Step 3
Write the profile name and description then click on Add button to add SMTP settings.

Step 4
I am using Gmail SMTP configuration; you can use according to your settings. From email address is used to send email notifications.

Step 5
When profile is ready then click Next to proceed.

Step 6
Configure your system level parameters like retry mechanism, attached files size, and allowed/prohibited extensions. Click on Next. On next screen shows all the details of your account simply select Finish. Your all configuration is successfully built.

Step 7
Create an Operator
Now we have to make an operator to whom you want to send email when the SQL job is failed
Expand SQL Server agent
Right-click on Operators and select new operator

Step 8
In the following screen enter your Operator name and E-mail name to whom you want to send emails. If you want to send email to more than one person just separate the email address with ';'

After that click OK and operator is ready to send emails.

Step 9
Create a New SQL Job

Step 10
Select Notifications and check on email and select operator which we define previously.
It helps to send email when the jobs fail. After that click on OK.

HostForLIFEASP.NET SQL Server 2019 Hosting

As a product owner, you are always worried about the different security aspects of your application, and SQL Server Database is one of the main important parts for which you might worry.

And you always think there should be some kind of checklist which you or your team have to check whether your database is secure or not and find all the vulnerabilities.

And obviously, you might have purchased different tools for this assessment as well that will provide you security loopholes but when we talk about Databases the option is limited and some options are very costly.

With SQL SERVER 2017 latest SQL Management Studio one of the problems will be resolved cross-check your database vulnerability.

You heard it right. Although, this feature is already available in SQL Azure now you can do this assessment of your database using SQL Server 2017’s management studio.

This vulnerability assessment report can be generated on the database with a few simple clicks and you will get different High, Medium, Low risks of your database.

The vulnerability assessment report not only provides risks details but also helps you to identify which category of it and this will not stop here you will get a recommendation as well to fix those problems. Sometimes, you will get direct scripts that you can run to fix those issues, and sometimes you will get the links on how to implement those.

Let’s understand this by step-by-step action.

Before starting make sure you have SQL Server 2017 Management Studio’s latest version.

Step 1
Once you opened the SQL Server management studio right click on the database which you want to cross-check. in this example, I am using the AdventureWorks database. As shown in the below figure.

Here you have 2 options either Scan for Vulnerabilities or Open Existing Scan

Step 2
Now, as we are doing it the first time click on Scan for Vulnerabilities option. and you will get the following screen where you can provide the location of the scan file.


Step 3
Just click on the OK button to proceed further and wow you will get all the loopholes of your database.
You can easily check what are different points on which your Database is failed with risk Assessment.

As shown in the above figure, we have 6 checkpoints on which our database failed in which 1 is on high risk, 3 medium risk, and 2 Low risks.

And if you see carefully there are different categories as well like data protection, Authentication, and Authorization, Surface Area Reduction, etc.

Here as the name suggests Data Protection is mostly related to encryption of your sensitive data like SSN, DOB, etc, or TDE.

Authentication and Authorization are more related to login access to the database.

Surface Area reduction is more related to what extra option you have opened.

Step 4

Now, move a step further and click on any row in the grid. You will find the details of the row just below the grid. As you can see below the image when we click on data protection it suggests the column names which come under extra care and to on which we might think to apply encryption.

Step 5
The story does not end here, for some of the problems this assessment report provides a script as well, and if the script is not possible then provide a reference link to resolve that issue.

As you can see in the below screen we are getting recommendation scripts to apply.

Isn’t it cool and simple to assess your database’s vulnerability in a few clicks and secure your database?

Share your thoughts.

HostForLIFEASP.NET SQL Server 2022 Hosting

This is a query which will help to retrieve all the tables and functions used in a store procedure, even if the query is a dynamic one.
SELECT objects.name As suspected_dependencies
FROM   sys.procedures
 INNER
  JOIN sys.all_sql_modules
    ON all_sql_modules.object_id = procedures.object_id
 LEFT
  JOIN sys.objects
    ON objects.name <> procedures.name
   AND all_sql_modules.definition LIKE '%' + objects.name + '%'
WHERE  procedures.name = <Procedure Name>

Output

HostForLIFEASP.NET SQL Server 2019 Hosting

Sometimes, we may need to know the table's metadata like
    Column Name,
    Data Type
    Column Size
    Index
    Constraint
    ETC.

And to be honest there are various ways to determine this but in the tip, we are going to share 2 simple ways to determine the table's detail

1. SP_HELP
The syntax of sp_help is very simple as shown below
SP_HELP 'TABLENAME'
GO

For example, if I have a Users table in my database then I can write the SQL command like below and hit F5
SP_HELP 'Users'
GO

Now, the other way is very very simple. In this, you don't have to write SP_HELP

Just write the tableName in the Query window. Select the table Name and hit ALT + F1

This will show exact same result which you get from SP_HELP.

I hope this simple tip might help you.

Thanks

HostForLIFEASP.NET SQL Server 2019 Hosting

This is an interesting query in every developer's mind: What happens if the table has columns with default values only and we want to insert a new row with a default value?

Isn't it interesting? I am pretty much sure your mind horses started running and you are trying to solve this query but if not then never mind below few steps will help you to understand this concept.

To understand this let's take an example. Let's create a table with the default value  (Just to let you know we are using SQL Server :) )
CREATE TABLE #CSharpRegistration (Id INT IDENTITY(1,1),
FirstName VARCHAR(250) DEFAULT 'Registration fname',
LastName VARCHAR(250) DEFAULT 'Registration Lname')

Now, once the table is created you might be wondering what's new in this, it has a default value and identity column. There is nothing much in creating a statement but magic exists in the below statement.
INSERT INTO #CSharpRegistration (FirstName,LastName) VALUES (DEFAULT,DEFAULT)

Once you write the above insert statement and run it. You will find that a default row is inserted in the table. To check run below simple SQL Statement.
SELECT * FROM #CSharpRegistration

See, below image of execution of above statement.

I hope this small article solves your query.

Please, do comment and share if you like it.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will learn how to use Built-In functions in SQL Server.

What are Built-In functions?
A built-in function is a piece for programming that takes zero or more inputs and returns a value. There are many built-in functions in SQL Server. Here we are discussing about string, date, and time functions in SQL Server.

String Functions
ASCII()
ASCII (American Standard Code for Information Interchange) is a code that uses numbers to represent characters. This function returns the ASCII value for the specific character. To get the ASCII code of a character, we can call the ASCII() function. It takes a character as an argument and returns the ASCII code of the character. For example:
SELECT ASCII('A') AS [In ASCII Format]
Result: ‘65’   (ASCII code of “A” is 65)
SELECT ASCII('a') AS [In ASCII Format]
Result: ‘97’   (ASCII code of “A” is 65)

CHAR()
This function returns the character based on the ASCII code. It takes a numeric value as an argument and returns the character. For example:
SELECT CHAR(65) AS [In CAHR Format]
Result: ‘A’   (ASCII code of “A” is 65)
SELECT CHAR(85) AS [In CHAR Format]
Result: ‘U’   (ASCII code of “U” is 85)

CHARINDEX()
This function returns the position of a substring in a string. This function return 0(Zero) if the substring is not found. It takes three arguments. Syntax,
CHARINDEX (substring, string) / CHARINDEX (substring, string, start_position)

In the above syntax “substring” is the string to search for. “string” is the string to be searched. “start_position” is the position where the search will start. This is an optional parameter. If it is zero or negative value, the search starts at the beginning of the string.

SELECT CHARINDEX('CHAR', 'SQL Server CHARINDEX() function examples') AS MatchPosition;
Result: ‘A’   (We get 20 as the index point of “CHAR”)
SELECT CHARINDEX('CHAR1', 'SQL Server CHARINDEX() function examples') AS MatchPosition;
Result: ‘A’   (We get 0 as the index point of “CHAR1”, because it’s not found in the above string)

SOUNDEX()& DIFFERENCE()
This function compares two SOUNDEX values, and returns an integer. The integer value indicates the match for the two SOUNDEX values from 0 to 4, where 0 indicates weak or no similarity and 4 indicates strong similarity.

The SOUNDEX() function converts a string to a four-character code. This function compares the similarity between strings in terms of their sounds when it is spoken.

For example,
SELECT SOUNDEX('Juice') soundex1,SOUNDEX('55') soundex2,DIFFERENCE('Juice', '55');
Result: ‘J200, 0000, 0’   (Here SOUNDEX codes are totally different so we get 0)
SELECT SOUNDEX('Juice') soundex1,SOUNDEX('Debezium') soundex2,DIFFERENCE('Juice', 'Debezium');
Result: ‘J200, D125, 1’   (Here SOUNDEX codes are near to each other so we get 1)
SELECT SOUNDEX('Juice') soundex1,SOUNDEX('Apple') soundex2,DIFFERENCE('Juice', 'Apple');
Result: ‘J200, A140, 2’   (Here SOUNDEX codes are more near to each other so we get 2)
SELECT SOUNDEX('India') soundex1,SOUNDEX('Indian') soundex2,DIFFERENCE('India', 'Indian');
Result: ‘I530, I535, 3’   (Here SOUNDEX codes are near to similar so we get 3)
SELECT SOUNDEX('Juice') soundex1,SOUNDEX('Jucy') soundex2,DIFFERENCE('Juice', 'Jucy');
Result: ‘J200, J200, 4’   (Here SOUNDEX codes are same so we get 4)

FORMAT()
Using this function we can format a value with a specified format. This function can be applied to various types of values such as integers, floating-point numbers, date, or time. It takes three arguments or parameters. Syntax:

FORMAT(value, format, culture)
value: is the type of value to be formatted. It can be a number, a date or time.

format : it specifies the format you want to apply to the first argument.

culture : is optional parameter, specifies a culture.

SELECT FORMAT(9658254870, '###-###-####');
Result: ‘965-825-4870’   (We get the above number in “###-###-####” format)
DECLARE @Salary INT = 56.68;
SELECT @Salary, FORMAT(@Salary, N'F'), FORMAT(@Salary, N'C', N'en-IN');
Result: ‘56, 56.00, ₹ 56.00’
SELECT FORMAT(GETDATE(), 'yyyy-MMM-dd'), FORMAT(GETDATE(), 'dd-MM-yyyy');
Result: ‘2021-Dec-27, 27-12-2021’   (Here we get the same date in two different format)

LEFT()
This function is used to get a number of characters from a string starting from left. This function takes two arguments. The first argument specifies the original string and the second argument specifies the number of characters from the most-left. For example:
SELECT LEFT('Peter Scott', 4) AS [Name];
Result: ‘Peter’   (We get only 4 character of the string)
SELECT LEFT('Sql Server Function Examples', 13) AS [Example];
Result: ‘965 Built-In Functions’

LEN()
This function returns the length of a string. This function takes one argument as the string to be considered. For example:
SELECT LEN('Peter Scott') AS [Count1];
Result: ‘12’   (Returns total character of the string including space)
SELECT LEN('Built-In Functions LEN() in SQL Server – Part One') AS [Count2];
Result: ‘49’   (Returns total character of the string including space)

LOWER() & UPPER()
LOWER() converts a string to lower-case and UPPER() function converts a string to upper-case. These functions take one argument as a string.
SELECT LOWER('Peter Scott') AS [Name];
Result: ‘peter scott’   (Returns the given string in lower case)
SELECT UPPER('Built-In Functions in SQL Server – Part One') AS [Example];
Result: ‘BUILT-IN FUNCTIONS IN SQL SERVER – PART ONE’   (Returns the given string in upper case)

LTRIM() & RTRIM()
LTRIM function removes all leading spaces from a string starting from left and RTRIM() function removes all leading spaces from a string from right.
SELECT LTRIM('       Peter Scott') AS [Name];
Result: ‘Peter Scott’   (Remove all space from left side of the string)
SELECT RTRIM('       Peter Scott         ') AS [Name];
Result: ‘       Peter Scott’   (Remove space from right)
SELECT LTRIM(RTRIM('       Peter Scott         ')) AS [Name];
Result: ‘Peter Scott’   (Remove all space from both left and right side of the string)

PATINDEX()
The PATINDEX() function returns the first occurrence of a pattern in a string. It returns 0 if the pattern is not found. It takes two arguments. First one is the pattern to search and it must be surrounded by “%”. Here we can also use other wildcards like “%”, “_”, “[]”, “[^]”. The second one is the string to be searched.

SELECT PATINDEX('%Part%', 'Built-In Functions in SQL Server – Part One') AS [Example];
Result: ’36’   (we search the pattern %Part% in the specified string)
SELECT PATINDEX('%S_rver%', 'Built-In Functions in SQL Server – Part One') AS [Example];
Result: ‘27’   (we search the position for the pattern “S” followed by “rver” in the string)
SELECT PATINDEX('%[^0-9A-z]%', 'Built-In Functions in SQL Server – Part One') AS [Example];
Result: ‘6’   (Here we want to finds out the position of the character without an alphabet, number)

REPLACE()
This function replaces all occurrences of a substring within a string, with a new substring. This function takes three arguments. The first is the string that will be used as reference. The second argument is a character or a sub-string to look for in the first argument. The third argument is a character or a sub-string to replace the second argument. For example: If we want to replace “SQL Server” as “MS SQL Server” in the “'Built-In Functions in SQL Server – Part One'” string.

SELECT REPLACE('Built-In Functions in SQL Server – Part One', 'SQL Server', 'MS SQL Server') AS [Example];
Result: ‘Built-In Functions in MS SQL Server – Part One’   (Here we replace “SQL Server” with “MS SQL Server”)
SELECT REPLACE('Peter Scott Thomas', 'Scott', '') AS [Name];
Result: ‘Peter Thomas’   (Here we replace “Scott” with “” blank)

REPLICATE()
This function repeats a string with a specified number of times. It takes two arguments. The first argument is the string to repeat and the second argument is no of time to repeat the string. For example:

SELECT REPLICATE(' Built-In Functions in SQL Server', 4) AS [Example];
Result: ‘Built-In Functions in SQL Server Built-In Functions in SQL Server Built-In Functions in SQL Server Built-In Functions in SQL Server’   (Here we get the above string repeated 4 times.)

REVERSE()
This function reverses a string. It takes one argument as string to reverse.

For example,

SELECT REVERSE('Peter Scott') AS [Name];
Result: ‘ytnahoM timA’
SELECT REVERSE('Built-In Functions in SQL Server – Part One') AS [Example];
Result: ‘enO traP – revreS LQS ni snoitcnuF nI-tliuB’

SPACE()
This function returns a string of the specified number of space characters.

SELECT 'Peter' + SPACE(10) + 'Scott' AS [Name];
Result: ‘Peter          Scott’    (It added 10 spaces between the two strings)

STR()
This function converts a numeric value to a string value. It takes three arguments.
    The first argument is the number to convert to string.
    The second argument is the length of the returning string and default value is 10.
    The third argument is the number of decimals to display in the returning string and the default value is 0.

The second and the third arguments are optional. For example,

SELECT STR(10.513) result;
Result: ‘11’    (result is rounded because we didn’t pass the decimal places so it takes defaults as 0)
SELECT STR(10.513, 5, 2) result;
Result: ‘10.51’

STUFF()
This function removes a part of a string and then inserts another part into the string, in a specified position. It takes four arguments. The first argument is the string to be processed or modified. The second argument is the position in string to start deletion and insertion of some characters. The third argument is the number of characters to delete from string. The fourth argument is the new sub-string to replace into string at the start position. For example,

SELECT STUFF('My name is ', 11, 1, ' Peter Scott!');
Result: ‘My name is Peter Scott!’
SELECT STUFF('My name is ', 1, 1, ' Peter Scott!');
Result: ‘Peter Scott!y name is’

SUBSTRING()
This function extracts a substring with a specified length starting from a position in a string. It takes three arguments. The first argument is the string from which we extract a character or sub-string. The second argument specifies the position where the returned substring starts. The third argument is the number of characters of the substring to be returned ant it must be a positive number. For example:

SELECT SUBSTRING('Built-In Functions in SQL Server – Part One', 4, 16) AS ResultString;
Result: ‘lt-In Functions’    (we get the string starting index from 4 to no of 16 characters)
SELECT SUBSTRING('Built-In Functions in SQL Server – Part One', 10, 16) AS ResultString;
Result: ‘Functions in SQL’

Date and Time Function
CURRENT_TIMESTAMP

This function returns the current date and time, in a “YYYY-MM-DD hh:mm:ss.mmm” format. For example:

SELECT CURRENT_TIMESTAMP;
Result: ‘2021-12-27 13:43:09.640’   (Date Time in “YYYY-MM-DD hh:mm:ss.mmm” format)

DATEADD()
This function adds a time/date interval to a input date and then returns the modified date. It takes three arguments. The first argument is the time/date interval to which we will add value. The second argument is the number of interval to be added to first argument. It can be positive or negative number. The third argument is the date that will be modified.

For example,
SELECT DATEADD(YEAR, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2023-12-25 13:50:47.233’
SELECT DATEADD(MONTH, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2022-02-25 13:50:47.233’
SELECT DATEADD(DAY, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-27 13:50:47.233’
SELECT DATEADD(WEEK, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2022-01-08 13:50:47.233’
SELECT DATEADD(HOUR, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-25 15:50:47.233’
SELECT DATEADD(MINUTE, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-25 13:52:47.233’
SELECT DATEADD(SECOND, 30, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-25 13:51:17.233’
SELECT DATEADD(MILLISECOND, 200, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-25 13:50:47.433’

DATEDIFF()
This function returns the difference between two dates in years, months, weeks, etc. It takes three arguments.

The first argument is “datepart” which is the part of date like a year, a month, a week, a day etc.

The second and third arguments are “startdate” and “enddate” to be compared.

For example,
SELECT DATEDIFF(YEAR, '2020/10/13', '2021/10/13') AS [DiffYear];
Result: ‘2’
SELECT DATEDIFF(MONTH, '2020/10/13', '2021/09/13') AS [DiffMonth];
Result: ‘11’
SELECT DATEDIFF(HOUR, '2020/10/13 07:00', '2021/10/13 12:45') AS [DiffHour];
Result: ‘8765’

DATEFROMPARTS()
This function returns a date from the specified parts like year, month, and day values. For example:

SELECT DATEFROMPARTS(2021, 12, 31) AS [DateFromParts];
Result: ‘2021-12-31’

DATENAME() & DATEPART()
The DATENAME() and DATEPART() function returns a specified part of a date. But the DATENAME() function returns the result as string value and DATEPART() return the result as integer value.

SELECT DATENAME(MONTH, '2021/12/25') AS [DateNameMonth],
       DATENAME(WEEKDAY, '2021/12/25') AS [DateNameDay];
Result: ‘December, Saturday’
SELECT DATEPART(MONTH, '2021/12/25') AS [DatePartMonth],
       DATEPART(WEEKDAY, '2021/12/25') AS [DatePartDay];
Result: ‘12, 7’

DAY(), MONTH() & YEAR()
The DAY() function returns the day of the month for a specified date. The value is from 1 to 31.

The MONTH() function returns the month part for a specified date. The value is from 1 to 12.

The YEAR() function returns the year part for a specified date.

SELECT DAY('2021-12-25') AS [Day];
Result: ‘25’
SELECT MONTH('2021-12-25') AS [Month];
Result: ‘12’
SELECT YEAR('2021-12-25') AS [Year];
Result: ‘2021’

GETDATE(),GETUTCDATE() & SYSDATETIME()
The GETDATE() function returns the current database date and time, in a “YYYY-MM-DD hh:mm:ss.mmm” format.

The GETUTCDATE() function returns the current database date and time in UTC, in a “YYYY-MM-DD hh:mm:ss.mmm” format.

The SYSDATETIME() function returns the date and time of the computer where the SQL Server instance is running.

SELECT GETDATE() AS [GetDate], GETUTCDATE() AS [UTCDate], SYSDATETIME() AS [SysDateTime];
Result: ‘2021-12-27 15:06:35.127, 2021-12-27 09:36:35.127, 2021-12-27 15:06:35.1283952’

ISDATE()
This function checks an expression is valid date or not. It returns 1 if it is a valid date, otherwise 0.
SELECT ISDATE('2021-12-25') AS [STATUS];
Result: ‘1’ (returns 1 because it’s a valid date)
SELECT ISDATE('2021-15-25') AS [STATUS];
Result: ‘0’ (returns 0 because not a valid date)

In the above article, we learned how to use string, date and time built-in functions in SQL Server. Hope this will help the readers.

HostForLIFEASP.NET SQL Server 2021 Hosting

Usually, we install SQL Server by default, it works just fine. However, sometimes, we might face some problems in the new version installation. I got an issue when I installed the SQL Server Developer Edition 2019, that is related to a PolyBase issue.  This article will introduce the problem, the reason and the fix. (SQL Server 2019 Developer Edition Installation see here)

Once in 2020, after I installed an instance of SQL Server 2019 Developer Edition, I found my machine hard disk was eaten very fast. The following is what I met this situation again early this year, one can see the hard disk reduced close to 100 GB in just one day.

Reason
I am not SQL Server database administrator, I do not want to debug it, and I do not care about the real reason, I just want to have a fix on it. By online searching, I found the solution from one excellent article, in which one can get the real reason and debugging process, what I will introduce here is just the conclusion.

The problem is caused by, in our installation, we installed this component: PolyBase

and configure it like this:

I do not know what this component is for, and the installation is successfully completed:

However, there is an issue with this component.  If we open service, we can see that the PolyBase components are always in the starting status, which means they always cannot be started:


The issue is caused by that these components need to connect to SQL Server by TCP provider, however, the TCP/IP as a network protocol for SQL server is unable by default.  This can be seen by opening the Computer Management:

Therefore, the SQL server will report this problem automatically in log file, about every half an hour making a dump file that is about 400 MB, which will eat the hard disk 20~30 GB every day.

Fix
Simply make the SQL Server TCP/IP protocol enabled like this:

After restarting the SQL Server and the PolyBase service, you will see the PolyBase components are running.

Then there will be no more garbage dump files created in the SQL Server log folder. And, you probably need to manually delete the previous dump files to get the lost space back.

This bug is fixed and the main idea is from the article below in reference, thanks a lot for the Author. If anyone has an interest in the technical details for this issue, you can read this article.

HostForLIFEASP.NET SQL Server 2019 Hosting