TechFruits

SQL to find first and last day of a month

2009-04-27T13:14:00.006-04:00

How to get the first and last day of any month.

To get the first and last day of any month when given a date we can use the date functions
as below.

Suppose we want the first and last day for the date '04/28/2009', that is for April 2009.

declare @given_date datetime
select @given_date = '04/28/2009'

-- SQL to get the first day of the month
select dateadd(dd,-(day(dateadd(mm,1,@given_date))-1),dateadd(mm,0,@given_date))

-- SQL to get the last day of the month
select dateadd(dd, -day(dateadd(mm,1,@given_date)), dateadd(mm,1,@given_date))

To get next or previous month's first or last day change the highlighted number accordingly.

--SQL to get the first day of the second next month
select dateadd(dd,-(day(dateadd(mm,1,@given_date))-1),dateadd(mm,2,@given_date))

-- SQL to get the last day of the second next month
select dateadd(dd, -day(dateadd(mm,1,@given_date)), dateadd(mm,3,@given_date))

How to get underlying table information of a proxy table ?

2009-04-20T11:42:00.003-04:00

How to check proxy table information ?

To know underlying server, database and table information of a proxy table we can
query sysattributes table from within local database.

The query can be as below.

select object_type, object_cinfo, char_value from sysattributes where object_type = 'OD'

OD stands for 'Object Definition' , no clue as of now why 'OD' though :( , will get it some where
down the line... no rush :)

How does a view works ?

2009-04-18T14:04:00.003-04:00

How does a sybase view works internally?
How does a view perform as against a direct SQL?

While trying to understand the performance aspect of a view, this is what i came across in Sybase infocenter documentation.

A view can be derived from one or more underlying tables. Only the definition of the view is stored int e database (syscomments table), and not the view data.

When we use a view in a SQL statement, ASE combines the statement with this stored definition
to translate the statement to query underlying tables. This process is called as view resolution.

Consider the following view definition

create view hiprice
as select *
from titles
where price > $15
and advance > $5000

Now, if we use this view like below,

select title, type
from hiprice
where type = "popular_comp"

Internally, ASE combines the query with "hiprice" view's definition, converting the query to:

select title, type
from titles
where price > $15
and advance > $5000
and type = "popular_comp"

Note that it is not only "resolving" the 'where' clause but also the selected column list.

This shows that performance wise it should work as good as any other SQL statement, and should not have any considerable extra overhead.

How to estimate table size

2009-03-06T12:40:00.006-05:00

Once you create a table, you can estimate the table size using command sp_estspace.

To estimate the amount of space required by a table and its indexes:
1. Create the table.
2. Create all indexes on the table.
3. Run sp_estspace, giving the table name, the estimated number of rows for the table, and the optional arguments, as needed.

You do not need to insert data into the tables. sp_estspace uses information in the system tables--not the size of the data in the tables--to calculate the size of tables and indexes.

Example :
sp_estspace TableA, 50000

where TableA is table name and 50000 is estimated rows in the table.

Further details can be found in Sybase Manuals

How to copy whole directory tree

2008-07-01T23:54:00.005-04:00

To copy a whole directory structure you can use -r option of cp
It copies all the files in a directory and its subdirectories.

#-- Copy every thing from /homeDir/yourDir to /homeDir/tempDir/

cp -r /homeDir/yourDir/* /homeDir/tempDir/

Note:
You can use other supported wild cards ( instead of * ) to be more "selective" while copying.

#-- Copy every file/directory, which starts from 'S', from /homeDir/yourDir to
#-- /homeDir/tempDir/

cp -r /homeDir/yourDir/[S]* /homeDir/tempDir/

rollback SQL changes on database

2008-06-24T01:27:00.006-04:00

How to rollback SQL changes done on a database?
Well... it's a tip to be used in all your future SQL adventures with sensitive database.

Sometimes, you run a query which does some thing undesired and you want to 'rollback' your changes. But Sybase does allow to 'rollback' only when there is a transaction.
The tip is very simple, but very much neglected by developers.

Whenever you execute delete/update/insert SQLs, use transaction.
This gives you a chance to control the changes done to database.

e.g.

Suppose you want to delete data from yourTable where col_1 = 100

--#Execute a wrong SQL.
delete from yourTable where col_1 = 10

You can not rollback this, as there is no transaction.

--#Now, try this...
begin tran
delete from yourTable where col_1 = 10

Now, if you check the table, all the data where col_1 = 10, is deleted.
But, this will not be a permanent change, unless you execute 'commit tran'

--#Do a rollback
rollback tran

The changes will be 'rollback'.

--#Now run the correct query
delete from yourTable where col_1 = 100

Now, if you check the table, all the data where col_1 = 100, is deleted,
this is what we want.

--#At this time you can execute ...
commit tran

This will make the changes permanent.

How to get list of 'identity' columns in a database

2008-06-18T01:07:00.005-04:00

How to list identity columns available in a database, along with table names.

There are many ways to list identity columns .. but the simplest one I ever came up with is

--# Respective table name is also included.
select object_name(id) tableName, name columnName from syscolumns where status = 128

Related Posts :
1. Another way to get List of tables having 'identity' column

How to get physical size of a row ?

2008-06-09T00:56:00.005-04:00

What is the physical row size of my table ?

If you observe, the output of the sp_help myTable, the third column ( Length ) indicates the physical length for each of the columns. That means, to get the physical length of a row, we need the sum of these values.

We can query syscolumns table, to get the physical size of a row, as below.

--#Get the physical row size for a table 'myTable'
select sum(length) from syscolumns where id = object_id('myTable')

what is 'update statistics'

2008-06-04T23:40:00.003-04:00

How update statistics works to improve the query performance ?
When to use update statistics ?
Difference between update statistics and update index statistics.

Indexes are of great help in SQL performance. The keys involved in this index are spread all over the table's memory, database server keeps a "statistics" of these key's distribution. This statistics is then used by query optimizer while making the decision about, which index to be used in query or if at all index should be used.

update statistics, updates this "statistics data" and makes sure the database optimizer gets updated information.

Use update statistics, If there is significant change in the key values in your index, or if a great deal of data in an indexed column has been added, changed, or removed.

You can update statistics for all the indexes in a table or specifically only for one index.

--#update statistics for the leading columns of all indexes on the table.
update statistics tableName

--#updates statistics for the leading column of an index.
update statistics tableName indexName

Note that, in above examples only 'leading' columns of the index are considered while updating statistics. To update statistics for all of the index columns use following.

--#update statistics for all the columns of all indexes on the table.
update index statistics tableName

--#updates statistics for all the columns of an index.
update statistics tableName indexName

update statistics note :

When you create a nonclustered index on a table that contains data, update statistics is automatically run for the new index.
When you create a clustered index on a table that contains data, update statistics is automatically run for all indexes.

Related Posts :
How to get last 'update statistics' run date

List of tables having 'identity' column

2008-06-03T23:31:00.008-04:00

How to get the list of tables with identity column?

ASE 12.5.x, provides a function next_identity(), you can use this function to list the tables
having identity column.
Actually, this function returns, the next possible value for the identity column in a table.

So, to get the list of tables which contains a identity column, our logic is simple,
check the next identity value for each of the tables in database, and if NULL, that means
table does not contain any identity column, else it does.

--#List the table names, which has identity column.
select name from sysobjects where type = 'U' and next_identity(name) != NULL

--#List the table names, which has identity column, along with the next possible identity value.
select name, next_identity(name)
from sysobjects
where type = 'U' and next_identity(name) != NULL

Related Posts :
1. How to get list of identity columns along with table names.

select n rows per group from a table

2008-05-30T00:45:00.005-04:00

Suppose, after doing group by, you want to select a specific number of rows from each group.

1. How to select one row per group from a table ?

There are many ways to achieve this. But the simplest I feel, is as below.
Use min/max on one of the available columns and then implement group by

--# Get first/lowest row from each group.
select col_1, min(col_2) from yourTable group by col_1

--# Get last/highest row from each group.
select col_1, max(col_2) from yourTable group by col_1

2. How to select n rows per group, from a table ?

Make sure you have unique combination of col_1, col_2 in you table. If not you can
delete duplicates from the table
or you can move the data to a temp. table and execute below on the temp table.

--#Get first n rows per group from a table
select distinct * from yourTable A where
( select count(*) from yourTable B where A.col_1 = B.col_1 and A.col_2 > B.col_2 ) < n

--#Get first 3 rows per group from a table
select distinct * from yourTable A where
( select count(*) from yourTable B where A.col_1 = B.col_1 and A.col_2 > B.col_2 ) <>

--#Get last 3 rows per group from a table
select distinct * from yourTable A where
( select count(*) from yourTable B where A.col_1 = B.col_1 and A.col_2 <>

Ref. Table :
create table yourTable ( col_1 varchar(10) , col_2 int )
go
insert into yourTable values ( 'red', 1)
insert into yourTable values ( 'red', 10)
insert into yourTable values ( 'blue', 11)
insert into yourTable values ( 'blue', 12)
insert into yourTable values ( 'blue', 13)
insert into yourTable values ( 'yellow', 100)
insert into yourTable values ( 'yellow', 211)
go

How to select random rows from a table

2008-05-29T01:28:00.000-04:00

How to get a random row or multiple random rows from a table ?

--#SQL to get a single random row from a table.
select top 1 column1, column2, column3 from tableName order by newid()

To get multiple random rows from a table, you need to just change the 'top' count.

--#SQL to get 10 random rows from a table.
select top 10 column1, column2, column3 from tableName order by newid()

How to get last 'update statistics' run date

2008-05-28T01:12:00.003-04:00

What was last time, when 'update statistics' ran on a table ?

Whenever a query or SP does not perform as before, among all other investigation steps, checking update statistics on the underlying tables, is one of the very first steps.

You can use the following SQL to get the 'last run date', of update statistics on a table.

--#sysstatistics table contains the table statistics data.
select moddate from sysstatistics where id = object_id("your_table_name")

Note that, this may produce a list of dates. This is because, sysstatistics table maintains one or more entries for each of the index columns of the table. It may contain entries for non-indexed columns as well.

Make sure, you run update statistics on tables regularly, specially the tables whose volume changes considerably, over the time.

Related Posts:
what is 'update statistics'

Office Humor - 4

2008-05-27T22:55:00.011-04:00

Please click on image to view it clear ... thanks.

© Samir Badhe , may not be reproduced without permission.

See all office humors...

ORDER BY issue, with numeric values stored in char.

2008-05-26T16:14:00.009-04:00

How to use order by on integer values stored in a char. field ?

This is a SQL problem observed many times, when we need to order by ( sort ) numeric values which are stored in char field. Being stored as char, some numeric values are placed before others which should not be the case, if treated as numeric.
e. g. if stored as char, 2 > 12.

Example:
create table orderTable ( col_1 varchar(10) )

insert into orderTable values ( '1' )
insert into orderTable values ( '2' )
insert into orderTable values ( '11' )
insert into orderTable values ( '12' )
insert into orderTable values ( '21' )
insert into orderTable values ( '111' )

--order without converting char to interger.
select col_1 from orderTable order by col_1

col_1
-----
1
11
111
12
2
21

Observe the output, it does'n look in order, if you considered numeric values.

The solution is pretty straight forward, you need to convert char value into a numeric, before you do order by.

select col_1 from orderTable order by convert(int, col_1)

col_1
-----
1
2
11
12
21
111

If your numeric data is a part of a string, you can use substring or other string functions, to get numeric data out, and then convert it to numeric.

SQL to delete duplicate rows / records

2008-05-24T01:55:00.006-04:00

How to display duplicate rows / records ?
How to delete duplicate rows / records ?

Consider a table ( table_a ) as below,

create table table_a
(
col_1 int --unique key
duplicateColumn1 varchar(10), --column for which we want to check duplicates
duplciateColumn1 varchar(20) --another column for which we want to check duplicates
)

--# How to select duplicate rows.

select A.* from table_a A
group by duplicateColumn1, duplicateColumn2
having count(*) > 1

--# How to delete duplicate rows, keeping only one copy of the row.

delete table_a
from table_a A, table_a B
where
A.duplicateColumn1 = B.duplicateColumn1
AND A.duplicateColumn2 = B.duplicateColumn2
AND A.col_1 > B.col_1

or you can use SQL below,

delete table_a
where col_1 not in
( select max( col_1 ) from table_a group by duplicateColumn1 , duplicateColumn2 )

--# How to delete duplicate rows, when table does not have a primary / unique key.

You can add an identity column to the table and treat it as an unique key. And follow above SQL.
Or select table data into a temp table with identity column, follow above SQL on temp table, then" refresh" table_a data with the temp table data.

What is timestamp data type

2008-05-21T23:28:00.005-04:00

What is the difference between datetime and timestamp data types ?

First of all, let me tell you that, there no similarity between timestamp and datetime data types. timestamp has nothing to do with calender date or time.

Then what is timestamp data type ?

timestamp is a custom data type defined as varbinary(8).
The value of the timestamp is maintained on server level, and current value of the timestamp is stored in a global variable, @@DBTS.

To see the current value of timestamp ..

select @DBTS
go

Observe the value returned, and you will agree that it has nothing to do with date/time.

So where should I use the timestamp datatype ?

If defined in a table, it can be used as a flagging column for each row in a table. Whenever, any of the column in a row gets updated the value of the timestamp column gets automatically updated by the current value of @@DBTG.

So in multi-client environment, say you are updating a row, then you can check the old and new timestamp value for the row in subject.
If the old and new value matches that means there is no other updates to the row while you were accessing the row for manipulation, so you are fine to go and do update.
Else, if the old and new value does not match, that means, there has been some kind of update on the row, when you were browsing it, so depending on the requirements, you can develop the further logic for this condition.

In brief, it is used to prevent an update on a row that has been modified during the operation.

You need to use tsequal() function is used to compare these timestamp values.

It's just name ( timestamp ) which is pretty easily confusing most of us ;-).

P.S. - You can have only one column of datatype timestamp in a table.

Get number of rows without using count() function

2008-05-21T03:03:00.009-04:00

Efficient way to count the number of rows in a table.
How to get table row count without using count function?
How to get index and data size of a table?

You can use sp_spaceused to display total number of rows in a table, index size and data size.

sp_spaceused tableName
go

If you want, to display information for each of the indexes available on this table

sp_spaceused tableName , 1
go

If there is any text/image field in this table, then above will also display size reserved and unused by this column.

You can use this,
when you think table is too big to even run count(*)
when you want to check index size
when you want see space utilized by a text/image column
or just take it as an database interview question :)

Related Posts:
select count(*) vs. select count(1)

SQL to display page wise data

2008-05-20T01:03:00.005-04:00

Data pagination on database using SQL.
SQL to display output records, page wise without using cursors.

I did device following simple SP, some time back to address this, sharing here with you.

How it works :
You need to pass on the page number for which you want a result set to be displayed.
The SP will return back a result set for each page, the page size needs to be predefined.

So, basically if you pass this SP,
1, you will get first 10 records,
2, will fetch you next 10 records,
3, will get next 10 records for you,
and so on ...

--# Description : SP to implement pagination using SQL
create proc getPageWiseData
(
@pageNo int --# Pass the page number as an argument.
)
as
BEGIN
--# Page size defines the number of rows you are planning to show per page.
declare @pageSize int
select @pageSize = 10

--# variable to hold the first row number of the page.
declare @from int
select @from = ( @pageNo * @pageSize ) - ( @pageSize - 1 )

--# variable to hold last row number of the page.
declare @to int
select @to = ( @pageNo * @pageSize )

--# get the data into a temp table, with simulated rownum.
select rownum = identity(10) , * into #dataWithRownum from yourTable

--# select the data with the calculated range for first and last row on page.
select * from #dataWithRownum where rownum >= @from and rownum <= @to END

If the data which needs to be retrieved in the #dataWithRownum is too big, consider retrieving it in batches.

You may need few modifications to match your exact requirements or if, you are working with any other database, but the concept remains the same.

What is clustered index and nonclustered index

2008-05-19T23:57:00.011-04:00

What is a table index ?
What are Non-clustered and clustered indexes ?

Lets try to understand it with a analogy, a library.

Consider a library, where books are arranged in sequence of Author names in the shelves. So, if you need a book written by 'Arundhati Roy' , you can go directly to the shelf which has the books written by she, instead of going sequentially through all the books.
This is like clustered index in database.

Now suppose, you realized that some books are rather requested by titles. But as we have already arranged books in sequence of their authors, we can not rearrange it with titles in sequence, so you created a list of title and it's shelf number, so you ask me a book title, and i can get it for you from the shelf noted with it.
This is some thing like, non-clustered index.

Now, consider that, we frequently need a report to show books availability. So, we created a list of titles and whenever somebody takes it we mark it as 'Not Available'. This means, if we need the report, we don't even have to go to the shelf to check the availability of the book, all required information is in the list, call it a covered index in database terms.

Continuing this analogy, table indexes can be viewed as follows,

1. In case of clustered indexes, table data is physically stored in order of keys.
2. There can be only one clustered index per table and multiple non-clustered indexes.
3. Clustered indexes are good for range searches.
4. Non-clustered indexes are good for random searches.
5. Inserts and deletes can become an expensive affair in case of clustered index, as data needs to be shuffled to stored physically sorted.
6. When, all the required columns in 'select' clause and 'where' clause of a SQL are available in a index key, the data page is not get accessed, resulting a good performance. This kind of indexing is called as 'covered index'.

Indexes is a huge subject in on it's own. This post is to give a basic understanding of the indexes.
Improving your 'library operations' will help you improving your index selections :). For more information check out following links.

Related posts :
How to use indexes for better performance.

Further Readings :
How table index works

Office Humor - 3

2008-05-16T00:30:00.011-04:00

Please click on image to view it clear ... thanks.

© Samir Badhe , may not be reproduced without permission.

See all office humors...

select count(*) vs. select count(1)

2008-05-13T00:08:00.022-04:00

What is the difference between select count(*) and select count(1) ?

This was a long pending question in my mind so, I did a little bit of search and study to convince myself that ... there is a difference, may be because SQL Query Optimizer would expand '*' into the column list.

But if we compare, SQL query plans and performance is same for both of them.

Then, I thought of finding the answer using the way Sybase ASE tends to think.
Depending on that, it seems there is no difference in 'select count(1)' and 'select count(*)'
Even, 'select count(100)' would give the same performance.

Here are my findings ...

Consider a table
create table TAB_A
(
col_1 int null,
col_2 varchar(20) null,
col_3 char(1) null
)
Data:
insert into TAB_A values ( 1, 'Seabiscuit' , 'H')
insert into TAB_A values ( 2, 'Schindlers List' , 'H')
insert into TAB_A values ( 3, 'Trueman Show' , 'H')
insert into TAB_A values ( NULL, 'Office Space' , 'H')
insert into TAB_A values ( NULL, NULL, NULL)

Here is how 'count' function works.
1. count ( column_name ) : finds the total number of 'non null' rows in the table.
Exa.

select count(col_1) from TAB_A
---------
3

and

select count(col_2) from TAB_A
---------
4

2. count(*) : finds the total number of rows in the table, irrespective of null values.

Exa.

select count(*) from TAB_A
---------
5

Now, if we consider that Sybase expands '*' into the column list, then above two definitions of 'count' function does not allow this.

A. Suppose, sybase has expanded the '*' into column list, then the SQL will become

select count( col_1, col_2, col_3 ) from TAB_A
---------
Server Message : Number 102, Severity 15
Incorrect syntax near ',' .

This is not allowed, you can not pass comma separated column names to 'count' function.

B. Even if, we argue that point A is internal matter to optimizer and any how, it might have been worked around internally, in that case according to first definition, null values will be ignored while counting number. This is not correct, as we know 'count(*) ' always returns total number of rows, even if there are nulls.

So, from A and B we can say '*' can not get expanded internally.

Then, why is '*' here ?
I think, as opposed to "all columns" in general 'select query', here '*' indicates "all rows" of the table, including NULL values.

Also, after this analysis, I feel comfortable to say that, any number or string other than the column name might be getting converted to '*' , before executing the SQL to count the total number rows available in table.

Example :
select count( 1947) from TAB_A
---------
5

or

select count('someString') from TAB_A
---------
5

So, the only overhead, I can think of is to convert 1947 or 'someString' to '*' ,
which should be far less than negligible .

Related Posts:
Get number of rows without using count() function

How to get output of one Stored Procedure into another

2008-05-10T01:50:00.017-04:00

Sharing data between two stored procedures

How to return a status code from stored procedure
Using Output parameters in stored procedure
Use temporary table populated by inner stored procedure

1. How to return a status code from SP

In this case, we can simply 'return' some value, which can be some system defined status flags or you can return your own value.

--# SP, which returns 100 on some error condition otherwise 0
create proc sp_returnCode
as
... some processing ....
If ( ...some error condition... )
return 100 --#Your custom return code
else
return 0 --#Success code

This SP can be called by another SP or query, to collect this return status as below

declare @status int
execute @status = sp_returnCode
select @status

Note that, only one value can be "returned"

Sybase ASE does reserve 0 to indicate 'success' and -1 to -99 return codes to indicate different errors . For your custom return codes use other than this range.

2. Using Output parameters in SP

This is another way to return values from one SP to another SP or query .
Output parameters are defined like input parameters.

--#SP to multiply two INPUT parameters and return the result in OUTPUT parameter.

create proc sp_multiply
( @para_1 int, @para_2 int, @result int OUTPUT )
as
select @result = @para1 * @para2

--#Collect the output result
declare @result int exec sp_multiply 7, 3, @result OUTPUT

Unlike 'return code', you can return multiple OUTPUT parameters
Lets update above SP, to have a additional OUTPUT parameter, say addition of two numbers.

create proc sp_multiplyAdd
( @para_1 int, @para_2 int, @result_1 int OUTPUT, @result_2 int OUTPUT )
as
select @result_1 = @para1 * @para2
select @result_2 = @para1 + @para2

You can access the two OUTPUT variables of the stored procedure as below,

declare @result_1 int
declare @result_2 int
exec sp_multiplyAdd 7, 3, @result_1 OUTPUT , @result_2 OUTPUT
select @result_1 , @result_2

3. Sharing temp table between two Stored Procedures

If you want to use a result set from one stored procedure into another, temporary tables can be a good option.

Basically, we create a temp. table which is populated by first SP, and the same temp. table is then used by another stored procedure.

--#Create a temp. table, with same definition as that of the result set.
create table #shareMe ( col_1 int, col_2 char(9), ...... )

--#Create a SP to populate this table with the SP result set as below
create proc sp_populateResult
as
insert into #shareMe
select * from TAB_A where col_1 = @someValue

Note : Once this SP is compiled you can drop the temp table, as we will be creating it in calling SP.

Now you can access this temp table in another stored procedure.

create proc sp_consumeResult
as
BEGIN
--#Define the temp. table
select * into #shareMe from TAB_A where 1 = 2

--#Execute the SP which populates the result set.
exec sp_populateResult

--#Access temp table
select * from #shareMe

END

Office Humor - 2

2008-05-06T02:10:00.021-04:00

Please click on image to view full size and clear image... thanks.

© Samir Badhe , may not be reproduced without permission.

Office Humor - 1

Get column details of a table using syscolumns

2008-05-05T22:03:00.006-04:00

How to get table's column list ?

Various information along with column list can be retrieved using syscolumns table.

1.
--Get the list of columns and their data types, available in a table
SELECT A.name columnName, B.name dataType
FROM syscolumns A, systypes B
WHERE id = object_id( 'anyTableName') and A.type = B.type
ORDER BY colid

2.
--Get the list of tables which has a particular column name.
SELECT object_name(id) tableName, name columnName FROM syscolumns
WHERE name = 'someColumnName'

3.
--Get the list of tables which is like a particular column name.
SELECT object_name(id) tableName, name columnName
FROM syscolumns
WHERE name like 'someColumnName%'