Systems Engineering and RDBMS

Archive for December, 2008

SQL Server 2008 Certification Paths

Posted by decipherinfosys on December 31, 2008

This post is also in reply to one of our reader’s question. The question was about the certifications that are available for SQL Server 2008 and the upgrade paths for those who are certified in SQL Server 2005.

MSFT has a complete SQL Server 2008 certification path pdf document on their site that you can download from here:

http://download.microsoft.com/download/6/3/7/6370c653-e270-4e23-9685-15043f63dcce/SQLServer2008_CertPath_Complete.pdf

This covers the Technology specialist paths, IT professional paths and also the upgrade paths.

Posted in SQL Server | Leave a Comment »

Getting all the alternate Mondays in the Year

Posted by decipherinfosys on December 30, 2008

Got an interesting question from one of our readers yesterday. They have project plans where they do releases on every other Monday – he wanted to know if there is a quick way in T-SQL to generate a list of all bi-weekly Mondays in a year.  He was referencing one of our earlier posts in which we had discussed how to generate dates between two date values and wanted to know if we can extend it to provide him an answer to this current question as well. It can definitely be done by making changes to that UDF but there is an even simpler way to achieve this.

We will make use of the CTE solution that we had discussed before in a couple of our posts in order to generate a number table.  Using the same solution, we can get the desired output by using this SQL:

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 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n%10) – 1 FROM N4 AS X, N4 AS Y)
select DATEADD(dd, n * 14, ‘20090105’) as Monday_Date
from N5
WHERE DATEADD(dd, n * 14, ‘20090105’) <= ‘20091231’

Or, if you use a number table in your environment then the above query could be based directly at the top of it.  That is all what the above CTE does – prepare a number table based off which we can write of the query to get the desired result.

And in the above query, you can easily substitute the values for the dates as parameters and make it generic for any year.

Posted in SQL Server | Leave a Comment »

Checking permissions for different logins in SQL Server 2005

Posted by decipherinfosys on December 29, 2008

SQL Server 2005 has two table valued functions called:

a) fn_my_permissions(securable, ‘securable_class’), and
b) has_perms_by_name(securable, securable_class, permission, sub-securable, sub-securable_class)

that can be used to check the different permissions of the users on a securable.  Securable is just term being used for the different objects like table, stored procedures, functions, views etc.. The securable_class are like these: ASSEMBLY, OBJECT, SERVER, DATABASE, XML Schema Collection etc.  You can look at all the securable classes by using this SQL:

SELECT distinct class_desc
FROM sys.fn_builtin_permissions(default)
ORDER BY class_desc
GO

And specifying the word default just translates the input to Null in these functions.  So, how and when can you make use of these functions?  These are helpful if you want to prepare a report for the permissions for different logins.  Here is an example:

Let’s see my login permissions first (my login is also part of the sysadmin role):

/*Show me permissions on the server*/

SELECT * FROM sys.fn_my_permissions(default, ‘SERVER’);

/*Now, show me permissions on the database*/

SELECT * FROM sys.fn_my_permissions(default, ‘DATABASE’);

/*Or a specific object*/

SELECT * FROM sys.fn_my_permissions(‘usp_collect_stats’, ‘OBJECT’);

And now, if I want to check the permissions for another login, all I need to do is switch the security context first by using “EXECUTE AS” and then execute the same commands as above:

EXECUTE AS LOGIN = ‘appuser’;
SELECT * FROM sys.fn_my_permissions(default, ‘DATABASE’);

And once I am done reviewing the output, I can use the “REVERT” command to revert back to my login for that session.

One can also obtain this information by making use of the catalog views:

select
dp.type_desc AS principal_type_desc,
dbp.class_desc,
OBJECT_NAME(dbp.major_id) AS object_name,
dbp.permission_name,
dbp.state_desc AS permission_state_desc
from    sys.database_permissions dbp
INNER JOIN sys.database_principals dp
on dbp.grantee_principal_id = dp.principal_id
where USER_NAME(dbp.grantee_principal_id) = ‘sysdba’

Posted in SQL Server | 3 Comments »

Simulating Row Value Constructor in versions prior to SQL Server 2008

Posted by decipherinfosys on December 26, 2008

This was another question from one of our readers: “This is in reference to your post on the new feature of row value constructor in SQL Server 2008. Is there a way to do this in SQL Server 2000 or SQL Server 2005?”.

The answer is Yes – you can do it by using: “Insert into…. SELECT …” statement.  Here is an example:

use deciphertest
go
create table dbo.decipher_test (col1 int, col2 int);

