02 | June | 2015 | Improving my SQL BI Skills

Tags

Advanced SQL tutorial pdf, Can you explain sql server transaction log architecture?, Do you have any idea about sparse column?, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, How to capture the long running queries?, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, Stored Procedures vs Ad-hoc SQL, T-SQL Server Interview Questions, What is a boot page?, What is alzebrizer tree?, What’s the difference between a primary key and a clustered index?, What’s the fastest way to insert thousands of records into the database?, Where do you write business logic – in the application (as Ad-hoc SQL / in line query) or in the database (Stored Procedures)? Why?

SQL SERVER Interview Questions & Answers – SET 2 (40 Questions & Answers) [Page 4]

Download – SQL SERVER Interview Questions with Answers – Set 2 [40 Questions&Answers]

Question31. Can you explain sql server transaction log architecture?

Answer –

Please click on the URL for detailed answer.

https://technet.microsoft.com/en-us/library/jj835093(v=sql.110).aspx

Question32. What is a boot page?

Every database has a single page that stores critical information about the database itself. This page is called Boot Page. It’s always page 9 in file 1 (The first file in the PRIMARY filegroup)

Please click here for detailed explanation – http://www.sqlskills.com/blogs/paul/search-engine-qa-20-boot-pages-and-boot-page-corruption/

You can check out the page using below commands


--

DBCC DBINFO ('Pawan');


--

--
DBCC DBINFO ('Pawan');


DBINFO STRUCTURE:
DBINFO @0x00000000145FDAE0
dbi_version = 706                   dbi_createVersion = 706             dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 15034:5896:37 (0x00003aba:00001708:0025)
dbi_LastLogBackupTime = 2015-01-22 12:17:21.873
dbi_nextseqnum = 1900-01-01 00:00:00.000                                 dbi_status = 0x00010000
dbi_crdate = 2013-04-12 12:15:13.890dbi_dbname = Pawan                  dbi_dbid = 7
dbi_cmptlevel = 110                 dbi_masterfixups = 0                dbi_maxDbTimestamp = 2000
dbi_dbbackupLSN = 15034:5896:37 (0x00003aba:00001708:0025)               dbi_RebuildLogs = 0
dbi_differentialBaseLSN = 15034:5896:37 (0x00003aba:00001708:0025)       dbi_RestoreFlags = 0x0000
dbi_checkptLSN = 15193:4240:119 (0x00003b59:00001090:0077)               dbi_dbccFlags = 2
dbi_COWLastLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_DirtyPageLSN = 15193:4240:119 (0x00003b59:00001090:0077)             dbi_RecoveryFlags = 0x00000000
dbi_lastxact = 0x1efd1e             dbi_collation = 61448               dbi_relstat = 0x61000000
dbi_familyGUID = ced081af-d46a-41b0-911c-d42adc6b04ce                    dbi_maxLogSpaceUsed = 965496832
dbi_recoveryForkNameStack
entry 0
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = ced081af-d46a-41b0-911c-d42adc6b04ce
entry 1
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
dbi_differentialBaseGuid = 555d80f3-3003-419b-a1eb-c1b059f76d22          dbi_firstSysIndexes = 0001:00000014
dbi_oldestBackupXactLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_versionChangeLSN = 0:0:0 (0x00000000:00000000:0000)                  dbi_mdUpgStat = 0x0004
dbi_category = 0x0000000000000000   dbi_safetySequence = 0
dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000
dbi_pageUndoLsn = 0:0:0 (0x00000000:00000000:0000)                       dbi_pageUndoState = 0
dbi_disabledSequence = 0            dbi_dbmRedoLsn = 0:0:0 (0x00000000:00000000:0000)
dbi_dbmOldestXactLsn = 0:0:0 (0x00000000:00000000:0000)                  dbi_CloneCpuCount = 0
dbi_CloneMemorySize = 0             dbi_updSysCatalog = 1900-01-01 00:00:00.000
dbi_LogBackupChainOrigin = 15034:5896:37 (0x00003aba:00001708:0025)
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                          dbi_roleSequence = 0
dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000)                    dbi_localState = 0
dbi_safety = 0                      dbi_modDate = 2013-04-12 12:15:13.890
dbi_verRDB = 184552376              dbi_lazyCommitOption = 0
dbi_svcBrokerGUID = b5fe6d7f-a888-4c3f-af66-3e264784ba17                 dbi_svcBrokerOptions = 0x00000000
dbi_dbmLogZeroOutstanding = 0       dbi_dbmLastGoodRoleSequence = 0     dbi_dbmRedoQueue = 0
dbi_dbmRedoQueueType = 0            dbi_rmidRegistryValueDeleted = 0    dbi_dbmConnectionTimeout = 0
dbi_fragmentId = 0                  dbi_AuIdNext = 1099511628233
dbi_MinSkipLsn = 0:0:0 (0x00000000:00000000:0000)                        dbi_commitTsOfcheckptLSN = 0
dbi_dbEmptyVersionState = 0         dbi_CurrentGeneration = 0
dbi_EncryptionHistory
Scan 0
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
Scan 1
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
Scan 2
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
dbi_latestVersioningUpgradeLSN = 18:81:67 (0x00000012:00000051:0043)     dbi_splitAGE = 0
dbi_PendingRestoreOutcomesId = 00000000-0000-0000-0000-000000000000      dbi_ContianmentState = 0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--


