What business says does not mean what businees wants

This great sentence I learned form my exerience being a consultant

What business says does not mean what businees wants

TechEd 2010 in Eilat

Hi
I am going to attend TechEd 2010 in Eilat next week. It is great opportunity to learn new things , meet new and old friends.It is my second TechEd and I am will be focusing on Data Platform direction and BI. Hope to see you there.

http://www.microsoft.com/israel/TechEd2010/Tracks/BI.aspx

Blobs and covering indexes

Nowadays it is common to store/save pictures,documents in the databbase. Since SQL Server 2005 we use VARBINARY(MAX) datatype to store such data. The 'problem' I have seen recently at the client database is SELECT statement on very huge table that contain BLOB data and that data needs to be return to the client works pretty slowly. Run the below
SET STATISTICS IO ON

SELECT col1,col2, blobdata FROM tbl WHERE....

Table 'tbl'. Scan count 1, logical reads 8125, physical reads 0, read-ahead reads 0, lob logical reads 261521, lob physical reads 0, lob read-ahead reads 0.
CPU time = 1252 ms, elapsed time = 12632 ms.

We see high number of reads to return the data from index/blob pages.

Execution plan shows that there is Bookmark to return the BLOB data from Clustered Index Key because of our NCI(NonClustered Index) does not cover all columns in SELECT statement.
Ok, at first glance you would re-create NCI to INCLUDE blobdata column to 'cover' SELECT ...Well, I noticed that recreating NCI index takes long long time and LOG file was grown dramatically. I see that after INCLUDE blobdata column I reduced logical reads as shown below

Table 'tbl'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 421521, lob physical reads 0, lob read-ahead reads 0.
CPU time = 1622 ms, elapsed time = 16512 ms.

But what happened? BLOB page reads were done almost 1.8 times more with covering index and actually I have not seen much improvment in performance of the query.
So finally, if you create an index or INCLUDE the BLOBs SQL Server create copy of that index for every blob column and thus it takes time to create index and reading the data from index page. Well, you were able to save some IO by covering BLOB column but internally SQL Server works much hardly to return the data and maintain the index.

Sometimes it is important to choose right collation for the server

The title looks like a joke, am I right? Sometimes we just take care of collation for specific database. Well that may lead you to unpleasant situation and moreover getting wrong or no data at all. Recently I helped my friend to solve the problem for his new software. He created a database with "right" collation let say Hebrew_CI_AS and the program searches for the data that contains hebrew characters. Suddenly the program does not return the data at all. He showed me that the data he is looking for are presented in the table and point me to the stored procedure that performs the searching. You have already guessed what was the problem, right? The problem was because withing a stored procedure he used a temporary table where the programm stores the data for final result (VARCHAR(n) for search string) for some logic. As you know tempdb is a system database which inherits a server's collation (SQL_Latin1_General_CP1_CI_AS) and hence no data were returned. In that specific case we changed the data type to NVARCHAR(n) and there is no need to reinstall the entire server, but think about the program(I have seen many of such) that such technique (store unicode character in temporary tables) are used in hundred of stored procedures and change the datatype for all of them is very big headache.

Window Functions (OVER Clause) – Help Make a Difference

Hello friends

As you probably know those days SQL Server 2011 is being developed. Itzik Ben-Gan believes (me too) that having those new window functions and enhance existing ones will benefit all of us SQL Server developers in the future more complete support for the standard window functions in SQL Server. Please read and vote..

http://www.sqlmag.com/blogs/puzzled-by-t-sql/tabid/1023/entryid/13085/Window-Functions-OVER-Clause-Help-Make-a-Difference.aspx

Be careful to grant dbCreator server role to the user

It is common that vendors ask for permission to create databases (or they applications need) on your servers and most of DBAs I have seen immediately grant them dbCreator server role. But they are not aware that members of that role are able to DROP/ALTER any databases on the entire server regardless of whether or not you even have a user account in the database.Did you really want that??

The right approach is to grant CREATE ANY DATABASE permission and then the user is able to DROP/ALTER he/she owns.

Another way to execute stored procedure

Just "discovered" that if you right click on the stored procedure you can clcik "Execute Stored Procedure" option. It gives you friendly GUI to provide parameters,however,as a data professional, I prefer to execute stored procedures in query builder. But it is nice feature for less experienced users.

Find dependency task again

