Saturday, September 15, 2018

Interview Questions and Answers - 2


What is the recovery model? List the types of recovery model available in SQL Server?

Ans. Recovery model basically tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model.
It also tells SQL server that which backup is possible in a particular recovery model selected. There are three types of recovery model:
·         Full
·         Simple
·         Bulk-Logged

What are the types of backups available in SQL Server?

Ans. Different possible backups are:
·         Full backup
·         Differential Backup
·         Transaction Log Backup
·         Copy Only Backup
·         File and File group backup

What is Mirroring? What are the advantages of the Mirroring?

Ans. Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from principal server to secondary server which keeps a secondary server up to date with the principal server.

Advantages of Mirroring are:
·         It is more robust and efficient that Log shipping.
·         It has an automatic failover mechanism.
·         The secondary server is synced with the primary in near real time.

Which TCL Commands are available on the SQL Server?

Ans.  There are 3 TCL Commands in the SQL Server. These are as follows:
Commit: This command is used to save the transaction permanently in the database.
Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
Save Tran: This is used for saving the transaction so as to provide the convenience that the transaction can be rolled back to the point wherever required.

What is the difference between a Local and a Global temporary table?

Ans. If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.


Interview Question and Answers - 1


1. Difference between TRUNCATE and DELETE
                - Truncate is DDL command so can't be rolled back while Delete is a DML                               command so it can.
                - Truncate keeps the lock on table while Delete keeps the lock on each row.
                - Truncate resets the counter of the Identity column while Delete doesn't do so.
                - Truncate does not fire trigger but Delete fires trigger.
                -Truncate removes all rows by de allocating data pages allocated to the table                      while Delete removes rows one by one.
2.  What are the advantages a stored procedure?
                Stored Procedures are pre compiled and stored in the database. This enables the database to    execute the queries much faster. Since many queries can be included in a stored procedure,  round trip time to execute multiple queries from source code to database and back is   avoided.
3.  What are the advantages and disadvantages of views in a database?
   Advantages:
