There are two types of data compression in SQL Server.
- ROW Compression. This compression feature takes into account the variable data type structures defining a column. Take, for instance, a CHAR(100) column stored in a variable length storage format will only use up the amount of storage defined by the data. Storing “SQL Server 2008 ” in the column will only require storing fifteen characters instead of the full 100 characters, thereby, a 85% savings on storage space. This is the extension of the vardecimal storage format made available in SQL Server 2005 Service Pack 2. Note, also, that this compression feature does not take any disk space for zero or null values.
- PAGE Compression. This compression feature is a superset of ROW compression and takes into account the redundant data in one or more rows on a given page. It also uses prefix and dictionary compression. What this simply means is that for both page compression techniques, the storage engine cuts down on repeated data in the page. For example, if a table is partitioned using a column prefix, all data in a specific partition will have the same or similar prefix. Let’s say the value of columns start with something like A1000Q-xxxx like some product codes, the storage engine store the A1000Q- once on the page and then refer to this value from all other occurrences of this value on the same page. This can also be said of a column with a defined DEFAULT constraint. Page compression only occurs when the page is full to optimize the performance.
Right click on the Table ==> Properties==> Storage==> Manage Compression
This will launch the Data Compression Wizard. On the Welcome to Data Compression Wizard, click Next.
On the Select Compression Type page, click the drop-down menu on the Compression Type column to select your compression of choice.
Click the Calculate button to display a similar information provided by the table/stored procedure.
1.Data Compression in SQL Server has been made available in SQL Server 2005
with Service Pack 2.
2.SQL Server 2008 supports both row and page compression for both tables and
3.PAGE compression feature is a superset of ROW compression.
Page compression only occurs when the page is full to optimize the performance.
Reference: ( For the above information with clear example follow below link)
DBCC SHOWFILESTATS WITH NO_INFOMSGS
Q.What are the different types of Indexes available in SQL Server?
Ans: The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
Q.What is the difference between Clustered and Non-Clustered Index?
Ans: In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
Q.What is Heap Table ?
Ans: Heaps (Tables without Clustered Indexes), A heap is a table without a clustered index. One or more non-clustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order.
Q.What is Fill Factor?
Ans: Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.
Q.What is the default fill factor value?
Ans: By default the fill factor value is set to 0.
Q.Where do you find the default Index fill factor and how to change it?
Ans: The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes.
The other option of viewing and changing this value is using
1.Difference between SQL Server 2000 and 2005
|SQL Server 2000||SQL Server 2005|
1.Query Analyzer and Enterprise Manager are separate
1.Both are combined as SQL Server Management Studio (ssms – 2008 onwards,sqlwb-2005)
2. No SSIS introduced
2. SSIS introduced
|3. No Compression||3. Compression was introduced|
|4. Bulk Copy (BCP) not possible||4. Bulk Copy (BCP) is possible|
|5. Database Mirroring is not there||5. Database Mirroring introduced with SP1.|
2. 2005 – 2008
|SQL Server 2005||SQL Server 2008|
|1.File Stream not there||1.File Stream was introduced|
|2. No backup Encryption||2. Backup Encryption introduced|
Working on….for more points
1.GUI Steps to connect SQL Azure database using SQL Server Management Studio:
2.T-SQL Script to create Login and User to connect SQL Azure Database: