SQL Server and MS BI With a Geek Touch

Everyone reading this must have come across these datatypes, but have you ever thought what are the differences and advantages between these two?

The obvious difference that comes to our mind is related to the length. Yes! You are right. The varchar supports variable length and Char supports fixed length. This is the main advantage varchar datatype. for example,  let us consider varchar(10) and char(10) . To store “GEEKEPISODE”, both the datatypes will use the complete length allocated and to store “GEEK”, CHAR datatype will use the complete 10 characters  allocated, but VARCHAR will use only 4.

From the above examples it might seem that VARCHAR is better than CHAR all the time. But remember, CHAR was included as a separate datatype because it has certain advantages over VARCHAR.

VARCHAR uses additional space to store the length of the string stored in it and to mark the end of the string. Whereas, CHAR datatype doesn’t use this additional space.  This certainly is the advantage of CHAR datatype.

Summary:

Read more…

I often create batch files to execute sql scripts ( specially SPs) and then schedule it using SQL agent job/Task Scheduler. In this post , I will show you how to create a batch file with sql commands.

First of all, The command that is used to execute SQL queries from commnd prompt is “SQLCMD“. The general syntax of this command is available in the link. I will go with a simple one as below

sqlcmd –E –S Servername –Q Query -o “Output Path”

C:\Users\geekEpisodes>  sqlcmd -E -S “Local-SRV01″ -Q “Select top 10 * from MyTestDatabase..[ForecastHistory]” -o “C:\New Folder\Output.txt”

All the commands that you execute in the command prompt can be executed from batch/command files. to create a batch file with SQL commands follow the steps below

Read more…

It is often necessary to change the database to single user mode, especially if you are a DBA. A simple example would be to change the collation settings or any DB settings. The single user will allow only one  user ( usually DBA) to access the database. Hence it will be easy to make changes without the worry of deadlocks or any other type of contention for DB and also without affecting the users.

It is very easy to change the database to Single user mode; in fact, it is just an execution of the script away. Use the script below to change the mode.

ALTER  DATABASE <<Database Name>>  SET SINGLE_USER  WITH NO_WAIT

Read more…

Encrypting a stored procedure will hide the definition of the procedure from users and it definition will not be displayed in the activity monitor.

The procedures can be encrypted by using “WITH ENCRYPTION” clause after the create statement.

ex: Read more…

Recompiling an object is advantageous when ‘indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. sp_recompile is a system stored procedure in SQL that will recompile an object the next time it runs.  By recompiling , you can reoptimize the queries.

Below is the query to recompile all the SPs in the database.

Declare @name nvarchar(125),@cmd nvarchar(300)

Declare SPList cursor FOR      /* Declare Cursor to loop through all the SPs in the DB */
Select Name from sys.objects
where type = ‘P’

Open SPList    

fetch next from SPList     /* fetch Record from cursor*/
into @Name 

While @@FETCH_STATUS = 0
begin
Select @cmd = ‘EXEC sp_recompile  ['+@Name+']‘

Exec @cmd  /* recompile the SP */

fetch next from SPList    /* fetch next SP name from Cursor */
into @Name

End

Close SPList
Deallocate SPList

The GUID (uniqueidentifier) data type is one of the most interesting data types available. uniqueidentifier column holds a GUI, a string of 32 random hexa-decimal characters (0-9, A-F) in blocks separated by hyphens.  A GUID will look like

5166AA1D-A18F-4D3C-A7CA-3F3CBF8CEE2B 

EDCF4B8A-4154-449B-972A-2224F8450BF1

CEA4C383-51FD-4B99-A67D-F5C34D5F1013     

5D9FDC84-632D-4450-B183-04CBBEE87DC6

GUIDs have both Pros and cons. GUIDs are pretty big in size and require 16 bytes of storage. This usually makes it slow during joins of large dataset.

Now you might think, What is so great about GUID? Although it occupies lot of space there are several advantages. First, a GUID will be a completely unique value and no other GUID in the world will share the same string. This means that you can use GUIDs as PKs on your tables if you will be moving data between databases. This technique prevents duplicate PKs when you actually copy data. GUIDs are widely used in Microsoft products to identify interfaces, replica sets, records, and other objects.

In Sql Server, you can create a new GUID by using the function NEWID(). This function is a non-deterministic function. It generates a new value every time you execute function .

Select NEWID()

 the datatype is named in SQL as “uniqueidentifier”

Renaming a database is not done very often. However, I have done it myself many times.  this can be done in two ways. The common method used for rename is by using ” Alter Database ” script. An example is as shown below.

ALTER DATABASE OldDbName MODIFY NAME = NewDbName

The above script will rename the database “OldDbName” with new name “NewDbName”. Another way of acheiving the same is by using the stored Procedure “SP_RENAMEDB” The syntax is as below

EXEC SP_RENAMEDB 'OldDbName' , 'NewDbName'

“Alter” is preferred over the SP_RenameDB as the SP will be deprecated in the future version of  SQL Server.

SQL Server might throw an error while renaming a database as below.

This is due to the existing conections to the database. to overcome this error,

1.you need to drop all the connections to the database.  Refer to my previous blog for more info on this.http://awesomesql.wordpress.com/2010/02/08/script-to-drop-all-connections-to-a-database/

2. You can set the database to single user mode . refer the link for more info on this. http://awesomesql.wordpress.com/2009/08/04/changing-the-collation-of-a-database/

There are different ways of fetching the current time from the sql server. Sql server 2005 provides 3 different functions to fetch the datetime value.

• Getdate()
• Current_TimeStamp
• {fn NOW()}

All these functions are non-deterministic . They all return the same values when executed.

In addition to these functions SQL server 2008 Provides an additional function ”SYSDATETIMEOFFSET()”. This function provides the current time offset with GMT.

a sample execution is as below.

SELECT
CURRENT_TIMESTAMP as [CURRENT_TIMESTAMP] , GETDATE() as [GETDATE], {fn NOW()} as [FN]

 
Result:

CURRENT_TIMESTAMP        GETDATE                 FN
2010-01-25 14:40:10.067 2010-01-25 14:40:10.067 2010-01-25 14:40:10.067

In the above example you can see that the results are same irrespective of the functions. The performance are not different either.

Very often, it is important to check the last identity value inserted into a table. This can be easily done using the system function “@@IDENTITY” . This System function returns the last inserted identity value. 

The function returns an integer value or a NULL. After an INSERT statement the @@IDENTITY function will contain the latest value inserted into the identity field. It will contain NULL, If the insert/ select statement does not affect any records. one Key feature that needs to be noted is that the @@IDENTITY value does not revert to a previous value even if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back. However the Triggers fired doesn’t have an impact on the function. Failed statements and transactions can change the  identity for a table and create gaps in the identity column values.

to check these , Execute the function with out performing an insert in a new session.

SELECT  @@IDENTITY
Result will be "NULL"

 Now insert some value into a table containing an identity field. If you do not have one, the below script might be of use. 

CREATE TABLE [dbo].[TestTable](
[Ident] [int] IDENTITY(100,1) NOT NULL,
[ColUmn_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Insert the data by executing the statements below

INSERT INTO TESTTABLE ( COLUMN_1) VALUES ('HELLO');
SELECT  @@IDENTITY AS CURRENT_IDENTITY_VALUE
     , IDENT AS INSERTED_IDENTITY_VALUE
FROM
 TESTTABLE ;

Result:

Current_identity_value    Inserted_Identity_Value
100                       100

The Scope of the function is restricted to the current session. To get the identity value on a different server , we need to use functions / procedures.

The first thing that goes through your mind might be ” why cant we SP_SPACEUSED instead ? “. You are right. However , it will result in a row by row operation rather than a set operation. This will be a major headache in the usual production environment with large number of tabls and huge data. The below query can give you the result in few seconds. The step 2 can be avoided by careful coding . I have included update statement for easier understanding of the code. 

SELECT OBJECT_ID

 , SUM (RESERVED_PAGE_COUNT) RESERVEDPAGES

 , SUM (USED_PAGE_COUNT) USEDPAGES

 , SUM ( CASE WHEN (INDEX_ID < 2) THEN (IN_ROW_DATA_PAGE_COUNT + LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT) ELSE LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT END )PAGES, SUM ( CASE WHEN (INDEX_ID < 2) THEN ROW_COUNT ELSE 0 END ) AS [ROWCOUNT]  

 INTO   #SIZE  

 FROM  SYS.DM_DB_PARTITION_STATS 

 GROUP BY OBJECT_ID     

UPDATE #SIZE  
SET RESERVEDPAGES= (RESERVEDPAGES + RESERVED_PAGE_COUNT)

 ,USEDPAGES = (USEDPAGES + USED_PAGE_COUNT)  

 FROM

#SIZE T

 ,SYS.DM_DB_PARTITION_STATS P

 ,SYS.INTERNAL_TABLES IT  

WHERE T.OBJECT_ID=IT.PARENT_ID AND IT.INTERNAL_TYPE IN (202,204) AND P.OBJECT_ID = IT.OBJECT_ID   

 

 

SELECT  DATASIZEINKB= SUM(PAGES) * 8 , INDEXSIZEINKB = SUM((CASE WHEN USEDPAGES > PAGES THEN (USEDPAGES - PAGES) ELSE 0 END)) * 8, UNUSEDSIZEINKB = SUM((CASE WHEN RESERVEDPAGES > USEDPAGES THEN (RESERVEDPAGES - USEDPAGES) ELSE 0 END)) * 8

FROM #SIZE  

 

DROP TABLE #SIZE 

 

 pardon me for the allignments, as i could not get it right .