Tuesday, January 25, 2011
How SQL Server stores indexes on variable length columns
In the last months I had done a lot of performance tuning workshops, and there is almost the same question: How SQL Server stores indexes on variable length columns? Therefore I thought this would be a great topic for a weblog posting. To examine the concrete storage details of an index defined on a variable length column, I have created the following table and populated it with 80.000 records:
-- Create a new table
CREATE TABLE Customers
(
CustomerName VARCHAR(255) NOT NULL PRIMARY KEY,
Filler CHAR(138) NOT NULL
)
GO
CREATE TABLE Customers
(
CustomerName VARCHAR(255) NOT NULL PRIMARY KEY,
Filler CHAR(138) NOT NULL
)
GO
-- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
INSERT INTO Customers VALUES
(
'CustomerName' + CAST(@i AS VARCHAR),
'Filler' + CAST(@i AS VARCHAR)
)
SET @i += 1
END
GO
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
INSERT INTO Customers VALUES
(
'CustomerName' + CAST(@i AS VARCHAR),
'Filler' + CAST(@i AS VARCHAR)
)
SET @i += 1
END
GO
As you can see I have created the PRIMARY KEY constraint on a VARCHAR(255) column, which is enforced by a UNIQUE CLUSTERED INDEX by SQL Server. In the next step I have retrieved some physical information about the created clustered index by using the DMF sys.dm_db_index_physical_stats:
-- Retrieve physical information about the clustered index
SELECT * FROM sys.dm_db_index_physical_stats
(
DB_ID('VariableClusteredKeyStructure'),
OBJECT_ID('Customers'),
NULL,
NULL,
'DETAILED'
)
GO
SELECT * FROM sys.dm_db_index_physical_stats
(
DB_ID('VariableClusteredKeyStructure'),
OBJECT_ID('Customers'),
NULL,
NULL,
'DETAILED'
)
GO
When you look into the output, you can see that the column min_record_size_in_bytes show you a value of 7 and the column max_record_size_in_bytes shown you the value 28 in the index page. This leads us to the conclusion that the clustered keys are stored as variable length columns inside an index record. So let’s examine an index record on an index page. I’ve used the DBCC IND command to retrieve all pages for our clustered index and stored that output in a little helper table.
-- Create a helper table
CREATE TABLE HelperTable
(
PageFID TINYINT,
PagePID INT,
IAMFID TINYINT,
IAMPID INT,
ObjectID INT,
IndexID TINYINT,
PartitionNumber TINYINT,
PartitionID BIGINT,
iam_chain_type VARCHAR(30),
PageType TINYINT,
IndexLevel TINYINT,
NextPageFID TINYINT,
NextPagePID INT,
PrevPageFID INT,
PrevPagePID INT,
PRIMARY KEY (PageFID, PagePID)
)
GO
CREATE TABLE HelperTable
(
PageFID TINYINT,
PagePID INT,
IAMFID TINYINT,
IAMPID INT,
ObjectID INT,
IndexID TINYINT,
PartitionNumber TINYINT,
PartitionID BIGINT,
iam_chain_type VARCHAR(30),
PageType TINYINT,
IndexLevel TINYINT,
NextPageFID TINYINT,
NextPagePID INT,
PrevPageFID INT,
PrevPagePID INT,
PRIMARY KEY (PageFID, PagePID)
)
GO
-- Write everything in a table for further analysis
INSERT INTO HelperTable EXEC('DBCC IND(VariableClusteredKeyStructure, Customers, 1)')
GO
INSERT INTO HelperTable EXEC('DBCC IND(VariableClusteredKeyStructure, Customers, 1)')
GO
-- Retrieve the root index page (1 page)
SELECT * FROM HelperTable
WHERE IndexLevel = 2
GO
SELECT * FROM HelperTable
WHERE IndexLevel = 2
GO
In my case SQL Server stored the index root page on the page id 458, which I have dumped out through the DBCC PAGEcommand (after enabling the trace-flag 3604 to get the DBCC PAGE output):
DBCC TRACEON (3604)
GO
GO
-- Dump out the root index page
DBCC PAGE(VariableClusteredKeyStructure, 1, 458, 1)
GO
DBCC PAGE(VariableClusteredKeyStructure, 1, 458, 1)
GO
Each slot in the output represents one index record, like:
26 95020000 0100 0100 1b00 43757374 6f6d6572 4e616d65 31333533
Let’s examine those hex values.
- 26: The first byte represents status bits.
- 95020000: The next 4 bytes are the child-page-id to which this index record points
- 0100: The next 2 bytes are the child-file-id to which this index record points
- 0100: The next 2 bytes are the number of variable length columns
- 1b00: For each variable length column SQL Server stores a 2 byte entry which points to the offset at which the variable length column ends – it’s the same as when you store variable length columns on a regular data page. In this case we have one variable length column, therefore SQL Server has to store one 2 byte offset – byte offset 27 in this case. This means that the next bytes until the byte offset 27 are part of our variable length column – the clustered key.
- 43757374 6f6d6572 4e616d65 31333533: The hex value for our clustered key – the column CustomerName
As you can see from this explanation SQL Server stores variable length index columns in the same format as regular variable length columns on data pages. But you have to be aware that there is a slight overhead with this, because you need 2 additional bytes for storing the count of variable length columns and for each variable length column you need 2 bytes in the variable column offset array. Keep this in mind when you are designing your indexes and when you calculate how many index records fits onto one index page. You can download the whole script from here and play around with it.
-Klaus
.NET Web Services
Web services are small units of code built to handle a limited task.
What are Web Services?
- Web services are small units of code
- Web services are designed to handle a limited set of tasks
- Web services use XML based communicating protocols
- Web services are independent of operating systems
- Web services are independent of programming languages
- Web services connect people, systems and devices
Small Units of Code
Web services are small units of code designed to handle a limited set of tasks.
An example of a web service can be a small program designed to supply other applications with the latest stock exchange prices. Another example can be a small program designed to handle credit card payment.
Benefits of Web Services
- Easier to communicate between applications
- Easier to reuse existing services
- Easier to distribute information to more consumers
- Rapid development
Web services make it easier to communicate between different applications. They also make it possible for developers to reuse existing web services instead of writing new ones.
Web services can create new possibilities for many businesses because it provides an easy way to distribute information to a large number of consumers. One example could be flight schedules and ticket reservation systems.
5 Websites to Edit PDF files Online for free
Portable Document Format(PDF) file is a open standard format for the documents exchange. It was created by Adobe in the 90′s and now the most popular and convenient document format all around the world. There are many reasons why it is so popular. The first is it is more compact(surely smaller in size), Combines everything inside the document like Text, images, font, etc. You can view it from any computer with the reader software and the documents are also seem almost same on all computers.
But it is also said that PDF files can’t be edited, thats just a old story. There are many free softwares and even free Online tools to edit the PDF files of yours or from the third party. So in this post here are 5 best websites where you can easily edit the PDF files for free.
PDFfiller
PDFfiller is the ultimate Online Tool for editing the PDF files without using any softwares.You can Upload the PDF file from your hard drive or may enter the URL of the file and start editing the PDF file online staying within the browser window. After edit you can secure place to keep your PDF forms and you can access, fill, write, fax, email and print your forms from any computer.PDFvue
PDFvue is a online PDF viewer and PDF editor trhat lets you edit or view the PDF file Online without using any software directly uploading from your hard drive. It combines cool features like viewing, commenting, annotations, page deletion or rotation and adding fillable form fields to PDF’s. Since PDFvue requires to upload your PDF file to upload on their own serves, It might take more times for large files and slow internet connection.PDFescape
PHFescape is a free of charge web-based PDF editor program written in JavaScript, HTML, CSS and ASP. PDFescape offers features like PDF editing, form filling, page arrangement, printing, saving, , etc. Open PDF file from the web or from the local computer, make different changes to the PDF file from the toolbox in the website and download the file in the local computer or store it on PDFescape server for short period of time.FillanyPDF
FillanyPDF is actually an website where you can Upload your PDF file and link other related people so that they can make a sign on them. Its can be also said as Online PDF editing tool that requires no software installed to edit the PDF files online. It is currently under beta version allowing lots of features to its users.PDFhammer
PDFhammer is the most popular among the all online PDF editing tools that can easily edit our PDF files directly within browser window without installing any software. With PDFhammer you can create the single polished document merging multiple documents, edit them online, protect the files with a password, Update PDf meta data and ore. This service is currently closed for maintenance.20 Web Services To Send Free Fax Online without the Fax Machine
Fax is an excellent service to send and recieve important files and documents worldwide. But fax machines have always been essential for fax. Well, not anymore. There is no need to purchase an expernsive fax machine or run to local stores and copy center for faxing when you can fax online for free from home without even requiring any fax machine.
Here is the list of 20 free web services that allow users to send faxes via internet connection. Most of the internet faxing services are totally free. But some services are supported by the advertisements on the coverpage of the faxes sent due to which the faxes may look unprofessional.
1) FaxZero
FaxZero is an online fax service that allows users to send free fax in the form of PDF files and Word Documents to any fax machine around USA and Canada. This fax service doesn’t require any fax machine but a valid email address is required. FaxZero limits its online fax services to its users by allowing them to send twice fax for a day and each time the fax can be only of maximum three pages. Another disadvantage of FaxZero is that it is supported by ads that are displayed on the cover pages of the free faxes that are sent. It prints an ad on the fax coversheet but leaves the main page uncorrupted.2) TPC Fax
TPC Fax is a simple and free online HTML fax service. It allows you to send free faxes using either your web browser or via email. Its service covers most large metro areas including cities outside the United States. Its services are compatible with K7.net free fax numbers and faxes can bes sent from TPC to K7 numbers.3) ScanR
ScanR is a fantastic online service that turns you camera phone into a digital scanner and a fax machine in your pocket. Here’s how ScanR works: You have to take the picture of the document you want to share with others and then send it to the ScanR email address. Then, ScanR sends it on by fax to the receiver fax machine as .pdf file. There are no charges for their basic services.4) PamFax
PamFax is fully integrated with Skype and allows its users to send some faxes for free in many formats than just PDF and DOC file. It is highly secured as the faxes are sent in fully encrypted form. You have to download and install it in its supported OS environment such as Windows, OS X , Linux and even in Android. It doesn’t send ads on your faxes. It is easy to use and fully reliable.5) MyFax
MyFax allows users to send 100 free faxes per month and recieve 200 faxes for free. This free online fax service allows you to send fax over 40 countries such as United states, United Kingdom , Canada, Argentina, Belgium , Brazil , Czech Republic etc. MyFax allows you to send two faxes per day;10 pages each. It is compatible with many types of file formats and integrates easily with Microsoft Office and Outlook.6) FreePopfax service
FreePopfax service is the free internet faxing service provided by Popfax.com where you can sign up for a trial and start faxing for free right away from your computer. There is no requirement for any kind of software installation and your documents are kept safe in their system for thirty days.You can send unlimited free faxes to any fax number all over the world. However all free internet faxing will be supported by advertising on the cover pages of the sent faxes making your faxes unprofessional. In order to send the free fax , you have to simply compose the fax online or attach one page document in PDF or DOC format.7) Nextiva Fax
Nextiva Fax service allows you to send and recieve faxes from any part of the world that has internet access. It is useful for the people who have to send huge volume of faxes as it offers 500 free faxes per month and provides more flexibility on how you want to send and recieve the faxes. For example : Fax by E-mail, Fax through Microsoft Office, Fax thorugh you web protal, or Fax through your existing Fax machine. If the limit of sending free fax is exceeded , then also the cost for sending the additional faxes is low and affordable in comparision to other services .8 ) TrustFax
TrustFax is one of the top online faxing services available in the market. It is simple, easy to use and it is provided with lot of excellent features to provide the best services to its users. It allows its users to send about 250 free faxes per month to over 100 countries. The faxes are sent to the receiver’s email inbox in Adobe PDF format.A file cabinet will store all of your faxes and a fax tracker keeps you updated about the status of the faxes sent. TrsutFax also offers 24/7 email support,a toll free support phone number and a convenient user forums.9) Fax Freedom
Fax Freedom is another excellent online fax service to send , recieve , printing and archiving faxes. One of its unique feature is that it offers 800 numbers and vanity numbers or you can even use your own 800 and 888 number when you send faxes through them.10) Send2Fax
Send2Fax services allow you to send documents as well as images via fax.It allows its users to send 100 free faxes per month. The image formats supported by its faxing service are TIF, JPG,GIF and PNG files.11) RingCentral Fax
RingCentral Fax service allows its users to send 150 free faxes per month. It comes with most advanced fax features available anywhere at no extra charge.You will be able to send and receive faxes in a snap – from Microsoft Office, email or your phone. It also provides free fax software to electronically sign and edit faxes and design cover pages. It is PC and Mac compatible. A desktop call controller helps in managing all of your faxes.12) GotFreeFax.com
GotFreeFax service is free online faxing service for US and Canada. You can send 2 free fax per day with each fax having maximum of 3 pages via GotFreeFax.com. You can send the fax through it in a simple and easy way. You have to just type the message in the text box provided or simply upload the files to be faxed for processing. Currently it supports PDF (.pdf) file format , Word 97-2003 (.doc), and Word20o7 (.docx) file format only.13) eFax
eFax allows a 30 day free trial where you can send fax online 50 pages and recieve up to 130 pages at no charge. You can send and recieve faxes via email as email attatchments. eFax supports file formats such as PDF, TIFF, EFX and many more. You can recieve faxes on multiple accounts. It allows you to fax from your mobile phone also.14) Interpage
Interpage allows you to send free faxes to anywhere in the US in its 7 day trial service. It supports various file formats such as DOC,PDF, Unicode , HTML, Microsoft Office etc.15) Free FAX Button
Free FAX Button allows you to send free faxes to over 30 countries. You can send two free faxes per day to the USA, Canada, Europe and Asia. The faxes are sent as emails. But you cannot uplaod files to the faxes.16) Free POP Fax
Free POP Fax allows you to send free faxes to US and 45 other countries. The service is supported by advertising on the cover pages of the free faxes sent. You can compose the fax online or attatch a one page document.17) FreeFAX.com
FreeFAX allows you to send free faxes to virtually any part of the world for free. Registration is not required for sending free fax online. But after being its member ,you can also recieve fax via email, create custom fax sheets or group fax.18) FaxOrama.com
FaxOrama allows you to send free faxes to anywhere in North America. It allows you to send maximum of 2 documents per day each with 5 pages. The file formats supported by its service are .DOC , .DOCX , .RTF, .GIF,. JPG,.JPEG,.PDF ,.XLS and many more. Like others , it is not supported by the advertisement on the coverpage of the fax. It only comes with listed on your faxcoverpage is the FaxOrama.com logoand the information about your fax.19) Wonderfax
Wonderfax is free virtual UK fax number that recieves your fax. You need to signup to get a free Wonderfax number where all the faxes are collected when they are sent to you. Then they are converted to a PDF attatchment and forwarded directly to your email. After you have recieved the faxes , you can simply treat them like any email and you can file , forward or print them.20) MessageNet – Online Free UK/Italy services
MessageNet is a free fax service for Italians to send fax to an email services to UK and Italy. However you can send 3 free faxes per month only and it requires email to activate their services.
Posts