/*Normal way of inserting the data*/
insert into dbo.decipher_test values (1, 10);
insert into dbo.decipher_test values (2, 20);
insert into dbo.decipher_test values (3, 30);

/*Using row value constructor in SQL Server 2008*/
Insert into dbo.decipher_test (col1, col2) values (1, 10), (2, 20), (3, 30)

/*Another method which will work regardless of the version*/
insert into dbo.decipher_test (col1, col2)
select 1, 10
union all
select 2, 20
union all
select 3, 30

Posted in SQL Server | Leave a Comment »

Disabling a login account

Posted by decipherinfosys on December 26, 2008

One of the readers had asked us recently about how he can disable the sysadmin account in SQL Server since he did not want anyone to use the “sa” login account. This team has over 20+ DBAs/DB Developers in the team and as per him, even in the application connection strings, the teams were using the “sa” login to make the connections.

One can easily disable any account in SQL Server 2005 and above by using the ALTER LOGIN command:

If you want to disable the “sa” login account, make sure that you are logged in using windows authentication and also make sure that you have other accounts set up with sysadmin privs as well.

ALTER LOGIN [sa] DISABLE;

will disable the login and replacing DISABLE with ENABLE and executing it will enable it. This can be done via the GUI as well. If you right click on the login in SSMS, select properties and go to the last page called “Status”, over here you will see the option of enabling/disabling the login as shown in the image below:

login_ed_1

Posted in SQL Server | 2 Comments »

We Wish you a Merry Christmas

Posted by decipherinfosys on December 24, 2008

From all of us at Decipher Information Systems, we wish you and your loved ones a very Merry Christmas.  We wish that all your dreams and aspirations come true in a wonderful way this coming year…and always.

Best Wishes to all of you.

Posted in General | Leave a Comment »

More on Implicit Conversion issues

Posted by decipherinfosys on December 22, 2008

In one of our blog posts, we had covered how the implicit conversion creates an issue in the CASE statement. In that case, one actually catches it before it becomes an issue since it will give an error when you try to run it. Another issue that can arise is in scenarios like the one shown below:

/*Create a Dummy Table with 2 columns and create an index on the second column*/
create table dbo.testcase (col1 int identity, colx varchar(10))
go
create index testcase_ind_1 on testcase (colx)
go
/*Create a Million records*/
declare @i int
set @i = 1
while (@i <= 1000000)
begin
insert into dbo.testcase (colx) values (‘ABC’ + cast(@i as varchar(7)))
set @i = @i + 1
end
go

Now, check the execution plan of this code:

declare @par1 nvarchar(10)
set @par1 = N’ABC100000′
select * from dbo.testcase where colx = @par1

Note that the parameter has been declared as Nvarchar(10) instead of varchar(10) as it is in the table. Now, that value is unique so we are really selecting 1 single record out of 1 million and we even have an index on that filter column so it should jump right at it and use the index. However, it will have to do an implicit conversion in order to convert the varchar column into nvarchar and then do a comparison. When a function gets applied on the indexed column, the index will not get used as we have demonstrated before in our posts (unless you have a computed column which uses that function and then you have an index on that computed column).

Execution Plan:

StmtText
—————————————————————————————————————————————————
|–Table Scan(OBJECT:([DECIPHERTEST].[dbo].[testcase]), WHERE:(CONVERT_IMPLICIT(nvarchar(10),[DECIPHERTEST].[dbo].[testcase].[colx],0)=[@par1]))

If we were to declare the parameter as varchar(10), then this is what the execution plan would look like:

