Chittibabu

General HR questions

1. Tell about yourself and job
2. Tell about current project
3. What are sequence diagrams, collaboration diagrams and difference between them
4. What is your role in the current project and what kinds of responsibilites you are handling
5. What is the team size and how do you ensure quality of code
6. What is the S/W model used in the project. What are the optimization techniques used. Give examples.
7. What are the SDLC phases you have invloved
8. About educational background
9. About work experience
10. About area of work
11. Current salary, why are looking for a change and about notice period About company strength, verticals, clients, domains etc.
12. Rate yourself in different areas of .NET and SQL
13. About procsses followed
14. Notice period
15. Appraisal process
16. About effort estimation
17. Whether salary negotiable
18. Why are looking for a change
19. How fo you appraise a person
20. Do you think CMM process takes time
21. About peer reviews
22. About educational background, work experience, and area of work

<html>
<head runat="server">
<title></title>
<script type="text/javascript" src="vkboard.js"></script>
<script></script>
</head>

<body>
<form id="form1" runat="server">
<div>
<table border="0" width="60%">
<tr>
<td width="100px">
<textarea id="textfield" rows="12" cols="50"></textarea>
</td>
<td width="10px">
</td>
</tr>
</table>
<p>
<a href="javascript:keyb_change()" onclick="javascript:blur()" id="switch" style="font-family: Tahoma;
font-size: 14px; text-decoration: none; border-bottom: 1px dashed #0000F0; color: #0000F0">
Show keyboard</a></p>
<div id="keyboard">
</div>
</div>
</form>
</body>
</html>

Prime number sample program using vb.net.

Public Sub PrintPrimeNumber()
Dim Num As Long
Dim NN As Long
Dim IsPrime As Boolean

For Num = 2 To 100
IsPrime = True
For NN = 2 To Int(Num / 2)
If Num Mod NN = 0 Then
IsPrime = False
Exit For
End If
Next
If IsPrime Then
Response.Write(Num & " is a prime number!" & "<br>")
End If
Next
End Sub

Odd number sample program using vb.net

Public Sub PrintOddNumber()
For number As Integer = 0 To 100
If (number Mod 2) = 1 Then
Response.Write(number & ", ")
End If
Next
End Sub

Even number sample program using vb.net

Public Sub PrintEvenNumber()
For number As Integer = 0 To 100
If (number Mod 2) = 0 Then
Response.Write(number & ", ")
End If
Next
End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Response.Write(IsEmail("chittimca3@gmail.com"))
End Sub

Public Function IsEmail(ByVal Email As String) As Boolean
Dim strRegex As String = "^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" & "\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" & ".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"
Dim re As New Regex(strRegex)
If re.IsMatch(Email) Then
Return (True)
Else
Return (False)
End If
End Function

I have a javascript code to redirect mobile phone users. Could you please check the code, I've picked it up from the net, total newb and I'd value your input regarding the quality of the script... Does it cover all types of mobile phones?

<script type="text/javascript">
function RedirectSmartphone(url){
    if (url && url.length > 0 && IsSmartphone())
    window.location = url;
}
function IsSmartphone(){
    if (DetectUagent("android")) return true;
    else if (DetectUagent("iphone")) return true;
    else if (DetectUagent("ipod")) return true;
    else if (DetectUagent("symbian")) return true;
    return false;
}
function DetectUagent(name){
    var uagent = navigator.userAgent.toLowerCase();
    if (uagent.search(name) > -1)
    return true;
    else
    return false;
}
RedirectSmartphone("http://mobile.version.com");
</script>

Ty very much!

A cursor is a set of rows together with a pointer that identifies a current row.

In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

The basic syntax of a cursor is:

DECLARE @AuthorID char(11)
 
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

 PRINT @AuthorID

 FETCH NEXT FROM c1
 INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.
The READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cursor.

In this example, I just print the contents of the variable. You can execute any type of statement you wish here. In a recent script I wrote I used a cursor to move through the rows in a table and call a stored procedure for each row passing it the primary key. Given that cursors are not very fast and calling a stored procedure for each row in a table is also very slow, my script was a resource hog. However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem. In this case, I might have something like this:

EXEC spUpdateAuthor (@AuthorID)

instead of my Print statement. The CLOSE statement releases the row set and the DEALLOCATE statement releases the resources associated with a cursor.
If you are going to update the rows as you go through them, you can use the UPDATE clause when you declare a cursor. You'll also have to remove the READ_ONLY clause from above.

