RSS

Looking Deep Into Storage Structure For SQL Server.

This article is looking deep into storage structures starting with the Heap table. This is something to become familiar with as part of the basics of SQL Server internals.
Prerequisite Terms
The following are terms to be familiar with before looking deep into storage structures.
1. Page: is 8kb in size. A page is divided into 3 parts, the Header, Records and an Offset Array. The Header is 96 bytes and contains meta information about a page, like page number, owning object and type of page. At the end of the page is an Offset Array of 36 bytes providing pointers to a location. Between these two areas are 8060 bytes where records are stored.
Header -> 96 bytes
Records -> 8090 bytes
Offset Array -> 36 bytes

2. Extent: contains 8 pages or 64 KB in size. is a collection of 8 pages. The group called is called an extent. It can be of 2 types, either mixed or Uniform Extent. A Mixed Extent is pages allocated to multiple objects. For example, if a table has less than eight pages then they are put into a Mixed Extent. All ages of Uniform Extents are allocated to a single object. We can say they are a contiguous allocation of pages.
3. Page Free Space (PFS): It keeps the information regarding free space in pages. There will be a PFS for every 8000 pages.
4. Global Allocation Map (GAM): It’s the third page in each data file. 1 GAM covers up to 4GB of data.
5. Shared Global Allocation Map (SGAM): It’s the fourth page in each data file. It keeps information related to Extents, whether it is Mixed or not. 1 SGAM covers up to 4 GB of data.
Let’s run the following code:
CREATE DATABASE IndexInternalsDeep
GO

USE IndexInternalsDeep
GO

CREATE TABLE HeapTable
(
id INT IDENTITY(1,1),
vchName VARCHAR(8000),
)

INSERT INTO HeapTable VALUES(‘Vikrant More’)
INSERT INTO HeapTable VALUES(‘Suraj Sheikh’)

SELECT * FROM HeapTable
— Output from the above table like this

id vchName
1 Vikrant More
2 Suraj Sheikh

—- LOOKING FOR EXTENT INFORMATION.
DBCC EXTENTINFO(‘IndexInternalsDeep’,’HeapTable’)
GO

Description of columns in EXTENTINFO:
file_id and page_id: represents a file number where the pages reside and a page number within the file where the data lives.
pg_alloc: Number of pages allocated from the extent to the object.
ext_size: Size of the extents
object_id: Object id for the table.
index_id: Provides the id of the index; 0 for the heap, 1 for the clustered index and non-clustered ids >= 2.
partition_number: Partition Number for the heap or index.
iam_chain_type: Type of data stored (in row data, row overflow and so on)
pfs_bytes: byte array that identifies the number of free space.
— LOOKING FOR INDEX INFORMATION
DBCC IND(‘IndexInternalsDeep’,’HeapTable’,-1)
GO

Description of columns in IND:
• PageFID and PagePID: represents a file number where pages reside and a page number within the file where data lives.
• IAMFID: Fileid of the file containing the page (refer to sysfiles).
• ObjectID: Objectid of the table used.
• IndexID: Provides the id of the index. 0 for heap, 1 for clustered index, non-clustered ids >= 2.
• PartitionNumber: it’s for the heap or index.
• PartitionID: it’s for the heap or index.
• iam_chain_type: Type of data stored (in row data, row overflow and so on).
• PageType: 1 refers to Data page, 2 -> Index page, 3 and 4 -> text pages.
• IndexLevel: 0 refers to leaf. The highest value refers to the root of an index.
• NextPageFID and PrevPageFID: refers to the next and previous file numbers.
• NextPagePID and PrevPagePID: refers to the next and previous page numbers.
Script to check space allocation details using the following query.
SELECT     obj.name
            ,obj.object_id
            ,p.index_id
            ,AU.total_pages
            ,AU.used_pages
            ,AU.data_pages
            ,first_iam_page
            ,first_page
            ,root_page
    FROM
    sys.objects obj
    INNER JOIN sys.partitions p on obj.object_id = p.object_id
    INNER JOIN sys.allocation_units AU on au.container_id = p.hobt_id
    INNER JOIN sys.system_internals_allocation_units SIAU on SIAU.container_id =au.container_id
    WHERE obj.object_id = object_id(‘Heaptable’)

