Starting in SQL Server 2012 and in Azure SQL Database, Microsoft introduced the concept of a contained database. A contained database is a database that is completely un-reliant on the instance of SQL Server that hosts the database including the master database. Theoretically, this makes a database much easier to move between servers (You’ll note the absence of SQL Agent jobs from this post, that’s a different problem.). One of the biggest benefits is that it allows database level authentication, so there is no need to have user logins at Server level.

Contained database now enables us to make a database more portable. I can backup and restore to any instance of SQL Server and the database will carry all its logins with it. There is no longer a need to script out all logins and create those at the server instance level for a user to connect to that restored database. I personally have run into the issue of missing logins at instance level when restoring to another server. In these cases, I have had to go back and script out those logins to apply them to the new instance. You can see how in an emergency where the source server may not be available that not having access to those logins could present a real issue. This is also beneficial for databases that are members of Always On Availability Groups--you don’t have to create logins on each server.

In addition to portability, contained databases allow us to expand the control of login creation to more than just the database administrator or highly-privileged user accounts. Traditional databases require you to create server level roles and server level permissions in order to grant database rights to a user.  With contained databases, you avoid this, database owner and users with an ALTER ANY USER permission can now control access to the database. One drawback is the database user account must be independently created in each database that the user will need which adds a little more maintenance.

Below, I will show you how to enable this option at both the server and database levels. From there, I will show you how to create user logins and what the difference is between traditional (non-contained) login accounts and contained users.

Enable at Server level

Script
EXEC sys.sp_configure N'contained database authentication', N'1' 
GO 
RECONFIGURE WITH OVERRIDE 
GO 

GUI

Enable at database level

Note the word “Partial” in the dropdown and script.

PER MSDN
The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

Use the sys.dm_db_uncontained_entities and sys.sql_modules (Transact-SQL) view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.

Script
USE [master] 
GO 
ALTER DATABASE [AdventureWorks2016CTP3] SET CONTAINMENT = PARTIAL WITH NO_WAIT 
GO 

GUI

To Add a User
Below, you will note a few differences in syntax. Traditionally we used the work LOGIN while contained uses USER. Also, note that when adding or changing database permissions, the ALTER statements are very different. Traditional uses ROLE and MEMBER while Contained uses AUTHORIZATION and SCHEMA.

Traditional NON-Contained, adding user and granting READ/WRITE to a database,
CREATE LOGIN JoeShmo WITH PASSWORD = '1234Password'; 
 
USE [AdventureWorks2016CTP3] 
GO 
CREATE USER [JoeShmo] FOR LOGIN [JoeShmo] 
GO 
USE [AdventureWorks2016CTP3] 
GO 
ALTER ROLE [db_datareader] ADD MEMBER [JoeShmo] 
GO 
USE [AdventureWorks2016CTP3] 
GO 
ALTER ROLE [db_datawriter] ADD MEMBER [JoeShmo] 
GO 

Contained Database adding user and granting READ/WRITE to a database -- this works for both SQL Authentication and Windows.
CREATE USER JoeShmo WITH PASSWORD = '1234strong_password'; 
 
USE [AdventureWorks2016CTP3] 
GO 
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [JoeShmo] 
GO 
USE [AdventureWorks2016CTP3] 
GO 
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [JoeShmo] 
GO 

If changing to Contained database and you want to convert all your Server Logins to contained database users, Microsoft has given us a great script to use. I have reposted it below. The example must be executed in the contained database.
DECLARE @username sysname ;  
DECLARE user_cursor CURSOR  
    FOR   
        SELECT dp.name   
        FROM sys.database_principals AS dp  
        JOIN sys.server_principals AS sp   
        ON dp.sid = sp.sid  
        WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;  
OPEN user_cursor  
FETCH NEXT FROM user_cursor INTO @username  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        EXECUTE sp_migrate_user_to_contained   
        @username = @username,  
        @rename = N'keep_name',  
        @disablelogin = N'disable_login';  
    FETCH NEXT FROM user_cursor INTO @username  
    END  
CLOSE user_cursor ;  
DEALLOCATE user_cursor ; 