StmtText
——————————————————————————————————————————————————-
|–Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|–Index Seek(OBJECT:([DECIPHERTEST].[dbo].[testcase].[testcase_ind_1]), SEEK:([DECIPHERTEST].[dbo].[testcase].[colx]=[@par1]) ORDERED FORWARD)
|–RID Lookup(OBJECT:([DECIPHERTEST].[dbo].[testcase]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

with the seek operation done correctly. This kind of an issue would actually be a bug in the application but we did see this happening in one of the vendor applications at one of our client sites when a patch that was delivered was using unicode parameters against a non-unicode data type schema and all performance went down the drain after the patch install. A simple flag option was set wrongly in the configuration file of their install (application configuration file) which caused this so it was fixed easily but we wanted to post it out so that you are aware of this as well in case you ever face this.

In addition, other types of such implicit conversions can cause some serious performance issues so make sure that when doing joins or when you are applying filter conditions, implicit conversions do not take place.

Posted in SQL Server | Leave a Comment »

Check constraints and an overlooked issue

Posted by decipherinfosys on December 20, 2008

We have covered check constraints and their usefulness in some of our blog posts in the past. Ran into an issue at a client site the other day where the DBA had used a UDF (User Defined Function) in the check constraint and it was not doing what the DBA had thought that it would do. Before we present the issue, it is important to know that the check constraints in SQL Server are evaluated one row at a time so if you are planning to use a UDF which spans multiple rows and is based on SET logic, then you may not be getting the results that you thought you would.

Here is an example of t he issue that our client was facing:

CREATE TABLE dbo.GENERAL_LEDGER (GENERAL_LEDGER_ID INT IDENTITY PRIMARY KEY, AMT NUMERIC(7,2) NOT NULL DEFAULT 0, AP_AR BIT);
GO

The table is a simple General Ledger table with the amount and the AP/AR column flag.  Let’s create a function which checks to make sure that the amount is always 0 (picking up a hypothetical condition to show the point that we are trying to make here).

CREATE FUNCTION dbo.GL_Sum
()
RETURNS INT
AS
BEGIN
DECLARE @amt INT;
SELECT @amt = (SELECT SUM(AMT) FROM dbo.GENERAL_LEDGER);
RETURN @amt;
END
GO

Now, let us insert some data first:

INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR) VALUES (1000, 1);
INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR) VALUES (-1000, 2);
INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR) VALUES (10, 1);
INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR) VALUES (-10, 2);

And now, let us add a check constraint using the UDF that we created above.

ALTER TABLE dbo.GENERAL_LEDGER ADD CONSTRAINT CK_AMT CHECK (dbo.GL_SUM() = 0);

Now, let’s try to update all the records with a positive number hence violating the constraint:

UPDATE dbo.GENERAL_LEDGER SET AMT = abs(AMT)

And we will get this error:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint “CK_AMT”. The conflict occurred in database “DecipherTest”, table “dbo.GENERAL_LEDGER”.
The statement has been terminated.

And that is perfectly fine but imagine loading up data in bulk from another table by doing a “Insert into general_ledger () select … from” statement.  Let’s try to simulate that by trying to insert 2 records which will satisfy the business rule as well since we will have a +ve value in one and a -ve in the other one.

INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR)
SELECT 90, 1
UNION ALL
SELECT -90, 2

However, this time also it will give us the same error as before even though the business rule is not really violated since this is part of the same transaction.  So, the constraint is really getting checked per row rather than by one single unit of work.  This is just one of the issues that we have seen a couple of times at different client sites and hence wanted to bring it to the attention of our readers.  What happens then is that you end up with logic that does not work the way you thought it would.  If there is a need for a business rule like the one above, and you do want to handle it in the DB layer, you can do so by using a trigger.  Triggers are fired per statement and not per row.

Posted in SQL Server | Leave a Comment »

SQL Server 2005 SP3 released

Posted by decipherinfosys on December 18, 2008

The much awaited SP3 has been released for SQL Server 2005. You can download it from here. And the list of the bugs that are fixed in SP3 are listed in this KB article. And also, here is an excellent post by Aaron Bertrand giving more information on the service pack.

Posted in SQL Server | Leave a Comment »

Page Verification and Emergency State in SQL Server 2008

Posted by decipherinfosys on December 18, 2008

Prior to SQL Server 2008, we could do torn page detections in SQL Server by setting up the option for the database or also by using the ALTER DATABASE command. In SQL Server 2008, we can also have a checksum based data page verification done by using the same command. It can not only detect the failures that you can otherwisse detect with torn page detection but also hardware related failures which go un-noticed by the torn-page detection feature. But since it does all that, it is also more resource intensive. First, you should check whether anything is already set for the database – you can do so using this query:

select page_verify_option from sys.databases where name = ‘DecipherTest’;

If the value is 1 then it is set for torn page detection, if it is 2, then it is set for checksum and if it is 0, then nothing has been set. This should also tell you that you can have only either torn page set or the checksum option set.

In order to set the checksum option, you can use the ALTER DATABASE command:

ALTER DATABASE DECIPHERTEST SET PAGE_VERIFY CHECKSUM;

And if you want to set it to 0, you can just replace CHECKSUM with NONE in the command above. In addition to this, in SQL Server 2008, we can also set a database in an emergency state. It essentially makes the database read only and only the members of the sysadmin fixed server role can then access the database. It is especially useful if you have encountered a “suspect” database state before. You can now set it in an emergency mode and fix errors.  One can set it easily using the ALTER DATABASE command:

ALTER DATABASE DECIPHERTEST SET EMERGENCY;

And in order to turn the emergency state off, just execute:

ALTER DATABASE DECIPHERTEST SET ONLINE;

Posted in SQL Server | Leave a Comment »