identifying First Page ID based on First_page
First_page value = 0x420100000100.
A set of 2 Hexadecimal numbers represent a byte.
Read the following number from right to left (excluding the “0x” prefix):
0x 42 01 00 00 01 00
After reading it from right to left it should be:
0x 00 01 00 00 01 42
The preceding hexadecimal number is divided into 2 groups, the first group is 2 bytes and the second group is 4 bytes.

The first group, 00 01, represents the file group number.
second group, 00 00 01 42, represents the page number.
decimal equivalent of the first group, in other words 00 01, is 1 so the File Group ID = 1.
Now let’s convert 00 00 01 42 into the decimal equivalent,
following is the way to convert the hexadecimal number into decimal equivalent:

=> 1*16^2 +4*16^1 +2 * 16^0
=> 1 * 256 + 4 *16 + 2 * 1 (anything raise to 0 = 1)
=> 256 + 64 + 2
=> 322 — PagePID

 
Leave a comment

Posted by on February 10, 2015 in Microsoft SQL SERVER

 

Moving, Adding or Removing TempDB Files.

Few days ago i was working on tempDB contentions and how to resolve it. Here in this article i am not going to explain in deep about it but going to help you to resolve error that encounter during adding and deleting multiple tempDB data files.

To check the how much tempDB files are there, run the below query in SQL Server Management Studio.

USE tempDB
GO
EXEC SP_HELPFILE;

This will gives you details about (data files, log files and ndf files).

To Move Temp file location use below query,

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘d:\tempdb\tempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘d:\tempdb\templog.ldf’);
GO

To add data file in tempDB use following query,

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev2, FILENAME = ‘W:\tempdb2.mdf’, SIZE = 256MB,FILEGROWTH = 5MB);

And run

USE tempDB

GO

EXEC SP_HELPFILE;

You will see 3 files. This way you can add as many as tempDB data files.

Now to remove tempDB data files use the following query,

Restart SQL Server Instances and then execute the following query.

USE tempdb;

GO

DBCC SHRINKFILE(‘tempdev2’, EMPTYFILE)

GO

USE MASTER;

GO

ALTER DATABASE tempdb

REMOVE FILE tempdev2;

This will shrink the tempdb2.mdf data file and remove it.

If still the error exists then modify tempdev2 file by minimize its size to 1 MB.

To modify temp db size run following query,

ALTER DATABASE [tempdb] MODIFY FILE (

NAME = N’tempdev2′,

SIZE = 1024KB );

and now once again do Restart SQL Server Instances and then execute the following query.

USE tempdb;

GO

DBCC SHRINKFILE(‘tempdev2’, EMPTYFILE)

GO

USE MASTER;

GO

ALTER DATABASE tempdb

REMOVE FILE tempdev2;

This will definitely remove the tempdev2 data file.

 
1 Comment

Posted by on February 6, 2014 in Microsoft SQL SERVER

 

Top in SQL Server with Select,Insert,Update and Delete Statements

Top is really a nice and important in SQL (SQL SERVER 2008).

We can use this top with following queries

1] Select

2] Insert

3] Delete

4] Update

Basically most of us know about top we can use with select query to get the top element from the query.
But this top we can use in then Insert, Delete and Updating the table as well.
We will see how we can use top with Insert, Delete and Update.
TO show practically using the following structure for table

CREATE TABLE #ALL_Student

(

ID INT IDENTITY(1,1)

,SFName varchar(15)

,SLName varchar(15)

,[Subject] varchar(20)

,Marks float

)

And same structure for #Student
Terms: – SFName = Student First Name
SLName = Student Last Name

We have a data in #All_Student table and we are picking up top data from it and inserting it into #Student.

#All_Student table data,

ID SFName SLName Subject Marks
1 Vikrant More Maths 70
2 Vikrant More English 80
3 Vikrant More Sanskrit 99
4 Anurodh Kaswate Maths 60
5 Anurodh Kaswate English 96
6 Anurodh Kaswate Sanskrit 100
7 Suraj Sheikh Maths 55
8 Suraj Sheikh English 100
9 Suraj Sheikh Sanskrit 100
10 Reshu Jain Maths 40
11 Reshu Jain English 97
12 Reshu Jain Sanskrit 91

1] Top with select statement:-

Syntax: –              SELECT TOP 10 * FROM <TNAME>

2] Top with Insert Statement:-

It is when we have to insert only top few records then we can use this,

Syntax: –              Insert top (10) into <TName> (column1, column2…..)

Select column1, column2….. From <TName1>

or