DECLARE c1 CURSOR FOR
SELECT au_id, au_lname
FROM authors
FOR UPDATE OF au_lname

You can code your UPDATE statement to update the current row in the cursor like this

UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1

That covers the basics of cursors. You can check Books Online for more detailed information.

The "SELECT INTO" technique will create a table with the same columns but doesn't reproduce the keys, constraints, and defaults.

This query is used to create a dupliate table with same columns and data

Code:

select * into newtable from oldtable

The following are a wide range of tips and tricks I have collected over the years working with various versions or Microsoft's SQL Server. I always enjoy finding little snippets of code which are both simple and powerful. Why write complicated routines when one or two lines of code can do it?
Although you may very well find the following code useful, I don't really want small posts like this from detracting from the overall purpose of my blog, which is to help me develop my writing skills. I am therefore going to create a section on this bloggie called my Code Notebook which will hopefully serve two purposes:

It will provide me with an area to note down and share pieces of code which I am proud of creating and,
It will keep the main focus of the bloggie on improving my writing.

So without further ado, I present to you my SQL Server Tips and Tricks...

Date Only

Quite a useful function for producing or saving only the date part of the smalldatetime datatype. This can be useful if you only need to store the date or you need to group some results by dateonly.

Select
Convert([smalldatetime],floor(Convert([float],getdate(),(0))),(0))

Produce a Row or Table Checksum

This can be useful for checking if any of the contents of a row or table has changed without saving the entire contents of row or table.

Table Checksum
Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK)
Row Checksum
Select CheckSum_Agg(Binary_CheckSum(*)) From Table Where Column = Value

Reindex an entire database

EXEC [sp_MSforeachtable] @command1 = "RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"

Count Character in a String

This very simple statement allows you to count how many times a certain character appears in a string.

Select Len('///xxx') - Len(Replace('///xxx', '/', ''))

Single & Multi User Mode

By default, a database is set to Multi User Mode, allowing more than one connection to connect to the database at a time. However, it may prove useful at times, for example database maintenance, to restrict the database to only 1 connection. This allows that 1 user to carry out work on the database without any locks.

ALTER DATABASE MyDatabase SET SINGLE_USER

ALTER DATABASE MyDatabase SET MULTI_USER

Smart Date Ranges

Find records which date falls somewhere inside the current week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ), 0 )

Find records which date occurred last week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

Returns the date for the beginning of the current week.

select dateadd( week, datediff( week, 0, getdate() ), 0 )

Returns the date for the beginning of last week.

select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

Drop all Database Connections

Although this could be a very dangerous script to run, it is very useful when you need to do some maintenance on a database and there are some rouge connections open stopping you from doing so. The script will run through all users currently connection to the specified database and kill their connection.

Use Master
Go
Declare @dbname sysname
Set @dbname = 'name of database you want to drop connections from'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

If anyone know more tips or tricks please share them with me...

To delete all tables with a single query

sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' "

==============================================

To delete all procedures from sqlserver

CREATE Procedure [dbo].[DeleteAllProcedures]
As
declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
--Go
--Grant Execute On dbo.DeleteAllProcedures To Public
--Go

Introduction

In most of our application scenario we need to get
latest inserted row information through SQL query. And for that we have multiple
options like
All three functions return last-generated identity values.
However, the scope and session on which last is defined in each of
these functions differ.

Compare

@@IDENTITY :
It returns the last identity value generated for any table in
the current session, across all scopes.
Let I explain this, suppose we create an insert trigger on
table which insert a row in another table with generate an identity column then
@@IDENTITY returns that identity record which is created by
trigger.
SCOPE_IDENTITY :
It returns the last identity value generated for any table in
the current session and the current scope.
Let I explain this, suppose we create an insert trigger on
table which insert a row in another table with generate an identity column then
SCOPE_IDENTITY result is not affected but if a trigger or a user defined
function is effected on the same table that produced the value returns that
identity record then SCOPE_IDENTITY returns that identity record which is
created by trigger or a user defined function.
IDENT_CURRENT :
It returns the last identity value generated for a specific
table in any session and any scope.
In other words we can say it is not affected by scope and
session, it is only depend on a particular table and return that table related
identity value which is generated in any session or scope.

SQL Query

I am explaining the above process with help of some sample query, hope it helps-

CREATE TABLE Parent(id int IDENTITY);

CREATE TABLE Child(id int IDENTITY(100,1));

GO

CREATE TRIGGER Parentins ON Parent FOR INSERT

AS

