European Windows 2019 Hosting BLOG

BLOG about Windows 2019 Hosting and SQL 2019 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2021 Hosting - HostForLIFE :: SQL's STRING_AGG Function

clock June 23, 2022 08:19 by author Peter

Before SQL Server 2017 (SQL Server 2014 below), concatenating rows of strings into one column could be done using the STUFF function that combines with FOR XML PATH. However, in my opinion, it's quite messy. In this article, we'll explore SQL's STRING_AAG function and see how we can concatenate rows of strings into one column string using a separator.

The examples provided will work with SQL Server 2017 and later.

Ok, let's get started.

What's SQL's STRING_AAG Function?

    It is an aggregate function that concatenates strings into a single line separated by a separator.
    The separator is not appended to the end of the result string.
    This function was introduced into SQL Server 2017 and later.

Syntax
STRING_AGG(string expression, separator) [order clause]
    The string expression could be any type.
        It can be converted into VARCHAR or NVARCHAR during concatenation.
        Non-string types are converted to NVARCHAR types.
    The separator is used as a separator for the concatenated strings.
        It can be literal or variable. Just remember it doesn't add itself at the end of the result string.
    The order clause is the sort order of the result string using the WITHIN GROUP clause.

Syntax of WITHIN GROUP
WITHIN GROUP (ORDER BY expression [ASC | DESC])
    The expression can be used for sorting results; only one expression is allowed per query.
        The default order is ascending.

Examples
Before we show examples, the AdventureWorks database will be used for the first of our samples.
Using STRING_AGG First Example

To have an overview, the [Sales].[SalesOrderHeader] under the AdventureWorks database does have a [SalesOrderNumber] column.

And we wanted to show the list of different sales-order-number per customer by using the STRING_AGG function.

Ok, let's see the examples below.

SELECT CustomerID as [Customer ID],
       COUNT(CustomerID) as [Number Of Sales Order] ,
       STRING_AGG([SalesOrderNumber], ',') as [Sales Order List]
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
GROUP BY CustomerID


As you can see, with the query that we have we did get the [CustomerID] column as our reference for a particular customer.

Then by knowing the number of its records, we can show the different sales-order numbers per record.

But, of course, using the STRING_AGG.

That's why we can come up with the query above.

Let's see the output below.

Output

Now, for us to appreciate the WITHIN GROUP syntax when using STRING_AGG.

Let's look at the example below.

SELECT CustomerID as [Customer ID],
       COUNT(CustomerID) as [Number Of Sales Order],
       STRING_AGG([SalesOrderNumber], ',')
       WITHIN GROUP (ORDER BY [SalesOrderNumber] DESC)
       as [Sales Order List]
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
GROUP BY  CustomerID


Output

Now, let's try to see the difference between the two outputs.

Output Difference

Using STRING_AGG Second Example
In this section, I will try to give another example. The idea here is to get all the phone numbers of a particular customer; it seems easy, right?
Yap, let's show the code now, and let's create the table structure.

Note: We'll use a local temporary table to avoid tabl structure complexities.

Build the structure first.
-- 1. Let's create the tables needed.
IF OBJECT_ID(N'tempdb..#Customers') IS NOT NULL
BEGIN
    DROP TABLE #Customers
END

CREATE TABLE #Customers(
    Id int,
    FirstName nvarchar(50),
    LastName nvarchar(50)
)

IF OBJECT_ID(N'tempdb..#CustomersPhone') IS NOT NULL
BEGIN
    DROP TABLE #CustomersPhone
END

CREATE TABLE #CustomersPhone(
    PhoneId int,
    UserId int,
    PhoneNumber nvarchar(50)
)

Second, let's put some data on it.
-- 2. Let's put some data on it.

INSERT INTO #Customers VALUES
(1, 'Peter', 'Scott'),
(2, 'Mark', 'Tom')

INSERT INTO #CustomersPhone
VALUES
(1, 1, '+63 895 789 5751'),
(2, 1, '+63 795 689 5752'),
(3, 1, '+63 695 589 5753'),
(1, 2, '+63 915 739 5651'),
(2, 2, '+63 917 649 5552'),
(3, 2, '+63 095 559 5453');

Third, let's create a query that will show the customer's name, the number of phones they have, and their phone list separated by a comma.

-- 3. Let's show the number of phone and phone list a customer have.
SELECT CONCAT(C1.[LastName], ', ', C1.[FirstName]) as [FullName],
       COUNT(C1.Id) as [Total Phone Number],
       STRING_AGG (P1.[PhoneNumber], ',') as [Phone List]
FROM #CustomersPhone P1
INNER JOIN #Customers C1 ON P1.UserId = C1.Id
GROUP BY C1.Id, C1.[LastName], C1.[FirstName]

HostForLIFEASP.NET SQL Server 2021 Hosting

 


 



SQL Server 2021 Hosting - HostForLIFE :: New String Function in SQL Server 2012

clock June 17, 2022 09:27 by author Peter

SQL Server 2012 introduced two new string functions: CONCAT and FORMAT. The CONCAT string function concatenates two or more strings into one string. CONCAT takes string arguments as input and concatenates these string inputs into a single string. It requires a minimum of two strings as input, otherwise it raises a compile time error. Here all arguments (inputs) are converted into a string type implicitly. A null value is implicitly converted into an empty string.
 
Syntax
CONCAT ( stringvalue1, stringvalue2 ,…, stringvalueN )
 
Argument / parameter
String Value:  A String value to concatenate with the other.
 
Example
--Example of simple string concatenation
SELECT CONCAT('Hi, ', 'I AM ', 'Peter' )


--Output
-- Hi, I AM Peter
--Example of NULL  string concatenation
SELECT CONCAT('Hello ', NULL, 'World' );

--Output
-- Hello World
--Example of other Data type  concatenation with string

SELECT CONCAT('Date : ', CAST(GETDATE() AS DATE));
--Date : 2013-09-25
SELECT CONCAT(123 + '.' + 45  );

--123.45

If all arguments are null then this function returns a string of type VARCAHR (1). The return type of this function depends on the arguments.
    If an argument is a SQL type NVARCHAR (MAX) or SQL CLR system type then the return type of this function is NVARCHAR (MAX)
    If an argument is VARBINARY (MAX) or VARCHAR (MAX) then the result type is VARCHAR (MAX) and if one of argument is NVARCHAR then the output is NVARCHAR (MAX).
    If an argument is NVARCHAR (<= 4000) than result  type is NVARCHAR (<= 4000)
    All other cases result in a type of VARCHAR (<=8000).
    When the length of the arguments are less than 4000 for NVARCHAR or less than 8000 for VARCHAR, implicit conversions can affect the length of the result type.

Other data types, like INT and FLOAT have different lengths when converted to a string. For example, an INT data type length is 12 when converted to a string, so the result of concatenating two integers has a length of 24.
 
FORMAT
The FORMAT string function returns a string formatted value with the specified format and culture (this is optional). We can use the FORMAT function for locale-aware formatting of a date and time and number values as a string.
 
Syntax
FORMAT (Value, Format , Culture)
 
Arguments / parameters
Value -  value in supported data type to format
Format - NVARCHAR format pattern. This argument must contain a valid .NET framework format string. Composite formatting is not supported. It is either a standard format string or a pattern for custom characters for dates and numeric values.
Culture - NVARCHAR Type specifies a culture.

A culture argument is optional, if we are not providing the culture value then the language of current session is used. The language can be set implicitly or explicitly (using a SET LANGUAGE statement). The culture argument accepts all cultures supported by the .NET Framework. It is not limited to the languages supported by SQL Server. If the culture argument is invalid then the Format string function throws an error.

Please refer to http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo(v=vs.80).aspx to learn more about cultures supported by the .NET Framework.
 
Return Type is NVARCHAR or NULL
A FORMAT string function always returns NULL for an error when the culture is invalid. For the argument value, supported data types are numeric (like TINYINT, SMALLINT, INT, BIGINT, FLOAT, NUMERIC, DECIMAL, SMALLMONEY, MONEY and REAL) and date and time data type (like DATE, TIME, SMALLDATETIME, DATETIME, DATETIME2 and DATETIMEOFFSET). This function will not be remote and it depends on the presence of the Common Language Runtime (CLR).
 
Example
--Example of simple FORMAT string function
DECLARE @mydate DATETIME = '09/25/2013';
SELECT FORMAT ( @mydate, 'd', 'en-US' ) AS 'US Format'
,FORMAT ( @mydate, 'd', 'en-gb' ) AS 'GB Format'
--Output
--US Format         GB Format
----------------  -------------
--09/25/2013         25/09/2013
DECLARE @mydate DATETIME = '09/25/2013';
SELECT FORMAT ( @mydate, 'D', 'en-US' ) AS 'US Format'
,FORMAT ( @mydate, 'D', 'en-gb' ) AS 'GB Format'

--Output
--US Format GB Format
--Wednesday,September 25, 2013      25 September 2013  
--Example of custom formatting string
DECLARE @mydate DATETIME = '09/25/2013';

SELECT FORMAT ( @mydate, 'dd/MM/yyyy', 'en-US' )
--Output
--25/09/2013

SELECT FORMAT(555230655,'###-##-####')
--Output
--555-23-0655
--Example of formatting numeric type
SELECT FORMAT (8875.644 , 'C' ,  'en-US' ) AS 'Currency Format1'
,FORMAT (8875.644 , 'C0',  'en-US' ) AS 'Currency Format2'


Output
-- Currency Format1         Currency Format2      
  $8,875.64                        $8,876

 
These two newly introduced functions are very useful. The CONCAT string function is useful to concatenate two or more values and values of one or more data types. The FORMAT string function gets a formatted value with the specified format and optional culture.

HostForLIFEASP.NET SQL Server 2021 Hosting

 



SQL Server 2021 Hosting - HostForLIFE :: Format Date And Time In SQL Server Using FORMAT Function

clock June 13, 2022 09:06 by author Peter

In this article, you will learn how to Format Dates in SQL Server.
Here we will use the “FORMAT” function to format a date in SQL Server

Prerequisites

    Install SQL Server
    Install SQL Server Management Studio (SSMS)

For the above prerequisites, you can follow the article How to Install SQL Server and SQL Server Management Studio (SSMS)

Currently, I have installed SQL Server 2019 and SQL Server Management Studio 18.10 (SSMS) on my windows 11 machine.
Built-in function in SQL Server to get the DateTime value in a specific format

By using some built-in function in SQL Server we can get the DateTime value in a specific format.

For example,

GETDATE()
It returns server DateTime in “YYYY-MM-DD HH:mm:ss.fff” format.

SELECT GETDATE() AS [GETDATE()]
Result:-2022-06-09 12:28:37.787

GETUTCDATE()

It returns DateTime in GMT.

SELECT GETUTCDATE() AS [GETDATE()];
Result:-2022-06-09 07:10:54.350

SYSDATETIME()
It returns the server’s DateTime

SELECT SYSDATETIME() AS [GETDATE()];
Result:2022-06-09 12:41:46.8713228

SYSDATETIMEOFFSET()

It returns the server’s DateTime with time zone in which SQL Server instance is running.

SELECT SYSDATETIMEOFFSET() AS [GETDATE()];
Result:2022-06-09 12:42:15.7936382 +05:30

SYSUTCDATETIME()
It returns server DateTime in GMT.

SELECT SYSUTCDATETIME() AS [GETDATE()];
Result:2022-06-09 07:12:54.4664815