Insert into <TName> (column1, column2…..)

Select TOP (10) column1, column2….. From <TName1>

Example:-

— 2] TOP WITH INSERT STATEMENTS….

— I WANTE TO INSERT TOP 2 STUDENT FROM SUBJECT ENGLISH WHO SCORES MORE

INSERT TOP (2) INTO #STUDENT

SELECT SFName,SLName,Subject,Marks

FROM #ALL_Student

WHERE Subject = ‘ENGLISH’

ORDER BY Marks DESC

3] Top with Delete Statement:-    

Deleting top records from table

Syntax: –              Delete top (10) from <TName> where <Cond>

Example:-

–3] TOP WITH DELETE STATEMENT…

— I WANT TO DELETE TOP 1 STUDENT FROM #STUDENT TABLE HAVING SUBJECT ENGLISH.

DELETE TOP (1) FROM #STUDENT WHERE Subject =’ENGLISH’

4] Top with Update statement:-

Updating top records from table

Syntax: –              Update top (10) <TName> set Colmn1=<value>

Example: –

–4] TOP WITH UPDATE STATEMENT….

— I WANT TO UPDATE TOP 1 STUDENT FROM #ALL_STUDENT TABLE AND CHANGES HIS SUBJECT FROM SANSKRIT TO HINDI

UPDATE TOP (1) #ALL_Student SET Subject=’Hindi’,Marks = NULL WHERE Subject=’SANSKRIT’

NOTE: – There is no any business logic related to the last query I just wanted to show how we can use top (1) with update query. Also there is no such concept in subject and can’t change Sanskrit to Hindi in real life example.

Demo of above concept:-

 

DROP TABLE #ALL_Student
CREATE TABLE #ALL_Student
(
ID INT IDENTITY(1,1)
,SFName varchar(15)
,SLName varchar(15)
,[Subject] varchar(20)
,Marks float
)

INSERT INTO #ALL_Student VALUES (‘Vikrant’,’More’,’Maths’,’70’),(‘Vikrant’,’More’,’English’,’80’),(‘Vikrant’,’More’,’Sanskrit’,’99’)
INSERT INTO #ALL_Student VALUES (‘Anurodh’,’Kaswate’,’Maths’,’60’),(‘Anurodh’,’Kaswate’,’English’,’96’),(‘Anurodh’,’Kaswate’,’Sanskrit’,’100′)
INSERT INTO #ALL_Student VALUES (‘Suraj’,’Sheikh’,’Maths’,’55’),(‘Suraj’,’Sheikh’,’English’,’100′),(‘Suraj’,’Sheikh’,’Sanskrit’,’100′)
INSERT INTO #ALL_Student VALUES (‘Reshu’,’Jain’,’Maths’,’40’),(‘Reshu’,’Jain’,’English’,’97’),(‘Reshu’,’Jain’,’Sanskrit’,’91’)

SELECT * FROM #ALL_Student

drop table #student
CREATE TABLE #STUDENT
(
ID INT IDENTITY(1,1)
,SFName varchar(15)
,SLName varchar(15)
,[Subject] varchar(20)
,Marks float
)

— 2] TOP WITH INSERT STATEMENTS….

— I WANTE TO INSERT TOP 2 STUDENT FROM SUBJECT ENGLISH WHO SCORES MORE

INSERT TOP (2) INTO #STUDENT
SELECT SFName,SLName,Subject,Marks
FROM #ALL_Student
WHERE Subject = ‘ENGLISH’
ORDER BY Marks DESC

SELECT * FROM #STUDENT

–3] TOP WITH DELETE STATEMENT…

— I WANT TO DELETE TOP 1 STUDENT FROM #STUDENT TABLE HAVING SUBJECT ENGLISH.

DELETE TOP (1) FROM #STUDENT WHERE Subject =’ENGLISH’

SELECT * FROM #STUDENT

–4] TOP WITH UPDATE STATEMENT….

— I WANT TO UPDATE TOP 1 STUDENT FROM #ALL_STUDENT TABLE AND CHANGES HIS SUBJECT FROM SANSKRIT TO HINDI

UPDATE TOP (1) #ALL_Student SET Subject=’Hindi’,Marks = NULL WHERE Subject=’SANSKRIT’

SELECT * FROM #ALL_Student

 
Leave a comment

Posted by on October 6, 2012 in Microsoft SQL SERVER

 

Magic Tables in SQL SERVER.

I would like to discuss about the Magic tables used in the SQL Server.

As per my studies I found that there are 2 Magic tables.

1] Inserted
2] Deleted

Before start Magic table I would like to discuss the following thing,

i have one question the one who is reading this article.

What happen when you update the table or when you run the Update command?

Is it going to really update the existing record?

no, it will first delete the record and then re-insert the new changed values or wise wears,

Magic tables we can used with the following command,
1] Insert
2] Delete
3] Update

Note :- to use the magic tables you have to use OUTPUT in the query

I have table name Student having following fields

iStudentID vchStudentFirstName vchStudentLastName vchDepartment

1

FirstName1 LastName1 BCA

2

FirstName2 LastName2 MCA

3

FirstName3 LastName3 BCS

4

FirstName4 LastName4 BA

1] Insert: – when we insert records in the table that time Inserted Table will be used and whatever we are inserting it in the table comes in the Inserted table.

Query to demo Inserted Table used in Insert Statement,

DROP TABLE #Student

CREATE TABLE #Student

(

IStudentID int identity (1, 1)

,vchStudentFirstName varchar(100)

, vchStudentLastName varchar (100)

, vchDepartment varchar (50)

)

INSERT INTO #Student OUTPUT Inserted.* values (‘FirstName1′,’LastName1′,’BCA’)

2] Delete: – When we are deleting the records deleted tables will be used, and stored the deleted records in the deleted table.

Query to demo Deleted Table used in Delete Statement,

DROP TABLE #Student

CREATE TABLE #Student

(

IStudentID int identity (1, 1)

,vchStudentFirstName varchar(100)

, vchStudentLastName varchar (100)

, vchDepartment varchar (50)

)

INSERT INTO #Student values (‘FirstName1′,’LastName1′,’BCA’)

INSERT INTO #Student values (‘FirstName2′,’LastName2′,’MCA’)

INSERT INTO #Student values (‘FirstName3′,’LastName3′,’BCS’)

INSERT INTO #Student values (‘FirstName4′,’LastName4′,’BA’)

DELETE FROM #Student OUTPUT deleted.*  WHERE iStudentID=3

3] Update :- when we are updating the records then it will used Inserted and Deleted table, first it will Delete the record and then Insert and stored these results in the Inserted & Deleted Table.

Query to demo Inserted & Deleted Table used in Update Statement,
DROP TABLE #Student

CREATE TABLE #Student

(

iStudentID int identity(1,1)

,vchStudentFirstName varchar(100)

,vchStudentLastName varchar(100)

,vchDepartment varchar(50)

)

INSERT INTO #Student values (‘FirstName1′,’LastName1′,’BCA’)

INSERT INTO #Student values (‘FirstName2′,’LastName2′,’MCA’)

INSERT INTO #Student values (‘FirstName3′,’LastName3′,’BCS’)

INSERT INTO #Student values (‘FirstName4′,’LastName4′,’BA’)

UPDATE #STUDENT  SET vchDepartment=’M-Com’ output Inserted.*,Deleted.* WHERE iStudentID=4

Before Updating Department for Student having ID = 4

iStudentID vchStudentFirstName vchStudentLastName vchDepartment

1

FirstName1 LastName1 BCA

2

FirstName2 LastName2 MCA

3

FirstName3 LastName3 BCS

4

FirstName4 LastName4 BA

After Updating Department ‘M-Com’ for Student having ID = 4

iStudentID vchStudentFirstName vchStudentLastName vchDepartment

1

FirstName1 LastName1 BCA

2

FirstName2 LastName2 MCA

3

FirstName3 LastName3 BCS

4

FirstName4 LastName4 M-Com

UPDATE #STUDENT  SET vchDepartment=’M-Com’ output Inserted.*,Deleted.* WHERE iStudentID=4

Inserted Table:-

iStudentID vchStudentFirstName vchStudentLastName vchDepartment

4

FirstName4 LastName4 M-Com

Deleted Table:-

iStudentID vchStudentFirstName vchStudentLastName vchDepartment

4

FirstName4 LastName4 BA
 
Leave a comment

Posted by on September 10, 2012 in Microsoft SQL SERVER

 

Programming fun in database (SQL SERVER 2008)

when i was thinking about the programming logic ,

i suddenly remember my old graduation days where i generally used to run the program in C/CPP the programs like printing of * in piramid or in reverse of it.

