SQL Puzzle | Find rows with more than 2 dots(.)[Number of occurance of a character in a string]

10 Wednesday Jan 2018

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

SQL Puzzle | Find rows with more than 2 dots(.)[Number of occurance of a charater in a string]

In this puzzle you need to find rows with more than 2 dots are present in the column Vals. For more details please check the sample input and expected output.

Sample Input

ID	Vals
1	0.0
2	2.3.1.1
3	4.1.a.3.9
4	1.1.
5	a.b.b.b.b.b..b..b
6	6.

Expected Output

ID	Vals
2	2.3.1.1
3	4.1.a.3.9
5	a.b.b.b.b.b..b..b

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE testDots
(
	 ID INT
	,Vals VARCHAR(100)
)
GO

INSERT INTO testDots VALUES
(1,'0.0'),
(2,'2.3.1.1'),
(3,'4.1.a.3.9'),
(4,'1.1.'),
(5,'a.b.b.b.b.b..b..b'),
(6,'6.')
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

SELECT * FROM testDots WHERE Vals LIKE '%.%.%.%'

--

Output-1

--                               
  
ID          Vals
----------- ----------------------------------------------------------------------------------------------------
2           2.3.1.1
3           4.1.a.3.9
5           a.b.b.b.b.b..b..b

(3 rows affected)


                                              
                                    
--

Solution – 2

--

SELECT * FROM testDots WHERE LEN(Vals) - LEN(REPLACE(Vals,'.','')) > 2

--

Output-2

--                               
  
ID          Vals
----------- ----------------------------------------------------------------------------------------------------
2           2.3.1.1
3           4.1.a.3.9
5           a.b.b.b.b.b..b..b

(3 rows affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2016- VII [ USE HINT query hint argument ]

10 Wednesday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

NEW T-SQL FEATURES IN SQL SERVER 2016- VII [ USE HINT query hint argument ]

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

New feature – VII | [ USE HINT query hint argument ]

Microsoft Introduces a new query hint argument, USE HINT, which lets you drive the query optimizer without elevated credentials or without being a member of the
sysadmin server role. Now the advantage is that we do not have to remember the trace flags and we do not need higher role to perform this.

Sample:-

Option	Equivalent trace flag	Description
FORCE_LEGACY_CARDINALITY_ESTIMATION	TF 9481	Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier versions, regardless of the compatibility level of the database.
ENABLE_QUERY_OPTIMIZER_HOTFIXES	TF 4199	Controls query optimizer changes released in SQL Server Cumulative Updates and Service Packs.
DISABLE_PARAMETER_SNIFFING	TF 4136	Disables parameter sniffing unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR value is used.
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES	TF 4137	Causes SQL Server to generate a plan by using minimum selectivity when estimating AND predicates for filters to account for correlation, under the query optimizer cardinality estimation model of SQL Server 2012 and earlier versions.
DISABLE_OPTIMIZER_ROWGOAL	TF 4138	Causes SQL Server to generate a plan that does not use row goal adjustments with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords.
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS	TF 4139	Enable automatically generated quick statistics (histogram amendment) regardless of key column status. If this option is used, regardless of the leading statistics column status (ascending, descending, or stationary), the histogram used to estimate cardinality will be adjusted at query compile time.
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS	TF 9476	Causes SQL Server to generate a plan by using the Simple Containment assumption instead of the default Base Containment assumption, under the query optimizer cardinality estimation model of SQL Server 2014 through SQL Server 2016 versions.
DISABLE_OPTIMIZED_NESTED_LOOP	TF 2340	Causes SQL Server not to use a sort operation (batch sort) for optimized nested loop joins when generating a plan.
FORCE_DEFAULT_CARDINALITY_ESTIMATION	TF 2312	Enables you to set the query optimizer cardinality estimation model to the SQL Server 2014 through SQL Server 2016 versions, dependent of the compatibility level of the database.

Example

--                               
  
DECLARE @Vals AS VARCHAR(100) = 'SomeValue'
SELECT * from yourtable
WHERE columnname = @vals
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));

--

Refer Microsoft LINK below for more details-

1.https://support.microsoft.com/en-in/help/3189813/update-introduces-use-hint-query-hint-argument-in-sql-server-2016

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Server | Deterministic and Nondeterministic Functions?

10 Wednesday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions, Uncategorized

≈ Leave a comment

Tags

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

ABS	DATEDIFF	POWER
ACOS	DAY	RADIANS
ASIN	DEGREES	ROUND
ATAN	EXP	SIGN
ATN2	FLOOR	SIN
CEILING	ISNULL	SQUARE
COALESCE	ISNUMERIC	SQRT
COS	LOG	TAN
COT	LOG10	YEAR
DATALENGTH	MONTH
DATEADD	NULLIF

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

@@CONNECTIONS
GETDATE
@@CPU_BUSY
GETUTCDATE
NEWID
PARSENAME
RAND

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-

1.https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | Find Non matching records from Second table

10 Wednesday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ 3 Comments

Tags

SQL Puzzle | Find Non matching records from Second table

In this puzzle you need to find the non matching records from column Vals. Need Vals column from SecondTable and Id column from firstTable. For more details please check the sample input and expected output.

Sample Input

First Table

ID	Vals
1	a
2	b
3	c
4	NULL

Second Table

ID	Vals
1	a
2	NULL
3	z
4	NULL

Expected Output

Id	Vals
2	NULL
3	z
4	NULL

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE firstTable
(
	 ID  INT    	                
	,Vals VARCHAR(10)     
)
GO

INSERT INTO firstTable VALUES
(1    ,'a'),
(2    ,'b'),
(3    ,'c'),
(4	  , NULL)
GO

CREATE TABLE SecondTable
(
	 ID  INT    	                
	,Vals VARCHAR(10)   
)
GO

INSERT INTO SecondTable VALUES
(1    ,'a'),
(2    ,NULL),
(3    ,'z'),
(4    ,NULL) 
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

SELECT f.Id,s.Vals FROM firstTable f
INNER JOIN SecondTable s ON f.ID = s.ID 
AND ( f.Vals <> s.Vals OR f.Vals IS NULL OR s.Vals IS NULL )

--

Output-1

--                               
  
Id          Vals
----------- ----------
2           NULL
3           z
4           NULL

(3 rows affected)
                                              
                                    
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2017- III [ STRING_AGG() ]

10 Wednesday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER

≈ 3 Comments

Tags

NEW T-SQL FEATURES IN SQL SERVER 2017- III [ STRING_AGG() ]

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

New feature – III | [ STRING_AGG() ]

This function concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

Notes:-

1. Now we do not have to use the old XML trick.
2. STRING_AGG is available in any compatibility level.
3. STRING_AGG aggregate takes all expressions from rows and concatenates them into a single string.
4. Null values are ignored.
5. The return type is depends on first argument (expression). If input argument is string type (NVARCHAR, VARCHAR), result type will be same as input type.
6. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions. Please refer below table for automatic conversions

Data type of Input	Result
NVARCHAR(MAX)	NVARCHAR(MAX)
VARCHAR(MAX)	VARCHAR(MAX)
NVARCHAR(1…4000)	NVARCHAR(4000)
VARCHAR(1…8000)	VARCHAR(8000)
int, bigint, smallint, tinyint, numeric, float, real, bit, decimal, smallmoney, money, datetime, datetime2,	NVARCHAR(4000)

Example

Data Generation

--

CREATE TABLE Agg
(
	 DepartId INT
	,EmpName VARCHAR(100)
)
GO

INSERT INTO Agg VALUES
(1,'Pawan'),
(1,'Ramesh'),
(2,'Krishan'),
(3,'Sharlee'),
(2,'Nepa'),
(2,'Chandni'),
(4,'Bob')
GO


--

Solution using STRING_AGG function

--

SELECT DepartId, 
    STRING_AGG (EmpName, ',') WITHIN GROUP (ORDER BY DepartId ASC) Names 
FROM dbo.Agg 
GROUP BY DepartId; 

--

OUTPUT

--

DepartId    Names
----------- -----------------------------
1           Pawan,Ramesh
2           Krishan,Nepa,Chandni
3           Sharlee
4           Bob

(4 rows affected)

--

Refer below for more details-

1.https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Improving my SQL BI Skills

Daily Archives: January 10, 2018

SQL Puzzle | Find rows with more than 2 dots(.)[Number of occurance of a character in a string]

NEW T-SQL FEATURES IN SQL SERVER 2016- VII [ USE HINT query hint argument ]

SQL Server | Deterministic and Nondeterministic Functions?

SQL Puzzle | Find Non matching records from Second table

NEW T-SQL FEATURES IN SQL SERVER 2017- III [ STRING_AGG() ]

Share this

Share this

Share this

Share this

Share this