Find out how many users are connected to our SSMS database using SQL Server

 Here we will discuss to find out how many users are connected to our SSMS database using SQL Server.

Below is the query to find out the number of users is a connection to the SQL server.

This query works above SQL server 2005 version

SELECT conn.client_net_address, 
       SE.host_name, 
       SE.login_name, 
       ST.text
FROM   sys.dm_exec_sessions SE
	   INNER JOIN sys.dm_exec_connections conn
           ON SE.session_id = conn.session_id
CROSS APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) ST
WHERE  SE.program_name LIKE 'Microsoft SQL Server Management Studio%'
ORDER BY SE.program_name, 
         conn.client_net_address;

----------------------------------------------------

Differences Among CTE, Derived Table, Temp Table, Sub Query And Temp Variable

Sometimes we're required to store the results of queries temporarily for further use in future queries. SQL Server provides CTE, Derived table, Temp table, subqueries and Temp variables for this. All of these can be used to store the data for a temporary time. We will learn the differences among all these temporary storage mechanisms and also in what type of scenario or condition which mechanism will be used.

Today I will explain all of these temporary storage mechanisms and also explain the conditions and queries where they can be used.

CTE:
The CTE is an abbreviation of “Common Table Expression.” CTE was introduced in SQL Server. It works as a temporary result set that is defined within the execution scope of a single select, insert, update, delete statements. CTE is typically the result of complex sub queries. Similar to temporary tables CTE doesn’t store as an object; the scope is limited to the current query. CTE improves readability and ease in maintenance of complex queries and sub-queries.

Example:

1. WITH CTE(Emp_ID, Emp_Name, Project_Name)  
2. AS  
3.     (  
4.         SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e INNERJOIN dbo.Project p ON e.Emp_Id = p.Project_Id  
5.     )  
6. SELECT * FROM CTE  

Output:


CTE with recursion query:

1. DECLARE @Min int;  
2. DECLARE @Max int;  
3. SET @Max = 10;  
4. SET @Min = 1;  
5.   
6. WITH Sequence_ AS  
7.   
8. (  
9.     SELECT @Min ASnum UNIONALL SELECT num + 1 FROM Sequence_ WHERE num + 1 <= @Max  
10. )  
11. SELECTnumFROM Sequence_  

Output:


In the above example we used the recursive CTE and generated the sequence between 1 and 10.

When to use CTE

• Use for recursive query.
• CTE is easy to implement compared to complex queries which involves several sub-queries.

Temp Variables:
Temp Variables are also used for holding the data fora  temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.

Example:

1. Declare Temp variable:  
2. Declare @My_var2TABLE  
3.     (  
4.         IIDint,  
5.         NameNvarchar(50),  
6.         SalaryInt,  
7.         City_NameNvarchar(50)  
8.     )  

Insert result set of another query into temp variable,

1. Insert Into @My_var2   
2. Select * from Employee  
3. WHERE IID<8 AND Salary>20000  

Retrieve data from temp variable: 

1. Select*from@My_var2  

Output:


Limitations Of Temp Variables

1. Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter.
   
2. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.
   
3. We cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table. It can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. Indexes cannot be added after the table has been created.

When to use Temp Variables:

• When you are required to use the current result set in next queries then store result into temp variables.
  
• When your data set is smaller, approximately less than 1000 records, then use temp variables , if your result set is larger then use temp table instead of temp variables.

Temp Table:
Temporary tables are tables that are available only to the session that created them. 

These tables are automatically destroyed at the termination of the procedure or session that created them. SQL Server provides the concept of temporary table which helps the developers in a great way. These tables can be created at runtime and can do all kinds of operations that one normal table can do. In SQL Server all temporary tables are present in tempdbdatabase.

Types of Temporary Tables:
SQL Server contain two types of Temporary tables:

1. Local Temporary Tables
2. Global Temporary Tables

Local Temporary Tables:
Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables start with(“#”) hash sign.

Example:

1. Create local temp table.  
2. CREATE TABLE# TEMP_SAVE  
3.     (  
4.         Emp_IdINT,  
5.         Emp_NameVARCHAR(30),  
6.         Project_NameVARCHAR(30)  
7.     )  
8.   
9. Insert data into temp table.  
10.   
11. INSERT INTO # TEMP_SAVE  
12. SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e  
13. INNERJOIN  
14. dbo.Project p  
15. ON  
16. e.Emp_Id = p.Project_Id  

Select data from temp table.



Global Temporary Tables:
Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table, then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of Global temp tables start with (“##”) double hash sign.

Example:

Create a Global temp table 

1. Create Table## TEmp  
2.     (  
3.         IIDintidentity(1, 1),  
4.         Namenvarchar(50),  
5.         Salaryint  
6.     )  

Insert Data into Global Temp table

1. Insert Into##TEmp  
2. Select 'Pankaj', 25000 UnionAll  
3. Select 'Rahul', 24000 UnionAll  
4. Select 'Sanjeev', 23000 UnionAll  
5. Select 'Sandeep', 22000 UnionAll  
6. Select 'Naru', 27000   

Fetch Data From Global temp Table



When to Use Temporary Tables

1. We required to hold data from further query.
2. When we have a complex Join operation.
3. In some cases it may be a replacement of cursors.

Derived Table:
Derived tables are the tables which are created on the fly with the help of the Select statement. Derived table expression appears in the FROM clause of a query. In derived table server create and populate the table in the memory, we can directly use it and we also don’t require to drop the table. But scope of derived table is limited to the outer Select query who created it. Derived table can’t be used further outside the scope of outer select query.

Example:

1. SELECT * FROM  
2. (SELECT e.Emp_Id,e.EmployeeName,p.Project_Name FROM dbo.Employee e  
3. INNERJOIN  
4. dbo.Project p  
5. ON  
6. e.Emp_Id=p.Project_Id)Tab  
7. WHERE Tab.Emp_Id%2=1  

Output:


Example:


Consider the above table, now we have the sum the salary of employees for each group of age.

1. SELECT SUM(Tab.Salary)AS Total_Salary,Tab.Age  
2. FROM  
3. (SELECT e.Salary,e.AgeFROMdbo.Emp e  
4. WHERE e.Age>20)Tab  
5. GROUPBY Tab.Age  

Output:


When to Use:

• When you are required to perform some complex query and result of inner query work as table for outer query.

Subquery: 
A subquery is a SELECT statement that is nested within another statement. Subquery is also known as nested query. Result of subquery is used in the main query as a condition to further restrict the data to be retrieved. The subquery used in select, update, delete and insert command.

Example:

1. SELECT * FROM dbo.Emp e  
2. WHERE e.Id IN(SELECT e2.Emp_Id fromdbo.Employee e2);  

Output:


Example:

1. SELECT * FROM dbo.Emp e  
2. WHERE e.Id>(SELECT max(e2.Emp_Id) from dbo.Employee e2);  

Output:


When to use:

• When you require that inner query or condition must be fulfilled before generating any result.
• Use subquery when the result that you want requires more than one query and each subquery provides a subset of the table involved in the query.
• If the query requires a NOT EXISTS condition, then you must use a subquery because NOT EXISTS operator only work with subquery.

After the basic introduction of temp table, temp variable, subquery, derived table and CTE now we read the difference between all these.

Difference between subquery and derived table:

Subquery	Derived
Subqueries must be enclosed within parentheses.	Derived table must be enclosed within parentheses and table name must be provided.
Subquery can have only one column.	Derived table can have one or more column.
Subquery mainly use in where clause.	Derived table used in from clause.

Difference b/w Temp table and Temp variable:

Temp Table	TempVariable
Scope of Temp Table is wider the temp variables of. Local temporary tables are temporary tables that are available only to the session that created them and Global temporary tables are temporary tables that are available to all sessions and all users.	Scope of the Temp variables are limited up to current batch and current stored procedure.
Temp tables can be create using Create Table and Select Into commands	Temp variables only through Declare command can’t be created using select into command.
Temp tables can be drop through Drop Command.	We can not drop a Temp variables but we can use truncate command for temp variables.
Name of Temp table can have maximum 116 characters.	Name of a temp variables can have MAXIMUM 128 characters.
Temp table support foreign key concept.	Temp variable doesn’t support foreign key.
Temp tables support transaction management.	Temp variables doesn’t support transaction management. Rollback not work for temp variables.
Temp tables can easily handle large amount of data.	Temp variables are suitable with small amount of data.

Difference b/w CTE and Derived table:

CTE	Derived Table
A CTE can be referenced multiple times in the same query. So CTE can use in recursive query.	Derived table can’t referenced multiple times. Derived table can’t use in recursive queries.
CTE are better structured compare to Derived table.	Derived table’s structure is not good as CTE.

General Questions of SQL SERVER

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What are the properties of the Relational tables?

Relational tables have six properties:

· Values are atomic.

· Column values are of the same kind.

· Each row is unique.

· The sequence of columns is insignificant.

· The sequence of rows is insignificant.

· Each column must have a unique name.

What is Normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by Adding redundant data. It is sometimes necessary because current DBMSs implement the Relational model poorly. A true relational DBMS would allow for a fully normalized database At the logical level, while providing physical storage of data that is tuned for high Performance. De-normalization is a technique to move from higher to lower normal forms of database modelling in order to speed up database access.

What are different normalization forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out Into distinct tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related Many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model Notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies is said to be in DKNF. Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it Must first fulfil all the criteria of a 2NF and 1NF database.

What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database. e.g.  sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to Maintain the referential integrity of data by changing the data in a systematic fashion. A Trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a Data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as Triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure While triggers are implicitly executed. In addition, triggers can also execute stored Procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so When the trigger is fired because of data modification it can also cause another data Modification, thereby firing another trigger. A trigger that contains data modification logic Within itself is called a nested trigger.

What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as Well as updating or deleting rows. Rows updated or deleted in the view are updated or Deleted in the table the view was created with. It should also be noted that as data in the Original table changes, so does data in the view, as views are the way to look at part of the Original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an Existing table to locate rows more quickly and efficiently. It is possible to create an index on One or more columns of a table, and each index is given a name. The users cannot see the Indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, Scans have a terrific impact on performance.

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, Joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Cursor?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. In order to work with a cursor we need to perform some steps in the following order:

· Declare cursor

· Open cursor

· Fetch row from the cursor

· Process fetched row

· Close cursor

· Deallocate cursor

What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width

What is Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

What is sub-query? Explain properties of sub-query?

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

What are different Types of Join?

Cross Join 

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join

A join that displays only the rows that have a match in both joined tables is known as inner Join.  This is the default type of join in the Query and View Designer.

Outer Join

A join that includes rows even if they do not have related rows in the joined table is an Outer Join.  You can create three different outer join to specify the unmatched rows to be included:

· Left Outer Join: In Left Outer Join all rows in the first-named table i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.

· Right Outer Join: In Right Outer Join all rows in the second-named table i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

· Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

Self Join

This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

What are primary keys and foreign keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is User Defined Functions? What kind of User-Defined Functions can be created?

User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

Different Kinds of User-Defined Functions created are: 

Scalar User-Defined Function

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and Timestamp data types are not supported. These are the type of user-defined functions that Most developers are used to in other programming languages. You pass in 0 to many Parameters and you get a return value.

Inline Table-Value User-Defined Function

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

What is Identity?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

What is DataWarehousing?

· Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;

· Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;

· Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.

· Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.