Archive for the 'SQL' Category

SQL SERVER - Full Transaction Log (What to do)

Published
by
rs
on December 4, 2008
in SQL and Technical
. 0 Comments

Transaction log mechanism enables SQL Server to record all transactions before writing them to the database. This capability enables SQL Server to automatically recover the database to a consistent state when experiencing a power failure, operating system crash, and so forth.

The SQL Server transaction log can become full, which prevents further UPDATE, DELETE, or INSERT activity in the database.

One way to fix this is to back up and truncate the transaction logs.

BACKUP LOG <DbName> WITH TRUNCATE_ONLY

To save more disk space, we could shrink the truncated log file to it’s minimum size.

DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DbName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

Sql Date Format - Month and Year Only

Published
by
rs
on August 30, 2006
in SQL and Technical
. 3 Comments

i remember there is one time where i have to edit an old module where in the date field in sql is not a date data type but instead, it is a char type… they did that because they wanted to store only the month and year of the date in sql… at first it wasn’t a big deal after all until i have to return the data with a given date range… now that’s a problem… my problem…

since the date is in char type, it won’t be easy to select the data with a given range..
and so i came up with this solution… this is how i make a date range with char data type..

say we have to select all datas with these given date range: from 04/2003 to 03/2006

my query would be:

select field1, field2, dateField
from   table1
where  (cast(substring(dateField,1,2) as int) >= 4 and cast(substring(dateField,4,4) as int)=2003) or
       (cast(substring(dateField,1,2) as int) <= 3 and cast(substring(dateField,4,4) as int)=2006) or
       (cast(substring(dateField,1,2) as int) between 1 and 12 AND cast(substring(dateField,4,4)
       as int) > 2003 and cast(substring(dateField,4,4) as int) < 2006

now lets disect the code…
for the first where condition:
(cast(substring(dateField,1,2) as int) >= 4 and cast(substring(dateField,4,4) as int)=2003)
this will return all the data with the date range from 04/2003 to 12/2003…

for the second where condition:
(cast(substring(dateField,1,2) as int) <= 3 and cast(substring(dateField,4,4) as int)=2006)
this will return all the data with the date range from 01/2006 to 03/2006..

for the third and last where condition:
(cast(substring(dateField,1,2) as int) between 1 and 12 AND cast(substring(dateField,4,4) as int) > 2003 and cast(substring(dateField,4,4) as int) < 2006
this will return all the data with the date from 01/2004 to 12/2004 and from 01/2005 to 12/2005

if you don’t know how to use the substring function in sql… just google it… there are lots of threads and forums out there…

a smarter way to do this is convert the varchar type date into a real date format… in that way, you can easily make a date range because it is now in date type…
say you have “08/2006″ as august 2006, all you have to do is insert a “01/” on the left side to make it “01/08/2006″.. after that you can now convert it to date format… since you only need the month and year, the value of the day does not matter…

to insert the “01/” in the left side of your varchar date and convert it to date format:

convert(datetime, ‘01/’ + dateField,103)
*101 means the date format will be dd/mm/yy

now every time you need to the month and year.. use the code month() to return only the value of month and year() for the value of year…

sample:
say we have “30/03/1984″ in dateField

select month(dateField)
from sampleTbl

this will return “03″… and if you use the year() function.. it will return “1984″

another way is to use a contiguous string format… just convert your varchar date into this format ‘200608′ (means agust 2006).. then use <,>,= operators to create a range… just use substring() function to arrange your data into this format…

SQL isZero() Solution For Divide By Zero Error

Published
by
rs
on August 4, 2006
in SQL and Technical
. 21 Comments

I had a query once that contains a divide operation….

SELECT   x, y, ROUND(x/y) as quotient
FROM     dataTbl

It always returns a correct value…
but divide by zero error occurs whenever
there is a zero(0) not NULL value in y column…
now, what i want is instead of having
an error, i want it to return a zero(0) value also…
so I wonder if there is a function isZero(), I can simply
change the y value from 0 to NULL to avoid such error…
I was thinking of a code like this: isZero(y, NULL)…
unfortunately, there is no such functions…
I googled for the correct syntax and I came up with
the: NULLIF(fieldName, condition) function…

SELECT   x, y, ROUND(x/NULLIF(y, 0)) as quotient
FROM     dataTbl

by using the query above, divide by zero error is impossible…
what happen is SQL returns a NULL in y column if its value is zero(0)…
though the error was avoided… the result still does not satisfy me…
it only returns a NULL value in quotient field instead of zero(0)…
well, the solution is simple:

SELECT   x, y, ISNULL(ROUND(x/NULLIF(y, 0)), 0) as quotient
FROM     dataTbl

by using the ISNULL(fieldName, replacementValue) function,
It sort of like reversed the effect of NULLIF function…
what happen in this function is, if the value IS NULL
then replace it with what ever the replacementValue is…
in my query, it replaces the NULL value by zero(0)…

now that’s satisfying…. divide by zero error was avoided
and quotient field is never NULL….

SQL Server Joins And Group By (Performance Tuning)

Published
by
rs
on August 2, 2006
in SQL and Technical
. 1 Comment

I was given a certain module where in it performs a simple computation (the SUM function). What I’m tryin’ to do is add all the data from a specific fields and group by a certain field from another table. but the problem is the table containing the data to be calculated is three tables apart from the table containing the field needed for the group by function and four fields are needed for groupings.

to make it clear, here’s the query sample:


SELECT  a.field1, a.field2, a.field3, SUM(d.field2+d.field3) as totalFld
FROM    table1 a
        LEFT OUTER JOIN table2 b
        ON a.field4= b.field1
        LEFT OUTER JOIN table3 c
        ON b.field2= c.field1
        LEFT OUTER JOIN table4 d
        ON c.field2= d.field1
GROUP BY a.field1, a.field2, a.field3

By running the query above, it took me more than five minutes before it returns the data.

And so i started investigating and found out that the reason for slow performance is because I am joining the tables on the field that is not an index. So what I did is I added an indexes to the columns in each table used to join the tables. I’ve learned that for maximum performance, the indexes on the columns to be joined should have the same data type and it should be a clustered key for optimum performance.

Another thing is the group by function. I’ve found out that this also affects the performance. As the number of groupings increases the performance decreases.

to create an index using t-sql:

CREATE CLUSTERED INDEX indexName on tableName(fieldName1, fieldName2,….)

to delete an index using t-sql:

DROP INDEX tableName.indexName

SQL Union All

Published
by
rs
on July 20, 2006
in SQL and Technical
. 0 Comments

I was working in this certain module last week wherein I will create a web app page that will generate monthly reports. Everything was fine and I manage to do it with an ease until the boss checks my work, On the report generated, there was a certain field column wherein NULL values are present, though some has a value, still it is impossible because that field is required in the form, and so the 1st problem occurs

In that module, what I did is I created a views in sql wherein it contains the computations of the data selected from a certain table, and I also added a field named “officerFld” which will contain an officer’s name selected from a certain field from another table.

I started to investigate the problem and found out that the officer’s name is stored in two different tables wherein I only selected from one of those tables. So I started searching for a way on how to select those officer’s name from the second table and insert it on the “officerFld” on the views that i’ve created, in that case, all the NULL values will be replaced by the officer’s name from the second table and all the officer’s name from the first table will still remain in the “officerFld” found on the views that i created.

Thanks to kiko(my older brother), who gave me the idea to use the UNION ALL command in sql. Well, I’m just starting to learn sql so don’t expect me to know all the commands. But now I’m starting to acquire knowledge so that’s what i did, i used the UNION ALL command in sql to combine the results of my two queries together and I used LEFT OUTER JOIN to combine it to my views.