With the release of SQL Server 2017, a new TRIM() is also introduced which helps to remove the white space/characters from both sides of a string. Before 2017, this functionality was achieved by using the following SQL functions.
    REPLACE - used o replace a character from a string
    LTRIM - trim the white spaces from the left side of a string
    RTRIM - trim the white spaces from the right side of a string

I can explain the functionality with two scenarios.

Let's assume, we have a string named ' ABC ' and we are going to eliminate the white spaces from both sides of the string.
 
In SQL, we usually use the LTRIM and RTRIM function like in the code below.
    SELECT LTRIM( RTRIM(' ABC ')) 

Now, this can be done by using a single TRIM function.
    SELECT TRIM(' ABC ') 

Test results from SSMS can be seen below.

Assume we have a string named 'X ABC Y' and we need to extract 'ABC' from that. As usual, we will go with the REPLACE function as follows.
    SELECT REPLACE(REPLACE('X ABC Y','X ',''),' Y','') 

Here you go with the TRIM function.
    SELECT TRIM('XY ' FROM 'X ABC Y') 

Test results from SSMS are shown below.
Note - It is necessary that you have to mention the trailing charter in the TRIM function, otherwise, this will not work as expected.
 
For example, if you try to remove the 'white space' only from the string 'X  ABC  Y', then TRIM will not help you. Similarly,  if you don't mention the letter 'Y', TRIM will not remove the white space after the string, even though you already mentioned the 'X' and the 'white space' characters inside the TRIM function. See these scenarios in the below screenshot.
 
Test results from SSMS,

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu 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.

In this article, I described how to calculate the maximum range of various integer data types in SQL Server. TINYINT, SMALLINT, INT and BIGINT are all number data types. The difference between these data types are in the minimum and maximum values. So let's have a look at a practical example of how to calculate the maximum range of the integer data type in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Calculating the maximum range of various integer data types.

Bigint Data Type
The Bigint data type represents an integer value. It can be stored in 8 bytes.

Formula   2^(n-1) is the formula of the maximum value of a Bigint data type.
In the preceding formula N is the size of the data type. The ^ operator calculates the power of the value.
Now determine the value of N in Bit:
Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'BIGInt' 

Determine the maximum range of Bigint
The formula is:
2^(n-1) here N=64

Select Power(cast(2 as varchar),(64) -1) as 'Bigint max range'  from sys.types Where name = 'BIGInt'

The range of a Bigint data type is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

INT Data Type
Int represents an integer value that can be stored in 4 bytes. INT is the short form of integer.

Formula
2^(n-1) is the formula to find the maximum of an INT data type.
In the preceding formula N is the size of data type. The ^ operator calculates the power of the value.

Now determine the value of N in Bit:
Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'Int'

Determine the maximum range of int
The formula is:
2^(n-1) here N=32
Select Power(cast(2 as varchar),(32) -1) as 'int max range'  from sys.types Where name = 'Int'

The range of an int data type is -2,147,483,648 to 2,147,483,647.

Smallint Data Type
Smallint represents an integer value that can be stored in 2 bytes.

Formula 
2^(n-1) is the formula to find the maximum of a Smallint data type.
In the preceding formula N is the size of the data type. The ^ operator calculates the power of the value.

Now determine the value of N in Bit:Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'Smallint'

Determine the maximum range of Smallint
The formula is:
2^(n-1) here N=64
Select Power(cast(2 as varchar),(16) -1) as 'Smallint max range'  from sys.types Where name = 'SMALLInt'

The range of a Smallint data type is -32768 to 32767.
Tinyint Data Type
Tinyint represents an integer value that can be stored in 1 byte.
The range of a Tinyint data type is 0 to 255.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu 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.

In this series of articles, we will go deep into SQL Server from scratch and will gain knowledge of queries, optimization, and database administration. This is the first article of the series where we will learn about general SQL queries and their functioning. Images have been used wherever necessary so as to make you understand every command properly.

All Queries which I am posting today you can use  directly on your query plan like copy, paste and execute this query.
Each query has a valid column name and similarly I have shown in the form of image for proper understanding and proper usage

Find all Primary key in Give Database in following format,