BEGIN

   INSERT Child DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT id FROM Parent;
--id is empty.

SELECT id FROM Child;
--ID is empty.
 

--Do the following in Session 1
INSERT Parent DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the

INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT('Child');

/* Returns value inserted into Child, that is in the trigger.*/

SELECT IDENT_CURRENT('Parent');

/* Returns value inserted into Parent. This was the INSERT statement four statements before this query.*/

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action

up to this point in this session.*/

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action

up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/

View post:

System functions perform operations and return information about values, objects, and settings in an instance of SQL Server. The large majority of system functions are nondeterministic.

CASE Function

CASE is typically classified as a system function; however, it could also be considered as a statement. There are two general uses of the CASE function. The first one is used to replace occurrences of one value with other values, as specified by the programmer. Syntax for this flavor of CASE is as follows:

1.SELECT column_name = CASE WHEN column_name = 'a' THEN 'b' ELSE 'c' END

For instance, the following query attempts to specify the salary level for each job title within Adventure Works DW database:

SELECT DISTINCT TITLE,
                SALARYRANGE = CASE
                                WHEN TITLE LIKE 'Chief%' THEN 'unlimited'
                                WHEN TITLE LIKE '%manager%' THEN '100K to 250K'
                                WHEN TITLE LIKE '%assistant%' THEN '20K to 40K'
                                WHEN TITLE LIKE '%supervisor%' THEN '50K to 65K'
                                WHEN TITLE LIKE '%technician%' THEN '30K to 60K'
                                WHEN TITLE LIKE 'vice president%' THEN '250K to 500K'
                                ELSE 'unknown'
                              END
FROM   DIMEMPLOYEE

Results (abbreviated):

Title                                              SalaryRange  
-------------------------------------------------- ------------  
Accountant                                         unknown 
Accounts Manager                                   100K to 250K  
Assistant to the Chief Financial Officer           20K to 40K  
Buyer                                              unknown 
Chief Executive Officer                            unlimited  
Chief Financial Officer                            unlimited  
Document Control Assistant                         20K to 40K  
Document Control Manager                           100K to 250K  
Engineering Manager                                100K to 250K  

The other variation of CASE, which is sometimes referred to as the searched CASE, evaluates a Boolean expression and returns different values accordingly. For instance, we could use the searched CASE to categorize the top internet customers within Adventure Works DW database as follows:

1.SELECT CAST(FirstName + ', ' + LastName AS VARCHAR(35)) AS FullName, CustomerCategory = CASE WHEN SUM(SalesAmount) < 12000 THEN 'SILVER' WHEN SUM(SalesAmount) BETWEEN 12000 AND 13250 THEN 'GOLD' WHEN SUM(SalesAmount) BETWEEN 13250 AND 15000 THEN 'PLATINUM' ELSE 'CREAM OF THE CROP' END, SUM(SalesAMount) AS TotalOrders FROM dimCustomer a INNER JOIN FactInternetSales b ON a.CustomerKey = b.CustomerKey GROUP BY FirstName + ', ' + LastName HAVING SUM(SalesAmount) >=11000 ORDER BY 3 DESC

Results:

FullName                            CustomerCategory  TotalOrders  
----------------------------------- ----------------- ---------------------  
Jordan, Turner                      CREAM OF THE CROP 15999.0996  
Willie, Xu                          PLATINUM          13490.0596  
Nichole, Nara                       PLATINUM          13295.38  
Kaitlyn, Henderson                  PLATINUM          13294.27  
Margaret, He                        PLATINUM          13269.27  
Randall, Dominguez                  PLATINUM          13265.99  
Adriana, Gonzalez                   GOLD              13242.70  
Rosa, Hu                            GOLD              13215.65  
Brandi, Gill                        GOLD              13195.64  
Brad, She                           GOLD              13173.19  
Francisco, Sara                     GOLD              13164.64  
Maurice, Shan                       GOLD              12909.6682  
Janet, Munoz                        GOLD              12489.1696  
Lisa, Cai                           SILVER            11469.1882  
Franklin, Xu                        SILVER            11284.9707  
Lacey, Zheng                        SILVER            11248.4582  
Larry, Munoz                        SILVER            11068.0082

COALESCE Function

The COALESCE function returns the first value from a supplied list that is not NULL. COALESCE is a powerful tool if you are returning numerous values to a user and want to substitute occurrences of NULL with values from a different column or with an expression. The syntax is:

1.COALESCE(expression1, expression2, expressionN)

