Wednesday, December 31, 2008

Default user of login mapped to role SQL Server Administrator

The post I m writing is slightly confusing. If a SQL server login is assigned to System Administrator Server role of SQL Server 2000, then it will have rights to perform any activity in SQL Server Installation. That is what, SQL Server says. This may be problematic some times.

 

Now, Lets say there is a login stcsa with mapped to role System Administrator. First, you login to ISQLW with login stcsa and create a table with name tbl_test as below.

 

create table tbl_test (t varchar(10))

 

Now, check the owner of tbl_test, it will be dbo not stcsa. Suprising Not? This is because stcsa is System Adminitrator. But, you can call the table as simply tbl_test without "dbo." in front of the object name. The following select query works.

 

select    * from            tbl_test

 

Now, lets say, you again created a table with same name but like below.

 

create table stcsa.tbl_test ( t varchar(200))

 

Then, there will be two tables with name tbl_test and owners as dbo and stcsa. Now, if you use simply tbl_test, which one object do you think will it call? No not, it will call dbo.tbl_test not stcsa.tbl_test despite you are logged in as stcsa. This is where problem comes. In normal case, if you are logged in with a sql login and that login is mapped with user of same name, then if you call sql objects without prefix username, then it will search for username.objectname.

 

I am not sure that whether this can be called a bug in SQL Server or not. I just want to specify that, it's always useful to call sql objects by using username.objectname. It becomes more useful, when the login used is mapped to the role like SQL Server Administrator.

 

Read more...

Friday, December 26, 2008

Remove Single Line Comments from SQL Server Procedure

If you follow this blog regularly, you would have noticed that I have written logic as well as code to remove multi-line comments from SQL Server Procedure. You also can follow the link to get to that. Here, my concern is to remove single line comment from SQL Server procedure. It's a much simple compared to removing multi-line comment.

 

Single line comment in SQL Server is preceded by double dash like "—". So, you just consider following two points.

1.      You need to remove the lines beginning with double dash.

2.      If a line contains double dash at the middle, then remove part of line after the double dash.

 

An example will clarify more on this. First, I created a test procedure with name sp_helptext.

 

create proc sp_proc1  

as   

begin   

 create table #tt  

 (  

  col1 varchar(200)  

 )  

 

-- insert into ##tt values ('test data 1')

insert into #tt values ('test data 2')  -- test comments

-- insert into ##tt values ('test data 3')

  -- test comments

 

select *

from #tt 

end   

 

Then, I created a temporary table with name #tbl_sp_text to populate the help text of procedure on that. Finally, I applied above mentioned logic to retrieve the lines without any single line comments. Here is the complete code.

 

create table #tbl_sp_text ( id int identity(1,1), sp_text varchar(8000))

insert into #tbl_sp_text

exec sp_helptext  sp_proc1 /* procedure name */

 

 

select      case when charindex('--',sp_text) > 0 then substring(sp_text,0,charindex('--',sp_text))

      else sp_text end sp_text

from  #tbl_sp_text

 

Read more...

Wednesday, December 17, 2008

How to calculate rank using SQL trick?

Suppose you have data as shown below in a table. It contains marks obtained by students in different subjects. Here, student_id and subject_id makes the primary key of the table.

 

student_id

subject_id

obtd_marks

rank

5

2

99.1

0

7

2

98.1

0

8

2

98.1

0

1

2

92.5

0

2

2

92.1

0

3

2

91.1

0

4

2

85.1

0

6

2

56.1

0

8

9

98.1

0

2

9

91.1

0

 

At first, rank is 0 for all. Now, you have to write a SQL query to rank the students on the basis of their obtained marks. Here is the query.

 

update      tbl_marks

set   rank  = (

      select      count('1')

      from  tbl_marks b

      where a.subject_id = b.subject_id

      and   b.obtd_marks > a.obtd_marks

      ) + 1

from  tbl_marks a

 

where tbl_marks is the name of table.

 

This query considers the case of multiple students having same marks giving them same rank. It also considers ranking of different subjects separately. The output thus becomes as shown below. Note that, student 7 and 8 have same rank in subject 2.

 

student_id

subject_id

obtd_marks

rank

5

2

99.1

1

7

2

98.1

2

8

2

98.1

2

1

2

92.5

4

2

2

92.1

5

3

2

91.1

6

4

2

85.1

7

6

2

56.1

8

8

9

98.1

1

2

9

91.1

2

 

Read more...

Tuesday, December 16, 2008

Easy way to search for database objects

 If you are a database administrator, you come across thousands of database objects like stored procedures, tables, views, functions, triggers, etc. So, it's not possible to remember name of each of these objects. So, how will you find out a particular object?

 

In SQL Server 2000, there is good search facility for searching database objects. It can be launched by pressing F4 in SQL Query Analyzer or from Tools -> Object Search -> New.

The screen shot of Object Search window is shown below.

 

 

You can type name of object or part of name of object included between % signs in Object name filed and check the type of objects like User table, Stored procedures and finally click Find Now to get the search results. The interesting thing in search result window is that you can right click in particular object and select option to delete or script object.

 

You can search for stored procedures and functions by using keyword sp_stored_procedures like given below. It works for current selected database only.

 

sp_stored_procedures '%rep_proc%'

 

This command when executed in query browser will list out stored procedures and functions with name containing word rep_proc. If sp_stored_procedures is executed alone without any arguments, then it will list out all stored procedures and functions of current database.

 

Also, you can search for tables and views by using keyword sp_tables like shown below. It also works for current selected database only.

 

sp_tables '%tmp%'

 

This command when executed in query browser lists out tables and views with name containing word tmp. If sp_tables is executed alone without any arguments, then it will list out all tables and views of current database.

 

Read more...

Monday, December 15, 2008

Small trick to speed up your select query

If you want to select data from a database table, but somebody else is inserting data in same table, then in general case, you will have to wait till the insert completes. Your query is deadlocked.

 

In many cases, the dead lock is justified. It is because, the select data will be more correct after the insertion completes. But it may not be the case always. If you want to select sales data from 2005 to 2007, but someone is inserting data of date 2008, then you will have nothing to do with what other is inserting. In such case, you can use nolock keyword of SQL Server, to perform select operation. It simply allows performing raw read in the table. It ignores locks and also does not lock the table. So, the select operation becomes faster.

 

It is used like this.

 

Select *

From    CSAVatInvoiceHdr a (nolock)

Where  csavatinvoicedt >= '2005-01-01'

And      csavatinvoicedt <= '2007-01-01'

Read more...

Friday, December 5, 2008

User Name does not show up in Task Manager

Through Task Manager, one can find out the services running on a windows machine. I usually use it to find out unnecessary processes and stop them or to find out the memory usage of a process.

 

Sometimes, may be due to some virus, username in Processes tab of Task Manager may not show up.

 

If so, then Go to Run -> Type Services.msc and press Enter -> Search for Terminal Process in right tab -> Right Click it and select Properties -> in Properties Tab set its start up type to Automatic and click Apply -> Click Start to start the process. This will show up the User Name in Task Manager.

 

Usually harmful processes run with username as that of windows user not as System or others. So, it is helpful to find out suspicious processes.

 

 

Read more...

Wednesday, December 3, 2008

Faster way to get rowcount on sql table

 

If you want to get the number of rows of a sql table, you would probably think of using count keyword of sql server. If "retailer" is a sql table, then it would look like this.

 

select            count(*)

from     retailer

 

It gives the result, but it would be costlier in case of CPU usage and time as it uses an aggregate function count.

The query can be optimized as follows.

 

select rowcnt

from sysindexes

where id = object_id('retailer')

and indid = 1

 

sysindexes is a system table that contains details of indexes and tabular objects (table, views). This query will have lesser cost compared to previous one so, results faster. It may be beneficial when there are millions of data in the table or when row count is to be obtained repeatedly.

 

 

Read more...

Monday, December 1, 2008

Comments in different Programming Languages

Comments are helpful medium for storing useful information in application codes while developing it. A successful software is not only represented by how well it does it task but also by how well it is documented and how well it's programming logic can be understood by a new comer. So, the software enhancements can be supported by new comer, which helps in long run of the software.

 

Without writing any further on its usefulness, I will directly move to the topic. There are two types of comments in programming languages. One is single line comment, which works for the line where it is given. It usually has starting mark but no ending mark. Other is multi-line comment, which works for until the closing mark is found.

 

Comment in C, C++, C#, PHP

A single line comment is preceded by double slash (//).  An example is shown below.

 

//function to reverse an array

Int reverse(int a[])

{

            //code goes her

}

No doubt, you can use double slash to comment multiple consecutive lines provided that each line is preceded by double slash (//).

Multi-line comment starts with single slash and an asterisk sign (/*) and ends with (*/).

 

Int reverse(int a[])

/*function to

            reverse an array */

 

This commenting style is applicable for many programming languages.

 

Comment in VB, ASP, VBScript

Single line comment is preceded by single apostrophe (').

 

Comment in SQL Server

Single line comment is preceded by double dash (--) while multi-line comment is represented by text between (/*) and (*/) marks.

If you give comment inside comment, then also whole thing is represented as comment.

 

Comment in HTML

Multi-line comment begins with (<!--) and ends with (--!>). An example is shown below.

<!--<div id="footer">                   

                    ©2008 Aptech Suraj Lubhu

                </div>-->

 

Comment in JavaScript

Comment in JavaScript is similar as in C and C++. Single line comment is preceded by double slash (//) while multi-line comment begins with (/*) and ends with (*/) marks.

 

Comment in CSS Style Sheets

Multi-line comments in CSS are preceded by (/*) and followed by (*/).

 

Read more...