Tuesday, March 22, 2011

Find Created date and Modified date of Database objects

This script will return the objects in sql server database with modified and created date.
However you can change the order by clause as well as the Where clause according to your requirements

SELECT sys.schemas.name + '.' + sys.objects.name,
create_date,
modify_date
FROM sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE type = 'P'
OR type = 'U'
OR type = 'FN'
OR type = 'V'
ORDER BY modify_date DESC

Thursday, March 10, 2011

The newsequentialid() built-in function can only be used in a DEFAULT expression

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.


This error occurs when we try to use the NEWSEQUENTIALID() function in the SQL statements or in SET statements. e.g.

SELECT NEWSEQUENTIALID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique
OR
INSERT INTO AA(id, name) VALUES(NEWSEQUENTIALID(),'abc')

The NEWSEQUENTIALID() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.


Monday, March 7, 2011

SQL Server Interview Questions (Part 2)

Few days back, i went for an interview , following questions were asked
  • How to Performance Tune a Stored Procedure?
  • Type of Triggers?
  • How to get the old value of a field from a trigger when updating a table?
  • What is the difference between horizontal partition and vertical partition?
  • if we have three parameters in the where condition (query has the joins with two or more tables table1.filed1=4 and table2.field2=5 and table3.field3=1)
    what will be the Order of execution of the where condition
    Which condition would be executed first i.e. field1 or field2 or field3?
  • What are the disadvantages of the cursors?
  • A stored procedure has cursors in it, how we can eliminate the cursors?
  • What is the difference between temporary table and table variable?
  • How to restore the Differential Backup?
  • What are Backup Types and difference between them?
  • What are Replication Types, What is snapshot replication?
  • What is the difference between Replication and Mirroring?
  • Can a sub report in Crystal Reports can have another sub report?
  • What is log shipping?
  • Should we normalize the Database on more than 3rd Normal form?

Share This