CURRENT_TIMESTAMP
It returns current DateTime of the server.

SELECT CURRENT_TIMESTAMP AS [GETDATE()];
Result:2022-06-09 12:43:40.650

After the CONVERT function, SQL Server added a function (FORMAT) to handle date formatting, giving us a new way to format dates in SQL Server.

To format the date and time data types from a date column (Date, DateTime, etc. Data type) in a table or a variant such as GETDATE(), use the FORMAT function.
Date Format with FORMAT Function

We have many ways to format dates as given below
DD/MM/YYYY
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date;
Result:09/06/2022


DD/MM/YYYY, HH:MM:SS
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date;
Result:09/06/2022, 04:56:44

DDDD,MMMM,YYYY
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date;
Result:Thursday, June, 2022

MMM DD YYYY
SELECT FORMAT (getdate(), 'MMM dd yyyy') as date;
Result:Jun 09 2022

MM.DD.YY
SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.09.22


MM-DD-YY
SELECT FORMAT (getdate(), 'MM-dd-yy') as date;
Result:06-09-22

HH:MM:SS TT
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date;
Result:05:17:37 PM

MM/DD/YYYY (Standard: USA)
SELECT FORMAT (getdate(), 'd','us') as date;
Result:06/09/2022

YYYY-MM-DD HH:MM:SS TT
SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss tt') as date;
Result:2022-06-09 05:18:55 PM

YYYY.MM.DD HH:MM:SS T
SELECT FORMAT (getdate(), 'yyyy.MM.dd hh:mm:ss t') as date;
Result:2022.06.09 05:19:53 P

DDDD,MMM,YYYY in Spanish
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy','es-es') as date;
Result:jueves, junio, 2022

DDDD DD, MMMM,YYYY in Japanese
SELECT FORMAT (getdate(), 'dddd dd, MMMM, yyyy','ja-jp') as date;
Result:木曜日 09, 6月, 2022

Date Format with Culture
We can get regional formatting by using the culture option as shown below:

English-USA
SELECT FORMAT (getdate(), 'd', 'en-US') as date;
Result:6/10/2022

French-France
SELECT FORMAT (getdate(), 'd', 'fr-FR') as date;
Result:10/06/2022

French - Belgium
SELECT FORMAT (getdate(), 'd', 'fr-BE') as date;
Result:10-06-22

French - Canada
SELECT FORMAT (getdate(), 'd', 'fr-CA') as date;
Result:2022-06-10

Danish - Denmark
SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.10.22

Dari - Afghanistan
SELECT FORMAT (getdate(), 'd', 'prs-AF') as date;
Result:1401/3/20


Simplified Chinese
SELECT FORMAT (getdate(), 'd', 'zh-CN') as date;
Result:2022/6/10

Divehi - Maldives
SELECT FORMAT (getdate(), 'd', 'dv-MV') as date;
Result:10/06/22

Bosnian Latin
SELECT FORMAT (getdate(), 'd', 'bs-Latn-BA') as date;
Result:10. 6. 2022.

isiXhosa / Xhosa - South Africa
SELECT FORMAT (getdate(), 'd', 'xh-ZA') as date;
Result:2022-06-10

Hungarian - Hungary
SELECT FORMAT (getdate(), 'd', 'hu-HU') as date;
Result:2022. 06. 10.

Spanish - Bolivia
SELECT FORMAT (getdate(), 'd', 'es-bo') as date;
Result:10/6/2022

Here is a list of all CultureInfo codes along with country names and language.

Country Language CultureInfo Code
Afghanistan Pashto ps-AF
Dari prs-AF
Albania Albanian sq-AL
Algeria Arabic ar-DZ
Argentina Spanish es-AR
Armenia Armenian hy-AM
Australia English en-AU
Austria German de-AT
Bahrain Arabic ar-BH
Bangladesh Bengali bn-BD
Basque Basque eu-ES
Belarus Belarusian be-BY
Belgium French fr-BE
Dutch nl-BE
Belize English en-BZ
Bolivarian Republic of Venezuela Spanish es-VE
Bolivia Quechua quz-BO
Spanish es-BO
Brazil Portuguese pt-BR
Brunei Darussalam Malay ms-BN
Bulgaria Bulgarian bg-BG
Cambodia Khmer km-KH
Canada French fr-CA
English en-CA
Caribbean English en-029
Catalan Catalan ca-ES
Chile Mapudungun arn-CL
Spanish es-CL
Colombia Spanish es-CO
Costa Rica Spanish es-CR
Croatia Croatian hr-HR
Cyrillic, Azerbaijan Azeri az-Cyrl-AZ
Cyrillic, Bosnia and Herzegovina Serbian sr-Cyrl-BA
Cyrillic, Bosnia and Herzegovina Bosnian bs-Cyrl-BA
Cyrillic, Mongolia Mongolian mn-MN
Cyrillic, Montenegro Serbian sr-Cyrl-ME
Cyrillic, Serbia Serbian sr-Cyrl-RS
Cyrillic, Serbia and Montenegro (Former Serbian ) sr-Cyrl-CS
Cyrillic, Tajikistan Tajik tg-Cyrl-TJ
Cyrillic, Uzbekistan Uzbek uz-Cyrl-UZ
Czech Republic Czech cs-CZ
Denmark Danish da-DK
Dominican Republic Spanish es-DO
Ecuador Quechua quz-EC
Spanish es-EC
Egypt Arabic ar-EG
El Salvador Spanish es-SV
Estonia Estonian et-EE
Ethiopia Amharic am-ET
Faroe Islands Faroese fo-FO
Finland Finnish fi-FI
Swedish sv-FI
Sami, Northern se-FI
Sami, Skolt sms-FI
Sami, Inari smn-FI
Former Yugoslav Republic of Macedonia Macedonian mk-MK
France French fr-FR
Breton br-FR
Occitan oc-FR
Corsican co-FR
Alsatian gsw-FR
Galician Galician gl-ES
Georgia Georgian ka-GE
Germany German de-DE
Upper Sorbian hsb-DE
Lower Sorbian dsb-DE
Greece Greek el-GR
Greenland Greenlandic kl-GL
Guatemala K'iche qut-GT
Spanish es-GT
Honduras Spanish es-HN
Hungary Hungarian hu-HU
Iceland Icelandic is-IS
India Hindi hi-IN
Bengali bn-IN
Punjabi pa-IN
Gujarati gu-IN
Oriya or-IN
Tamil ta-IN
Telugu te-IN
Kannada kn-IN
Malayalam ml-IN
Assamese as-IN
Marathi mr-IN
Sanskrit sa-IN
Konkani kok-IN
English en-IN
Indonesia Indonesian id-ID
Iran Persian fa-IR
Iraq Arabic ar-IQ
Ireland Irish ga-IE
English en-IE
Islamic Republic of Pakistan Urdu ur-PK
Israel Hebrew he-IL
Italy Italian it-IT
Jamaica English en-JM
Japan Japanese ja-JP
Jordan Arabic ar-JO
Kazakhstan Kazakh kk-KZ
Kenya Kiswahili sw-KE
Korea Korean ko-KR
Kuwait Arabic ar-KW
Kyrgyzstan Kyrgyz ky-KG
Lao P.D.R. Lao lo-LA
Latin, Algeria Tamazight tzm-Latn-DZ
Latin, Azerbaijan Azeri az-Latn-AZ
Latin, Bosnia and Herzegovina Croatian hr-BA
Latin, Bosnia and Herzegovina Bosnian bs-Latn-BA
Latin, Bosnia and Herzegovina Serbian sr-Latn-BA
Latin, Canada Inuktitut iu-Latn-CA
Latin, Montenegro Serbian sr-Latn-ME
Latin, Nigeria Hausa ha-Latn-NG
Latin, Serbia Serbian sr-Latn-RS
Latin, Serbia and Montenegro (Former Serbian ) sr-Latn-CS
Latin, Uzbekistan Uzbek uz-Latn-UZ
Latvia Latvian lv-LV
Lebanon Arabic ar-LB
Libya Arabic ar-LY
Liechtenstein German de-LI
Lithuania Lithuanian lt-LT
Luxembourg Luxembourgish lb-LU
German de-LU
French fr-LU
Malaysia Malay ms-MY
English en-MY
Maldives Divehi dv-MV
Malta Maltese mt-MT
Mexico Spanish es-MX
Mohawk Mohawk moh-CA
Monaco French fr-MC
Morocco Arabic ar-MA
Nepal Nepali ne-NP
Netherlands Dutch nl-NL
Frisian fy-NL
New Zealand Maori mi-NZ
English en-NZ
Nicaragua Spanish es-NI
Nigeria Yoruba yo-NG
Igbo ig-NG
Norway Norwegian, Bokmål nb-NO
Sami, Northern se-NO
Norwegian, Nynorsk nn-NO
Sami, Lule smj-NO
Sami, Southern sma-NO
Oman Arabic ar-OM
Panama Spanish es-PA
Paraguay Spanish es-PY
Peru Quechua quz-PE
Spanish es-PE
Philippines Filipino fil-PH
Poland Polish pl-PL
Portugal Portuguese pt-PT
PRC Tibetan bo-CN
Yi ii-CN
Uyghur ug-CN
Puerto Rico Spanish es-PR
Qatar Arabic ar-QA
Republic of the Philippines English en-PH
Romania Romanian ro-RO
Russia Russian ru-RU
Tatar tt-RU
Bashkir ba-RU
Yakut sah-RU
Rwanda Kinyarwanda rw-RW
Saudi Arabia Arabic ar-SA
Senegal Wolof wo-SN
Simplified, PRC Chinese zh-CN
Simplified, Singapore Chinese zh-SG
Singapore English en-SG
Slovakia Slovak sk-SK
Slovenia Slovenian sl-SI
South Africa Setswana tn-ZA
isiXhosa xh-ZA
isiZulu zu-ZA
Afrikaans af-ZA
Sesotho sa Leboa nso-ZA
English en-ZA
Spain, International Sort Spanish es-ES
Sri Lanka Sinhala si-LK
Sweden Swedish sv-SE
Sami, Northern se-SE
Sami, Lule smj-SE
Sami, Southern sma-SE
Switzerland Romansh rm-CH
German de-CH
Italian it-CH
French fr-CH
Syllabics, Canada Inuktitut iu-Cans-CA
Syria Syriac syr-SY
Syria Arabic ar-SY
Thailand Thai th-TH
Traditional Mongolian, PRC Mongolian mn-Mong-CN
Traditional, Hong Kong S.A.R. Chinese zh-HK
Traditional, Macao S.A.R. Chinese zh-MO
Traditional, Taiwan Chinese zh-TW
Trinidad and Tobago English en-TT
Tunisia Arabic ar-TN
Turkey Turkish tr-TR
Turkmenistan Turkmen tk-TM
U.A.E. Arabic ar-AE
Ukraine Ukrainian uk-UA
United Kingdom Welsh cy-GB
Scottish Gaelic gd-GB
English en-GB
United States English en-US
Spanish es-US
Uruguay Spanish es-UY
Vietnam Vietnamese vi-VN
Yemen Arabic ar-YE
Zimbabwe English en-ZW

As you saw above, we have used a lot of options for date and time formatting, which are detailed below,

  • hh - this is the hour from 01-12
  • HH - this is the hour from 00-23
  • mm - this is the minute from 00-59
  • ss - this is the second from 00-59
  • dd - this is day of month from 01-31
  • dddd - this is the day spelled out
  • MM - this is the month number from 01-12
  • MMM - month name abbreviated
  • MMMM - this is the month spelled out
  • yy - this is the year with two digits
  • yyyy - this is the year with four digits
  • tt - this shows either AM or PM
  • d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
  • us - this shows the date using the US culture which is MM/DD/YYYY

HostForLIFEASP.NET SQL Server 2021 Hosting

 

 



SQL Server 2021 Hosting - HostForLIFE :: Difference Between SQL And NoSQL

clock June 10, 2022 09:45 by author Peter

1. Relational vs. non-relational

  • SQL databases are relational while NoSQL databases are non-relational.
  • A relational database is a digital database based on the relational model of data.
  • A non-relational database is not based on the traditional table structure that you may be used to. Instead, it is based on a more flexible model that can be adapted to fit the needs of the application.

2. Data schemas

  • SQL databases use a predefined schema and structured query language.
  • NoSQL databases have dynamic schemas that can accommodate unstructured data, which is often stored in a variety of ways.

3. Scaling

  • SQL databases are known for their ability to scale vertically in most situations.
  • It means you can increase the performance by adding more resources like CPU, RAM, or faster hard drives.
  • NoSQL databases are able to scale horizontally, meaning they can handle an increased workload by adding more servers.

4. Data structure

  • SQL databases store data in tables.
  • NoSQL databases are usually a document or key-value stores.

5. Use cases

  • SQL databases are the best choice for complex queries. If the data integrity and transactions are the requirements the SQL is better than NoSQL.
  • If you're working with constantly changing data structures or JSON data, NoSQL could be a better choice.

HostForLIFEASP.NET SQL Server 2021 Hosting

 



SQL Server 2021 Hosting - HostForLIFE :: How To Configure SMTP O365 Migration Using TLS 1.2 For SQL Database Mail?

clock June 8, 2022 10:06 by author Peter

This article will explain to you how exactly you need to configure TLS 1.2 as a default protocol and setup SMTP O365 migration with Database mail using SQL 2012 and above versions for Windows Server 2012 and above.

Common SMTP exceptions we usually faced

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2. Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

Just follow the steps. The below steps are verified, proven, and compatible with the below servers and SQL versions,
    Windows Server 2012 R2 – SQL Server 2014 SP3
    Windows Server 2012 R2 – SQL Server 2012
    Windows Server 2016 – SQL Server 2016
    Windows Server 2016 – SQL Server 2017 and above.

Step 1

Check if your .NET framework is 4.6 or above, especially for SQL 2014/2016 version. Otherwise, install the latest .Net version. To verify the current version, open PowerShell, and run as administrator. Copy and execute the below command

reg query "HKLM\SOFTWARE\Microsoft\Net Framework Setup\NDP\v4" /s

Step 2
Check if .NET 3.5 was installed (You can skip this installation for SQL Server 2017, but the .Net framework installation must be .Net 4.8 or above)

Powershell command to list out the installed.NET versions - Run as admin
gci 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP’ -recurse | gp -name Version -EA 0 | where { $_.PSChildName -match ‘^(?!S)\p{L}’} | select PSChildName, Version

If not installed, either install 3.5 or place a config file in Binn folder
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe.config

Place the XML content
<?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <startup useLegacyV2RuntimeActivationPolicy="true">
            <supportedRuntime version="v4.0"/>
            <supportedRuntime version="v2.0.50727"/>
        </startup>
    </configuration>

Save file as DatabaseMail.exe.config, with UTF-8 and saved in config format
MSSQL13/14/15 – Version depends on the SQL server installed. Forex: MSSQL13 – 2014 and MSSQL14-2016

Step 3
Install the Database mail advanced configuration. Execute the first query
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Step 4
Enable the Windows firewall in the Azure portal under networking for VM
Add a rule in Inbound port rules and Outbound port rules with the highest priority by enabling the recommended port 587 with TCP protocol

Step 5
Enable the TLS 1.2 by adding/updating the registry Keys under Regedit. Click on start and type registry editor, run as admin, go to the respective Path and add the key(folder) and (DWord 32-bit) value

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727]
"DefaultSecureProtocols"=dword:00000001
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\.NETFramework\\v4.0.30319]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\\SOFTWARE\\Wow6432Node\\Microsoft\\.NETFramework\\v4.0.30319]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 2.0\\Client]
"DisabledByDefault"=dword:00000001

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\TLS 1.2\\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\TLS 1.2\\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp]
"DefaultSecureProtocols"=dword:00000800

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp]
"DefaultSecureProtocols"=dword:00000800

NOTE: Make sure you add only the pre-defined registry keys. Any typo error will stop the registry functions instantly. Copy and paste exactly the same.

Step 6
Configure SQL Database mail with account Name, Email-address [email protected], Server Name, PORT, Enable SSL, and Basic Auth

Step 7
Restart the machine and try the Database mail. If this does not work, add the below registry keys

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 2.0\\Client]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 2.0\\Server]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 3.0\\Client] #apply only if the registry key (SSL 3.0) is present
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 3.0\\Server] #apply only if the registry key(SSL 3.0) is present
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\TLS 1.0\\Client]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\TLS 1.0\\Server]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\TLS 1.1\\Client]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\TLS 1.1\\Server]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

Step 8
Restart again and Check if the TLS 1.2 is enabled through PowerShell. Copy, paste, and execute

$key = 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\'
if (Test-Path $key) {
  $TLS12 = Get-ItemProperty $key
  if ($TLS12.DisabledByDefault -ne 0 -or $TLS12.Enabled -eq 0) {
    Throw "TLS 1.2 Not Enabled"
  }
  else{
 "TLS is enabled"
  }
}