If you have ever dealt with projects where you need to script out the objects and the recreate them on the destination database you first always have been thinking of sys.sql_dependencies system table (if you are on SQL Server 2005 and onwards) . At our company we have a project to create script on many client machines that have variuos versions of SQL Server Express Edition from SQL Server 2000 to SQL Server 2008 R2. The team has automated script that creates all objects on the client by using old syscomments table. They could not rely on sys.dependencies table because it has some critical bugs as you are aware of. They did script out all views into a text file and run it for more than once because it is possoble that for the first run it is failed (if you have nested views.).It is very complicated script and in narrow down we can reproduce the problem as the following.

USE AdventureWorks
GO
CREATE TABLE t1 (c INT)
GO
CREATE VIEW v1
AS
SELECT c FROM v2
GO
/*

Msg 208, Level 16, State 1, Procedure v1, Line 3
Invalid object name 'v2'
*/
CREATE VIEW v2
AS
SELECT * FROM t1
--Clean up
DROP VIEW v1,v2
DROP TABLE t1

It worked well for a while but very slowely and they look for improvment. I would like to share with you very reliable and fast option developed by colleague of mine by using VB.NET. Please test it before running on production server.

This script reads the view definition, find the level of dependecy, and then set priority to create.

Var
_dsV – Data table of views and column of “PL” – Place or priority
_dsD - data table of dependences (view on view )
_dsC –context of views


Function
CreateDependence(_dsV.Tables(0), _dsC.Tables(0), _dsD) - find on context of view if exist other views
CheckChildViews(_dsV.Tables(0), _dsD, _dsD.Select(), 0) - recursive function which set priority of views.
On the start all view with priority 1000 (it’s don’t have dependence) . When view has some dependence then it gets priority = 0 or parent priority + 1. All child views (nested view) get higher among (parent priority +1) or its priority.
GetViews(_dsV.Tables(0), _dsC.Tables(0)) - get all view by sort of priority descending


Private Sub CreateViews()
Dim cldb As clDB ‘ class - connection to database
Dim _dsV As DataSet
Dim _dsD As New DataTable("DEP")
Dim _dsC As DataSet
lblResultSqlScript.Text = ""
Try
cldb = New clDB
cldb.ConnectionString = conectionString
Dim tmp As String = ""
'all views
tmp = " SELECT ID,NAME,1000 as PL "
tmp &= " FROM dbo.sysobjects "
tmp &= " WHERE xtype = 'V' and category=0 "
tmp &= " order by name " & vbNewLine
_dsV = cldb.GetDataSet(tmp)

'context of views
tmp = " SELECT text ,COLID,ID "
tmp &= " FROM syscomments "
tmp &= " ORDER BY COLID " & vbNewLine
_dsC = cldb.GetDataSet(tmp)

'depandences
CreateDependence(_dsV.Tables(0), _dsC.Tables(0), _dsD)

ClearIndex(_dsV.Tables(0), _dsD)
CheckChildViews(_dsV.Tables(0), _dsD, _dsD.Select(), 0)
-----GetViews(_dsV.Tables(0), _dsC.Tables(0))
RunViews (_dsV.Tables(0), _dsC.Tables(0))
Catch ex As System.Exception
Finally
cldb = Nothing
End Try
End Sub


Private Sub CreateDependence(ByVal dsV As DataTable, ByVal dsC As DataTable, ByRef dt As DataTable)
'create datatable
Dim cl1 As New DataColumn("PNAME", System.Type.GetType("System.String"))
Dim cl2 As New DataColumn("PID", System.Type.GetType("System.Int32"))
Dim cl3 As New DataColumn("CNAME", System.Type.GetType("System.String"))
Dim cl4 As New DataColumn("CID", System.Type.GetType("System.Int32"))
dt.Columns.Add(cl1)
dt.Columns.Add(cl2)
dt.Columns.Add(cl3)
dt.Columns.Add(cl4)
Dim cur As Integer = -1
Dim tmp As String = ""

