Sql Server Interview Questions and Answers (1758) - Page 33

Both are used for the same purpose. They both are used to convert from one data type to another specified data type.
The major differences are:
a) CAST cannot allows you to specify the format of the result which you wants to convert, whereas CONVERT allows it.
b) CAST is a part of SQL-92 specification whereas CONVERT is not the part.
c) CONVERT can be used to format dates as strings, whereas CAST cannot.

Example:

Usage of CAST:

USE Sample

GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice

FROM Production.Product

WHERE CAST(ListPrice AS int) LIKE '3%';

GO

Usage of CONVERT:

USE Sample

GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice

FROM Production.Product

WHERE CAST(int, ListPrice) LIKE '3%';

GO

If a user is connected to the database initially, then a session will be established.
While the user is in session, he can use any of the SQL commands. He can manipulate data in the database, and can define database structures such as tables.
Each SQL session is associated with a user identifier and role name.

Example to get the session ID,type:

SELECT @@SPID

By deleting a specified number of characters from 1st string expression and replacing them with 2nd string expression, a string will be formed.
This SQL Stuff() function is used to return the newly formed string.
Important note is that, for deletion purpose, you have to specify the starting position and the length of the string.

Example:

SELECT STUFF('1234567',2, 3, '999');

It will display the result as: 1999567

If any modifications are done to the row of the table, this BINARY CHECKSUM function identifies them which takes case-sensitivity into account.

Example:

SELECT ProductID, BINARY_CHECKSUM(*) AS 'Binary Checksum'

FROM Products

This CHECKSUM_AGG function will returns a value to evaluate whether the changes are happened. This function will returns the value for a specific column or for the entire table.
The datatype for this function will be either an integer datatype or BINARY_CHECKSUM function result.

Example:

Using CHECKSUM_AGG function with BINARY_CHECKSUM to detect changes in a table:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(UnitsInStock))

FROM Products

 

Output
7913472

By using this PIVOT operator, you can rotate the rows in a table to seperate columns.
The main advantage of this operator is that, it takes a normalized table into consideration and will convert it into a new table in which the values of the columns are derived from the original table values.

Example:

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

The Vendor id, the day of the week they are referring to and what the income on that day was.
So let’s fill it with some data.

insert into DailyIncome values ('SPIKE', 'FRI', 100)

insert into DailyIncome values ('SPIKE', 'MON', 300)

insert into DailyIncome values ('FREDS', 'SUN', 400)

insert into DailyIncome values ('SPIKE', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'TUE', 200)

insert into DailyIncome values ('JOHNS', 'WED', 900)

insert into DailyIncome values ('SPIKE', 'FRI', 100)

insert into DailyIncome values ('JOHNS', 'MON', 300)

insert into DailyIncome values ('SPIKE', 'SUN', 400)

---

insert into DailyIncome values ('SPIKE', 'SAT', 100)

insert into DailyIncome values ('FREDS', 'SAT', 500)

insert into DailyIncome values ('FREDS', 'THU', 800)

insert into DailyIncome values ('JOHNS', 'TUE', 600)

Now, if we select out the flat data that we have, we will get the following:

VendorId   IncomeDay  IncomeAmount

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

SPIKE      FRI        100

SPIKE      MON        300

FREDS      SUN        400

SPIKE      WED        500

SPIKE      TUE        200

...

SPIKE      WED        500

FREDS      THU        800

JOHNS      TUE        600

To find the average for each vendor, run this query:

select * from DailyIncome

pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

Output:

VendorId   MON         TUE         WED         THU         FRI         SAT         SUN

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

FREDS      500         350         500         800         900         500         400

JOHNS      300         600         900         800         300         800         600

SPIKE      600         150         500         300         200         100         400

This IDENT_CURRENT function is used to return a value which is the last identity value inserted in the table.
It takes only one parameter i.e., table name.

Example:

Now here is an example of creating a table with identity column and inserting values in it.

Create table emp_dummy

{

 ID int IDENTITY(5,2),

 Name varchar(20)

}

In the above created table, IDENTITY(5,2) means the identity values will start from 5 and will increment by 2.

Insert emp_dummy values('ABC')

Insert emp_dummy values('XYZ')



Select * from emp_dummy

Output:

ID Name

5  ABC

7  XYZ

Select IDENT_CURRENT('emp_dummy')

Result:

It displays 7 as the result

This SCOPE_IDENTITY function is used to return a value which is the last generated identity in the current scope.
This scope is of different types. This scope can be a stored procedure or a module or a function or a batch.

Example:

INSERT INTO [Northwind].[dbo].[Shippers]([CompanyName],[Phone]) 

VALUES ('Load Runner','(503) 555-9830')



SELECT SCOPE_IDENTITY()

Output
4