as now i am SQL Developer i tried to implement the same in SQL SERVER 2008.

i tried the above code, if you find other approch or find some any good solution please replly here.

Please run the following or attached code in text mode of SQL SERVER MANAGEMENT STUDIO 2008.

      –/*  code is generated on the basis of geralization method

      —    you can check by taking as much as column you want and check like

      —    say suppose i am taking 9 columns where i am inserting *   

—    it is printing Pyramid like

      —    but while running following code select Result to text

      —    * * * * * * * * *

      —      * * * * * * *

      —        * * * * *

      —          * * *  

      —           * *

      —            *

      –*/

     

      IF(EXISTS(SELECTOBJECT_ID(‘Astrick’)))

            DROPTABLE Astrick

 

      CREATETABLE Astrick

      (

            id INTIDENTITY(1,1)

            ,C1 VARCHAR(10)

            ,C2 VARCHAR(10)

            ,C3 VARCHAR(10)

            ,C4 VARCHAR(10)

            ,C5 VARCHAR(10)

            ,C6 VARCHAR(10)

            ,C7 VARCHAR(10)

            ,C8 VARCHAR(10)

            ,C9 VARCHAR(10)

      )

      DECLARE @i INT,@i1 INT,@HOLD INT,@i2 INT,@HOLD2 INT,@loop INT

      DECLARE @INSERT VARCHAR(MAX)   — column name of insert query

      DECLARE @Insert_Query VARCHAR(MAX)— Insert query

      DECLARE @VALUE VARCHAR(MAX)    — value part of insert query

      SET @I1=1

     

      SELECT @I2=COUNT(*)FROMsys.columns S WITH(NOLOCK)

      JOINsys.all_objects O WITH(NOLOCK)ON S.object_id=O.object_id

      WHERE O.name LIKE’Astrick’and collation_name isnotnull

      SET @loop = @i2

      SET @i = @i2

 

      SET @INSERT=”

      SET @VALUE=”

      WHILE(@i >0)

      BEGIN

            SET @HOLD = @I1 

            SET @HOLD2 = @I2

            WHILE(@I1 <=@I2)

            BEGIN

                 

                  IF(@I1=@I2)

                  BEGIN

                        SET @INSERT = @INSERT +’C’+CONVERT(VARCHAR(10),@I1)       

                        SET @VALUE=@VALUE +”’     *”’

                  END

                  ELSE

                  BEGIN

                        SET @INSERT=@INSERT +C’+CONVERT(VARCHAR(10),@I1)+’,’

                        SET @VALUE =@VALUE +”’     *”,’

                  END

                  SET @I1 = @I1 +1

            END

           

            SET @Insert_Query =

‘INSERT INTO Astrick(‘+@INSERT+’) VALUES(‘+@VALUE+’)’

           

            EXECUTE(@Insert_Query)

            SET @i = @i -2

            SET @I1 = @HOLD + 1

            SET @I2 = @HOLD2 -1

            SET @INSERT=” 

— resetting string to blank to use it agin in the same loop

            SET @VALUE=”

      END

      — code to print Asteric table

      SETNOCOUNTON

     

      DECLARE @Asterik_Code VARCHAR(MAX),@Select_Code VARCHAR(MAX)

      DECLARE @c INT

     

      SET @c=1

      SET @Asterik_Code =”

      WHILE(@c<=@loop)

      BEGIN

           

            IF(@c=@loop)

            BEGIN

           

            SET @Asterik_Code=@Asterik_Code+’isnull(‘+ (

SELECT a.name FROM (                                             

                  SELECT s.name,ROW_NUMBER()OVER(ORDERBY s.name) id

FROMsys.columns S WITH(NOLOCK)

                  JOINsys.all_objects O WITH(NOLOCK)

ON S.object_id=O.object_id

                  WHERE O.name LIKE’Astrick’and collation_name isnotnull

) a WHERE a.id=@c)+’,””)’

                                                     

            END

            ELSE

            BEGIN

            SET @Asterik_Code=@Asterik_Code+’isnull(‘+ (

SELECT a.name FROM (                                                          SELECT s.name,ROW_NUMBER()OVER(ORDERBY s.name) id

FROMsys.columns S WITH(NOLOCK)

                  JOINsys.all_objects O WITH(NOLOCK)

ON S.object_id=O.object_id

WHERE O.name LIKE’Astrick’and collation_name isnotnull) a WHERE a.id=@c)+’,””),’                                  

            END  

            SET @c = @c + 1

      END

      SET @Select_Code =’SELECT ‘+@Asterik_Code +’ FROM Astrick’

      EXECUTE(@Select_Code)

 