'load view
Dim bs As New Text.StringBuilder
For Each ddr As DataRow In dsV.Select("", "PL DESC")
Try
bs.Length = 0
For Each dr As DataRow In dsC.Select("ID=" & ddr("ID"), "COLID")
Try
bs.Append(dr("TEXT").ToString)
Catch ex As System.Exception
End Try
Next
'find dependence
'loop on all view without current
For Each ddr1 As DataRow In dsV.Select("ID<>" & ddr("ID"), "PL DESC")
cur = bs.ToString.IndexOf(ddr1("NAME").ToString)
If cur > -1 Then
'check if it's real name and not peace of name
tmp = bs.ToString.Substring(cur + ddr1("NAME").ToString.Length, 1)
If tmp = "" Or tmp = "." Or tmp = " " Or Asc(tmp) = 13 Then
'add to dependence
Dim newdr As DataRow = dt.NewRow
newdr("PNAME") = ddr("NAME")
newdr("PID") = ddr("ID")
newdr("CNAME") = ddr1("NAME")
newdr("CID") = ddr1("ID")
dt.Rows.Add(newdr)
End If
End If
Next
Catch ex As System.Exception
End Try
Next
End Sub
Private Sub GetViews(ByVal dsV As DataTable, ByVal dsC As DataTable)

txtSQLScript.Text = ""
Dim bs As New Text.StringBuilder
bs.Append(" SET QUOTED_IDENTIFIER OFF " & vbNewLine)
bs.Append(" GO " & vbNewLine)
bs.Append(" SET ANSI_NULLS OFF " & vbNewLine)
bs.Append(" GO " & vbNewLine)
For Each ddr As DataRow In dsV.Select("", "PL DESC")
bs.Append(" IF EXISTS (select * from dbo.sysobjects where id = object_id(N'" & ddr("NAME") & "') and OBJECTPROPERTY(id, N'IsView') = 1)" & vbNewLine)
bs.Append(" DROP VIEW " & ddr("NAME") & vbNewLine)
bs.Append(" GO " & vbNewLine)

For Each dr As DataRow In dsC.Select("ID=" & ddr("ID"))
Try
bs.Append(dr("TEXT").ToString)

Catch ex As System.Exception
End Try
Next
bs.Append(vbNewLine & " GO " & vbNewLine)
bs.Append(vbNewLine & "--------------------------------" & vbNewLine)
Next
txtSQLScript.Text = bs.ToString
End Sub
Private Sub ClearIndex(ByRef dsV As DataTable, ByVal dsd As DataTable)
For Each ddr As DataRow In dsV.select
If dsd.Select("PID=" & ddr("ID").ToString).Length > 0 Then
ddr("PL") = 0
End If
Next
End Sub

Private Sub CheckChildViews(ByRef dsV As DataTable, _
ByVal dsd As DataTable, _
ByVal dr() As DataRow, ByVal index As Integer)
For Each ddr As DataRow In dr
Try
Dim pd As DataRow = dsV.Select("ID=" & ddr("PID").ToString)(0)
If pd("PL") < index Then pd("PL") = index
CheckChildViews(dsV, dsd, dsd.Select("PID=" & ddr("CID").ToString), index + 1)
Catch ex As System.Exception
End Try
Next
End Sub

Private Sub RunViews(ByVal dsV As DataTable, ByVal dsC As DataTable)
txtSQLScript.Text = ""
Dim bs As New Text.StringBuilder
Dim cldb As New dbAdministration.clDB
For Each ddr As DataRow In dsV.Select("", "PL DESC")
bs.Length = 0
bs.Append(" IF EXISTS (select * from dbo.sysobjects where id = object_id(N'" & ddr("NAME") & "') and OBJECTPROPERTY(id, N'IsView') = 1)" & vbNewLine)
bs.Append(" DROP VIEW " & ddr("NAME") & vbNewLine)
cldb.Execute(bs.ToString()) 'run delete if exist view

bs.Length = 0

For Each dr As DataRow In dsC.Select("ID=" & ddr("ID"))
Try
bs.Append(dr("TEXT").ToString)
Catch ex As System.Exception
End Try
Next
cldb.Execute(bs.ToString()) 'run create view
Next
End Sub

Column level permission

Uh,it's so hard to back to work from the vacation:-).Yesterday,for the first time, I have been faced the requirement to implement a column level permission on the table, have you done something before? A little script below demonstrates it.

USE dbname;
CREATE TABLE demo (c INT, c2 INT)

GRANT SELECT (c) ON demo TO test


EXECUTE AS USER = 'test';

SELECT * FROM fn_my_permissions('dbo.demo', 'OBJECT')
ORDER BY subentity_name, permission_name ;

REVERT

PS: subentity_name tells you on what column given permission.

It's The Time To Go on Vacation

Yep, on 21st May I am going to Moscow to see a World Table Tennis Championship. I love playing table tennis and even took a part of two World Table Tennis Championships in earliest 90. So I hope to meet there may old friends from all around the world, so I am very exited about it.. See you later on.

