Deterministic and Nondeterministic Functions

This was asked to me in a recent technical interview so i thought of sharing it with you all. Lets first go through the definitions.

Deterministic Functions

Deterministic functions are the functions which will always return the same result any time with same input given with the same state of the database.

Nondeterministic Functions

Nondeterministic functions are the functions that may return different results each time they are called with the same input values even with the same
of the database.

Sample list built in Deterministic Functions

Sample Example of Deterministic Functions | ISNULL()

This function will return the same value each time you execute it on any DB.

--

DECLARE @sample AS INT = NULL
SELECT ISNULL(@sample, 10) Op

Op
-----------
10

(1 row affected)

--

Sample list of built in Nondeterministic Functions

Examples of built in Nondeterministic Functions

This function will return the different value each time you execute it.

--

DECLARE @sample AS INT = NULL
SELECT ISNULL(@sample, 10) Op

Op
-----------
10

(1 row affected)

--

--

DECLARE @sample AS INT = NULL
SELECT ISNULL(@sample, 10) Op

Op
-----------
10

(1 row affected)

--

Refer below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2017- I [ DBCC CLONEDATABASE ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

Microsoft introduced a new management command called DBCC CLONEDATABASE command in MS SQL SERVER 2017. This command will generate a clone of a database by using DBCC CLONEDATABASE. With DBCC CLONEDATABASE feature you can generate a clone of a database in order to investigate a performance issue related to a Query or Workload.

Cloning process:-

1. Creates a new destination database that uses the same file layout as the source but with default file sizes as the model database.
2. Creates an internal snapshot of the source database.
3. Copies the system metadata from the source to the destination database.
4. Copies all schema for all objects from the source to the destination database.
5. Copies statistics for all indexes from the source to the destination database.

Notes–

1. This new database can use used to investigate a performance issue related to the query optimizer.
2. The new DB is primarily intended for troubleshooting and diagnostic purposes.
3. Microsoft recommends detaching of the cloned database after the database is created.
4. DBCC CLONEDATABASE is not supported to be used as a production database.
5. The new cloned database created is ReadOnly, with no data. The most important point is that it will have Statistics.
6. As best practice suggested by Microsoft product team this clone Database is not supposed to remain in PROD database
, but can be moved to a Dev or Test box for further troubleshooting and diagnostic purposes.

On other SQL Versions–

You can get the same command in other versions also by installing the service packs.

Sample example for [ CREATE OR ALTER ]

Generate Sample Data

--

--Create a new Database

CREATE DATABASE testPawan
GO

--Use the new database

USE testPawan
GO

--Create a new table in the new database

CREATE TABLE [dbo].[SplitStrings](
    [Id] [int] NULL,
    [Vals] [varchar](100) NULL
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (1, N'a,b')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (2, N'a')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (3, N'b,3')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (4, N'c,u')
GO


--

TableData

CLONEDATABASE Command

--

--CLONEDATABASE Command

DBCC CLONEDATABASE ( 'testPawan' , 'NewPawan' )
GO
                                    
--

Output

--

Database cloning for 'testPawan' has started with target as 'NewPawan'.
Database cloning for 'testPawan' has finished. Cloned database is 'NewPawan'.
Database 'NewPawan' is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use 
in a production environment. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
                                    
--

The CLONEDATABASE command will create the new readonly database called NewPawan. Check out the details

Check Data in the new database table

--

USE NewPawan
GO

SELECT * FROM NewPawan.dbo.SplitStrings



Id          Vals
----------- ----------------------------------------------------------------------------------------------------

(0 rows affected)
                                    
--

Refer below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2016- III [ CREATE OR ALTER ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

Microsoft has introduced a new T-SQL language feature – CREATE OR ALTER. This statement unites the CREATE and ALTER statements and creates object if it does not exist, or alter the object if it is already there.

CREATE OR ALTER can be applied on following objects-

Sample example for [ CREATE OR ALTER ]

Generate Sample Data

--

USE [Pawan]
GO
/****** Object:  Table [dbo].[SplitStrings]    Script Date: 1/7/2018 9:36:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SplitStrings](
	[Id] [int] NULL,
	[Vals] [varchar](100) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (1, N'a,b')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (2, N'a')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (3, N'b,3')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (4, N'c,u')
GO


--

TableData

Stored Procedure

--

CREATE OR ALTER PROC TestPawan
AS
BEGIN
		
	SELECT Id , Vals
	FROM [SplitStrings] 
	
END
GO
                                    
--

Output

--

EXEC TestPawan

Id          Vals
----------- --------
1           a,b
2           a
3           b,3
4           c,u

(4 rows affected)

                                    
--

VIEW

--

CREATE OR ALTER VIEW Vw_SplitString
AS
	SELECT Id , Vals
	FROM [SplitStrings] 
GO
                                    
--

Output

--

SELECT * FROM Vw_SplitString

Id          Vals       
----------- ---------- 
1           a,b        
2           a          
3           b,3        
4           c,u        
                       
(4 rows affected)      
                 

                                    
--

FUNCTION

--

CREATE OR ALTER FUNCTION fnSplitstring()
RETURNS INT
AS
BEGIN
   RETURN ( SELECT COUNT(1) FROM [SplitStrings] )
END;
GO
                                    
--

Output

--

SELECT dbo.fnSplitstring()


-----------
4

(1 row affected)

                                    
--

TRIGGER

--

CREATE OR ALTER TRIGGER trTest 
ON [SplitStrings] 
AFTER INSERT, UPDATE 
AS
 RAISERROR ('Pawan is Here - MSBISkills.com', 1, 10);
                                    
--

Output

--

INSERT INTO [SplitStrings] VALUES
(5,'Alpha,Beta')

Pawan is Here - MSBISkills.com
Msg 50000, Level 1, State 10

(1 row affected)

                                    
--

Refer below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2016-I

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

This is the new addition in T-SQL where you can drop the object using DROP IF EXISTS command. Also note that even if the object does not exists, the command will not fail and execution will continue. As of the now following objects can be dropped using DROP IF EXISTS command.

Sample example for DROP DATABASE IF EXISTS

--

CREATE DATABASE testpawan
GO

--

Output

--

/*------------------------
CREATE DATABASE testpawan
GO

------------------------*/
                                    
--

--

DROP DATABASE IF EXISTS testpawan
GO

--

Output

--

/*------------------------
DROP DATABASE IF EXISTS testpawan
GO
------------------------*/
                                    
--

Refer for more details –

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com