Sql Server Interview Questions and Answers (1758) - Page 12

Join puts data from two or more tables into a single result set.

In Database for any table or view When a trigger is fired for any DML command.
Then 2 tables automatically create on backend.
One table is for Insert and other one is for Delete.
These tables are called Magic Tables.
Number of records in both tables should be same.

Returns the first not null expression among its arguments.
COALESCE(X,Y,Z)

If X is Null then It'll check for Y,
If Y is Null It'll Check for Z.

Let X is NULL, Y='5' and Z='9'
Here Output is : Y

2 Type of Authentication :

1. Windows Authentication and
2. SQL Server authentication

Dynamic sql is the bunch of statements that dynamically constructed at run time and not stored in database.
Where as Stored procedures are stored in data base in complied form.

The global variable @@Version is used to determine the service pack installed.
Ex : SELECT @@Version
O/P : Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

False.
There can only be one IDENTITY column per table in SQL Server.
Its auto Incremented Column defined by the user.

Yes, it is. You can use FOR clause with XML or BROWSE options.

Wildcard characters Determines whether a given character string matches a specified pattern.
A pattern can include regular characters and wildcard characters.
During pattern matching, regular characters must match exactly the characters specified in the character string. Wildcard characters, however, can be matched with arbitrary fragments of the character string.
Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators.

There are 2 Wildcard characters i.e. '%' and '_'
%
Any string of zero or more characters.
WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.

_ (underscore)
Any single character.
WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean, such as Dean or Sean.

Transparent Data Encryption. The ability to encrypt an entire database.

Backup Encryption. Executed at backup time to prevent tampering.

External Key Management. Storing Keys separate from the data.

Auditing. Monitoring of data access.

Data Compression. Fact Table size reduction and improved performance.

Resource Governor. Restrict users or groups from consuming high levels or resources.

Hot Plug CPU. Add CPUs on the fly.

Performance Studio. Collection of performance monitoring tools.

Installation improvements. Disk images and service pack uninstall options.

Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.

Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)

LINQ. Development query language for access multiple types of data such as SQL and XML.

Data Synchronizing. Development of frequently disconnected applications.

Large UDT. No size restriction on UDT.

Dates and Times. New data types: Date, Time, Date Time Offset.

File Stream. New data type VarBinary(Max) FileStream for managing binary data.

Table Value Parameters. The ability to pass an entire table to a stored procedure.

Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.

Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.

Reporting Server. Improved memory management.

SQL Server Integration Service. Improved multiprocessor support and faster lookups.

MERGE. TSQL command combining Insert, Update, and Delete.

SQL Server Analysis Server. Stack improvements, faster block computations.

SQL Server Reporting Server. Improved memory management and better rendering.

The best query is

Select ID,Name from Table1

Don't use

Select * from Table1

As it creates an extra overhead and reduces performance. Always fetch only those column which are needed to get better performance.

COALESCE Returns the first not null expression among its arguments.

document.all["ScreenStyle"].setAttribute ("HREF", "GreenColorStyles.css", 0);

We can do this through SQL Job

sp_addsrvrolemember = Used to add a login as a member of fixed server role.sp_helpdb = Used to see the details of database.sp_bindrule = Binds the rule with database object.sp_unbindrule = Unbinds the rule from database object.sp_rename = Renames the table,index and views.sp_help = Used to see the details of tables.sp_helpindex = Used to display info about the indexes.sp_fulltext_database = Used to enable the database to use full text service.sp_helptext = Used to display definition of user-defined rule.sp_configure = Used to enable the CLR integration.sp_lock = Used to display info about locks.sp_dbremove = Used to rename a database.

select * from sysdatabases

By the query

select *  from sysobjects where xtype='U' order by name

Using the query

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'Emp' ORDER BY COLUMN_NAME

The stored procedure is created using keyword Create Proc or Create Procedure.The procedure should be paramerized along with data types.
The required logic should be inserted in begin and end block
In order to execute the stored procedure use exec

ASCII will return the ascii value of the character expression