Tag Archives: Cardinality

Cardinality in SQL

08 Wednesday Apr 2015

Posted by in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Cardinality

Last week one of my friend called me and asked me what is Cardinality. He told me that in one of the interviews the interviewer asked this question. Before this I have never heard that this question was asked in a MSBI Interview.

https://en.wikipedia.org/wiki/Cardinality_(SQL_statements)

As per Wikipedia article on Cardinality (SQL statements), defines it as follows:

Cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column

There are 3 types of cardinality:

High-cardinality, Normal-cardinality, and Low-cardinality

High-cardinality refers to columns with values that are very uncommon or unique. High-cardinality column values are typically identification numbers, email addresses, or user names. An example of a data table column with high-cardinality would be a Emps table with a column named EMPID. Since the values held in the EMPID column are unique, this column’s cardinality type would be referred to as high-cardinality.

Normal-cardinality refers to columns with values that are somewhat uncommon. Normal-cardinality column values are typically names, street addresses, or vehicle types. An example of a data table column with normal-cardinality would be a CUSTOMER table with a column named LAST_NAME, containing the last names of customers. While some people have common last names, such as Smith, others have uncommon last names. Therefore, an examination of all of the values held in the LAST_NAME column would show “clumps” of names in some places (e.g.: a lot of Smith’s ) surrounded on both sides by a long series of unique values. Since there is a variety of possible values held in this column, its cardinality type would be referred to as normal-cardinality.

Low-cardinality refers to columns with few unique values. Low-cardinality column values are typically status flags, Boolean values, or major classifications such as gender. An example of a data table column with low-cardinality would be a CUSTOMER table with a column named NEW_CUSTOMER. This column would contain only 2 distinct values: Y or N, denoting whether the customer was new or not. Since there are only 2 possible values held in this column, its cardinality type would be referred to as low-cardinality.

Keep learning. We all need to learn.

MSBISkills.com

Advanced SQL SERVER Interview Questions – Set 1

07 Tuesday Apr 2015

Posted by in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Advance SQL SERVER Interview Questions – Set 1

Recently I have attended a SQL Server Architect Interview. There were 3 interviewers & since the requirement is for SQL Architect, they asked all the questions from SQL only. Please find some of the questions below. Will post answers to these questions tomorrow.

Question
What are statistics? Where they are used and how to check statistics.
What are the types of Fragmentations
Can we use GUID as Primary key in a table ?
Difference between Unique Index vs Unique Constraint
What are RED Flags in SQL Server and what is there usage ?
What are the types of physical joins in SQL Server ? What are the different join operators in SQL Server?
What is a Latch in SQL Server ?
Difference between Latch and Lock ..
2014 New Features in DB Engine.
In which scenarios we should not use CTE’s.
What do you mean by Cardinality in SQL Server.
What are Histogram and Density Vector.
Why we can’t put Order by inside the view?
What is a cross apply and how to use this?
Can we use more than one CTE in a single select query?
I have opened a nested transaction inside an outer transaction, if i do rollback which transaction will be undone?
How do you know the total transaction count?
I have created a table variable can we use it in a nested stored procedure? If not what is the scope of a table variable?
If suppose we have a user defined data type. If we can modify the length of the data type does it effects in all places across the database?
Can we fire a trigger manually?
What are the magic tables? Do we have “Updated” magic table?
What is the difference between UnionAll and Merge?
Basic difference between stored procedure and user defined function?
See we have a simple query that’s calling a static function, like “Select * from employee where joiningdate < getstaticdate()”? Does it call function for every time or only for matched rows? How you tune this query?
Why should we use CTE?
What is the difference between sub query and correlated query
What is the difference between pessimistic locking and optimistic locking?
What is XACT_ABORT ON?
How to filter nested stored procedure code from profiler?
What happens when a rollback happens in inside a nested stored procedure?
What is a bitmap index?
How to avoid bookmark lookup in execution plan?
When index scan happens?
Does temp tables and table variables both stored in tempdb?
Why cursors are so costly?
Can we call a procedure from a function?
Can we write DML inside a function?
What is the best value for MAXDOP value?
Which is better “Left Outer” Or “NOT EXIST”?
How to find the statistics are outdated?
How to find the query running on a given SPID?
What are the limitations on “SET ROWCOUNT”?
While creating a “Include” columns on which basis we should consider main column and include columns?
How to find the last statistics update date?
An indexed view is referring only one base table. Both view and table are having index defined on them. Which index would be utilized when a query executed against the table.
I have an indexed view, now base table data has been modified, does the modified data reflected in view automatically?
Does “TRUNCATE” DDL or DML command?
I have written a recursive CTE, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 45000 times. How could you be able to handle the situation?
What is index reorganization?
How sql engine knows which index has to be used while dealing with indexed views?
How to prevent bad parameter sniffing? What exactly it means?
What dll file that handle the transaction logs in logshipping?
How to find all dependent objects of a table?
Which is better a CTE or a subquery? Why?
How to rebuild a master database? Ho to restore a master database?
Any alternative to triggers?
Top performance tuning tools
What events need to be added to capture execution plan in sql profiller?
What is the new lock escalation in sql 2008?
What is a filtered index?
What happens when a transaction runs on SQL server? Let’s say simple update statement “Update Table set col1 = value where col2 = value”
What is fragmentation? How it happens?
I have a log file which is of 250 GB. Log is full. We don’t have a disk space on any other drive for creating .ndf, auto growth is ON , and essentially there are no options to allocate new space for the file. What’s your action plan?
How B-Tree formed for Clustered and non clustered indexes?
How B-Tree forms for indexes with included column?
Does alzebrizer tree stores in memory for stored procedures, views and constraints?
How to design TempDB files? And what is the limit?
How VLF’s created for tempDB?
When the checkpoint can happen? What it exactly do?
When the lazywriter happens and what it’ll do?
What is memory grant in SQL Server?
Why resourceDB introduced?
How to move master database?
How to rebuild master database and what are the considerations?
What is a boot page?
What is .TUF file? What is the significance of the same? Any implications if the file is deleted?
What are the different shrink options?
How do you know whether statistics are latest or expired?
How to create linked server
Can you give some examples for One to One, One to Many and Many to Many relationships?
How to capture a trace from production without any impact on performance?
How to capture the long running queries?
You have any idea on Table Partitions?
You have got a request to execute a query which is an “Update” query. That update is updating 5 million rows, after an hour it’s still executing and you are getting lot of requests from report users that their things are getting slow down. What’s your action plan?
See we have a view which is getting data from different tables. One day it’s starts executing infinitely. I have seen no blocking , no bulk operation happened. I have stopped all jobs and maintenance plans on the server. No one is connected to the database but still it’s been taking longer time. What might be the possible reasons?
Can you explain sql server transaction log architecture?
What are the phases of sql server database restore process?
Do you have any idea about sparse column?
We have a procedure which is running fine till today afternoon. Suddenly it started taking long time to execute and there of it leads to a timeout error from application. What might be happening? How you troubleshoot?
What are the critical issues you faced in your career?
How to change the port number for SQL Server? Default port no of SQL SERVER
Difference between ISNULL and COAELSCE
Difference between partition by and patindex
What is the difference between CROSS / OUTER APPLY AND JOINS in T-SQL?
What is stuff function? Difference between stuff and replace?
We have a query which is running fine in development but facing performance issues at production. You got the execution plan from production DBA. Now you need to compare with the development execution plan. What is your approach?
What is lock Escalation
How do you retrieve random 5 rows from a table
What exactly you check in the query execution plan window?
What is the output of len(1234.56)
Where in MS SQL Server is ’100’ equal to ‘0’?
Explain clearly about all the normal forms with proper example
I have a table with millions of rows. I want to retain only last 5% of the rows ? How do u do it ? (Expectation :- transfer last 5% to temp table
Considerations  while designing a DB
Considerations while creating tables and its naming conventions
Explain transaction log in detail
I have a 500 GB Table which is performing slow ? Your plan of action ?
Explain Column Store Index
Explain Isolation Levels ? Default one ?

 

Happy Learning..

SQL Server Query – 6 | Nth Highest Salary

03 Tuesday Aug 2010

Posted by in T SQL Puzzles

≈ 3 Comments

Tags

, , , , , , , , , , , , , , , , , ,


T-SQL Query | [Nth Highest Salary Puzzle] – Write a query which will find the Nth highest salary from the table. In this case we are finding 2nd Highest Salary

Sample Input

Name Salary
e5 45000
e3 30000
e2 49000
e4 36600
e1 58000

Expected Output

Name Salary
e2 49000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create table

CREATE TABLE NthHighest
(
 Name  varchar(5)  NOT NULL,
 Salary  int  NOT NULL
)

--Insert the values
INSERT INTO  NthHighest(Name, Salary)
VALUES 
('e5', 45000),
('e3', 30000),
('e2', 49000),
('e4', 36600),
('e1', 58000)

--Check data
SELECT Name,Salary FROM NthHighest

Update May 14 | Solution


--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------

SELECT * FROM NthHighest N WHERE 1 = (SELECT DISTINCT COUNT(*) FROM NthHighest N1 WHERE N1.Salary > N.Salary )

---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------

SELECT TOP 1 WITH TIES Name , Salary FROM NthHighest N1 
WHERE Salary IN (SELECT TOP 2 WITH TIES Salary FROM NthHighest ORDER BY Salary DESC)
ORDER BY Salary

---------------------------------------
--Sol 3 | Pawan Kumar Khowal
---------------------------------------

SELECT A.Name, B.Salary
FROM (
	SELECT MAX(Salary) Salary
	FROM NthHighest N1 
	WHERE N1.Salary != (SELECT MAX(Salary) FROM NthHighest) ) B
CROSS APPLY (SELECT NAME FROM NthHighest WHERE SALARY = B.SALARY ) A

---------------------------------------
--Sol 4 | Pawan Kumar Khowal
---------------------------------------

;WITH CTE AS
(
	SELECT * , RANK() OVER (ORDER BY SALARY DESC) rnk FROM NthHighest
)
SELECT Name, Salary FROM CTE WHERE rnk = 2


--

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

Keep Learning

http://MSBISkills.com