Unlike SCOPE_IDENTITY function, this @@IDENTITY function will returns the value which is the last generated identity in the current session.
A session may contain one or more sessions.
An important point to note is that, if at a time, two users using two different connections connected to SQL server inserts two rows with identity column in a table, then each of them will get the value they have just inserted.

Example showing the difference between SCOPE_IDENTITY and @@IDENTITY

Let’s check how Scope_Identity() function got impacted in both the scopes.

CREATE TABLE [dbo].[DimUser]

(

	[userId] int IDENTITY(1,1) ,

	[userName] varchar(100) NULL

)

 

GO



CREATE TABLE [dbo].[DimUser1]

(

	[userId1] int IDENTITY(1,1) ,

	[userName1] varchar(100) NULL

)

GO

Let’s create a SP named “sp_InsertData” as:

CREATE PROCEDURE sp_InsertData

AS

BEGIN

    INSERT  INTO [dbo].[DimUser1]

    VALUES ('xyz'),

    ('pqo'),

    ('abc');

END

[dbo].[DimUser1] is another table created similar to [dbo].[DimUser] stated above.

Now, let’s execute our sample query:

INSERT  INTO [dbo].[DimUser]

VALUES ('Arun'),

('John'),

('Bunty'),

('Stenly'),

('Kumar');

GO

EXEC sp_InsertData

 

SELECT @@IDENTITY AS [@@IDENTITY], 

       SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

Result:

  @@IDENTITY  SCOPE_IDENTITY

  3	      5

This COALESCE function is used to return a value which is the first non-null expression among all its arguments.
If all the arguments are NULL, in that case COALESCE will return NULL.
It is also used to display any other value instead of NULL value in the result.

Example:

SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone) Contact_Phone 

FROM Contact_Info; 

Result:

Name  Contact_Phone 

Jeff   531-2531 

Laura  772-5588 

Peter  594-7477 

This function will let the user know whether he has the effective permission on a securable(Ex:Table).
This function cannot be used to check permissions on linked server.

If the user wants to know whether he has the permission for SELECT on the customer's table, he can use the below query:

select Has_perms_by_name('Customers', 'Objects', 'SELECT')

It will return either 1(true) or 0(false).
To check all the tables in which you have select permission, below query is used:

select Has_perms_by_name 

 (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),

    'OBJECT', 'SELECT') As have_select, name FROM sys.tables

By using both login and user, the database server can do the authentication process.
The authorization process can be scoped to the database.
With this advantage, if your database server is moved to another server, then also you can remap the user-login relationship on the database server, but your database need not to be changed.

This DMV returns the information about the SQL Server machine, available resources and the resource consumption.It also provides information like
a) CPU Count: Number of logical CPUs in the server
b) Hyperthread-ratio: Ratio of logical and physical CPUs
c) Physical_memory_in_bytes: Amount of physical memory available
d) Virtual_memory_in_bytes: Amount of virtual memory available
e) Bpool_commited: Committed physical memory in buffer pool
f) OS_Priority_class: Priority class for SQL Server process
g) Max_workers_thread: Maximum number of workers which can be created

This DMV returns all the hosts registered with SQL Server 2005.It also provides information like
a)Name: Name of the host registered
b)Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]
c)Active_tasks_count: Number active tasks host placed
d)Active_ios_count: I/O requests from host waiting

This DMV helps to identify if there is any CPU bottleneck in the SQL Server machine. The number of runnable tasks is generally a nonzero value that
indicates that tasks have to wait for their time slice to run. If the runnable task counts show high values, then there is a symptom of CPU bottleneck.

SELECT
scheduler_id,current_tasks_count,runnable_tasks_count
FROM sys.dm_os_schedulers

Result

scheduler_id	current_tasks_count	runnable_tasks_count

0	8	0

1	12	0

1048578	1	0

1048576	2	0

1048579	1	0

1048580	1	0

1048581	1	0

1048582	1	0

1048583	1	0

This DMV will return the I/O requests pending in SQL Server side. It provides the below information
a)Io_type: Type of pending I/O request
b)Io_pending: Indicates whether the I/O request is pending or has been completed by Windows
c)Scheduler_address: Scheduler on which this I/O request was issued

This DMV uses RING_BUFFER_RESOURCE_MONITOR and gives information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state.

A Fill Factor is a specification done during the creation of indexes so that a particular amount of space can be left on a leaf level page to decrease the occurrence of page splits when the data has to be accommodated in the future.

When an index is created or rebuilt, the Fill Factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth.

Fill Factor specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild.Fill Factor is always an integer valued from 1 to 100. By setting the Fill Factor value, we specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.Specifying a Fill Factor value of 70 would implies that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table.The empty space is reserved between the index rows rather than at the end of the index.

The Fill Factor option is designed for improving index performance and data storage.

The clauses are logically processed in following order:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY