Feeds:
Posts
Comments

Archive for March, 2013

I have been using exist() function for a long time. This is very handy when we need to check the existence of any data in the xml document. There is also a bug related to this function when it comes to NULL but this bug is fixed in SQL Server 2012.

Let me explain it with examples.
Example 1 :
Create the given below script in ealier version of SQL Server (2005 & 2008)

DECLARE @xmltest XML;
SET @xmltest = null;
SELECT COUNT(*) as [T_Count] WHERE @xmltest.exist('/raresql') = 0;
--OUTPUT

T_Count
———–
1

(1 row(s) affected)

Ooopps…… The expected result is 0 because ‘/raresql’ doesn’t exist in the @xmltest but it returned 1 .

Example 2 :
Lets create the same sample in SQL Server 2012 and view the output.

DECLARE @xmltest XML;
SET @xmltest = null;
SELECT COUNT(*) as [T_Count] WHERE @xmltest.exist('/raresql') = 0;
--OUTPUT

T_Count
———–
0

(1 row(s) affected)

The expected result is 0 because ‘/raresql’ doesn’t exist in the @xmltest and as expected the query returned 0 as well.

Conclusion :
The behavior issue with exist() function on xml datatype is fixed in SQL Server 2012. If the SQL Server is upgraded from earlier version to 2012 and if some scripting is added to correct this behavior in the earlier version, then these scripts should be removed.

Reference : MSDN

Read Full Post »

How to separate date and time from DateTime ? I came across this question many times in multiple blogs.

Given below are different methods to separate date and time from datetime which are compatible with different versions. Make sure you are using correct method in your SQL Server version.

Mehtod 1 :
This method will work under SQL Server 2000 and above.

Declare @Datetime as datetime
Set @Datetime =getdate()
Select @Datetime as [Datetime]
Select Convert(varchar(10),@Datetime,120) as [Date]
Select Convert(varchar(20),@Datetime,114) as [Time]
--OUTPUT

Datetime
———————–
2013-03-18 22:02:02.960

(1 row(s) affected)

Date
———-
2013-03-18

(1 row(s) affected)

Time
——————–
22:02:02:960

(1 row(s) affected)

Mehtod 2 :
This method will work under SQL Server 2008 and above.

Declare @Datetime as datetime
Set @Datetime =getdate()
Select @Datetime as [Datetime]

Select Convert(Date,@Datetime) as [Date]
Select Convert(Time,@Datetime) as [Time]
--OUTPUT

Datetime
———————–
2013-03-18 22:04:24.930

(1 row(s) affected)

Date
———-
2013-03-18

(1 row(s) affected)

Time
—————-
22:04:24.9300000

(1 row(s) affected)

Mehtod 3 :
This method will work in SQL Server 2012 and above.

Declare @Datetime as datetime
Set @Datetime =getdate()
Select @Datetime as [Datetime]

Select Format(@Datetime,'yyyy-mm-dd') as [Date]
Select Format(@Datetime,'hh:mm:ss tt') as [Time]
--OUTPUT

Datetime
———————–
2013-03-18 22:14:23.763

(1 row(s) affected)

Date
———————–
2013-14-18

(1 row(s) affected)

Time
———————–
10:14:23 PM

(1 row(s) affected)

Conclusion :
I have mentioned 3 different methods to separate date and time from datetime but if you have SQL Server 2008 and above, then I would recommend Method 2.The reason behind this recommendation is in Method 2 we just convert datetime into a date type and time type but in other methods we first convert datetime into date type and time type and then give a style as well.

Read Full Post »

sys.dm_os_sys_info is one of the handy dynamic management views when it comes to query about the miscellaneous information of computer, resources & consumption by SQL Server. sys.dm_os_sys_info is with SQL Server family since SQL Server 2005, but whenever there is a new version of SQL Server, it always brings modification / additional helpful information in this view. But this modification / addition may  break your code if you don’t consider these changes with respect to versions. To avoid any such errors, I have prepared a comparison sheet of different versions of sys.dm_os_sys_info along with the changes to make life far easier.

Given below is the screen image of the comparison sheet. (Also attached Excel file for version 2007 & above):

sys.dm_os_sys_info.1.1

Reference : MSDN

Read Full Post »

Performance plays a vital role in SQL server. The importance of performance increases with the rise in the data growth. In other words, increase in data growth is directly proportional to the increase in performance. In SQL Server, index plays an important role to increase the performance as well. In SQL server 2012, we got a boost in performance (a new type of index came) namely ColumnStore Index. It is one of the nicest database features introduced in SQL Server 2012 built on xVelocity technology which is used to optimize memory. Lets discuss its syntax , purpose, examples & performance.

Syntax:
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name

ON ( column [ ,…n ] )

[ WITH ( [ ,…n ] ) ]

[ ON {

{ partition_scheme_name ( column_name ) }

| filegroup_name

| “default”

}

]

[ ; ]

::=
{
[database_name. [schema_name ] . | schema_name . ]
table_name
{

::=
{
DROP_EXISTING = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
}

Purpose :

The purpose of ColumnStore Index is to increase the performance in comparison to the traditional Index. It is a number of times faster than traditional index. The reason behind its performance is the way it handles the index. In Tradition Index, it stores multiple rows in each page and it retrieves in the same way. In ColumnStore Index, it stores each column in separate page and it retrieves in the same way.
In simple words you can say Tradition Index is a Row store while ColumnStore Index is a Column store.

Graphical Representation:
Given below is the graphical representation of traditional index & ColumnStore Index and you can see how each technology keeps the data pages in a different manner.

columnstoreindex1.1

Let’s test the performance of ColumnStore index Step by Step.

Step 1 :
Create a sample table.

CREATE TABLE dbo.[PurchaseOrderDetail_Sample](
[PurchaseOrderID] [int] NOT NULL,
[PurchaseOrderDetailID] [int] NOT NULL,
[DueDate] [datetime] NOT NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[LineTotal] numeric(18,2),
[ReceivedQty] [decimal](8, 2) NOT NULL,
[RejectedQty] [decimal](8, 2) NOT NULL,
[StockedQty] Numeric(18,2),
[ModifiedDate] [datetime] NOT NULL)

Step 2 :
Insert some data into table (PurchaseOrderDetail_Sample), because if table has few records you cannot measure the performance of columnstore index properly.

--This insertion is just to demonstrate,
--It is not recommended on production server.
Insert into dbo.[PurchaseOrderDetail_Sample]
Select * from [Purchasing].[PurchaseOrderDetail]
GO 100

Step 3 :
Next step is to create a Columnstore index in this table.

CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_PurchaseOrderDetail_Sample_ColumnStore]
ON [PurchaseOrderDetail_Sample]
(UnitPrice, OrderQty,ReceivedQty,ProductID)
GO

Step 4 :
Now, it is time to create a query using this table and view the performance. Remember this query has a ColumnStore Index.

SET STATISTICS TIME ON
SET STATISTICS IO ON
--This query will use columnstore index and will return the result set.
SELECT ProductID as [Product ID], AVG(UnitPrice) as [Average Unit Price]
,SUM(OrderQty) as [Purchase Order Qty], AVG(ReceivedQty) as [Received Qty]
FROM [dbo].[PurchaseOrderDetail_Sample]
GROUP BY ProductID
ORDER BY ProductID

go
--This query will ignore columnstore index and will return the result set.
SELECT ProductID as [Product ID], AVG(UnitPrice) as [Average Unit Price]
,SUM(OrderQty) as [Purchase Order Qty], AVG(ReceivedQty) as [Received Qty]
FROM [dbo].[PurchaseOrderDetail_Sample]
GROUP BY ProductID
ORDER BY ProductID
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

Step 5 :
Lets compare the result of IO & Time Statistics for both, with columnstore index and without columnstore index.

Type

Logical Reads

CPU Time

Elapsed Time

ColumnStore

34

47 ms

342 ms

Without ColumnStore

8345

1482 ms

1473 ms

ColumnStore1.2

Execution Plan comparison :

ColumnStore1.3

Index Scan comparison :
ColumnStore1.4

Conclusion :
I am quite impressed to see the performance of Columnstore index. It is very handy and recommended in a place where you are either facing performance issues with the query or dealing with data warehouse. However, Columnstore index has some limitations as well which I will post in my upcoming post.

Read Full Post »

sp_describe_first_result_set cannot be invoked when SET STATISTICS XML or SET STATISTICS PROFILE is on is one of the new error messages shipped with SQL Server 2012. This error message is related to sp_describe_first_result_set , a new system stored procedure shipped with SQL Server 2012 as well.

Let’s discuss this in detail:
Message Number: 11541

Severity : 16

Error Message: sp_describe_first_result_set cannot be invoked when SET STATISTICS XML or SET STATISTICS PROFILE is on

Error Generation:

Given below is the script that I tried to execute but it gave me this error.

Use AdventureWorks2012
GO
Set Statistics XML ON
Set Statistics Profile ON

EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vEmployeeDepartment];'
,@params=NULL,@browse_information_mode=0
GO

Msg 11541, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
sp_describe_first_result_set cannot be invoked when SET STATISTICS XML or SET STATISTICS PROFILE is on.

Ooopps…… I am unable to execute it.

Msg 11541.1.1

Resolution:
Sometimes , you don’t even see given below two lines but may be you turn it ON  for another query and forget to turn it OFF.

Set Statistics XML ON
Set Statistics Profile ON

What all you need to do is just switch off XML and profile statistics. Given below is the script.

Use AdventureWorks2012
GO
Set Statistics XML OFF
Set Statistics Profile OFF
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vEmployeeDepartment];'
,@params=NULL,@browse_information_mode=0
GO
--OUTPUT

Msg 11541.1.2

Conclusion :
Remember, whenever you use sp_describe_first_result_set, make sure XML and profile statistics is turned OFF.

Read Full Post »

I am writing this article in response to one of my junior database developer’s questions, how to insert Arabic data in SQL table, as he had inserted few Arabic names in the table. However, once he selected the data after insertion he found ????? instead of Arabic text in the field. This is a general problem with developers when they work for multi lingual (other than English) environment.

Lets generate this issue step by step.
Step 1 :
First create a table to demonstrate it.

Create table test
(
[Employee ID] int identity(1,1),
[Employee Name] varchar(50)
)

Step 2 :
Insert Arabic text (any non-English text) into the table.

Insert into test ([Employee Name]) values('عمران')

Step 3 :
Browse the data from table.

Select * from test
--OUTPUT

insertarabictext1.1
Oooops, it became garbage (?????) 

Resolution :
Lets resolve it step by step.

Step 1 :
As you can see, the [Employee Name] (Column) has a data type of varchar, so you need to change it to nvarchar data type. Given below is the script to change it.

Alter table dbo.test Alter column [Employee Name] nvarchar(50)

Step 2 :
Insert the same record again with additional N before name.

Insert into test ([Employee Name]) values(N'عمران')
--OUTPUT

insertarabictext1.2

Step 3 :
Browse the data from table.

Select * from test
--OUTPUT

insertarabictext1.3

Conclusion :
Whenever you insert any language (other than English) text into SQL table you must check two things :

  • Data type of the field must be nvarchar.
  • Insert N before the text.

Read Full Post »

DATABASEPROPERTY (metadata function) is being discontinued since the emerge of SQL Server 2012 and it is replaced by another metadata function namely DATABASEPROPERTYEX. Both functions take parameters (database name, property name) and return property value on the basis of input parameters. But the major difference between both functions is the return type. DATABASEPROPERTY returns only int datatype while DATABASEPROPERTYEX returns the sql_variant datatype. In simple words, you cannot expect any other data type than int from DATABASEPROPERTY.
I did some research and found some additional difference that I would like to share.

Given below are the scripts, that is written for DATABASEPROPERTYEX & DATABASEPROPERTY to compare both meta data functions.

--Code for DATABASEPROPERTYEX
SELECT DATABASEPROPERTYEX('AdventureWorks2012','Collation')
AS [Collation],
DATABASEPROPERTYEX('AdventureWorks2012','ComparisonStyle')
AS [ComparisonStyle],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiNullDefault')
AS [IsAnsiNullDefault],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiNullsEnabled')
AS [IsAnsiNullsEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiPaddingEnabled')
AS [IsAnsiPaddingEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiWarningsEnabled')
AS [IsAnsiWarningsEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsArithmeticAbortEnabled')
AS [IsArithmeticAbortEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoClose')
AS [IsAutoClose],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoCreateStatistics')
AS [IsAutoCreateStatistics],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoShrink')
AS [IsAutoShrink],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoUpdateStatistics')
AS [IsAutoUpdateStatistics],
DATABASEPROPERTYEX('AdventureWorks2012','IsCloseCursorsOnCommitEnabled')
AS [IsCloseCursorsOnCommitEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsFulltextEnabled')
AS [IsFulltextEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsInStandBy')
AS [IsInStandBy],
DATABASEPROPERTYEX('AdventureWorks2012','IsLocalCursorsDefault')
AS [IsLocalCursorsDefault],
DATABASEPROPERTYEX('AdventureWorks2012','IsMergePublished')
AS [IsMergePublished],
DATABASEPROPERTYEX('AdventureWorks2012','IsNullConcat')
AS [IsNullConcat],
DATABASEPROPERTYEX('AdventureWorks2012','IsNumericRoundAbortEnabled')
AS [IsNumericRoundAbortEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsParameterizationForced')
AS [IsParameterizationForced],
DATABASEPROPERTYEX('AdventureWorks2012','IsQuotedIdentifiersEnabled')
AS [IsQuotedIdentifiersEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsPublished')
AS [IsPublished],
DATABASEPROPERTYEX('AdventureWorks2012','IsRecursiveTriggersEnabled')
AS [IsRecursiveTriggersEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsSubscribed')
AS [IsSubscribed],
DATABASEPROPERTYEX('AdventureWorks2012','IsSyncWithBackup')
AS [IsSyncWithBackup],
DATABASEPROPERTYEX('AdventureWorks2012','IsTornPageDetectionEnabled')
AS [IsTornPageDetectionEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','LCID')
AS [LCID],
DATABASEPROPERTYEX('AdventureWorks2012','Recovery')
AS [Recovery],
DATABASEPROPERTYEX('AdventureWorks2012','SQLSortOrder')
AS [SQLSortOrder],
DATABASEPROPERTYEX('AdventureWorks2012','Status')
AS [Status],
DATABASEPROPERTYEX('AdventureWorks2012','Updateability')
AS [Updateability],
DATABASEPROPERTYEX('AdventureWorks2012','UserAccess')
AS [UserAccess],
DATABASEPROPERTYEX('AdventureWorks2012','Version')
AS [Version]
--Code for DATABASEPROPERTY
SELECT DATABASEPROPERTY('AdventureWorks2012','Collation')
AS [Collation],
DATABASEPROPERTY('AdventureWorks2012','ComparisonStyle')
AS [ComparisonStyle],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiNullDefault')
AS [IsAnsiNullDefault],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiNullsEnabled')
AS [IsAnsiNullsEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiPaddingEnabled')
AS [IsAnsiPaddingEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiWarningsEnabled')
AS [IsAnsiWarningsEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsArithmeticAbortEnabled')
AS [IsArithmeticAbortEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAutoClose')
AS [IsAutoClose],
DATABASEPROPERTY('AdventureWorks2012','IsAutoCreateStatistics')
AS [IsAutoCreateStatistics],
DATABASEPROPERTY('AdventureWorks2012','IsAutoShrink')
AS [IsAutoShrink],
DATABASEPROPERTY('AdventureWorks2012','IsAutoUpdateStatistics')
AS [[IsAutoUpdateStatistics]],
DATABASEPROPERTY('AdventureWorks2012','IsCloseCursorsOnCommitEnabled')
AS [IsCloseCursorsOnCommitEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsFulltextEnabled')
AS [IsFulltextEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsInStandBy')
AS [IsInStandBy],
DATABASEPROPERTY('AdventureWorks2012','IsLocalCursorsDefault')
AS [IsLocalCursorsDefault],
DATABASEPROPERTY('AdventureWorks2012','IsMergePublished')
AS [IsMergePublished],
DATABASEPROPERTY('AdventureWorks2012','IsNullConcat')
AS [IsNullConcat],
DATABASEPROPERTY('AdventureWorks2012','IsNumericRoundAbortEnabled')
AS [IsNumericRoundAbortEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsParameterizationForced')
AS [IsParameterizationForced],
DATABASEPROPERTY('AdventureWorks2012','IsQuotedIdentifiersEnabled')
AS [IsQuotedIdentifiersEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsPublished')
AS [IsPublished],
DATABASEPROPERTY('AdventureWorks2012','IsRecursiveTriggersEnabled')
AS [IsRecursiveTriggersEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsSubscribed')
AS [IsSubscribed],
DATABASEPROPERTY('AdventureWorks2012','IsSyncWithBackup')
AS [IsSyncWithBackup],
DATABASEPROPERTY('AdventureWorks2012','IsTornPageDetectionEnabled')
AS [IsTornPageDetectionEnabled],
DATABASEPROPERTY('AdventureWorks2012','LCID')
AS [LCID],
DATABASEPROPERTY('AdventureWorks2012','Recovery')
AS [Recovery],
DATABASEPROPERTY('AdventureWorks2012','SQLSortOrder')
AS [SQLSortOrder],
DATABASEPROPERTY('AdventureWorks2012','Status')
AS [Status],
DATABASEPROPERTY('AdventureWorks2012','Updateability')
AS [Updateability],
DATABASEPROPERTY('AdventureWorks2012','UserAccess')
AS [UserAccess],
DATABASEPROPERTY('AdventureWorks2012','Version')
AS [Version]

Given below is the output of both meta data functions and you can see that DATABASEPROPERTY will reveal the correct result whenever the return type of property value is int but on the other hand DATABASEPROPERTYEX will give you the result of any property value because its return type is sql_variant.
DatabaseProperty

Conclusion:
DATABASEPROPERTYEX is one of the handy metadata functions when we need to retrieve the property value on the basis of property and database name.
It also provides an additional info than DATABASEPROPERTY. Remember, DATABASEPROPERTY is discontinued in SQL Server 2012, so don’t use it in further developments. Also if you upgrade SQL Server earlier version to SQL Server 2012, change DATABASEPROPERTY to DATABASEPROPERTYEX in the script.

MSDN : Reference

Read Full Post »

In my earlier article, I wrote about different aspect of Concat function introduced in SQL Server 2012. In this article, we will compare the Concat function with the traditional concatenation techniques and analyze the performance.

Method 1 : Using ISNULL With + (String Concatenation)

Use AdventureWorks2012
GO
Select
ISNULL([firstName],'')
+ ' '
+ ISNULL([MiddleName],'')
+ ' '
+ ISNULL([LastName],'') from [HumanResources].[vEmployee]

Method 2 : Using COALESCE With + (String Concatenation)

Use AdventureWorks2012
GO
Select
COALESCE([firstName],'')
+ ' '
+ COALESCE([MiddleName],'')
+ ' '
+ COALESCE([LastName],'')
from [HumanResources].[vEmployee]

Method 3 : Using Concat function of SQL Server 2012

Use AdventureWorks2012
GO

Select
CONCAT (
[firstName]
, ' '
, [MiddleName]
, ' '
, [LastName]) from [HumanResources].[vEmployee]

All of the above methods will give you the same result sets but lets view their performance given below.

Method

Concatenation expression

CPU Time

Elapsed Time

1

ISNULL([firstName],”) + ‘ ‘+ ISNULL([MiddleName],”) + ‘ ‘ + ISNULL([LastName],”)

141 ms

2234 ms

2

COALESCE([firstName],”) + ‘ ‘ + COALESCE([MiddleName],”)  + ‘ ‘ + COALESCE([LastName],”)

187 ms

3185 ms

3

CONCAT( [firstName], ‘ ‘, [MiddleName], ‘ ‘, [LastName])

94 ms

1821 ms

Conclusion :
It is needless to stress that Concat function is much faster than the other methods.

Note : The above queries have been tested on ~100K records.

Read Full Post »

I had to migrate data from legacy system to new system and I found that we have some fields having alphanumeric data along with special character. The target was to extract the alpha numeric data from string. So, I started with web research and found few solutions but most of them are cursor based but I would like to develop it without cursor to improve the performance.

Given below is the solution.

CREATE FUNCTION dbo.[UDF_Extract_Alphanumeric_From_String]
(
@String VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

; WITH  N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2(n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3(n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4(n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_STRING=ISNULL(@RETURN_STRING,'')+ SUBSTRING(@String,Nums.n,1)
FROM N4 Nums
WHERE Nums.n<=LEN(@String) AND PATINDEX('%[0-9A-Za-z ]%',SUBSTRING(@String,Nums.n,1))>0

RETURN @RETURN_STRING
END

GO
SELECT dbo.[UDF_Extract_Alphanumeric_From_String] ('This! is a t_est s/tring a_t ra.re:s;ql') as [Result]
--OUTPUT

Result
————————————-
This is a test string at raresql

(1 row(s) affected)

Read Full Post »

LOG function is used to calculate the natural logarithm of a specified float expression and it is part of SQL Server since earlier versions of SQL Server. But in SQL Server 2012, this function is being modified and became more flexible. In the earlier versions of SQL Server, the base of log function was approximately 2.718281828 or EXP(1) and it is the default value (not editable). But in SQL Server 2012, you are allowed to set your own base value. Lets discuss its syntax and examples.

Syntax

--In SQL Server earlier versions
LOG ( float_expression )

--In SQL Server 2012
LOG ( float_expression [, base ] )

Let me explain it with simple examples :

Example 1 :
In the example 1, use LOG function with default base value.

DECLARE @variable float;
SET @variable = 10;
SELECT LOG(@variable) as [LOG with Default base value]
GO
--OUTPUT

LOG with Default base value
—————————
2.30258509299405

(1 row(s) affected)

Example 2 :
In example 2, lets provide an input base and see the results.

DECLARE @variable float;
SET @variable = 10;
SELECT LOG(@variable,EXP(1)) as [LOG with defined base value]
GO
--OUTPUT

LOG with defined base value
—————————
2.30258509299405

(1 row(s) affected)

If you observe examples 1 & 2, the result is same in both examples because by default the base value of LOG function is EXP(1).

Example 3 :
In example 3, lets pass any other value than the EXP(1) (Default value of base) and observe the result.

DECLARE @variable float;
SET @variable = 10;
SELECT LOG(@variable,EXP(2)) as [LOG with defined base value]
GO
--OUTPUT

LOG with defined base value
—————————
1.15129254649702

(1 row(s) affected

Conclusion:
In SQL Server 2012, LOG function is more flexible and gives you more control on base value but still base value is optional. So if you don’t pass base value it takes the default value (EXP(1) or approximately 2.718281828)

Read Full Post »

« Newer Posts - Older Posts »