SELECT i.name AS IndexName, 
    OBJECT_NAME(ic.OBJECT_ID) AS TableName, 
    COL_NAME(ic.OBJECT_ID, ic.column_id) AS ColumnName 
FROM sys.indexes AS i 
INNER JOIN sys.index_columns AS ic 
ON i.OBJECT_ID = ic.OBJECT_ID 
AND i.index_id = ic.index_id 
WHERE i.is_primary_key = 1  

Finding Constrains and Type of Constrain i.e. Primary and foreign key relation in the given database

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint, 
    SCHEMA_NAME(schema_id) AS SchemaName, 
    OBJECT_NAME(parent_object_id) AS TableName, 
    type_desc AS ConstraintType 
FROM sys.objects 
WHERE type_desc IN('FOREIGN_KEY_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')  

Detailed level relationship and description of primary key and foreign key

SELECT f.name AS ForeignKey, 
    SCHEMA_NAME(f.SCHEMA_ID) SchemaName, 
    OBJECT_NAME(f.parent_object_id) AS TableName, 
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
    SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName, 
    OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName, 
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id 
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id 

Use the above snippets as per your requirement.

In most of the cases it's is going to be used in the Database Analysis where Database size and table are large and high in number.

Thus, we learned about the basic queries of SQL. If you have some doubt, or want to add some more information in this article, please feel free to write me in the comments section.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu 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.

