SQL Server
584 views | +1 today
Follow
Your new post is loading...
Your new post is loading...
Scooped by vijay
Scoop.it!

The Page – How SQL Server Stores Data - sqlity.net

The Page – How SQL Server Stores Data - sqlity.net | SQL Server | Scoop.it
SQL Server organizes all data into virtual containers called pages. The DBCC PAGE command can be used to explore these pages. This post starts a series about pages in SQL Server.
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - As databases volumes become big and big the best way to optimize would be take the big problem

As databases volumes become big and big the best way to optimize would be take the big problem and break it up into smaller chunks. During a large server deployment DBA suggested implementing extensive partitioning for our transaction tables. What are the basics of creating, managing and deploying SQL Server partitioning?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Recently I was contacted by one of my friends operating a large database that he was running out of

Recently I was contacted by one of my friends operating a large database that he was running out of disk space because of some table with many indexes unnecessarily created. Even after truncating the table the space was not given back to the OS. When does SQL Server give the space back once data is deleted?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - As part of performance tuning exercise our developer always suggested to use SET based operations

As part of performance tuning exercise our developer always suggested to use SET based operations rather than using procedural languages. One of the suggestions given for substitute for hierarchical data structure was to use of CTE. What are CTEs inside SQL Server, what are its advantages and how can you rewrite a subquery using CTE?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Any database design can include only the Normal forms

Any database design can include only the Normal forms. But while a DBA performs a product deployment it is critical to take care of certain factors for Index creation. On that lines, explain FILL_FACTOR and PAT_FACTOR inside SQL Server? What are its uses and when would you use the same?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Work of a DBA is never to be underestimated

Work of a DBA is never to be underestimated. An effective DBA’s role maybe understated for the 364 days in a year but for that 1 day when the database gets into a corrupt state is when all the skills come to the fore. Recently while working on a 24x7 large database split into multiple filegroups got into a corrupt state. DBA brought the database ONLINE quickly and was doing a restore in the background. To understand what he would have done, what is Piecemeal Restore inside SQL Server? When was t
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - From SQL Server 2000 the use of XML has been more and more by applications

From SQL Server 200 the use of XML has been more and more by applications. With SQL Server 2005 XML became a first class datatype and applications are likely to use more natively this datatype. What are the Basic 5 methods provided by XML Datatype for access? When would you use one over the other?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - As an effective DBA it is critical to keep tap on the various server side databases

As an effective DBA it is critical to keep tap on the various server side databases. Recently was encountered with a Developer saying the “sys” schema objects were no longer in the MASTER database. Interestingly, what is the use of RESOURCE Database inside SQL Server? Tell us one change in RESOURCE Database inside SQL Server 2008 R2 edition? How can we find the properties of the RESOURCE database like version etc?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Recently got a DBA who said the Log files was growing bigger than the data file size

Recently got a DBA who said the Log files was growing bigger than the data file size. On discussion he mentioned using CHECKPOINT to solve this problem. So what does CHECKPOINT do inside SQL Server? Does the behavior change based on Database recovery models?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Compile and Recompile has very interesting meaning when learning Machine learning

Compile and Recompile has very interesting meaning when learning Machine learning. However, the same thing has similar but very interesting effect in SQL Server. Many related compilation of the code with performance at some degree it is correct and at some degree they have nothing to do with each other. When does your stored Procedures RECOMPILE inside SQL Server?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Developer and DBA both sometime have their own priority and preferences when it is about database

Developer and DBA both sometime have their own priority and preferences when it is about database designs. If you are DBA or Developer you must learn cross domain expertise features. One of the similar features is DACPAC. What is the use of DACPAC?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - I noticed very long stored procedure in the production database

I noticed very long stored procedure in the production database. This database creating temporary tables, using insert and select statement. When I asked the purpose of the stored procedure, I realize they should have just used OUTPUT clause. What is the OUTPUT clause inside SQL Server?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - A SQL developer got surprised when he learned that he does not have to use cursors to calculate rank

A SQL developer got surprised when he learned that he does not have to use cursors to calculate ranks in SQL Server 2008. He can now just use newly introduced ranking functions. What are the different RANKING functions inside SQL Server 2008 R2 and what are the new functions introduced inside SQL Server 2012?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Reporting and analytics cannot be achieved without a very good data model at the background

Reporting and analytics cannot be achieved without a very good data model at the background. And using Analysis services for reporting requirement is always something very common architecture. But getting data from relational engine and keeping data for analysis upto date is the challenge. What is the concept of Slowly Changing Dimension? And what is a Hybrid Slowly Changing Dimension.
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - There are multiple performance troubleshooting tools which a DBA can use inside SQL Server

There are multiple performance troubleshooting tools which a DBA can use inside SQL Server from Perfmon, SQL Profiler and multiple third Party tools. One of the hidden features most forgotten is the Data Collector inside SQL Server. What is a Data Collector? What are the predefined collectors inside SQL Server which can be used?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Using datetime functions inside SQL Server have always been a struggle and there are multiple option

Using datetime functions inside SQL Server have always been a struggle and there are multiple options. There have been interesting usages of GETDATE but interesting functions like SYSDATETIME(), GETUTCDATE() and SYSYTCDATETIME() functions were also introduced. When would you use these functions and what are their differences?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Compliance is one of the non-negotiable in this era

Compliance is one of the non-negotiable in this era. SQL Server has had so many options like C2 Auditing since the SQL Server 2000 days. With the advent of Policy Based Management inside SQL Server 2005 - What are the different aspects involving PBM one needs to know?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Though High-Availability is something critical for all large database deployments

SQL Server General Quiz 2012 - Though High-Availability is something critical for all large database deployments | SQL Server | Scoop.it
Though High-Availability is something critical for all large database deployments. There are multiple options one can use. For discussion today, what is database mirroring option? What are the Database Mirroring enhancements done with SQL Server 2008 R2 edition? How is it different from Log shipping?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Even though storage as a subsystem is cheap these days.We have seen DBA’s struggle to find Log file

Even though storage as a subsystem is cheap these days. We have seen DBA’s struggle to find Log files run out of space and stop transactions. Understanding size is a critical aspect, now tables data, index data, log data and more is becoming a competency of an effective DBA. So, how do you enable and disable an Index? What is the behavior of the same when compared to dropping an Index? How does storage change?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - In any performance testing discussion for large development one of the common requirements

In any performance testing discussion for large development one of the common requirements has been to send a multiple values to SQL Server in one shot. So to help this requirement, SQL Server introduced the MERGE statement. What is the construct of a MERGE statement? What are the restrictions of using the same?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Auditing is always fun when we do code reviews

Auditing is always fun when we do code reviews. And in a product backend check we found a lot of junk names for the Default constraints inside the database. What are the different ways you can create a CONSTRAINT inside SQL Server?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - It is saying that where you should use nail, you should not use sword

SQL Server General Quiz 2012 - It is saying that where you should use nail, you should not use sword | SQL Server | Scoop.it
It is saying that where you should use nail, you should not use sword. Every little tool has its own usage and its own purpose. What are the differences of CHAR, VARCHAR, NVARCHAR and VARCHAR(MAX) datatypes?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Some questions are good for debates

SQL Server General Quiz 2012 - Some questions are good for debates | SQL Server | Scoop.it
Some questions are good for debates. There are so many myths associated with few subjects that when the discussion starts it is hard to convince stubborn friend who is not read to accept the truth. Let me see you attempt both the side of the following question: What is the difference of table variable and temp tables?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - Performance tuning expert often encounter situation where it is difficult to figure out why certain

Performance tuning expert often encounter situation where it is difficult to figure out why certain activities are happening behind the scene in SQL Server Engine. Every action has some root cause and understanding the same is the key to be successful performance tuning expert. Here is one question which often puzzles many - When are the non-Clustered Indexes automatically rebuilt?
more...
No comment yet.
Scooped by vijay
Scoop.it!

SQL Server General Quiz 2012 - A query always ran in split second and suddenly it started to timeout

A query always ran in split second and suddenly it started to timeout. A quick look at the error log the cause was deadlock. What is a Deadlock and how to identify and resolve a Deadlock inside SQL Server 2008 R2?
more...
No comment yet.