--

DBCC TRACEON(3604);
DBCC PAGE(0,1,9,3);


--

--
DBCC TRACEON(3604);
DBCC PAGE(0,1,9,3);



DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:9)
BUFFER:
BUF @0x00000002FB1C0000
bpage = 0x00000002FA5DE000          bhash = 0x0000000000000000          bpageno = (1:9)
bdbid = 7                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 55356                       bstat = 0x10b
blog = 0x15a9a                      bnext = 0x0000000000000000
PAGE HEADER:
Page @0x00000002FA5DE000
m_pageId = (1:9)                    m_headerVersion = 1                 m_type = 13
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 6590                    m_freeData = 1600
m_reservedCnt = 0                   m_lsn = (15193:4928:13)             m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -1057360342
DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0, Offset 0x60, Length 1504, DumpStyle BYTE
Record Type = PRIMARY_RECORD        Record Attributes =                 Record Size = 1504
Memory Dump @0x00000000145FA060
0000000000000000:   0000e005 c202c202 00000000 00000000 00000000  ..à.Â.Â.............
0000000000000014:   f285ca00 28a40000 00000000 00000000 00000100  ò
Ê.(¤..............
0000000000000028:   755c907b f7efc900 9ea10000 50006100 77006100  u\.{÷ïÉ.¡..P.a.w.a.
000000000000003C:   6e002020 20202020 20202020 20202020 20202020  n.
0000000000000050:   20202020 20202020 20202020 20202020 20202020
0000000000000064:   20202020 20202020 20202020 20202020 20202020
0000000000000078:   20202020 20202020 20202020 20202020 20202020
000000000000008C:   20202020 20202020 20202020 20202020 20202020
00000000000000A0:   20202020 20202020 20202020 20202020 20202020
00000000000000B4:   20202020 20202020 20202020 20202020 20202020
00000000000000C8:   20202020 20202020 20202020 20202020 20202020
00000000000000DC:   20202020 20202020 20202020 20202020 20202020
00000000000000F0:   20202020 20202020 20202020 20202020 20202020
0000000000000104:   20202020 20202020 20202020 20202020 20202020
0000000000000118:   20202020 20202020 20202020 20202020 20202020
000000000000012C:   20202020 20202020 0a000000 07006e00 d0070000          ......n.Ð...
0000000000000140:   00000000 ba3a0000 08170000 25000000 ba3a0000  ....º:......%...º:..
0000000000000154:   08170000 25000000 593b0000 90100000 77000200  ....%...Y;......w...
0000000000000168:   00000000 00000000 00000000 593b0000 90100000  ............Y;......
000000000000017C:   77000000 1efd1e00 00000000 08f00000 00000000  w....ý.......ð......
0000000000000190:   00000061 00000000 af81d0ce 6ad4b041 911cd42a  ...a....¯.ÐÎjÔ°A.Ô*
00000000000001A4:   dc6b04ce 00508c39 00000000 00000000 00000000  Ük.Î.P9............
00000000000001B8:   00000000 00000000 00000000 00000000 af81d0ce  ................¯.ÐÎ
00000000000001CC:   6ad4b041 911cd42a dc6b04ce 00000000 00000000  jÔ°A.Ô*Ük.Î........
00000000000001E0:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001F4:   f3805d55 03309b41 a1ebc1b0 59f76d22 14000000  ó.]U.0A¡ëÁ°Y÷m"....
0000000000000208:   01006302 00000000 00000000 00000000 00000000  ..c.................
000000000000021C:   00000000 00000400 00000000 00000000 00000000  ....................
0000000000000230:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000244:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000258:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000026C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000280:   ba3a0000 08170000 25000000 00000000 00000000  º:......%...........
0000000000000294:   00000000 00000000 00000000 00000000 f7efc900  ................÷ïÉ.
00000000000002A8:   9ea10000 b80b000b 00000000 7f6dfeb5 88a83f4c  ¡..¸........mþµ.¨?L
00000000000002BC:   af663e26 4784ba17 00000000 00000000 00000000  ¯f>&Gº.............
00000000000002D0:   00000000 00000000 00000000 00000000 c9010000  ................É...
00000000000002E4:   00010000 00000000 00000000 00000000 00000000  ....................
00000000000002F8:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000030C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000320:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000334:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000348:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000035C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000370:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000384:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000398:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003AC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003C0:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003D4:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003E8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003FC:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000410:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000424:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000438:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000044C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000460:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000474:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000488:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000049C:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000004B0:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000004C4:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000004D8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000004EC:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000500:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000514:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000528:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000053C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000550:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000564:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000578:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000058C:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000005A0:   00000000 00000000 00000000 12000000 51000000  ................Q...
00000000000005B4:   43000000 00000000 00000000 00000000 00000000  C...................
00000000000005C8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000005DC:   00000000                                      ....
DBINFO @0x00000000145FA060
dbi_version = 706                   dbi_createVersion = 706             dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 15034:5896:37 (0x00003aba:00001708:0025)
dbi_LastLogBackupTime = 2015-01-22 12:17:21.873
dbi_nextseqnum = 1900-01-01 00:00:00.000                                 dbi_status = 0x00010000
dbi_crdate = 2013-04-12 12:15:13.890dbi_dbname = Pawan                  dbi_dbid = 7
dbi_cmptlevel = 110                 dbi_masterfixups = 0                dbi_maxDbTimestamp = 2000
dbi_dbbackupLSN = 15034:5896:37 (0x00003aba:00001708:0025)               dbi_RebuildLogs = 0
dbi_differentialBaseLSN = 15034:5896:37 (0x00003aba:00001708:0025)       dbi_RestoreFlags = 0x0000
dbi_checkptLSN = 15193:4240:119 (0x00003b59:00001090:0077)               dbi_dbccFlags = 2
dbi_COWLastLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_DirtyPageLSN = 15193:4240:119 (0x00003b59:00001090:0077)             dbi_RecoveryFlags = 0x00000000
dbi_lastxact = 0x1efd1e             dbi_collation = 61448               dbi_relstat = 0x61000000
dbi_familyGUID = ced081af-d46a-41b0-911c-d42adc6b04ce                    dbi_maxLogSpaceUsed = 965496832
dbi_recoveryForkNameStack
entry 0
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = ced081af-d46a-41b0-911c-d42adc6b04ce
entry 1
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
dbi_differentialBaseGuid = 555d80f3-3003-419b-a1eb-c1b059f76d22          dbi_firstSysIndexes = 0001:00000014
dbi_oldestBackupXactLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_versionChangeLSN = 0:0:0 (0x00000000:00000000:0000)                  dbi_mdUpgStat = 0x0004
dbi_category = 0x0000000000000000   dbi_safetySequence = 0
dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000
dbi_pageUndoLsn = 0:0:0 (0x00000000:00000000:0000)                       dbi_pageUndoState = 0
dbi_disabledSequence = 0            dbi_dbmRedoLsn = 0:0:0 (0x00000000:00000000:0000)
dbi_dbmOldestXactLsn = 0:0:0 (0x00000000:00000000:0000)                  dbi_CloneCpuCount = 0
dbi_CloneMemorySize = 0             dbi_updSysCatalog = 1900-01-01 00:00:00.000
dbi_LogBackupChainOrigin = 15034:5896:37 (0x00003aba:00001708:0025)
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                          dbi_roleSequence = 0
dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000)                    dbi_localState = 0
dbi_safety = 0                      dbi_modDate = 2013-04-12 12:15:13.890
dbi_verRDB = 184552376              dbi_lazyCommitOption = 0
dbi_svcBrokerGUID = b5fe6d7f-a888-4c3f-af66-3e264784ba17                 dbi_svcBrokerOptions = 0x00000000
dbi_dbmLogZeroOutstanding = 0       dbi_dbmLastGoodRoleSequence = 0     dbi_dbmRedoQueue = 0
dbi_dbmRedoQueueType = 0            dbi_rmidRegistryValueDeleted = 0    dbi_dbmConnectionTimeout = 0
dbi_fragmentId = 0                  dbi_AuIdNext = 1099511628233
dbi_MinSkipLsn = 0:0:0 (0x00000000:00000000:0000)                        dbi_commitTsOfcheckptLSN = 0
dbi_dbEmptyVersionState = 0         dbi_CurrentGeneration = 0
dbi_EncryptionHistory
Scan 0
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
Scan 1
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
Scan 2
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
dbi_latestVersioningUpgradeLSN = 18:81:67 (0x00000012:00000051:0043)     dbi_splitAGE = 0
dbi_PendingRestoreOutcomesId = 00000000-0000-0000-0000-000000000000      dbi_ContianmentState = 0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--

Question33. Where do you write business logic – in the application (as Ad-hoc SQL / in line query) or in the database (Stored Procedures)? Why?

Answer –

Mostly I used to write stored procedures because they are easier for us to test and fine tune. If you want to change the stored procedure in future it is easy. You can just change it and deploy on the server required and test the application. You don’t need to open the application, change the query and deploy it and after that you can test it. You can save lot of time in this case.

Stored Procedures don’t provide much advantage in security cases, unless restricting access to rows in complex manner. They are better to manage and change in future. Sps are better for complex operations.

So, which one is better to use SPs or ad-hoc SQL? The answer is “it depends.”

For details please visit –

https://www.simple-talk.com/sql/t-sql-programming/to-sp-or-not-to-sp-in-sql-server/

Question34. What’s the fastest way to insert thousands of records into the database?

Answer-

Use BULK INSERT – it is designed for exactly for huge insertions and significantly increases the speed of inserts.
You can also use Batch inserts. That is, only send 1000 rows at a time, rather than one row at a time, so you hugely reduce round trips/server calls.
You can also use SQL BCP utility.

Question35. What’s the difference between a primary key and a clustered index?

Answer –

Sr. No	Clustered Index	Primary Key
1	Clustered index will create only Index on the table. It will not create constraint on the table.	Primary key internally creates 2 objects. They are Index and Primary Key constraint. Both Index and Primary Key constraint can be clustered or non-clustered depending on what you have written in primary key definition. If you don’t specify anything then Unique clustered index will be created and a Primary key constraint (clustered) will be created.
2	Here if you create non unique clustered index you can insert multiple null values. If you create unique clustered index you can insert single null value.	We cannot insert null values if we have Primary key on the table. You will get below error if you try. Msg 515, Level 16, State 2, Line 6 Cannot insert the value NULL into column ‘ID’, table ‘Pawan.dbo.testPrimClus1’; column does not allow nulls. INSERT fails. The statement has been terminated.
3	We can also add Clustered index after table creation using create index command. We don’t need to alter table in this case. E.g. --- CREATE TABLE testPrimClus4 ( ID INT ) GO CREATE UNIQUE CLUSTERED INDEX Ix_Clx ON testPrimClus4(ID) ----	We can add primary key after that creating table using below alter command. Please note that we need to first drop existing constraints. Also primary key column should be non-null. E.g. -- CREATE TABLE testPrimClus113 ( ID INT NOT NULL ) GO ALTER TABLE testPrimClus113 ADD PRIMARY KEY (ID) --

