Friday, March 24, 2017

Contained Database in SQL Server



Contained Database is independent of the instance of the SQL Server on which it is hosted and also isolated from other databases.
SQL server 2012 allows the user to isolate the contained database in following ways
   1.       All the metadata is maintained in contained database instead of master database
   2.All metadata are defined using same collation
   3.      User authentication is performed by database instead of the server login

in this way contained database becomes fully portable without having the issue of orphaned users.
Process to implement contained database:
1.  Enable setting for contained database authentication.
2. Create a Contained database named ContainedDB' by selecting containment type='Partial' in option tab.
3. Create database user named ContainedDBUser with SQL Server authentication with no link at instance level.
4. While connecting to containedDB database, connect it by selecting in options of 'connect to server' with the new user named ContainedDBUser.


Use master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GO








Let us do it practically.


    1.       Create ContainedDB


   2.       In options tab select containment type to partial.


    3.       Create database user named ContainedDBUser with usertype as SQL USER WITH Password.


   4.       Connect to ContainedDB database using the above created user ContainedDBUser by mentioning details through options.
 in login mention ContainedDBUser

   5.       In connection properties tab, connect to database ContainedDB

66.       We will get ContainedDB connection as below.



f
     Please try above scenarios in Development environment only.

Thursday, March 23, 2017

WITH RESULT SETS IN SQL Server



SQL server 2012 provide with result sets stored procedure execution facility through which we can have better control over the procedure output results, data types and column names.



/*Create Sample Employee Table */
CREATE TABLE Employee(EmpId int, Name Varchar(50),Salary Money)

/* Insert Sample Data in  Table Employee */
INSERT INTO Employee(EmpId,Name,Salary)
select 1,'Ram',100.5
UNION ALL
SELECT 2,'Shayam',115.5
UNION ALL
SELECT 3,'Sita',120

/* Check the inserted Data*/
SELECT * FROM Employee

Scenarios:

Example 1:
/*Create Stored procedure with complete result set*/
Create Procedure ProcEmployee
as
SELECT * FROM Employee
GO


execute ProcEmployee
with result sets
(
(EmployeeId Int, EmployeeName Varchar(1), Salary Int)
)
/*Output
After execution of above stored procedure with, with Result sets clause, it shows the out put column name and data types as per definition in with result sets clause
 */

Example 2:
Create Procedure ProcEmployee_withNoResult
as
SELECT * FROM Employee
where 1=2
GO

execute ProcEmployee_withNoResult
with result sets
(
(EmployeeId Int, EmployeeName Varchar(1), Salary Int)
)
/* OUTPUT
If we execute stored procedure with no result set and execute it with, with result sets clause it executes successfully with no error
*/


Example 3:
CREATE Procedure ProcEmployee_withPrint
as
PRINT 'PrintStmt'
GO

execute ProcEmployee_withPrint
with result sets
(
 ( EmployeeName Varchar(100) )

)

/* OUTPUT
If we execute SP without any result set have just print statement, it shows below error.

PrintStmt
Msg 11536, Level 16, State 1, Line 44
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

*/

Example 4:
CREATE PROCEDURE ProcEmployee_withZeroSelect
as
select top 0 0
GO
execute ProcEmployee_withZeroSelect
with result sets
(
 ( EmployeeName Varchar(100) )
)

/* OUTPUT
If we execute stored procedure with no result set and execute it with, with result sets clause it executes successfully with no error
*/

Example 5:
CREATE PROCEDURE ProcEmployee_withError
as
select 1/0
GO

execute ProcEmployee_withError
with result sets
( ( EmployeeName Varchar(100) ))

/* OUTPUT
If we execute stored procedure having error while execution, it shows below error

Msg 8134, Level 16, State 1, Procedure ProcEmployee_withError, Line 87
Divide by zero error encountered.
*/

Example 6:
EXECUTE dbo.ProcEmployee
WITH RESULT SETS UNDEFINED;
GO
/* OUTPUT: if we don’t want to change any output value or column we can use UNDEFINED clause
*/


Example 7:
EXECUTE dbo.ProcEmployee
WITH RESULT SETS NONE;
GO

/* OUTPUT: if we execute SP with NONE clause even though its returning data, it shows below error

Msg 11535, Level 16, State 1, Procedure ProcEmployee, Line 97
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.


*/

Wednesday, March 22, 2017

Choose Function in SQL Server


Choose Function in SQL Server
Choose function pick the item located at specified index from the list of provided Values
Returns the data type with the highest precedence from the set of types passed to the function


Syntex:
CHOOSE ( index, val_1, val_2 [, val_n ] )


Examples: in the item list, we have varchar values, in our scenario we have 6 items['IT','India','Dinesh','Babu','Verma','Computer']

1.       If we pass index= 2 it will show value located at position 2
Select Choose(2     ,'IT','India','Dinesh','Babu','Verma','Computer')

2.       If we pass index=5.2(numeric) it will first convert into integer(5) and will show value located at position 5.

Select Choose(5.2  ,'IT','India','Dinesh','Babu','Verma','Computer')

3.       If we pass -Ve value, it will return NULL
Select Choose(-3.3  ,'IT','India','Dinesh','Babu','Verma','Computer')

4.       If we pass out of index value >6 then it will return NULL
Select Choose(9    ,'IT','India','Dinesh','Babu','Verma','Computer')


Scenario: in the list of provided items we should have same data type values Else it show error
Example:
Select Choose(3  ,'DB',,'IT','India','Dinesh','Babu','Verma','Computer')

If we execute above statement it will show below error.
Msg 245, Level 16, State 1, Line 24
Conversion failed when converting the varchar value 'IT' to data type int.

Please try above in Dev Environment.

Wednesday, December 28, 2016

SQLLOCALDB.exe

 
SQLLOCALDB is a utility to install the instance of SQL Server express LocalDB, it’s a command line tool which allow the developers to install and manage the instance of SQL Server express LocalDB
SqlLocalDB.exe have multiple arguments to pass i.e Create,Delete,Start, Stop etc.

Example:
sqlLocalDB.exe create "Organization" 
Above command will create the SQL server instance named Organization
 
for more details, please visit link

https://msdn.microsoft.com/en-us/library/hh212961.aspx

Monday, November 28, 2016

Tempdb Sizing

Many DBA’s Prefer to set 100-200 GB size for  database tempdb,  out of this space 90 % for data file(.mdf) and 10% for Log file(.ldf), this sizing help to prevent uncontrolled size growth & reduce contention.

Friday, October 14, 2016

MAX Value From Multiple Columns



CREATE TABLE #MAXCOLUMNVALUES
(ID INT IDENTITY(1,1)
,INFYSalary INT, WIPROSalary INT, SYNESalary INT, TCSSalary INT
)


INSERT INTO #MAXCOLUMNVALUES(InfySalary,WiproSalary,SyneSalary,TCSSalary) VALUES
(10000,20000,33000,25000),
(20000,55000,35000,45000),
(40000,24000,66000,50000),
(50000,51000,53000,77000),
(44000,32000,38000,30000)

SELECT * FROM #MAXCOLUMNVALUES
-- Select Max value from Multiple columns as column name MAXSalary

SELECT
*, (SELECT MAX(MaxSalary) FROM (VALUES (InfySalary),(WiproSalary),(SyneSalary),(TCSSalary)) AS Salary(MaxSalary)) AS MAXSalary
 FROM #MAXCOLUMNVALUES