SQL Exercise Examples (Basics)

Download SQL Server Examples from Here.
This Contains basics and are usefull for starters.

SQL Server 2008 requires Microsoft .NET Framework 2.0 SP2 to be installed

When you are installing SQL Server 2008 express edition, your installation might fail due to the lower version of .NET Framework.

To overcome this error
1. Install .NET Framework 3.5 and 3.5 SP1.
     .NET Framework 3.5 can be downloaded from Here.
     .NET Framework 3.5 SP1 can be downloaded from Here.
2. Install Windows installer 4.5.
    Windows installer 4.5 can be downloaded from Here.

Undocumented DATE and TIME functions in SQL

There are some Undocumented Date and time functions that are available in SQL.
These functions are listed below.
Run these functions and check their results, they are self-explanatory.
select {fn current_date()}
select {fn current_time()}
select {fn now()}
select {fn extract(hour from getdate())}
select {fn extract(minute from getdate())}
select {fn extract(second from getdate())}
select {fn extract(day from getdate())}
select {fn extract(month from getdate())}
select {fn extract(year from getdate())}
select {fn dayname(GetDate())}
select {fn monthname(GetDate())}
select {fn month(GetDate())}
select {fn year(GetDate())}

How to Backup Analysis Service Database

An Analysis Service database can be backed up using SSMS or XMLA Query Editor.

Backup using SSMS:

1. Login to SSAS instance using SSMS

2. Right-Click on the Analysis service database you want to backup.

3. And Choose the option Backup.

4. Now in the Backup Database page, Enter the location to where you want the backup file to be placed.

5. Choose the other options, if required.

6. And Click on OK to backup the database.
Backup Using XMLA Query Editor:
1. Open the XMLA Query Editor using the New Query Option.
2. Enter the below query.
 <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> 
   <Object>
     <DatabaseID>Test</DatabaseID>
   </Object>
   <File>E:\Test.abf</File>
   <AllowOverwrite>true</AllowOverwrite>
 </Backup>
3. Replace the DatabasesID with the Analysis Service database name which you want to backup.

4. And replace the File to where(location on disk) you want the analysis service database to be backed up to.

5. Execute the Query.

Backup Compression in SQL Server 2008

Backup Compression is a new feature in SQL Server 2008 that is introduced in SQL Server 2008. This new feature significantly reduces the backup and restore times as well as the size of the backups.
By default, backup compression is turned off on the server level.


Using this feature, the size of the backups can be kept small and the Backup and the restoration time will also be reduced.

To change the default configuration of Backup Compression, execute the below query

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

EXEC sp_configure 'backup compression default', 1
GO

RECONFIGURE

===============OR===============

Use the Server Properties

1.       1. Go to the “Server Properties”.

2.       2. Select “Database Settings”

3.       3. Click on the Check box “Compress Backup”.

4.       4. Click OK.

To compress the backups, follow the below steps

1.       1. Right-Click on the database which you want to backup.

2.       2. And Select Tasks

3.       3. Under Tasks Menu, Click on the “Back up” Option

4       4. In the backup window, select the “Backup type” and add the Destination.

1.       5. Now go to the “Options” page and select the Compression Options.

There are 3 options under compression

a.       Use the Default Server Setting

b.      Compress Backup

c.       Do not Compress Backup

The option “Use the Default Server Setting” will use the value that is set in server level.

By default, it will not compress the backups. But in our case the default server settings has been changed at the first, so the backups will be compressed.

The option “Compress Backup” will compress the backups and the option “Do not Compress Backup” will not compress the backups.

Now select either option “Use the Default Server Setting” or “Compress Backup” as both options will compress the backup because of the changes done to the server level in the beginning.

.       6. Click OK.

.       7. In the similar way take the backup of the same database setting the Compression option to “Do not  Compress Backup” and check the size difference of the both the backups.

You will find that the size of the compressed backup will be less.

To download the detailed test cases and testing results click here.

This article is also available in pdf format for downloading.

Please Click here to get your copy.