Question36. What is alzebrizer tree?

The algebrizer is used resolves all the names of the various objects, tables and columns, referred to within the query string. The algebrizer identifies, at the individual column level, all the data types (varchar (50)) versus datetime and so on) for the objects being accessed. It also determines the location of aggregates (such as GROUP BY, and MAX) within the query, a process called aggregate binding.

This algebrizer process is important because the query may have aliases or synonyms, names that don’t exist in the database, that need to be resolved, or the query may refer to objects not in the database. When objects don’t exist in the database, SQL Server returns an error from this step, defining the invalid object name.

The algebrizer outputs a binary called the query processor tree, which is then passed on to the query optimizer. The algebrizer’s output includes a hash, a coded value representing the query. The optimizer uses the hash to determine whether there is already a plan generated and stored in the plan cache. If there is a plan there, the process stops here and that plan is used. This reduces all the overhead required by the query optimizer to generate a new plan

For details please visit Grant Fritchey’s book online.

Question37. How to capture the long running queries?

Answer –

--

SELECT TOP 10
	r.session_id
,	r.start_time
,	TotalElapsedTime_ms = r.total_elapsed_time
,	r.[status]
,	r.command
,	DatabaseName = DB_Name(r.database_id)
,	r.wait_type
,	r.last_wait_type
,	r.wait_resource
,	r.cpu_time
,	r.reads
,	r.writes
,	r.logical_reads
,	t.[text] AS [executing batch]
,	SUBSTRING(
				t.[text], r.statement_start_offset / 2, 
				(	CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) 
						 ELSE r.statement_end_offset 
					END - r.statement_start_offset ) / 2 
			 ) AS [executing statement] 
,	p.query_plan
FROM
	sys.dm_exec_requests r
CROSS APPLY
	sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY	
	sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY 
	r.total_elapsed_time DESC;


--

Question38. Do you have any idea about sparse column?

Answer –

Sparse columns are normal columns. They have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.

Example


--


CREATE TABLE Sparses
(
	 ID INT 
	,NAME VARCHAR(100) SPARSE
)
GO


--

For details please refer – https://msdn.microsoft.com/en-IN/library/cc280604.aspx

Question40. What happens when a transaction runs on SQL server? Let’s say simple update statement “Update Table set col1 = value where col2 = value” ?

Answer –

Update Lock is used in SQL Server when performing an UPDATE statement. When you check the execution plan, you can see that such a plan always consists of 3 parts:

Reading data
Get New Value
Write data

UpdateLock

When SQL Server initially reads the data to be changed in the first part of the query plan, Update Locks are acquired on the individual records. And finally these Update Locks are converted to Exclusive (X) Locks when the data is changed in the third part of the query plan. UPDATE Locks are required to avoid deadlock situations in UPDATE query plans.

All the best

MSBISkills.com

Pawan Kumar Khowal

Share this