All expressions must have compatible data types. For instance, you can't coalesce DATETIME and INTEGER, but you could coalesce VARCHAR(20) and CHAR(20).
For example, the following query will examine the parent account key column for each account within DimAccount table; if the value is NULL the query will return "none" as the parent account:

1.SELECT a.AccountDescription, COALESCE(b.AccountDescription, 'None') AS ParentAccount FROM dimAccount a LEFT JOIN dimAccount b ON a.ParentAccountKey = b.AccountKey

Results (abbreviated):

AccountDescription                                 ParentAccount  
-------------------------------------------------- --------------------------------------  
Balance Sheet                                      None 
Assets                                             Balance Sheet 
Current Assets                                     Assets  
Cash                                               Current Assets  
Receivables                                        Current Assets  
Trade Receivables                                  Receivables  
Other Receivables                                  Receivables  
Net Income                                         None 
Operating Profit                                   Net Income  
Gross Margin                                       Operating Profit  
Net Sales                                          Gross Margin  
Gross Sales                                        Net Sales  
Intercompany Sales                                 Gross Sales  
Returns and Adjustments                            Net Sales  
Discounts                                          Net Sales  
Total Cost of Sales                                Gross Margin  
Statistical Accounts                               None 
Headcount                                          Statistical Accounts  
Current Installments of Long-term Debt             Current Liabilities  
Trade Sales                                        Gross Sales

COALESCE function is equivalent to the CASE function if it checks each expression for NULL values and returns the non-null expression, as in:

1.SELECT coalesce_equivalent = CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 ELSE NULL END

ISNULL Function

The ISNULL deterministic function is similar to COALESCE, but accepts only two parameters. The first parameter will be checked, and if NULL value is found, it will be replaced with the second parameter. Furthermore, ISNULL requires that both parameters have the same (not just compatible) data type. For example, we can return 'none' if the parent account of the given account is NULL:

1.SELECT a.AccountDescription, ISNULL(b.AccountDescription, 'None') AS ParentAccount FROM dimAccount a LEFT JOIN dimAccount b ON a.ParentAccountKey = b.AccountKey

Results would be identical to those results obtained with the COALESCE function.

NULLIF Function

The NULLIF deterministic function returns a NULL value if the two parameters it accepts are equivalent. NULLIF can be thought of as an opposite of ISNULL; for instance, we could substitute a NULL for number of employees if we find that number of employees for a particular reseller is 10:

1.SELECT NumberEmployees, NULLIF(NumberEmployees, 10) AS manipulated_number_of_employees FROM dimReseller WHERE NumberEmployees IN (10, 11)

Results (abbreviated):

NumberEmployees manipulated_number_of_employees  
--------------- -------------------------------  
10              NULL 
11              11  
11              11  
10              NULL 
10              NULL 
10              NULL 
11              11  
11              11  
10              NULL

GETANSINULL Function

The GETANSINULL function provides a quick way of checking whether column nullability is determined according to the ANSI 92 standard. The function returns 1 if ANSI null standard is used for column nullability, otherwise zero is returned. This function takes a single argument of database name. If database name isn't specified the setting is returned for the current database. For example:

1.SELECT GETANSINULL('AdventureWorksDW')

Results:

1.------

2.1

CAST and CONVERT Function

The CAST and CONVERT functions are very similar: Both translate a value from one data type to another. Although their performance is also similar, their syntax and potential usage is slightly different. The syntax is as follows:

1.CAST(expression AS new_data_type) CONVERT(new_data_type, expression, [style])

The expression must already have a data type that is translatable into the new_data_type. For instance, you can't convert an alphanumeric string into an integer.
Note: CONVERT has an optional parameter: style. This parameter is allowed only for cases when working with date and time values. SQL Server supports numerous formats for presenting date and time values; the style parameter is used to specify such format.
For example, suppose you want to retrieve dates from the dimTime table without returning the time portion. Either CAST or CONVERT function can be used as follows:

1.SELECT TOP 1 CAST(FullDateAlternateKey AS VARCHAR(12)) FROM DimTime

2.SELECT TOP 1 CONVERT(VARCHAR(12), FullDateAlternateKey, 109) FROM DimTime

Both return the same results:

1.------------

2.Jul 1 2001

If you needed to return a date value in which month, day, and year are separated by dashes you could use the CONVERT function with the style 110, as follows:

1.SELECT TOP 1 CONVERT(VARCHAR, FullDateAlternateKey, 110) FROM DimTime

Results:

1.------------------------------

2.07-01-2001