Kalen Delaney's seminar

Just backed home from the three days of seminar in Tel-Aviv named "Deep dive into SQL Server Internals with Kalen Delaney".
Two days Kalen explained about SQL Server Query Plans,Tuning and Recompile techniques and the last one was about SQL Server Data Storage Formats.

You know , I thought I know a lot about SQL Server performnace tunning and etc, however, attending that seminar I just realized how much I need to learn. There are lot if tips,thoughts how to tune the system, indexes and etc.

I would also mention about how easy was to understand Kalen (she spoke english at seminar :-)) and her methods to provide courses which is so important for attendants.

Don't speak to the DBA while he/she is doing the job.

Our DBA was very busy today, helped out our developers top write efficient code,explained to the programmer how to launch the old DTS package on SQL Server 2005 and etc. You know how it is, someone comes in and asking the question and you answer the question at the same time you are doing the work.
So,today answering one such questions our DBA deleted by mistake very ctitical database. Fortunately , we had zero data loss thanks to our backup/recovery strategy.

I think that is acceptable to say that you are busy right now please ask the question later on, what do you think?

Getting column level permision report

If you think about giving column level permission , you'll probably create a view with only needed columns to expose to ,then grant SELECT permission on that view and absolutely DENY access to underlaying tables to the user, right? Also , it depends on the policy of the company but you are able to grant SELECT/UPDATE/DELETE/INSERT operations on column level, have you ever used it:-)? So I recently visited our client who does perfom security permission on column level and wanted to know what are the columns and on what tables that specific user has access? I decided to perform the below testing, see if that might help you.

CREATE TABLE demo (c INT, c2 INT)

/* Grant SELECT permission on column named c on demo table*/
GRANT SELECT (c) ON demo TO test


EXECUTE AS USER = 'test';

SELECT * FROM fn_my_permissions('dbo.demo', 'OBJECT')
ORDER BY subentity_name, permission_name ;

REVERT

Have you dealt with ENTER key in INSERT table?

I have a client who uses the application that allows to insert data but also typing ENTER key in the middle. Well, the question is how do you want to store the data and later displaying on the client? He was not sure , so I showed him the below technique to display such kind of data....


--Create auxiliary table
SELECT TOP 8000 n = IDENTITY(INT)
INTO Numbers
FROM syscolumns s1, syscolumns s2 ;

CREATE TABLE test (col1 varchar(8000))

INSERT INTO test values ( 'Row----------1
Row--------2
Row--3
Row----4'
)

---See how it stores the data.
SELECT * FROM TEST

--Remove ENTER key
SELECT SUBSTRING(col1, n, CHARINDEX(CHAR(13), col1 + CHAR(13), n) - n)
FROM Numbers, test
WHERE SUBSTRING(CHAR(13) + col1, n, 1) = CHAR(13)
AND n < LEN(col1) + 1 ;

--Cleanup
DROP TABLE Numbers
DROP TABLE test

I was invited to blog on www.sqlblog.com

A few days ago Adam Machanic who is the owner of that great web site invited me to join his great company. I am really excited about and proud to be part of such great company of friends who write very good stuff about SQL Server.
I would like to keep bloging here as well , I do not know how long it will take but you can always catch me up on http://sqlblog.com/blogs/uri_dimant/

Think before unchecking sysadmin rights of BUILTIN\Administrators.

I have recently met our client who uchecked the sysadmin rights of BUILTIN\Administrators group before given any permissions to anotgher account.

That was NOT such problem if the BUILTIN\Administrators group was removed from sysadmin role accidentally/by mistake, then you must login with another sysadmin login. If there is no other sysadmin login, you must login with SQL authentication as sa with the password that was set during setup to sa. Once logged in as a member of sysadmin, you are able to add BUILTIN\Admisnitrators back to sysadmin role.
However everything above does not work for the client. Uhhh,the client also disabled SA accoount as well as DAC connection.
Moreover, there is no domain controller where you can create a sysadmin domain acoount and grant the access to the machine running SQL Server,that was a stand alone computer with single instance installed on.

The solution we found was to start SQL Server with single user mode. Using the single-user mode, SQL Server 2005 prevents a Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches. To someone who is not familiar how to start the instance in single user mode and adding login to the server role being system administrator please read the below link describing step by step the procedure.

http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx