sql question

A good sql question:- what command should you write other than print  after set statement to get the message as shown below :)

sql server interview questions mostly asked

·         What is RDBMS?

o   RDBMS stands for Relational Database Management System. RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields. 

RDBMS store the data into collection of tables, which might be related by common fields (database table columns). RDBMS also provide relational operators to manipulate the data stored into the database tables. Most RDBMS use SQL as database query language.

• What are the Properties of the Relational Tables?

o    Relational tables have six properties 
1. Values are atomic. 
2. Column values are of the same kind. 
3. Each row is unique. 
4. The sequence of columns is insignificant. 
5. The sequence of rows is insignificant. 
6. Each column must have a unique name. 

• What is Normalization?

o    Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.

o    Edgar F. Codd, the inventor of the relational model

• What is De-normalization?

o    Denormalization is a strategy that database managers use to increase the performance of a database infrastructure. It involves adding redundant data to a normalized database to reduce certain types of problems with database queries that combine data from various tables into a single table. The definition of denormalization is dependent on the definition of normalization, which is defined as the process of organizing a database into tables correctly to promote a given use.

• How is ACID property related to Database?

o    The ACID properties of a DBMS allow safe sharing of data. Without these ACID properties, everyday occurrences such using computer systems to buy products would be difficult and the potential for inaccuracy would be huge. Imagine more than one person trying to buy the same size and color of a sweater at the same time -- a regular occurrence. The ACID properties make it possible for the merchant to keep these sweater purchasing transactions from overlapping each other -- saving the merchant from erroneous inventory and account balances.

• What is a Stored Procedure?

o    The name for a batch of Transact-SQL or CLR code that is stored within SQL Server and can be called directly by applications or within other programming constructs.

Types of Stored Procedures

There are two main types of stored procedure -

System stored procedures and

User-defined stored procedures

·         What is Trigger?

o    A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries

• What is a View?

o    A view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

• What is the Difference between a Function and a Stored Procedure?

o    Procedure can return zero or n values whereas function can return one value which is mandatory.

o    Procedures can have input/output parameters for it whereas functions can have only input parameters.

o    Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

o    Functions can be called from procedure whereas procedures cannot be called from function.

o    Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in function.

o    We can go for transaction management in procedure whereas we can't go in function.

o    Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

o    UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

o    UDFs that return tables can be treated as another row set. This can be used in JOINs with other tables.

o    Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Rowsetoperations.

·         What is subquery?

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

Subqueries are an alternate way of returning data from multiple tables.

Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.

§   SELECT

§   INSERT

§   UPDATE

§   DELETE

·          Find rest sql questions:-

o    What are Different Types of Join?

o    What are Primary Keys and Foreign Keys?

o    What is User-defined Functions? What are the types of User-defined Functions that can be created?

o    What’s the Difference between a Primary Key and a Unique Key?

o    What is Difference between DELETE and TRUNCATE Commands?

o    What is the Difference between a HAVING clause and a WHERE clause?

o    Name 3 ways to get an Accurate Count of the Number of Records in a Table?

o    What is CHECK Constraint?

o    What is NOT NULL Constraint?

o    What is the difference between UNION and UNION ALL?

o    What is BCP? When is it used?

o    What is Aggregate Functions?

o    Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select from table)?

o    What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?

o    Does the Order of Columns in UPDATE statements Matter?

o    Explain Few of the New Features of SQL Server 2008 Management Studio 

sql server trim word from last

SQL server trim word from last

Suppose we have a string: I am a good boy.John is bad boy

Requirement: To remove boy from last.

DECLARE @replcae_str VARCHAR(max)

DECLARE @str VARCHAR(max)

SELECT @replcae_str = 'boy'

SELECT @str = 'I am a good boy. John is bad boy'

SELECT

 SUBSTRING

  (

  @str,

  1,

  CASE CHARINDEX(REVERSE(@replcae_str),REVERSE(@str))

  when 0 then 0   

  else LEN(@str) - ( CHARINDEX(REVERSE(@replcae_str), REVERSE(@str)) + (LEN(@replcae_str) - 1) ) + 1

  END - 1

  )

sql server query optimization tips

• 
  QUERY OPTIMIZATION SQL
•   The sql query becomes faster if you use the actual columns names in                               SELECT statement instead of than '*'
•  Select carefully where we have to use “where” and “having”

·         Sometimes you may have more than one sub queries in your main query. Try to minimize the number of sub query block in your query. 
For Example: Write the query as

“SELECT name
FROM employee
WHERE (salary, emp_age) = (SELECT MAX (salary), MAX (emp_age)
FROM employee_details)
AND dept = 'Electronics'; “

Instead of:

“SELECT name
FROM employee
WHERE salary = (SELECT MAX (salary) FROM employee_details)
AND age = (SELECT MAX (emp_age) FROM employee_details)
AND emp_dept = 'Electronics';”

·         Usually “IN” has the slowest performance, “IN” is efficient when most of the filter criteria is in the sub-query

·         “EXISTS” is efficient when most of the filter criteria is in the main query

·         Use “EXISTS” instead of “DISTINCT” when using joins which involves tables having one-to-many relationship. 
For Example: Write the query as

SELECT d.dept_id, d.dept_name
FROM dept d
WHERE EXISTS (SELECT 'X' FROM employee e WHERE e.dept_name = d.dept_name);

Instead of:

SELECT DISTINCT d.dept_id, d.dept_name
FROM dept d,employee e
WHERE e.dept_name = e.dept_name;

·         Try to use UNION ALL in place of UNION

·         Be careful while using conditions in WHERE clause. 
For Example: Write the query as

SELECT id, name, age FROM student WHERE age > 30;

Instead of:

SELECT id, name, age FROM student WHERE age! = 50;

SQL server Check Ascii charater value for a string

Check Ascii charater value for a string

DECLARE @string VARCHAR(50)

DECLARE @i INT

SET @string = 'your String'

SET @i = 0

WHILE @i <= DATALENGTH(@string)

BEGIN

PRINT SUBSTRING(@string, @i, 1) + ' : ' + CAST(ASCII(SUBSTRING(@string, @i, 1)) AS VARCHAR(3))

SET @i = @i + 1

END

Clean\delete\truncate full database having key constraints

Clean\delete\truncate full database having key constraints

-- Disable the Referential Integrity

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

EXEC sp_MSForEachTable 'DELETE FROM ?'

GO

-- Enable Referential Integrity again

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

Case Sensitive JOIN

For case senstive joins use

• COLLATE Latin1_General_CS_AS

For example:

SELECT table1.column1, table1.column2,table2.column1

FROM 

table1 T1 inner join table2 T2

ON

T1.column1 COLLATE Latin1_General_CS_AS = T2.column1 COLLATE Latin1_General_CS_AS