Step 9
Execute the PowerShell and check if the email has triggered. Change values for $From - (Domain Account), $To - (User Account) , $Email.Credentials - (Domain Account and Password) variables
#Email Information
$From = "[email protected]"
$To = "[email protected]"
$Subject = "Test authenticated O365 smtp email"
$Body = "Test smtp email sent through Office 365's smtp relay."
#SMTP Relay Settings
$SMTP = "smtp.office365.com"
$Port = 587
$Email = New-Object Net.Mail.SmtpClient($SMTP, $Port)
$Email.EnableSsl = $true
$Email.Credentials = New-Object System.Net.NetworkCredential("[email protected]", "Add Password")
$Email.Send($From, $To, $Subject, $Body)


Step 10
Send test email from step-6 Database mail

Step 11
Execute the below SQL query and verify your email has been sent
select top 10 * from msdb.dbo.sysmail_sentitems order by send_request_date desc;
select top 10 * from msdb.dbo.sysmail_event_log order by log_date desc;

Step 12
If you are still struggling with the email, then the issue is probably in the Exchange Center. To resolve that, log in to the O365 and go to the Microsoft 365 Admin Center, select Users and follow the steps

Go to the Active User([email protected]) and select Manage Email Apps under the Mail tab and enable IMAP, POP, and Authenticated SMTP and execute the Powershell or Database mail test email and see the magic :-)

HostForLIFEASP.NET SQL Server 2021 Hosting

 



SQL Server 2021 Hosting - HostForLIFE :: SQL's Choose Function

clock June 7, 2022 10:00 by author Peter

In some cases, using the CASE statement can lead to numerous conditions. You may agree or not; these multiple conditions will look extensive and lengthy in some situations. Moreover, it can be challenging to maintain because of its complexity.

That's why in this post, we'll explore CHOOSE function. It helps developers have a better alternative to the CASE statement when simplifying lengthy conditions.
What's CHOOSE Function in SQL Server?

    Introduced in SQL Server 2012
    A function returns a specific value from a list based on its number index.
    It looks like an array, but the index starts from 1.

Syntax
CHOOSE (INDEX, VALUE1, VALUE2, VALUE3, VALUE4...)

Index
This is the element's position we seek in the output. Remember that CHOOSE doesn't use a zero-based index strategy (meaning the first item starts with 1). If in case the index is not an integer, SQL converts it to an integer otherwise returns NULL.

Values
It is a comma-separated list of any data type. Returns an item based on the index specified in the first(index) parameter.
Examples
 
1. Item index starts at 1
--Let's just say we wanted to list our favorite programming languages

--output: JavaScript
SELECT CHOOSE (1, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: Python
SELECT CHOOSE (2, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: C#
SELECT CHOOSE (3, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: C++
SELECT CHOOSE (4, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: C
SELECT CHOOSE (5, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'


Output


2. When CHOOSE Function Returns NULL
--output: NULL
SELECT CHOOSE (0, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'

--output: NULL
SELECT CHOOSE (6, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'


Output


If we have passed the index outside the value list range, you'll be getting NULL as the return value.

3. Using Float or Decimal as Index Values
--output: NULL
SELECT CHOOSE (0.10, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: JavaScript
SELECT CHOOSE (1.10, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: Python
SELECT CHOOSE (2.23, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: C#
SELECT CHOOSE (3.9923423, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: C++
SELECT CHOOSE (4.7412122, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: C
SELECT CHOOSE (5, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: NULL
SELECT CHOOSE (6.636, 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'


Output


In our example above, we have seen that once we have passed a float or decimal data type, the value is implicitly converted into an integer as long as it's not int. We'll have the same output as from the first two examples.

4. Using String as Index-values
In this section, you'll see that we can still pass a string that has an integer value.

Let's see an example below.

--Let's just say we wanted to list our favorite programming languages
-- This time around, we'll use string but with a correct integer index
--output: JavaScript
SELECT CHOOSE ('1', 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: Python
SELECT CHOOSE ('2', 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: C#
SELECT CHOOSE ('3', 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: C++
SELECT CHOOSE ('4', 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: C
SELECT CHOOSE ('5', 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'

Output

However, if we pass a non-integer value like an alphanumeric value or decimal value, it will throw an exception.

Let's see an example below.

--output: exception
SELECT CHOOSE ('One', 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'
--output: exception
SELECT CHOOSE ('1.15', 'JavaScript','Python', 'C#', 'C++', 'C') as 'Programming Language'


5. Using CHOOSE in a SELECT Statements
This time, we'll be using the AdventureWorks database and trying to see the employees' birth months. By the way, we'll be using two tables [HumanResources].[Employee] and [Person].[Person] for us to get the names of the employees and show the birth months.

Let's see the example below.
SELECT
    FORMATMESSAGE('%s, %s %s',
                    P.[LastName],
                    P.[FirstName],
                    ISNULL(P.[MiddleName], '')) AS  [FullName],
    E.[BirthDate],
    CHOOSE(MONTH(E.[BirthDate]),  'Jan.',
                                'Feb.',
                                'Mar.',
                                'Apr.',
                                'May.',
                                'Jun.',
                                'Jul.',
                                'Aug.',
                                'Sep',
                                'Oct.',
                                'Nov.',
                                'Dec.') as [Birth Month]
  FROM
  [AdventureWorks2019].[HumanResources].[Employee] E
  INNER JOIN [Person].[Person] P ON
  E.[BusinessEntityID] = P.[BusinessEntityID]

HostForLIFEASP.NET SQL Server 2021 Hosting



SQL Server 2021 Hosting - HostForLIFE :: IIF and Choose Functions in SQL Server 2012

clock May 31, 2022 08:59 by author Peter

Here, I have provided an article showing you how to utilize the two new logical functions Choose and IIF in SQL Server. The Choose function works like an array kind of thing and the IIF function is used to check a condition. In this article we will see both functions with examples. These functions are also called new logical functions in SQL Server 2012. So let's take a look at a practical example of how to use the Choose and IIF functions in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

These are the two logical functions:
    IIF() Function
    Choose() Function

IIF() Function
The IIF function is used to check a condition. Suppose X>Y. In this condition a is the first expression and b is the second expression. If the first expression evaluates to TRUE then the first value is displayed, if not the second value is displayed.

Syntax
IIF ( boolean_expression, true_value, false_value )

Example
DECLARE @X INT;
SET @X=50;
DECLARE @Y INT;
SET @Y=60;
Select iif(@X>@Y, 50, 60) As IIFResult


In this example X=50 and Y=60; in other words the condition is false.  Select iif(@X>@Y, 50, 60) As IIFResult. It returns false value that is 60.

Output

Choose() Function
This function is used to return the value out of a list based on its index number. You can think of it as an array kind of thing. The Index number here starts from 1.

Syntax
CHOOSE ( index, value1, value2.... [, valueN ] )
CHOOSE() Function excepts two parameters,


Index: Index is an integer expression that represents an index into the list of the items. The list index always starts at 1.

Value: List of values of any data type.

Now some facts related to the Choose Function

1. Item index starts from 1
DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H') As ChooseResult

In the preceding example we take index=5. It will start at 1. Choose() returns T as output since T is present at @Index location 5.

Output

2.  When passed a set of types to the function it returns the data type with the highest precedence; see:
DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex ,35,42,12.6,14,15,18.7)  As CooseResult

In this example we use index=5. It will start at 1. Choose() returns 15.0 as output since 15 is present at @ShowIndex location 5 because in the item list, fractional numbers have higher precedence than integers.

3. If an index value exceeds the bound of the array it returns NULL
DECLARE @ShowIndex INT;
SET @ShowIndex =9;

Select Choose(@ShowIndex , 'M','N','H','P','T','L','S','H') 
As CooseResult

In this example we take index=9. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array; the last Index=8.

Output

4. If the index value is negative then that exceeds the bounds of the array therefore it returns NULL; see:
DECLARE @ShowIndex INT;

SET @ShowIndex =-1;


Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H')
  As CooseResult

In this example we take index= -1. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array.

Output

5. If the provided index value has a float data type other than int, then the value is implicitly converted to an integer; see:
DECLARE @ShowIndex  INT;

SET @ShowIndex =4.5;

Select Choose(@ShowIndex ,35,42,12.6,13,15,20)
As CooseResult

In this example we take index= 4.5. It will start at 1.  If the specified index value has a float data type other than int, then the value is implicitly converted to an integer. It returns the 13.0 as output since 15 is present at @ShowIndex=4.5 which means index is 4.

Output



SQL Server 2021 Hosting - HostForLIFE :: SQL Server INFORMATION_SCHEMA Views

clock May 25, 2022 07:42 by author Peter

Have you ever come across a situation where you need to check first if a table exists? Or have you come across a scenario where you need to check if the table's column exists?  Then alter the table by adding your desired column. If you have answered yes to any of these two, you came to the right place.

This post will explore the INFORMATION_Schema views, learn what it is, and show you some of its common usages.

By the way, I'll be using SQL Server 2016, but it can also be applied with other older and later versions of SQL Server.
What is INFORMATION_SCHEMA Database?

The INFORMATION_SCHEMA stores other databases' details on the server.

It gives you the chance to retrieve views/information about the tables, views, columns, and procedures within a database.

The views are stored in the master database under Views->System Views and will be called from any database you choose.

Let's see a screenshot below,


Benefits
As a database developer or administrator, understanding schema and what tables are in a specific database gives us a good idea what's the underlying structure, which can help you write SQL queries. It also allows us to check if everything is expected or on the database. Moreover, it also helps us avoid running queries multiple times to see if the schema name or column name is correct.

Common Usages
Before we start, we'll be using the Northwind database for all our examples. You can download it from here. Now, here are the steps we're going to take. First, get all the databases on our local SQL Server instance, get all the tables, third, let's search for a column, fourth search for constraints, and the last query some views.
Showing All Databases

Let's try to show first all of the databases on a current server instance that we're in.

Note that my result will differ from yours, but you'll see all the databases on your SQL Server instance once you run the query below.
SELECT * FROM sys.databases;

EXEC sp_databases;


Output

The syntax on how we were able to show the database isn't directly related to INFORMATION_Schema.

However, it is an excellent start for us as we go through from database to tables to columns to keys.
Showing All Tables

Now that we have an idea of getting the database on a SQL Server instance.

In this section, we will try to get all of the possible tables of the Northwind database.
USE [Northwind];
--show all table
SELECT * FROM INFORMATION_SCHEMA.Tables;


Querying Column of a Specific Table
In this example, we'll explore how to check a table and then a column if it exists.

Then add a new column to the categories table. Then let's set Tags as its column name and set its data type as NVARCHAR(MAX).

Let's try to see a sample query below.
USE [Northwind];

/*
 * Let's try to declare some variables here that we can use later when searching for them.
 */
DECLARE @TABLE_NAME NVARCHAR(50);
SET @TABLE_NAME = 'Categories';

DECLARE @COLUMN_NAME NVARCHAR(50);
SET @COLUMN_NAME = 'Tags';

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE
                    TABLE_NAME = @TABLE_NAME)
    BEGIN

        PRINT CONCAT('1. Categories table does exists.',
                    CHAR(13), '1.1 Let''s now create the Tags column.');

        IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = @TABLE_NAME
                    AND COLUMN_NAME = @COLUMN_NAME)
            BEGIN
                PRINT '2. Dropping the Tags column of the Categories table.';
                ALTER TABLE [Categories]
                DROP COLUMN [Tags];
            END

        BEGIN
            PRINT '3. Creating the Tags column of the Categories table.';

            ALTER TABLE [Categories]
            ADD [Tags] NVARCHAR(MAX);

            DECLARE @ADDED_COLUMNS NVARCHAR(MAX);

            SET @ADDED_COLUMNS = CONCAT('4. Categories table columns: ',
                                        (SELECT STRING_AGG(COLUMN_NAME, ',')
                                        FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME));

            PRINT @ADDED_COLUMNS;
        END
    END
ELSE
    BEGIN
        PRINT 'CATEGORY TABLE DOESN''T EXISTS';
    END


Going to the example above, as you can see, we have to check if the Categories-table exists. Then from that, we did check if the Tags column does exist. If it does, we need to drop the existing column. And after that, we have re-created the Tags column. Lastly, we have shown all the Categories-table columns by a comma-separated list.

Just a note, the STRING_AGG function is a function that can be applied to SQL Server 2017 and later.

Output

Find Foreign Keys, Primary Key, and Check Constraint in a Table
Let's see how we can query the primary key, foreign key and check the constraint of a specific table.

In this case, we're just going to use the [Order Details] table and show the constraints such as primary key, foreign key, and check constraint.

Let's see the query below.
USE [Northwind];

SELECT CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY', 'CHECK')
AND TABLE_NAME = 'Order Details';

Querying Views
In this last section, we'll make a simple query that will show the Views inside the Northwind database.

Let's see the query below.
USE [Northwind];

SELECT TABLE_CATALOG AS 'Database Name',
       TABLE_NAME AS 'View Name',
       View_Definition as 'Query'
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME  LIKE 'Products%'

I hope you have enjoyed this article. I know that there are many ways you can use INFORMATION_Schema.

Moreover, this article has given you a jump start by showing you how to get all the databases, get all the tables, search for a column, search for constraints and search for views using the INFORMATION_Schema.

Once again, I hope you have enjoyed this article/tutorial as I have enjoyed writing it.

Stay tuned for more. Until next time, happy programming!

Please don't forget to bookmark, like, and comment. Cheers! And Thank you!



SQL Server 2021 Hosting - HostForLIFE :: Database Backup Using SQL Queries And Its Type

clock May 18, 2022 08:53 by author Peter

In this article, you will learn how to take backup of database using SQL queries in a compressed condition and in uncompressed condition.

Prerequisites
    Install SQL Server
    Install SQL Server Management Studio (SSMS)

For above prerequisites, you can follow the article that is How to Install SQL Server and SQL Server Management Studio (SSMS)
Backup of Database in compressed condition

Use the below query to take the backup of database in compressed condition.
BACKUP DATABASE Database_Name TO
DISK = 'BackupPath\BackupFileName.bak'
WITH COMPRESSION
GO

Backup of Database in uncompressed condition
Use the below query to take the backup of database in uncompressed condition.
BACKUP DATABASE Database_Name TO
DISK = 'BackupPath\BackupFileName.bak'
WITH NO_COMPRESSION
GO


Restore the database using backup file with SQL query
RESTORE DATABASE Database_Name
FROM DISK = 'BackupPath\BackupFileName.bak'
WITH RECOVERY
GO

RESTORE DATABASE Database_Name
FROM DISK = 'BackupPath\BackupFileName.bak'
WITH RECOVERY
GO


Now I will demonstrate the same with the help of an example.
Before we start we need to know where do we define the following items in the SQL query as shown in figure-1.
    Database name
    Backup folder location
    Backup file name
    Backup file extension

As I have a database named as "Company" now I am taking backup of this database in compressed condition as shown in figure-2.

Now I can see the Backup file is created successfully and saved at the defined location as shown in picture below

now I am going to delete the Database using Drop query as shown in picture below

Now restore the database using backup file as shown in a picture below:

Now I am going to create a backup of the same database "Company" in uncompressed condition as shown in a picture below:

Now I am going to create a backup of the same database "Company" in uncompressed condition

Now follow the same step as shown in figure-4 to delete/drop the database, then follow the steps as shown in figure-5 to restore the database using compressed backup file.

 

Difference between Compressed Backup and Uncompressed Backup

S. No.            Compressed Backup                       Uncompressed Backup           
1 It is very small in size as compared to uncompressed backup.            It is large in size as compared to compressed backup.           
2 It takes time to create backup file as compared to uncompressed backup.            It takes an exceedingly small time to create backup file.           
3 Its restoring time is very less.            It takes time to restore the backup file as compared to compressed backup.           
4 It saves about 75% of disk space as compared to uncompressed backup.            Its size is approx 4 times of compressed backup.           

HostForLIFEASP.NET SQL Server 2021 Hosting

 



SQL Server 2021 Hosting - HostForLIFE :: Import CSV File Into SQL Server Using SQL Server Management Studio

clock May 10, 2022 12:14 by author Peter

In this article, we learn how to import CSV files into SQL server using SQL server management Studio
    Log in to your database using SQL Server Management Studio.
    Right-click the database and select Tasks -> Import Data.

Then click the Next button.

From Data Source, select Flat File Source and then use the Browse button to select the CSV file.


Here is my sample CSV file that has some dummy data.

Then select Advanced, and as per your CSV data size is required to set column width.


Then click the Next button.

Destination select as SQL Server Native Client and Enter the Server name;
Enter SQL Server Authentication data and click the Next button.

Then click the Next button.

Then click the Next button.

Then click the Finish button.


CSV file data imported successfully in SQL database table. The table is created as per the CSV file name. Select the database and execute a query.




About HostForLIFE

HostForLIFE is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2019 Hosting, ASP.NET 5 Hosting, ASP.NET MVC 6 Hosting and SQL 2019 Hosting.


Tag cloud

Sign in