·             Views don't store data in a physical location.
·             The view can be used to hide some of the columns from the table.
·             Views can provide Access Restriction, since data insertion, update and deletion is not    possible with the view.
Disadvantages:
·             When a table is dropped, associated view become irrelevant.
·             Since the view is created when a query requesting data from view is triggered, its a bit slow.
·         When views are created for large tables, it occupies more memory.
4. What  is the ACID property?
·             ACID (Atomicity Consistency Isolation Durability
·             Atomicity is an all-or-none rule for database modifications.
·             Consistency guarantees that a transaction never leaves your database in a half-finished state.
·             Isolation keeps transactions separated from each other until they are finished.
·             Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination and committed transactions will not be lost.
5. What are the different types of triggers?
  There are three types of triggers
·         DML trigger
              There are two kinds of DML triggers
                a. Instead of Trigger
                     Instead of Triggers are fired in place of the triggering action such as an                             insert, update, or delete.
                b. After Trigger
                    After triggers execute following the triggering action, such as an insert,                            update, or delete.
·         DDL trigger
                  This type of trigger is fired against DDL statements like Drop Table, Create                        Table, or Alter Table. DDL Triggers are always after Triggers.
·         Logon trigger
                  This type of trigger is fired against a LOGON event before a user session is                         established to the SQL Server.

Monday, September 10, 2018

How to Find the number of rows,allocated size, rows size, index size and free space in a table


exec sp_spaceused 'AddressType'

name                  rows   reserved     data        index_size unused
AddressType         6        48 KB         8 KB       40 KB 0 KB

It returns number of rows, reserved space for the table, data size, index size and unused space left.

We can use above to find for one table.
When we tried to find all the tables in a database then we can achieve by writing t-SQL script

 --Declare the table variable to store the table names

DECLARE @Tables TABLE (SchemaTable varchar(100));

DECLARE @TableName varchar(100); --To store each table name


-- Insert table names into the table variable
INSERT @Tables (SchemaTable)
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'

--Loop through the table names
WHILE (SELECT COUNT(*) FROM @Tables) > 0
BEGIN
SELECT TOP 1 @TableName = SchemaTable
FROM @Tables
ORDER BY SchemaTable;
EXEC sp_spaceused @TableName;
DELETE @Tables
WHERE SchemaTable = @TableName;
END;

This will give the list of all the tables.

SSRS : How to fit PDF export on one page in reporting services avoid column splitting to multiple pages

In SSRS when exporting all the columns and records to PDF, if it is larger column volumes then it is getting splitted to 2nd page or multiple pages.





To fix this follow the below steps.

1. Go to Report Properties ->Interactive Size 
2. Make sure width and Height is more than the report Body width and height size.
3. change the report Properties-> Margins to .5 in

Run the Report. Now all the columns will fit to the one page.





Sunday, September 9, 2018

Connecting to the Integration Services on the computer failed with following error : Access Denied

Installed SQL Server Integration Services. While tried to login Integration Services getting Access Denied Error. 


It will work fine when tried to login with Management Studio as "Run as a Administrator".

Here are the steps to access:

1. Run ->  dcomcnfg.exe
2. Navigate to Component Services ->Computers -> My Computer -> DCom Config
3. Search Application Name "Microsoft SQL Server Integration Services 11.0"
4. Open Properties:

5. Open Each edit option and add current user.

6. Save your changes.
7. Restart Your Service if required.

Watch the video for the details:




Sunday, September 2, 2018

Date Function : DATEFROMPARTS() and TIMEFROMPARTS()


DATEFROMPARTS()

It returns the date value on specified year, month and day value.
It available in SQL Server 2012.

Syntax:

DATEFROMPARTS ( year, month, day )

Example:

SELECT DATEFROMPARTS(2010,10,30) AS DATEPARTS


OutPut:
DATEPARTS
2010-10-30

TIMEFROMPARTS()

It returns the full time on specified hour, minute and second.
It available in SQL Server 2012

Syntax:

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Example:

SELECT TIMEFROMPARTS(10,23,45,123,3) AS TIMEPARTS


OutPut:

TIMEPARTS

10:23:45.123


SELECT TIMEFROMPARTS(10,23,45,123,4) AS TIMEPARTS


OutPut:

TIMEPARTS


10:23:45.0123

Date Function : DATEDIFF()

This is used to find the difference between two dates based on DatePart type
The DATEDIFF() function returns the time between two dates.

Syntax:
DATEDIFF(DatePart,StartDate,EndDate)

DatePart is Abbreviation used same as in DatePart() function.
StartDate and EndDate are valid date time.

Example:

SELECT DATEDIFF(day,'2015-01-23 10:00:44.470','2015-10-23 10:00:44.470') AS DiffDate

--The Difference between millisecond, nanosecond and microsecond should be less otherwise it will throw below error.

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

SELECT DATEDIFF(millisecond,'2015-10-23 10:00:43.470','2015-10-23 10:00:44.470') AS DiffDate

OutPut:

DatePart
Type
Return Value
year
yy, yyyy
1
quarter
qq, q
7
month
mm, m
21
dayofyear
dy, y
638
day
dd, d
638
week
wk, ww
91
weekday
dw, w
638
hour
hh
15312
minute
mi, n
918720
second
ss, s
55123200
millisecond
ms
100
microsecond
mcs
1000000
nanosecond
ns
1000000000

Monday, August 27, 2018

Sum Comma Separated values in SQL Server

Scenario: Comma Separated values are stored in a single column.
Need to show the output summation of values in single column.

Here is the expected Before/After:


Table structure:

create table SumCommaSeparated
(
  id int identity(1,1),
  value varchar(200)
)

Insert Values into table:

insert into SumCommaSeparated values('20,12,14,34');
insert into SumCommaSeparated values('20,15,14,34,82,90');
insert into SumCommaSeparated values('200');

Create SQL function to sum up and show the output:

Create FUNCTION [dbo].[fnSplit]
(
@list  NVARCHAR(2000)

RETURNS TABLE AS
RETURN

   WITH SplitRecords(LoopStart, LoopEnd) AS (
--Split Column values till End
     SELECT LoopStart = 1,
        LoopEnd = CHARINDEX(',' COLLATE Latin1_General_CI_AS, @list + ',')
     UNION ALL
     SELECT LoopStart = LoopEnd + 1,
            LoopEnd = CHARINDEX(',' COLLATE Latin1_General_CI_AS,@list + ',', LoopEnd + 1)

     FROM   SplitRecords
WHERE  LoopEnd > 0
  )
  SELECT sum(cast(LTRIM(RTRIM(SUBSTRING(@list, SplitRecords.LoopStart,
CASE WHEN SplitRecords.LoopEnd > 0 THEN SplitRecords.LoopEnd - SplitRecords.LoopStart ELSE 0 END))) as bigint)) 
--Cast your output according to requirement
AS VALUE
  FROM   SplitRecords
  WHERE  LoopEnd > 0


SQL Script:

select SCSep.id,SCSep.value BeforeAddition,fn.VALUE as AfterAddition
from SumCommaSeparated SCSep
cross apply [dbo].[fnSplit](SCSep.value) as fn




Tuesday, August 14, 2018

How to find underscore(_) using like operator in SQL Server


Like operator is used to match the specified pattern.
I came across the below scenario where i need to search the underscore using like operator.
Here is the example for title table

select Name from title where Name like 'santosh%'

Name
Santosh
Santosh_Kumar
Santoshkumar

select Name from title where Name like 'santosh_%'

Name
Santosh_Kumar
Santoshkumar

But I need the output only Santosh_Kumar.
So modify the SQL script like below. and output will be as expected.

select Name from title where Name like 'santosh[_]%'

Or use ESCAPE Operator

select Name from title where Name like 'santosh\_%' escape '\'

Name
Santosh_Kumar

Friday, August 10, 2018

Difference between COALESCE and ISNULL function in SQL Server


ISNULL()

Syntax: ISNULL(expression, Replace_value )
--It takes two parameter
--SQL Server Specific
--Return 1st parameter if it is not null.
Example:

SELECT ISNULL(NULL, 'SQLServer');
Result: SQLServer
SELECT ISNULL('Inside', 'Forum');
Result: Inside
SELECT ISNULL(NULL, 10);
Result: 10
SELECT ISNULL(18, 88);
Result: 18
SELECT ISNULL(NULL, '2015-10-20');
Result: '2015-10-20'

COALESCE()

Syntax: COALESCE( expression1, expression2, ... expression_n )

-It takes multiple parameters
-ANSI Standard
-Return 1st parameter if it is Not NULL.

Example:

SELECT COALESCE(NULL, 'SQL Server', 'Inside');
Result: SQL Server
SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4);
Result: 1

Difference:

COALESCE and ISNULL function is used to return the first non-null expression.

Here is the few differences:

1. COALESCE is a ANSI standard where ISNULL is SQL Server Specific
2. COALESCE can accept multiple parameters whereas, ISNULL can accept only two parameters

declare @first as int=NULL
declare @sec as int=1234
declare @third as int=12

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam

3. COALESCE will not support two NULL Parameters while ISNULL will support

select COALESCE(NULL,NULL) as ColParam, ISNULL(NULL,NULL) as IsNUllParam

4. COALESCE will not depend on the length of the datatype, it will return the whole string
ISNULL depends on the length of the first datatype.

declare @first as varchar(3)=NULL
declare @sec as varchar(10)='123456'

declare @third as varchar(4)='5689'

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam, ISNULL(@first,@third) as IsNUllParam1