Aside from the lack of support for MSDB, the one other issue I’ve run into with contained databases was an application that contained multiple databases supporting the applications but used SQL logins. In this case, it was a version of dynamics -- with Windows logins this is easy -- you simply create the login in each database and let Active Directory deal with the passwords. However, with contained databases the passwords are local to each database--so it’s a challenge to sync these accounts. With my current customer in this situation, we’ve reverted to server logins and used Dbatools to sync the passwords between servers. I can think of many ways contained database can add benefits, I can’t wait to play around with it more.

Stuff is a function in SQL Server used to perform special operations on a string value.
The below operations can be performed,

• Remove string part from string expression.
• Insert/Append string at specified index.

Syntax
select STUFF(string_value, start_index, no_of_chars_to_replace, replace_string); 

Remove String Part
select STUFF('hai_hello',0,2,''); 

Important Note
Start Index begins from 1 in STUFF Function.

Proper Index
select STUFF('hai_hello',1,2,''); 

Insert String Content
You can insert a string content by specifying the index location and set number of characters to replace to zero. Note that the third parameter value should be zero.
DECLARE @testString varchar(3) = 'abc'; 
select STUFF('hai_hello', 1, 0, @testString); 

Replace String Content
You cannot replace string by specifying the old characters here.
But you can replace the string by specifying start location and number of characters to replace.

Note
The third parameter value should be the length to replace.

Example 1
DECLARE @testString varchar(3) = 'abc'; 
select STUFF('hai_hello', 1, DATALENGTH (@testString), @testString); 

Example 2
select STUFF('hai_hello',1,2,'abc');

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 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.

The command "UNION" is the perfect way to join two tables with the same data context. Whether they have or do not have the same fields, you need to classify the data.
Look at this selected query.
Select t.* From ( 
(Select 1 as typePerson, tenName as namePerson, tenSalary as payMoney From Teachers Where tenAge>30) 
UNION 
(Select 0 as typePerson, stdName as namePerson, 0 as payMoney From Students Where stdYearFinish=2016) 
) t 
Where t.namePerson like ‘Maria%’ 
Order By t.tenName , t.typePerson;*

It creates a temporary alias “t”;
It classifies each data row “typePerson”, 1 (true) for teachers and 0 (false) to Students;
It filters the age of the teachers;
It filters the end year on the school to the year 2016;
After the UNION, it filters by the field name Person that begins with Maria.

Observations
All fields must be at the same position and the same data type, you can make all kinds of selects, joins, where etc. The "UNION ALL" command is better than UNION if you want to select all rows. If they are the same**, this is not the case. In this sample, you can make it a View.

CONCLUSION
You must be careful of the position of the fields and the type, and you can use cast too.

This selection is just an example.
The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned).

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.

I was working on a scenario in which I needed to assign a unique value to a column. I was trying to update the column using window ranking function ROW_NUMBER() but I got an error that ‘Windowed function can only appear in SELECT or ORDER BY clause’:

Then I did some workaround and used the Windows function indirectly using a CTE (Common Table Expression) for this. I will show you step by step how I accomplished this using CTE.

Let’s first create a table with some test data,
    CREATE TABLE Test 
    ( 
        ID INT, 
        Value VARCHAR(10) NOT NULL 
    ) 
    GO 
     
    INSERT INTO Test (Value) VALUES('Sunday'),('Monday'),('Tuesday'),('Wednesday'),('Thursday'),('Friday'),('Saturday') 
    GO 

As we can see that in column ID NULL values get inserted as we didn’t specify any values for this column during INSERT statement. So, when I tried this UPDATE statement using ROW_NUMBER() with SET I got the following error,
    UPDATE Test 
    SET ID = ROW_NUMBER() OVER(ORDER BY Value) 
    GO 

Then I used CTE to update the ID values in table test in this manner,
    ;WITH CTE AS 
    ( 
        SELECT Value, ROW_NUMBER() OVER(ORDER BY Value) as RN 
        FROM Test 
    ) 
     
    UPDATE T 
    SET ID = RN 
    FROM CTE C JOIN Test T ON T.value = C.Value 

When I ran this SQL code I didn’t get any error and ID column values to get updated with unique values.