In this post, we will learn how to use TRY CATCH in SQL procedure and store an error with error text. Here is a simple example for generating the error and storing it in a SQL table. Let's start coding. For saving the error in the table first we need to create the table in SQL Database. See below.
    CREATE TABLE [dbo].[Error_StoreProcedure]( 
        [ID] [bigint] IDENTITY(1,1) NOT NULL, 
        [ErrorNumber] [varchar](50) NULL, 
        [ErrorSeverity] [varchar](50) NULL, 
        [ErrorState] [varchar](50) NULL, 
        [ErrorProcedure] [varchar](500) NULL, 
        [ErrorLine] [varchar](50) NULL, 
        [ErrorMessage] [varchar](max) NULL, 
        [EntryDate] [datetime] NULL, 
     CONSTRAINT [PK_Error_StoreProcedure] PRIMARY KEY CLUSTERED  
    ( 
        [ID] ASC 
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
    GO

After creating the above table we need to create one procedure for saving the error in the table;  see below.
 CREATE PROCEDURE usp_GetErrorInfo   
    AS   
    BEGIN 
        INSERT INTO Error_StoreProcedure SELECT   
        ERROR_NUMBER() AS ErrorNumber   
        ,ERROR_SEVERITY() AS ErrorSeverity   
        ,ERROR_STATE() AS ErrorState   
        ,ERROR_PROCEDURE() AS ErrorProcedure   
        ,ERROR_LINE() AS ErrorLine   
        ,ERROR_MESSAGE() AS ErrorMessage 
        ,dbo.GetDateTimeZone()   
    END

After creating the above procedure now we have to use the above procedure inside the other procedure.
    CREATE PROCEDURE TESTING_ERROR_PROCEDURE 
      
    AS 
    BEGIN 
     SET NOCOUNT ON; 
     
        BEGIN TRY   
             
            -- Generate divide-by-zero error.   
            SELECT 1/0;   
         
        END TRY   
        BEGIN CATCH   
             
            -- Execute error retrieval routine.   
            EXECUTE usp_GetErrorInfo;   
         
        END CATCH;    
     
    END 
    GO

The above procedure generates the error and goes to the CATCH part and saves all information of the error into our error table.
Run this query SELECT * FROM Error_StoreProcedure

See the output of the above table. Output displays procedure name and line number of the error.

In this post, we will learn about how to replace newline characters from SQL fields. In this post, we have to use replace function for replace string and also use char function to remove newline characters and replace them. Here I will give you the syntax of replace function, how to use char function, and the meaning of 10 in char function.
Syntax
Description of parameter value,

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu 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.

One of the most important routes to high performance in SQL server database is an index. It is a database object which is used to speed up the querying process by providing quick access to rows in the database tables. By using Indexes we can save time and can improve the performance of database queries and applications. An Index contains keys built from one or more columns in the table mapped to the storage location of the specified data. When we create an index on any column, SQL server internally maintains a separate table called index table, so that whenever a user tries to retrieve the data from the existing table,  depending on the index Table SQL server goes directly to the table and retrieves the required data very quickly.

In the Table we can use a maximum of 250 Indexes. The Index Type refers to the way the index is stored internally by SQL server. So a Table can contain two types of indexes:

Clustered Indexes
The only time the data rows in a table are stored in sorted (ascending order only) order structure is when the table contains a clustered index. When a table has a clustered index, then it is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure. A table can have only 1 clustered Index on it, which will be created when a primary key constraint is used in a Table.

Non-Clustered Indexes
Non-clustered Indexes will not have any arrangement order (unordered structure) of the data in the table. In a table, we can create 249 non clustered Indexes.If we don't mention clustered indexes in a table then a default is stored as non-clustered Indexes.

If you’re a developer, irrespective of the platform, you  have to work with databases. Creating SQL statements for tables is quite often a monotonous job and it gets hectic especially when dealing with gigantic tables that have hundreds of columns. Writing SQL statements manually every time becomes a tiresome process. Before explaining the script, I want to share the reason to write this script and how it is helping my peers. We have code standard on the database side. Below points are standards.

• Need to maintain a separate stored procedure to every table
• Don’t use * in the query instead specify the column
• Use the correct data type and size of a column
• Every parameter should be nullable in a stored procedure.

I am developing an application which is related to machines using .NET and SQL Server. The database design consists of some master tables and transactional tables. All the transactional table has more than 30 columns.

To meet my code standards, I need to mention all columns with correct data type and size in stored procedure parameters like below,
CREATEproc [dbo].[USP_PCNitemCreation] ( @Id int, @machineName varchar(50)=NULL, @furnacename varchar(50)=NULL, @minValue int=NULL, @maxValue int=NULL, @createdDate datetime=nullvarchar(100)=NULL ) 

All the queries should specify the column instead of using the start(*).
select machineName,furnacename from trn_furnace where Id=@Id 

It consumes more time and is a boring task. So, I plan to write the script to is cut down on the time it takes and boring repeated work. We cannot automate the logic, but we can automate the repeated task.

Then I write the below script which really cuts down on all of our above pain points.

Auto Query Generator Stored Procedure for MSSQL Server,
CREATEproc [dbo].[USP_QuerycreationSupport] ( @table_Name varchar(100)=NULL ) AS  
BEGINDECLARE @InserCols   NVARCHAR(max)DECLARE @Inserparam  NVARCHAR(max)DECLARE @Insertquery NVARCHAR(max)DECLARE @Selectquery NVARCHAR(max)DECLARE @Update      NVARCHAR(max)DECLARE @DeleteQuery NVARCHAR(max) 
  -- sp paramSELECT '@'+c.NAME+Space(1)+Casecast(t.Nameasnvarchar(40))WHEN'nvarchar'THEN  
  t.NAME    +'('+cast(c.max_length asnvarchar(30))+')'  
WHEN'varchar'THEN  
  t.NAME+'('+cast(c.max_length asnvarchar(30))+')'  
WHEN'char'THEN  
  t.NAME+'('+cast(c.max_length asnvarchar(30))+')'  
WHEN'decimal'THEN  
  t.NAME        +'(18,2)'  
  ELSE t.nameend+'=null,'AS colss FROM sys.columns c innerjoin sys.types t ON c.user_type_id = t.user_type_id leftouterjoin sys.index_columns ic ON ic.object_id= c.object_idand ic.column_id = c.column_id leftouterjoin sys.indexes i ON ic.object_id= i.object_idand ic.index_id = i.index_id WHERE c.object_id=object_id(@table_Name)SELECT'Insert query'SET @InserCols=(selectdistinct  
  (  
         select sc.NAME+','  
         FROM   sys.tables st innerjoinsys.columns sc  
         ON st.object_id= sc.object_id  
         WHERE  st.NAME= @table_Name forxmlpath(''),  
                type).value('.','NVARCHAR(MAX)'))  
  -- Return the result of the functionSELECT @InserCols=LEFT(@InserCols,Len(@InserCols)-1)  
  --select @InserColsSET @Inserparam=(selectdistinct  
  (  
         select'@'+sc.NAME+','  
         FROM   sys.tables st innerjoinsys.columns sc  
         ON st.object_id= sc.object_id  
         WHERE  st.NAME= @table_Name forxmlpath(''),  
                type).value('.','NVARCHAR(MAX)'))  
  -- Return the result of the functionSELECT @Inserparam=LEFT(@Inserparam,Len(@Inserparam)-1)  
  --select @InserparamSET @Insertquery='insert into '+@table_Name+'('+@InserCols+')'+'values'+'('+@Inserparam+')'SELECT @InsertquerySELECT'Update Query'SET @Update=(selectdistinct  
  (  
         select sc.NAME+'=@'+sc.NAME+','  
         FROM   sys.tables st innerjoinsys.columns sc  
         ON st.object_id= sc.object_id  
         WHERE  st.NAME= @table_Name forxmlpath(''),  
                type).value('.','NVARCHAR(MAX)'))  
  -- Return the result of the functionSELECT @Update=LEFT(@Update,Len(@Update)-1)  
  --select @UpdateSET @Update='UPdate '+@table_Name+' set '+@UpdateSELECT @Update  
  -- For select QuerySELECT'Select Query'SET @Selectquery='select '+@InserCols +' from '+ @table_NameSELECT @Selectquery 
  -- For Delete QuerySELECT'Delete Query'SET @DeleteQuery='delete from '+ @table_NameSELECT @DeleteQuery 
end 

How to use this script,
Step 1 - Create the stored procedure using the above code or attached code.
Step 2 - Execute the stored procedure and pass your table name as a parameter.
Exec USP_QuerycreationSupport@table_Name='mstCustomer' 

Should not pass the database object in the table name
Exec USP_QuerycreationSupport@table_Name='[dbo].[mstCustomer]' 

Once you execute the Stored Procedure as mentioned above, you get all the SQL statements as shown here. You could easily use the generated SQL statements elsewhere. You get all basic SQL statements like Select, Insert, Update & Delete.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu 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.

In the release of SQL Server 2016 CTP 2 one of the features that was introduced is JSON clause. So, the first question that comes into everyone’s mind is What is JSON? JSON stands for JavaScript Object Notation. JSON is a lightweight format which is used for storing and interchanging the data. JSON uses standard JavaScript functions to convert the JSON data into native JavaScript objects. The main purpose of using FOR JSON is used to create new JSON objects. We can format the query results using FOR JSON clause in these ways,
With AUTO mode
With PATH mode
With ROOT option
Output with INCLUDE_NULL_VALUES option

In this blog, we will discuss the query formatting using FOR JSON clause with AUTO mode option.

Syntax for FOR JSON clause with AUTO option is like this:
FOR JSON AUTO

When AUTO option is used, the format of JSON is determined automatically on the basis of the number of columns present in the SELECT statement list. A FROM clause is necessary inquery with FOR JSON AUTO option.

When you join tables, columns present in the first table are used as properties of the root object in JSON array while columns present in the second table will be automatically formatted as a nested object within the root object.

Let’s execute the below query and see the JSON output.
SELECT sp.BusinessEntityID, 
   sp.TerritoryID, 
   st.CountryRegionCode, 
   st.[Group] TerrritoryGroup 
   FROM sales.salesperson sp 
   JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID 
   WHERE sp.TerritoryID = 10 
FOR JSON AUTO 

After executing the above query, we get the output in this format.
[{ 
    "BusinessEntityID": 289, 
    "TerritoryID": 10, 
    "st": [{ 
        "CountryRegionCode": "GB", 
        "TerrritoryGroup": "Europe" 
    }] 
}]

Brackets [ ] represents JSON array in output.

Here, in the output, we can see that table Sales.SalesTerritory is automatically formatted as a nested object under parent object.

So we have generated a formatted query output using JSON clause. I will continue with other ways of formatted output using JSON clause in my next blogs.