SQL Tips

As all of us know that Microsoft SQL Server Analysis Services SSAS only allows users of the same domain or trusted domains and it does not allow users from any domain except from these two. So, if we want to connect from a machine which is not part of the domain then here is a small trick to do that but please note that this is just a temporary solution and is not recommended for production systems.

Suppose you are on a developer machine which is not part of the domain of SSAS you are logged in with the user name UserSSAS then create a user on the computer where SSAS instance is running with exactly the same user name and password and now try to connect. You will be able to connect. This is because the user will be authenticated on the Analysis Services computer by using the local user account with the matching credentials.

For the detailed discussion of connection issues of SSAS please check this article on microsoft site Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios

It looks like SQL Server 2008 is finally about to release in coming months as Microsoft has released release candidate of SQL Server 2008.

SQL Server 2008 Release Candidate 0 RC0 is now available for download.

Please download the RC0 from here

In addition with RC0, you can download SQL Server 2008 Feature pack RC0 from here

Books online for SQL Server 2008 RC0 is also available to download from here

Microsoft has announced a new web based service called SQL Server Data Services as part of its software plus strategy. This service is currently in beta and is open for beta registration. So, what is SQL Server Data Services?

It is actually a new approach of online storage and is targeted to application developers not for end users. It provides an online database to store your data in primitive data types like numerice, datetime, boolean etc.

Your data will be ultimately stored in SQL Server hosted by Microsoft and will be geo-redundant for disaster recovery.

Application developers will be able to access the data by client libraries in C# and other .NET languages provided by Microsoft and LINQ interface will be used to manage the data.

There will be virtually no restriction on data size and you will mostly pay as you go. Since this service is currently in beta therefore, billing model is not finalized yet.

Security of data is first priority therefore, endpoints will be secured by SSL.

For further details and beta registration, please visit SQL Server Data Services

How many times you have typed something like this in Query window

declare @test int=0;

here we tried to declare an integer variable and at the same time we tried to assign a value in it. This syntax is not permitted in SQL Server 2005 and prior versions of SQL Server.

SQL Server 2008 has introduced Inline variable initialization and composite assignments which allows you to write TSQL statements as above and gives you the power of writing multiple assignment statements in single line of code For example:

Although, it is a very small issue but it sometimes irritates a developer and specially those who has the technical background of the languages like C#.

Microsoft has introduced an option in Windows 2008 installation which is called Server Core. This option allows you to install a smallest possible footprint of Operating System for a specific role to be performed by the server like File Server, Domain Controller etc. This installation does not have usual graphical interface of OS like Windows Shell, Control Panel, Add/Remove Programs etc. Interestingly, it does not even have Internet explorer too. With this stripped down installation, you will have less vulnerable server which can be managed by command line. This can be good news for System Administrators which maintain their servers and categorize them in their specific roles like Domain Controller, Mail Server, etc.

So far so good, right? Wrong! The bad news is that this installation does not contain .NET Framework also!!! which means you cannot run any software which is written in .NET on this OS. The direct impact of this is that you cannot run SQL Server on it!!! because SQL Server has dependencies on windows components which are not part of the Server Core installation and hence it can not be run on it. As per Microsoft experts here

“Server Core is not intended to be an application platform. It will ship with a set of fixed function roles that can be installed (AD, DNS DHCP, File, Print, Virtualization, IIS, Media Services, LDS). Exchange Server, SQL Server, etc. are unlikely to be roles on Server Core because of dependencies they have on components of Windows Server that are not part of Core. “

From many new features of SQL Server 2008, one of the best is the introduction of Data Compression which allows you to enable or disable data compression at row or page level.

Data compression is a sensitive issue in context of databases. Some people have opinion that if we compress data at database level to reduce storage requirement then it will start hurting your performance because you are adding extra overhead of decompression.

Above point is valid up to some extent but not fully. When we enable data compression, it means we will be able to cache more data pages which in turn reduce the need to physical disk I/O. Low disk I/O means direct performance improvements. So, actually like all other database tuning strategies, data compression in SQL Server 2008 is just another tuning option which can be used in good or bad manner depending on how good the DBA is in performance tuning.

SQL Server 2008 provides  two types of data compressions i.e.

1. ROW Compression: Remember varchar? Take the analogy of char and varchar data type where in varchar SQL Server just stores the charcters which made the string value and do not allocate any extra space but in char, length of string is fixed so ever value takes same space which in some case get wasted. The strategy has been implemented for all fixed types i.e. int, float etc. in SQL Server 2008 and if a value 120 is stored in int column then only 1 byte is enough for it therefore, SQL Server will just use 1 byte instead of 4 bytes to store value. This strategy will normally give you 25% to 75% space savings.

2. Page Compression: Pages contains the actual data stored in a column. Suppose you have an Invoices table where CustomerId column contains Identity of the customer for which this invoice was generated. As you can easily imagine that CustomerId column will have same values for multiple invoices if those invoices were generated for the same customer. In SQL Server 2005 every value will be stored in Page regardless whether it has already appeared in the same column for some other row or not but in SQL Server 2008, SQL Server will store the value only once and will refer it in all other occurrences, this is called Page Compression. To optimize the performance, Page compression occurs only when Page is full.

For more detailed discussion, please refer to following articles:

 SQL Server Storage Engine

SQL Server 2008 Data Compression

New Compression Features in SQL Server 2008

Knowledge of recovery model of a database is one of the key information elements for any database administrator. If you want to quickly find the recovery model of a given database then you can use sys.databases system table where recovery_model_desc column value will tell you the recovery model of the respective database.

Following is an example to find the recovery model of AdventureWorks database

Select recovery_model_desc from sys.databases Where name=‘AdventureWorks’

I was browsing through a sql server newsgroup and found a question about a way to find all those rows where a float column value is not a whole number. It is very simple by using the Floor function. Following scripts demonstrates the trick:

Use tempdb;

Go

Create table testTable

(

      itemId int not null,

      itemValue float not null

)

Go

Insert into testTable values (1, 1.234);

Insert into testTable values (1, 2);

Insert into testTable values (1, 3.33);

Go

Select * from testTable;

Go

Select * from testTable where itemValue > floor(itemValue)

Go

drop table testTable;

Go

If you want to find the size of each data file you database is using then you can use the command

DBCC SHOWFILESTATS;

you will get one row for each of your data file for the current database. Look for the column TotalExtents and multiply its value by 64 to get the total file size in KB. Reason for 64 is that each Extent consists of 8 pages and size of each page is of 8KB on disk.

Another column UsedExtents displays the number of extents used. Get its value in KB by multiplying it by 64 and then subtract the result from total file size calculated above to get free space left.

Following is the example code which creates a TestDatabase first and then show its file statistic, it then drops the newly created database.

Use master;

Go

CREATE DATABASE [TestDatabase] ON  PRIMARY

( NAME = N‘TestDatabase1′, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase1.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

( NAME = N‘TestDatabase2′, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )

 LOG ON

( NAME = N‘TestDatabase_log’, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

Go

Use TestDatabase;

Go

DBCC SHOWFILESTATS;

Go

Use master;

Go

drop database TestDatabase;

Go

Sometimes, we want to make sure that an integer column of a table e.g. customerId or any other column must have a unique number assigned to it. You can achieve this result by creating an identity column and then assigning its value to your target column but in this approach there is no guarantee of which row will get which number, what you will achieve is the unique number for each row. This is OK if you are just looking for unique id but if you want to assign the incrementing values according to a sort order of another column then it is not easy by just normal TSQL. Fortunately, in SQL Serever 2005 we have a great solution for this problem and that is using ROW_Number() and Common Table Expressions

Following example code will demonstrate this trick and you can see that it is pretty straight forward and quite fast also:

Use tempdb;

–Create a test table

Create table testTable

(

      customerId int,

      customerName varchar(50)

)

–Populate the table with some sample data

Insert into testTable (customerId, customerName) values (4,‘B Motors’);

Insert into testTable (customerId, customerName) values (2,‘Z Motors’);

Insert into testTable (customerId, customerName) values (3,‘X Motors’);

Insert into testTable (customerId, customerName) values (4,‘Y Motors’);

–Display data before update

Select * from testTable order by customerName;

–Create Common Table Expression with the specific order by clause

With CTE_testTable

as

(

      Select Row_Number() OVER(ORDER BY customerName) as UniqueId,customerId,customerName from testTable

)

–Update the table by using CTE

Update CTE_testTable Set customerId=UniqueId;

–Display the table data, you may notice that customerId column has properly incrementing Ids

Select * from testTable order by customerName;

–Clean the database

drop table testTable;