General information about Netezza : Default Error details

 How data can be copied from one table to another table?

INSERT INTO SELECT

This command is used to insert data into a table which is already created.

SELECT INTO

This command is used to create a new table and its structure and data can be copied from existing table.

How to delete duplicate rows in SQL Server?

Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.

What is an IDENTITY column in insert statements?

IDENTITY column is used in table columns to make that column as Auto incremental number or a surrogate key.

 What is recursive stored procedure?

SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels.

CREATE PROCEDURE [dbo].[Fact]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively)
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN
GO

 What is CHECK constraint?
A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.
. Can SQL servers linked to other servers?
SQL server can be connected to any database which has OLE-DB provider to give a link. Example: Oracle has OLE-DB provider which has link to connect with the SQL server group.
What is sub query and its properties?
A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as
A sub query should not have order by clause
A sub query should be placed in the right hand side of the comparison operator of the main query
A sub query should be enclosed in parenthesis because it needs to be executed first before the main query
More than one sub query can be included

What will be query used to get the list of triggers in a database?

Query to get the list of triggers in database-

Select * from sys.objects where type='tr'

What is the difference between UNION and UNION ALL?

• UNION: To select related information from two tables UNION command is used. It is similar to JOIN command.
• UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.

What are the differences between Stored Procedure and the dynamic SQL?

Stored Procedure is a set of statements which is stored in a compiled form. Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply execute during run time.

What is the difference between varchar and nvarchar types?

Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.

 What is COALESCE function?

COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL.

What is a sub query and what are the different types of subqueries?

Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.

There are two different types of subqueries:

• Correlated sub query

A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.

• Non-Correlated subquery

This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.

What is cross join?

Cross join is defined as the Cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table. 

What will be the maximum number of index per table?

For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

What is the difference between GETDATE and SYSDATETIME?

Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.