@@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() Function

The @@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() functions deal with IDENTITIY values or globally unique identifiers. SQL Server 2005 also supports the NEWSEQUENTIALID() function for default constraints of columns with the UNIQUEIDENTIFIER data type.
The NEWID() function could be used if you want to provide a default value for a column with the UNIQUEIDENTIFIER data type. This function can also be used to generate a new GUID in Transact-SQL. Columns with UNIQUEIDENTIFIER data type are often used as primary keys. Values generated by NEWID() function are impossible to predict and are not ordered, which can cause performance issues. For example, the following script creates a table and populates using NEWID() function for UNIQUEIDENTIFIER data type column. Note that resulting GUIDS are NOT sequential:

CREATE TABLE test (  test_column UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()  )   
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES   
SELECT * FROM test

Results:

test_column  
------------------------------------  
B7C4B585-7DDC-41FA-8AED-8EDD9F8BA483  
6E88893F-D913-43E0-9503-7A7BE0B9FAF2  
352E9644-07CC-4324-9FCE-AD61DAD4001A  
848F80F9-4E20-4969-A331-436E669819A8

Fortunately SQL Server 2005 supports the NEWSEQUENTIALID() function that generates a sequential GUIDS which tend to perform better than unordered GUID. The NEWSEQUENTIALID() function can only be used in a DEFAULT constraint expression. Since values generated by NEWSEQUENTIALID() are ordered one can predict the value that will be generated next; therefore this function shouldn't be exposed to the users if data security is of concern. For example, the following script creates a table and populates it with sequential values for UNIQUEIDENTIFIER data type column. Note that resulting GUIDS are sequential:

CREATE TABLE test (  test_column UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()  )   
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES 
INSERT test DEFAULT VALUES   
SELECT * FROM test

Results:

test_column  
------------------------------------  
00700F04-4AB7-DA11-804B-006073E94311  
01700F04-4AB7-DA11-804B-006073E94311  
02700F04-4AB7-DA11-804B-006073E94311  
03700F04-4AB7-DA11-804B-006073E94311

The IDENTITY function has limited use; in rare cases, when you use SELECT INTO syntax you can supply identity values for the newly created table using the IDENTITY function. For instance, suppose we want to add an identity column to the sales table (within a temporary table). We could use the following statement to copy all rows from sales table into #new_sales and add an identity column, all in one shot:

1.SELECT IDENTITY(INT, 1,1) AS sales_key, * INTO #new_sales FROM sales

The other three IDENTITY-related functions deserve more attention. You will often need to populate multiple related tables, perhaps within a single transaction. For instance, you could be populating the order and order_details tables in one transaction. If the order table has an identity column, you'll have to look up the identity value just inserted into the order table before you can add a related record in order_details. The @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() functions help you look up the last identity value inserted, but their behavior is slightly different from each other, as follows:

@@IDENTITY returns the last IDENTITY value inserted on the current connection. Suppose that you have an INSERT trigger on the order table that populates the audit_trail table, which also has an IDENTITY column. In such a case, the @@IDENTITY function will return the last identity value inserted, which would be the identity inserted in the audit_trail table instead of the identity value added to the order table. Therefore, if you try populating order_details with a value returned by the @@IDENTITY function, your data integrity will be compromised.
The IDENT_CURRENT() function accepts a table name as the parameter and returns the last identity value generated in that table by any connection. If you were trying to populate order_details with the last identity value inserted into the order table, then IDENT_CURRENT('order') could work if you were the only user of the system; however, another user might have added a row to the order table a few milliseconds after you added the row to the same table. Therefore, using IDENT_CURRENT() in a multi-user system might also compromise your data integrity.
The SCOPE_IDENTITY() function takes no parameters and returns the last identity value inserted within the current scope. So if an INSERT statement populating the order table executes a trigger and adds a row to the audit_trail table, SCOPE_IDENTITY() will return the last value added to the order table, whereas @@IDENTITY will return the last value added to audit_trail.

ISDATE Function

The ISDATE function determines whether the parameter passed is of a date and time data type. This function returns a BIT value, as in the following example:

1.SELECT ISDATE('february 31, 2009') AS 'february 39, 2009', ISDATE('January 1, 2009') AS '1/1/2009'

Results:

february 39, 2009 1/1/2009  
----------------- -----------  
0                 1

ISNUMERIC Function

The ISNUMERIC deterministic function determines whether the parameter passed is of a numeric data type. This function returns a BIT value, as in the following example:

1.SELECT ISNUMERIC('abc') AS 'abc', ISNUMERIC('123.45') AS '123.45'

Results:

abc         123.45  
----------- -----------  
0           1

CURRENT_TIMESTAMP Function

The CURRENT_TIMESTAMP function works exactly the same way as GETDATE: It returns current date and time. For example:

1.SELECT CURRENT_TIMESTAMP

Results:

1.-----------------------

2.2006-03-19 16:24:57.827

DATALENGTH Function

The DATALENGTH deterministic function is similar to the LEN function, which returns the length of a particular string expression. DATALENGTH returns the number of bytes used to represent an expression of any data type. For example, the following query returns the data length for currency which is stored as NCHAR(3) data type:

1.SELECT TOP 1 DATALENGTH(CurrencyAlternateKey) , CurrencyALternateKey FROM dimCurrency

Results:

CurrencyALternateKey  
----------- --------------------  
6           AED

@@TRANCOUNT Function

The @@TRANCOUNT function returns the number of open transactions on a particular connection. You can check the value of @@TRANCOUNT to troubleshoot blocking issues. @@TRANCOUNT can also be used for error handling; if @@TRANCOUNT returns anything other than 0, something must have gone wrong and you have uncommitted transactions on the current connection. This function does not take any parameters.

XACT_STATE Function

The XACT_STATE function is new with SQL Server 2005. It is similar to @@TRANCOUNT since it determines whether there are any uncommitted transactions on the current connection. The XACT_STATE function does not accept any parameters. Unlike @@TRANCOUNT the XACT_STATE function can also determine if the uncommitted transaction has been classified as an uncomittable transaction. This function can return one of the following values:

1 The session has an active transaction. The transaction can be committed.
0 The session has NO active transactions.
(-1) The session has an active transaction; an error has occurred which classifies the active transaction as uncommittable. The transaction cannot be committed; neither can the session request rolling back to a savepoint. Rather the entire transaction must be rolled back. After the transaction has been rolled back the session can initiate a new transaction.

You can effectively use XACT_STATE function for error handling, as shown below:

1.IF (XACT_STATE()) = -1 BEGIN PRINT 'The transaction is in an uncommittable state. Rolling back transaction.' ROLLBACK TRANSACTION; END

@@ERROR Function

The @@ERROR function returns the number of the last error encountered on the current connection. If there are no errors, @@ERROR returns 0. The @@ERROR function is used for error handling. With SQL Server 2000 and previous releases checking @@ERROR was the only way to diagnose and troubleshoot errors. SQL Server 2005 introduces TRY / CATCH syntax for error handling as well as several new functions: ERROR_LINE, ERROR_NUMBER, ERROR_SEVERITY, ERROR_STATE, and ERROR_MESSAGE. Note that @@ERROR returns the error number returned by the last executed statement, so it's important to catch the error immediately after it occurs. For example, the following query catches the error because it checks for error immediately after the statement that encountered the error:

1.SELECT 1 / 0 SELECT 'error number is: ' + CAST(@@ERROR AS VARCHAR)

Results:

-----------  
Msg 8134, Level 16, State 1, Line 1  Divide by zero error encountered.      
-----------------------------------------------  
error number is: 8134

However, the next example does NOT catch the error because it checks the @@ERROR function value too late, after a statement that completes successfully:

SELECT 1 / 0  
SELECT 'this is a successful statement. it resets @@ERROR to zero!' 
SELECT 'error number is: ' + CAST(@@ERROR AS VARCHAR)

Results:

-----------  
Msg 8134, Level 16, State 1, Line 1  Divide by zero error encountered.      
-----------------------------------------------------------  
this is a successful statement. it resets @@ERROR to zero!     
-----------------------------------------------  
error number is: 0

@@ROWCOUNT Function

The @@ROWCOUNT function returns the number of rows affected by the last query. This function can be used effectively to find out whether the number of rows modified is the same as what you intended to modify. If your query was supposed to update 15 rows but @@ROWCOUNT returns 10 then something must have gone wrong. This function is often used for error handling. Much like with @@ERROR it's important to get @@ROWCOUNT value immediately after the statement you want to examine. For example, the following query erroneously reports that total number of affected rows is one, even though your main query returned 10 rows, as desired:

SELECT TOP 10 * FROM dimCustomer  
SELECT 'this is a successfull statement. it resets @@ROWCOUNT to one!' 
SELECT 'number of rows affected is: ' + CAST(@@ROWCOUNT AS VARCHAR)

The ROWCOUNT_BIG() function does the same thing as @@ROWCOUNT, but returns a BIGINT data type instead of an INT data type.
Note: It's easy to confuse the functionality of ROWCOUNT and @@ROWCOUNT. The former advises SQL Server to affect only a specified number of rows (similar to the TOP keyword); the latter simply counts the number of rows affected, as shown here:

/* first limit the output to 2 rows */ 
SET ROWCOUNT 2    
/* this query will affect only 2 rows */ 
SELECT EnglishProductSubcategoryName FROM DimProductSubCategory    
/* now use functions to count the number of  affected rows */ 
SELECT @@ROWCOUNT AS '@@rowcount_output',   ROWCOUNT_BIG() AS 'rowcount_big_output'

Results:

EnglishProductSubcategoryName  
--------------------------------------------------  
Mountain Bikes  Road Bikes    
@@rowcount_output rowcount_big_output  
----------------- --------------------  
2                 2

APP_NAME() Function

The APP_NAME() function returns a string with the name of the application that initiated the database connection. APP_NAME() can be helpful if you're troubleshooting a connection and want to know which app initiated the offending process. For example:

1.SELECT APP_NAME()

Results:

1.-------------------------------------------------

2.Microsoft SQL Server Management Studio - Query

UPDATE() Function

The UPDATE() function is only available within INSERT or UPDATE triggers and determines whether a value of a single column has been modified. You can use this function to execute certain logic within a trigger only if the value of a particular column has changed. The function accepts column name as the only parameter. For example, the following query rolls back the transaction within trigger if the value of AccountCodeAlternateKey column has been changed:

1.IF UPDATE(AccountCodeAlternateKey) BEGIN RAISERROR('key cannot be updated', 16, 1) ROLLBACK END

COLUMNS_UPDATED() Function

The COLUMNS_UPDATED() function works similarly to UPDATE() except it checks for multiple columns being updated by the same statement. Like UPDATE() function the COLUMNS_UPDATED() is only available within insert and update triggers. This function returns a bit pattern with VARBINARY data type showing which columns have been affected by the INSERT or UPDATE statement that invoked the trigger. The function does not accept any parameters. For example, the following query checks whether columns 2, 3 and 4 have been modified:

1.IF (COLUMNS_UPDATED() & 14) = 14 BEGIN SELECT 'columns 2, 3, and 4 have been changed' END

ERROR_LINE Function

The ERROR_LINE function returns the line number at which the error occurred which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the line number where the error occurred:

BEGIN TRY
  SELECT 'empty string'
 
  SELECT 1 / 0
END TRY
 
BEGIN CATCH
  SELECT 'the error occurred at line ' + CAST(ERROR_LINE() AS VARCHAR)
END CATCH

Results:

------------  
empty string      
-----------      
--------------------------------------------------------  
the error occurred at line 3

ERROR_MESSAGE Function

The ERROR_MESSAGE function returns the text of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error text:

BEGIN TRY  
SELECT 1 / 0  
END TRY    
BEGIN CATCH  
SELECT 'the error was: ' + ERROR_MESSAGE() 
END CATCH

Results:

-----------      
--------------------------------------------------  
the error was: Divide by zero error encountered.

ERROR_NUMBER Function

The ERROR_NUMBER function returns the number of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error number:

BEGIN TRY  
SELECT 1 / 0  
END TRY    
BEGIN CATCH  
SELECT 'the error number was: ' + CAST(ERROR_NUMBER() AS VARCHAR) 
END CATCH

Results:

1.----------- --------------------------------------------------

2.the error number was: 8134

ERROR_PROCEDURE Function

The ERROR_PROCEDURE function returns the name of the stored procedure or trigger that encountered the error. This function does not accept any parameters and can be effectively called from CATCH block. For example, the following query creates a stored procedure that intentionally causes divide by zero error. Next the procedure is executed and the name of the erroneous stored procedure is returned:

CREATE PROCEDURE my_test_proc  AS   
SELECT 1 / 0  
GO   
BEGIN TRY  
EXEC my_test_proc  
END TRY    
BEGIN CATCH  
SELECT 'the erroneous procedure was: ' + ERROR_PROCEDURE() 
END CATCH

Results:

1.------------------------------------------

2.the erroneous procedure was: my_test_proc

ERROR_SEVERITY Function

The ERROR_SEVERITY function returns the severity of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error severity:

BEGIN TRY  
SELECT 1 / 0  
END TRY    
BEGIN CATCH  
SELECT 'the error severity was: ' + CAST(ERROR_SEVERITY() AS VARCHAR) 
END CATCH

Results:

1.------------------------------------------------------

2.the error severity was: 16

ERROR_STATE Function

The ERROR_STATE function returns the state of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error state:

BEGIN TRY  
SELECT 1 / 0  
END TRY    
BEGIN CATCH  
SELECT 'the error state was: ' + CAST(ERROR_STATE() AS VARCHAR) 
END CATCH

Results:

1.------------------------------------------------------

2.the error state was: 1

Additional System Functions Function

The following table provides a quick reference for what the rest of the system functions do:

System Function	Parameters	Description and Example
STATS_DATE	Table_id, index_id	Determining the last time statistics were updated. Example: view source print ? `1.SELECT` `TOP` `1` `STATS_DATE(object_id, index_id)` `FROM` `sys.indexes` Results: view source print ? `1.-----------------------` `2.2005-10-14 01:36:26.140`
COLLATIONPROPERTY	Collation_name, property	Determining the value of a certain collation property. Example: view source print ? `1.SELECT` `COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS',` `'codepage')` Results: view source print ? `1.----` `1252`
FN_HELPCOLLATIONS	None	Returns the list of supported collations. Example: view source print ? `1.SELECT` `*` `FROM` `FN_HELPCOLLATIONS()`
FN_SERVERSHAREDDRIVES	None	Returns the list of shared drives in clustered servers. Example: view source print ? `1.SELECT` `*` `FROM` `FN_SERVERSHAREDDRIVES()`
FN_VIRTUALFILESTATS	Database_id, file_id	Determining I/O stats for a certain database or log file. Example: view source print ? `1.SELECT` `*` `FROM` `FN_VIRTUALFILESTATS(10, 1)`
FORMATMESSAGE	Message number, parameter value	Constructing an error message using an existing message in sysmessages table. Example: view source print ? `1.sp_addmessage` `50002, 16,` `'terrible error on %s'` `2.GO` `3.DECLARE` `@var1` `VARCHAR(100)` `4.SELECT` `@var1` `=` `FORMATMESSAGE(50002,` `'Table1')` `5.SELECT` `@var1` Results: view source print ? `1.--------------------------` `terrible error` `on` `Table1`
SERVERPROPERTY	Property name	Returns a server property value. The following example returns the edition of the current instance: view source print ? `1.SELECT` `SERVERPROPERTY('edition')` Results: view source print ? `1.-----------------` `Developer Edition`
SESSIONPROPERTY	Option	Getting a session value for various session options configured using SET statements. The following query returns ANSI_NULLS setting: view source print ? `1.SELECT` `SESSIONPROPERTY('ANSI_NULLS')` Results: view source print ? `1.--` `1`
PARSENAME	Object_name, object_piece	Returning server, owner, database or name portion of the specified object. Object piece can take the following values: 1 - object name 2 - schema name 3 - database name 4 - server name Example: view source print ? `1.SELECT` `PARSENAME('dbo.DimAccount', 2)` Results: view source print ? `1.-----` `Dbo`

Pages

Monday, November 11, 2013

General HR questions

Friday, January 18, 2013

Friday, December 14, 2012

I have a javascript code to redirect mobile phone users. Could you please check the code, I've picked it up from the net, total newb and I'd value your input regarding the quality of the script... Does it cover all types of mobile phones?

Thursday, August 4, 2011

Wednesday, July 20, 2011

Date Only

Produce a Row or Table Checksum

Reindex an entire database

Count Character in a String

Single & Multi User Mode

Smart Date Ranges

Drop all Database Connections

Tuesday, July 12, 2011

Tuesday, February 1, 2011

Introduction

Compare

SQL Query

Contents

CASE Function

COALESCE Function

ISNULL Function

NULLIF Function

GETANSINULL Function

CAST and CONVERT Function

@@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() Function

ISDATE Function

ISNUMERIC Function

CURRENT_TIMESTAMP Function

DATALENGTH Function

@@TRANCOUNT Function

XACT_STATE Function

@@ERROR Function

@@ROWCOUNT Function

APP_NAME() Function

UPDATE() Function

COLUMNS_UPDATED() Function

ERROR_LINE Function

ERROR_MESSAGE Function

ERROR_NUMBER Function

ERROR_PROCEDURE Function

ERROR_SEVERITY Function

ERROR_STATE Function

Additional System Functions Function

How this blog is usefull?

Search This Blog

Feed

About Me

Labels