Leave a comment

Posted by on May 17, 2012 in Microsoft SQL SERVER

 

Tags:

70-433 MCTS Database Development Certification Topics

Most of the us don’t know about the certification topics for 70-433 or on the basis of 70-433 book of microsoft is not only sufficient to study and clear certification. To upgrade with the knowledge and the technology we need to do a study in deep.
so following are the 7 topics which focus on the certificaion of MCTS for database developer(70-433).

70-433 Exam Requirements

The MCTS Exam 70-433 focuses on 7

main areas:

  • Implementing tables and views
  • Implementing programming objects
  • Working with query fundamentals
  • Applying additional query techniques
  • Working with additional SQL Server components
  • Working with XML data
  • Gathering performance information

Let’s take a closer look at these 7 areas.

1. Implementing Tables and Views (14%)

Implementing tables and views takes up 14% of the total exam questions and assumes that you are familiar with:

  • creating and altering tables including computed and persisted columns, schemas, scripts to deploy changes to multiple environments and more
  • creating and altering views such as WITH ENCRYPTION, WITH SCHEMABINDING, WITH CHECK OPTION and others
  • creating and altering indexes including filtered, unique, clustered, non-clustered, FILL FACTOR, CREATE STATISTICS, indexing views and more
  • creating and modifying constraints including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling and more
  • implementing data types such as FILESTREAM, spatial, structured, and semi-structured, collations and others
  • implementing partitioning solution including partitioned tables and indexes, distributed partitioned views, etc

2. Implementing Programming Objects (16%)

The second largest focus point of the 70-433 exam focuses on implementing programming objects and makes up 16% of the total exam. This section measures your ability to:

  • create and alter stored procedures including able-valued parameters, parameter direction, manage permissions and more
  • create and alter user-defined functions (UDFs) including WITH SCHEMABINDING, EXECUTE AS, managing permissions, etc
  • create and alter DML (data manipulation language) triggers such as INSERTED, DELETED, INSTEAD OF, EXECUTE AS and others
  • create and alter DDL (data definition language) triggers such as enabling/disabling, return event data and others
  • create and deploy CLR-based objects including permission sets, SET TRUSTWORTHY and more
  • implement error handling including TRY/CATCH, RAISERROR, retrieving error information, custom error messages and more
  • manage transactions such as BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL and others

3. Working with Query Fundamentals (21%)

Working with query fundamentals takes up the largest portion of the 70-433 exam and requires that you are familiar with:

  • query data by using SELECT statements such as LIKE, WHERE, ORDER BY, and INTO
  • modify data by using INSERT, UPDATE, and DELETE statements
  • return data by using the OUTPUT clause as well as INSERTED, DELETED, and INTO statements
  • modify data by using MERGE statements such as INSERTED, DELETED, and OUTPUT
  • implement aggregate queries including built-in aggregate functions, GROUPING SETS, and GROUP BY, among others
  • combine datasets using CROSS APPLY, OUTER APPLY, UNION, UNION ALL, INTERSECT, EXCEPT and more
  • apply built-in scalar functions such as CAST and CONVERT, REPLACE, PATINDEX and CHARINDEX and others

4. Applying Additional Query Techniques (15%)

The next topic covers additional query techniques and accounts for 15% of the 70-433 exam. This area will test your ability to:

  • implement subqueries such as simple, correlated, scalar, list, and table valued
  • implement CTE (common table expression) queries including recursive and non-recursive queries
  • apply ranking functions such as RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, and NTILE
  • control execution plans like table hints and query hints
  • manage international considerations including collations, defining custom errors, filtering data, sort order, nvarchar, and more

5. Working with Additional SQL Server Components (11%)

Working with additional SQL Server components such as database mail, full-text search, SMOs and PowerShell scripts among others, accounts for 11% of the total exam. To prepare for this section of the exam you must be familiar with:

  • integrating database mail
  • implementing full-text search (CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE, STOPLIST)
  • implementing scripts using PowerShell and SQL Server Management Objects (SMOs)
  • implementing service broker solutions including services, queues, messages, message types, message validation, contracts and activation procedures
  • tracking data changes including database audit specification and CHANGETABLE
    http://www.sqldbatips.com/showarticle.asp?ID=136

6. Working with XML Data (12%)

The next focus area takes up 12% of the 70-433 exam and centers on working with XML data. For this portion of the exam you will need to know how to retrieve relational data as XML (FOR XML), transform XML data into relational data (OPENXML, sp_xml_preparedocument, sp_xml_removedocument), query XML data (XQUERY, XPATH) and manage XML data (XML datatype, XML indexes, XML schema collections).

7. Gathering Performance Information (11%)

The final section of the 70-433 exam focuses on gathering performance information and takes up just 11% of the total exam. To prepare for this portion of the exam you will need to know how to capture execution plans (SHOWPLAN), gather trace information by using the SQL Server Profiler, collect output from the Database Engine Tuning Advisor and collect information from system metadata (Dynamic Management Views (DMVs) and catalog views).

 
2 Comments

Posted by on May 11, 2012 in Microsoft SQL SERVER

 

Tags:

Temporary Tables in sql server

Temporary Tables are created under the tempdb database. 

Types of Temporary Table

  1. Global Temporary Table (represented by ##)
  2. Local Temporary Table (represented by #)
  3. Table Variable

1. Global Temporary Table

Global Temporary tables are created under the tempdb and will be represented by ##.

Say suppose I am creating Global Temporary table named as employee so it will be like ##employee.

we can use Global Temporary table in everywhere i.e. in query, SP etc except in Functions.

When we used Global Temporary Table in Query the scope of the table is through all the active sessions in Management Studio. we can create index on Global Temporary Table. We can use nolock clause with Global Temporary Table.

Syntax to create Global Temporary Table,

create table ##employee
(
empid int identity(1,1),
empName varchar(100),
empAddress varchar(100)
)

2. Local Temporary Table

Local Temporary tables are created under the tempdb and will be represented by #.

Say suppose I am creating Local Temporary table named as employee so it will be like #employee.

We can use Local Temporary table in everywhere i.e. in query, SP etc except in Functions.

When we used Local Temporary Table in Query the scope of the table is to the active session only in Management Studio. Where we are created Local Temporary table or we are directly inserting into it. we can create index on Local Temporary Table. We can use nolock clause with Local Temporary Table.

Syntax to create Local Temporary Table,

create table #employee
(
empid int identity(1,1),
empName varchar(100),
empAddress varchar(100)
)

3. Table Variable

Table variable is used like a variable where we declare a variable of type table.

Say suppose I am declaring the table variable.

declare @employee Table
(
empid int identity(1,1),
empName varchar(100),
empAddress varchar(100)
)

The table variable is worked like a table.

Table variable we can use in everywhere including functions. We can’t create the indexes on table variable.

We can’t use no lock clause with Table Variables.

Generally we used above #1,#2 & #3 when we are dealing with performance because using this in query improves the performance of a query.

—- Global Temporary Table
DROP TABLE ##employee

CREATE TABLE ##employee

(

empid INT IDENTITY(1,1),

empname VARCHAR(100),

empaddress VARCHAR(100)

)

INSERT INTO ##employee(empname,empaddress)

SELECT ‘Vikrant’,’Pune’

UNION ALL

SELECT ‘Shrikant’,’Ahamadnagar’

UNION ALL

SELECT ‘Ashish’,’Nagpur’

SELECT * FROM ##employee

—- Local Temporary Table
DROP TABLE #employee

CREATE TABLE #employee

(

empid INT IDENTITY(1,1),

empname VARCHAR(100),

empaddress VARCHAR(100)

)

INSERT INTO #employee(empname,empaddress)

SELECT ‘Vikrant’,’Pune’

UNION ALL

SELECT ‘Shrikant’,’Ahamadnagar’

UNION ALL

SELECT ‘Ashish’,’Nagpur’

SELECT * FROM #employee

— Table Variable
DECLARE @employee TABLE

(

empid INT IDENTITY(1,1),

empname VARCHAR(100),

empaddress VARCHAR(100)

)

INSERT INTO @employee(empname,empaddress)

SELECT ‘Vikrant’,’Pune’

UNION ALL`

SELECT ‘Shrikant’,’Ahamadnagar’

UNION ALL

SELECT ‘Ashish’,’Nagpur’

SELECT * FROM @employee

 
Leave a comment

Posted by on May 9, 2012 in Microsoft SQL SERVER

 

Tags: