Generate AJAX Load Image with ajaxload.info it generates .gif file.
For displaying please wait message for heavy loading task in your web application
Click here
Thursday, August 23, 2007
Generate AJAX Load Image
Displaying Google Map in your asp.net web application
Displaying Google Map in asp.net web application
Found 2 useful link which will help to display google map in asp.net web application within few minutes.
SQLScheduler to schedule various SQL jobs
SQLScheduler to schedule various SQL jobs
SQLScheduler is a fully functional client/server application written in C# that allows administrators to schedule various SQL jobs for SQL Server Express and other versions of SQL Server.
Features:
- Supports all versions of SQL Server 2000 and 2005
- Supports unlimited SQL Server instances with an unlimited number of jobs.
- Allows to easily schedule SQL Server maintenance tasks: backups, index rebuilds, integrity checks, etc.
- Runs as Windows Service
- Email notifications on job success and failure
- And more...
Requirements:
- Windows 2000, 2003, XP
- .NET Framework 2.0
- SQL Server 2000, 2005
Product Page
Screenshot
Wednesday, August 22, 2007
Tafiti.com - Silverlight Based Search Engine
Tafiti.com - Silverlight Based Search Engine
http://www.tafiti.com/
New stunning look for search
While Searching
Search Result
Filtering your search Result
Saving your search Result on Glass and Facility to Blog
As far as search result is concern, i don't find it as stunning as it looks well you can use SMART Search Engine for your Programming needs
Tips to become Happy Software Engineer
How to become happy software engineer
I come across few of articles on Net which discuss day to day life problem of software engineer.
Well Things sounds to be funny but its really inspiring to get out from box and shows how you can think bigger and achieve from the available resource, skill and environment.
- I don't get things that I deserve
- 18 Ways to Stay Focused at Work
- Improve your life one step at a time
- Goal setting
Saturday, August 18, 2007
How to send email from localhost
How to send email from local machine
Found an article on Sending Email from Localhost
Click here
Alternate way when you don't have your own SMTP server you can use third party email service to send email.
To know How to send email using Free Email Service. i.e. How to send email from gmail, yahoo, hotmail, rediffmail, etc
Click here
Friday, August 17, 2007
Download Visual Studio 2008
Visual Studio 2008 beta2 is available for download
- Download Visual Studio 2008 Beta 2
- Download Visual Studio 2008 Beta 2 Express Editions
- Get the .NET Framework 3.5 Beta 2
- Visual Studio on Windows Vista
- Visual Studio 2005 Service Pack
SQL Server 2005 Tutorial and Future of SQL Server
Brief and easy tutorial for SQL Server 2005 Tutorial
Lesson 1. SQL Server 2005 Overview
Lesson 2. Overview of SQL Server 2005 Architecture
Lesson 3. Installing SQL Server 2005
Lesson 4. Transact-SQL Enhancements in SQL Server 2005
Lesson 5. XML integration with SQL Server 2005
Lesson 6. Using the .NET CLR in SQL Server 2005
Lesson 7. Developing Client Applications with ADO .NET 2.0
Lesson 8. Using Service Broker
Lesson 9. Using Native HTTP Support
Lesson 10. Using Notification Services
Future of SQL Server
SQL Server 2008 code named "Katmai"
Best Practices for SQL Server 2000
Article on SQL Server 2000 Best Practices
It contain 35 useful tips, check out
Thursday, August 16, 2007
SQL Server Keyboard Shortcuts
SQL Server Keyboard Shortcuts
Complete list of SQL Server Keyboard Shortcuts
Click here
Useful Website for .Net Resource
Useful Website for .Net Resource, article are brief, and provide complete information on given topic.
Wednesday, August 15, 2007
Webcasts on ASP.NET and .NET Framework 3.0
ASP.NET Developer Series by Harish Ranganathan | ||||||||||||
| A series for Web Developers to understand some of the key concepts like ASP.NET 2.0 Deployment, Upgrading ASP.NET 2.0 Applications to AJAX and also understand some of the Advanced ASP.NET AJAX Patterns. It also provides a sneak preview onto the features that are part of the next version of ASP.NET and what is in Visual Studio 2008 for web developers and how it can help develop next generation web applications. | ||||||||||||
|
|
| ||||||||||||
| Making the most out of .NET Framework 3.0 by Bijoy Singhal | ||||||||||||
| Objective of the series is to help Developers understand the basic to advanced concepts of the .NET platform, how it is different from the WIN32 and how to leverage its capabilities to the maximum. | ||||||||||||
|
|
|
| Harish Ranganathan and Bijoy Singhal are Technology Evangelists with Microsoft India. |
| Register Now for Free. Limited seats available. |
Happy Independence Day
Tuesday, August 14, 2007
SQL Optimization Tips
SQL Optimization Tips
• Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to
server only stored procedure or view name (perhaps with some
parameters) instead of large heavy-duty queries text. This can be used
to facilitate permission management also, because you can restrict
user access to table columns they should not see.
• Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost
performance. So, you should use constraints instead of triggers,
whenever possible.
• Use table variables instead of temporary tables.
Table variables require less locking and logging resources than
temporary tables, so table variables should be used whenever possible.
The table variables are available in SQL Server 2000 only.
• Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL
statement does not look for duplicate rows, and UNION statement does
look for duplicate rows, whether or not they exist.
• Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance
degradation, you should use this clause only when it is necessary.
• Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in
comparison with select statements. Try to use correlated sub-query or
derived tables, if you need to perform row-by-row operations.
• Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the
GROUP BY clause. When you use GROUP BY with the HAVING clause, the
GROUP BY clause divides the rows into sets of grouped rows and
aggregates their values, and then the HAVING clause eliminates
undesired aggregated groups. In many cases, you can write your select
statement so, that it will contain only WHERE and GROUP BY clauses
without HAVING clause. This can improve the performance of your query.
• If you need to return the total table's row count, you can use
alternative way instead of SELECT COUNT(*) statement.
Because SELECT COUNT(*) statement make a full table scan to return the
total table's row count, it can take very many time for the large
table. There is another way to determine the total row count in a
table. You can use sysindexes system table, in this case. There is
ROWS column in the sysindexes table. This column contains the total
row count for each table in your database. So, you can use the
following select statement instead of SELECT COUNT(*): SELECT rows
FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So,
you can improve the speed of such queries in several times.
• Include SET NOCOUNT ON statement into your stored procedures to stop
the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive
the message indicating the number of rows affected by a T-SQL statement.
• Try to restrict the queries result set by using the WHERE clause.
This can results in good performance benefits, because SQL Server will
return to client only particular rows, not all rows from the table(s).
This can reduce network traffic and boost the overall performance of
the query.
• Use the select statements with TOP keyword or the SET ROWCOUNT
statement, if you need to return only the first n rows.
This can improve performance of your queries, because the smaller
result set will be returned. This can also reduce the traffic between
the server and the clients.
• Try to restrict the queries result set by returning only the
particular columns from the table, not all table's columns.
This can results in good performance benefits, because SQL Server will
return to client only particular columns, not all table's columns.
This can reduce network traffic and boost the overall performance of
the query.
1.Indexes
2.avoid more number of triggers on the table
3.unnecessary complicated joins
4.correct use of Group by clause with the select list
5 In worst cases Denormalization
Index Optimization tips
• Every index increases the time in takes to perform INSERTS, UPDATES
and DELETES, so the number of indexes should not be very much. Try to
use maximum 4-5 indexes on one table, not more. If you have read-only
table, then the number of indexes may be increased.
• Keep your indexes as narrow as possible. This reduces the size of
the index and reduces the number of reads required to read the index.
• Try to create indexes on columns that have integer values rather
than character values.
• If you create a composite (multi-column) index, the order of the
columns in the key are very important. Try to order the columns in the
key as to enhance selectivity, with the most selective columns to the
leftmost of the key.
• If you want to join several tables, try to create surrogate integer
keys for this purpose and create indexes on their columns.
• Create surrogate integer primary key (identity for example) if your
table will not have many insert operations.
• Clustered indexes are more preferable than nonclustered, if you need
to select by a range of values or you need to sort results set with
GROUP BY or ORDER BY.
• If your application will be performing the same query over and over
on the same table, consider creating a covering index on the table.
• You can use the SQL Server Profiler Create Trace Wizard with
"Identify Scans of Large Tables" trace to determine which tables in
your database may need indexes. This trace will show which tables are
being scanned by queries instead of using an index.
• You can use sp_MSforeachtable undocumented stored procedure to
rebuild all indexes in your database. Try to schedule it to execute
during CPU idle time and slow production periods.
sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
For SQL SERVER Frequently Asked Interview Questions
Monday, August 13, 2007
SQL Server FAQ Interview Questions
SQL Server FAQ Interview Questions
- SQL Queries FAQ
- FAQ on Indexes in SQL
- FAQ on Data Types in SQL Server
- FAQ on Joins in SQL Server
- FAQ on Stored Procedure in SQL Server
- FAQ on Trigger in SQL Server
- FAQ on Lock in SQL Server
- FAQ on View in SQL Server
- FAQ on Transaction in SQL Server
- SQL Server FAQ on Random Category
- FAQ on Tools in SQL Server
- FAQ on Permissions in SQL Server
- FAQ on Administration in SQL Server
- SQL Optimization Tips
FAQ on Administration in SQL Server
FAQ on Administration in SQL Server
159. Explain the architecture of SQL Server?
**
160. Different types of Backups?
o A full database backup is a full copy of the database.
o A transaction log backup copies only the transaction log.
o A differential backup copies only the database pages modified after
the last full database backup.
o A file or filegroup restore allows the recovery of just the portion
of a database that was on the failed disk.
161. What are `jobs' in SQL Server? How do we create one? What is tasks?
Using SQL Server Agent jobs, you can automate administrative tasks and
run them on a recurring basis.
**
162. What is database replication? What are the different types of
replication you can set up in SQL Server? How are they used?
Replication is the process of copying/moving data between databases on
the same or different servers. SQL Server supports the following types
of replication scenarios:
Snapshot replication
Transactional replication (with immediate updating subscribers, with
queued updating subscribers)
Merge replication
163. What are the different types of replications available in
sqlserver and brief about each?
**
164. What is snapshot replication how is it different from
Transactional replication?
Snapshot replication distributes data exactly as it appears at a
specific moment in time and does not monitor for updates to the data.
Snapshot replication is best used as a method for replicating data
that changes infrequently or where the most up-to-date values (low
latency) are not a requirement. When synchronization occurs, the
entire snapshot is generated and sent to Subscribers.
Snapshot replication would be preferable over transactional
replication when data changes are substantial but infrequent. For
example, if a sales organization maintains a product price list and
the prices are all updated at the same time once or twice each year,
replicating the entire snapshot of data after it has changed is
recommended. Creating new snapshots nightly is also an option if you
are publishing relatively small tables that are updated only at the
Publisher.
Snapshot replication is often used when needing to browse data such as
price lists, online catalogs, or data for decision support, where the
most current data is not essential and the data is used as read-only.
These Subscribers can be disconnected if they are not updating the data.
Snapshot replication is helpful when:
• Data is mostly static and does not change often. When it does
change, it makes more sense to publish an entirely new copy to
Subscribers.
• It is acceptable to have copies of data that are out of date for a
period of time.
• Replicating small volumes of data in which an entire refresh of the
data is reasonable.
Snapshot replication is mostly appropriate when you need to distribute
a read-only copy of data, but it also provides the option to update
data at the Subscriber. When Subscribers only read data, transactional
consistency is maintained between the Publisher and Subscribers. When
Subscribers to a snapshot publication must update data, transactional
consistency can be maintained between the Publisher and Subscriber
because the dat
FAQ on Permissions in SQL Server
FAQ on Permissions in SQL Server
149. A user is a member of Public role and Sales role. Public role has
the permission to select on all the table, and Sales role, which
doesn't have a select permission on some of the tables. Will that user
be able to select from all tables?
**
150. If a user does not have permission on a table, but he has
permission to a view created on it, will he be able to view the data
in table?
Yes.
151. Describe Application Role and explain a scenario when you will
use it?
**
152. What is the difference between the REPEATABLE READ and SERIALIZE
isolation levels?
The level at which a transaction is prepared to accept inconsistent
data is termed the isolation level. The isolation level is the degree
to which one transaction must be isolated from other transactions. A
lower isolation level increases concurrency, but at the expense of
data correctness. Conversely, a higher isolation level ensures that
data is correct, but can affect concurrency negatively. The isolation
level required by an application determines the locking behavior SQL
Server uses.
SQL-92 defines the following isolation levels, all of which are
supported by SQL Server:
• Read uncommitted (the lowest level where transactions are isolated
only enough to ensure that physically corrupt data is not read).
• Read committed (SQL Server default level).
• Repeatable read.
• Serializable (the highest level, where transactions are completely
isolated from one another).
Isolation level Dirty read Nonrepeatable read Phantom
Read uncommitted Yes Yes Yes
Read committed No Yes Yes
Repeatable read No No Yes
Serializable No No No
153. Uncommitted Dependency (Dirty Read) - Uncommitted dependency
occurs when a second transaction selects a row that is being updated
by another transaction. The second transaction is reading data that
has not been committed yet and may be changed by the transaction
updating the row. For example, an editor is making changes to an
electronic document. During the changes, a second editor takes a copy
of the document that includes all the changes made so far, and
distributes the document to the intended audience.
Inconsistent Analysis (Nonrepeatable Read) Inconsistent analysis
occurs when a second transaction accesses the same row several times
and reads different data each time. Inconsistent analysis is similar
to uncommitted dependency in that another transaction is changing the
data that a second transaction is reading. However, in inconsistent
analysis, the data read by the second transaction was committed by the
transaction that made the change. Also, inconsistent analysis involves
multiple reads (two or more) of the same row and each time the
information is changed by another transaction; thus, the term
nonrepeatable read. For example, an editor reads the same document
twice, but between each reading, the writer rewrites the document.
When the editor reads the document for the second time, it has changed.
Phantom Reads Phantom reads occur when an insert or delete action is
performed against a row that belongs to a range of rows being read by
a transaction. The transaction's first read of the range of rows shows
a row that no longer exists in the second or succeeding read, as a
result of a deletion by a different transaction. Similarly, as the
result of an insert by a different transaction, the transaction's
second or succeeding read shows a row that did not exist in the
original read. For example, an editor makes changes to a document
submitted by a writer, but when the changes are incorporated into the
master copy of the document by the production department, they find
that new unedited material has been added to the document by the
author. This problem could be avoided if no one could add new material
to the document until the editor and production department finish
working with the original document.
154. After removing a table from database, what other related objects
have to be dropped explicitly?
(view, SP)
155. You have a SP names YourSP and have the a Select Stmt inside the
SP. You also have a user named YourUser. What permissions you will
give him for accessing the SP.
**
156. Different Authentication modes in Sql server? If a user is logged
under windows authentication mode, how to find his userid?
There are Three Different authentication modes in sqlserver.
0. Windows Authentication Mode
1. SqlServer Authentication Mode
2. Mixed Authentication Mode
"system_user" system function in sqlserver to fetch the logged on user
name.
157. Give the connection strings from front-end for both type
logins(windows,sqlserver)?
This are specifically for sqlserver not for any other RDBMS
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Integrated
Security=SSPI (windows)
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Uid=" ";Pwd="
"(sqlserver)
158. What are three SQL keywords used to change or set someone's
permissions?
Grant, Deny and Revoke
For More SQL SERVER Frequently Asked Interview Questions
FAQ on Tools in SQL Server
FAQ on Tools in SQL Server
133. Have you ever used DBCC command? Give an example for it.
The Transact-SQL programming language provides DBCC statements that
act as Database Console Commands for Microsoft® SQL Serve 2000. These
statements check the physical and logical consistency of a database.
Many DBCC statements can fix detected problems. Database Console
Command statements are grouped into these categories.
Statement category Perform
Maintenance statements Maintenance tasks on a database, index, or
filegroup.
Miscellaneous statements Miscellaneous tasks such as enabling
row-level locking or removing a dynamic-link library (DLL) from memory.
Status statements Status checks.
Validation statements Validation operations on a database, table,
index, catalog, filegroup, system tables, or allocation of database pages.
DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC,
DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc.
134. How do you use DBCC statements to monitor various aspects of a
SQL server installation?
135. What is the output of DBCC Showcontig statement?
Displays fragmentation information for the data and indexes of the
specified table.
136. How do I reset the identity column?
You can use the DBCC CHECKIDENT statement, if you want to reset or
reseed the identity column. For example, if you need to force the
current identity value in the jobs table to a value of 100, you can
use the following:
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 100)
GO
137. About SQL Command line executables
Utilities
bcp
console
isql
sqlagent
sqldiag
sqlmaint
sqlservr
vswitch
dtsrun
dtswiz
isqlw
itwiz
odbccmpt
osql
rebuildm
sqlftwiz
distrib
logread
replmerg
snapshot
scm
regxmlss
138. What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a
transaction manager that allows client applications to include several
different sources of data in one transaction. MS DTC coordinates
committing the distributed transaction across all the servers enlisted
in the transaction.
139. What is DTS? Any drawbacks in using DTS?
Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) is a
set of graphical tools and programmable objects that lets you extract,
transform, and consolidate data from disparate sources into single or
multiple destinations.
140. What is BCP?
The bcp utility copies data between an instance of Microsoft® SQL
Server™ 2000 and a data file in a user-specified format.
C:\Documents and Settings\sthomas>bcp
usage: bcp {dbtable query} {in out queryout format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
141. How can I create a plain-text flat file from SQL Server as input
to another application?
One of the purposes of Extensible Markup Language (XML) is to solve
challenges like this, but until all applications become XML-enabled,
consider using our faithful standby, the bulk copy program (bcp)
utility. This utility can do more than just dump a table; bcp also can
take its input from a view instead of from a table. After you specify
a view as the input source, you can limit the output to a subset of
columns or to a subset of rows by selecting appropriate filtering
(WHERE and HAVING) clauses.
More important, by using a view, you can export data from multiple
joined tables. The only thing you cannot do is specify the sequence in
which the rows are written to the flat file, because a view does not
let you include an ORDER BY clause in it unless you also use the TOP
keyword.
If you want to generate the data in a particular sequence or if you
cannot predict the content of the data you want to export, be aware
that in addition to a view, bcp also supports using an actual query.
The only "gotcha" about using a query instead of a table or view is
that you must specify queryout in place of out in the bcp command line.
For example, you can use bcp to generate from the pubs database a list
of authors who reside in
bcp "SELECT * FROM pubs..authors WHERE state = 'CA'" queryout
c:\CAauthors.txt -c -T -S
142. What are the different ways of moving data/databases between
servers and databases in SQL Server?
There are lots of options available, you have to choose your option
depending upon your requirements. Some of the options you have are:
BACKUP/RESTORE, detaching and attaching databases, replication, DTS,
BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT
scripts to generate data.
143. How will I export database?
Through DTS - Import/Export wizard
Backup - through Complete/Differential/Transaction Log
144. How to export database at a particular time, every week?
Backup - Schedule
DTS - Schedule
Jobs - create a new job
145. How do you load large data to the SQL server database?
bcp
146. How do you transfer data from text file to database (other than DTS)?
bcp
147. What is OSQL and ISQL utility?
The osql utility allows you to enter Transact-SQL statements, system
procedures, and script files. This utility uses ODBC to communicate
with the server.
The isql utility allows you to enter Transact-SQL statements, system
procedures, and script files; and uses DB-Library to communicate with
Microsoft® SQL Server™ 2000.
All DB-Library applications, such as isql, work as SQL Server
6.5–level clients when connected to SQL Server 2000. They do not
support some SQL Server 2000 features.
The osql utility is based on ODBC and does support all SQL Server 2000
features. Use osql to run scripts that isql cannot run.
148. What Tool you have used for checking Query Optimization? What is
the use of profiler in sql server? What is the first thing u look at
in a SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to
monitor events in an instance of Microsoft® SQL Server™. You can
capture and save data about each event to a file or SQL Server table
to analyze later. For example, you can monitor a production
environment to see which stored procedures is hampering performance by
executing too slowly.
Use SQL Profiler to:
• Monitor the performance of an instance of SQL Server.
• Debug Transact-SQL statements and stored procedures.
• Identify slow-executing queries.
• Test SQL statements and stored procedures in the development phase
of a project by single-stepping through statements to confirm that the
code works as expected.
• Troubleshoot problems in SQL Server by capturing events on a
production system and replaying them on a test system. This is useful
for testing or debugging purposes and allows users to continue using
the production system without interference.
Audit and review activity that occurred on an instance of SQL Server.
This allows a security administrator to review any of the auditing
events, including the success and failure of a login attempt and the
success and failure of permissions in accessing statements and objects
For More SQL SERVER Frequently Asked Interview Questions
SQL Server FAQ on Random Category
SQL Server FAQ on Random Category
87. What are the constraints for Table Constraints define rules
regarding the values allowed in columns and are the standard mechanism
for enforcing integrity. SQL Server 2000 supports five classes of
constraints.
NOT NULL
CHECK
UNIQUE
PRIMARY KEY
FOREIGN KEY
88. There are 50 columns in a table. Write a query to get first 25 columns
Ans: Need to mention each column names.
89. How to list all the tables in a particular database?
USE pubs
GO
sp_help
90. What are cursors? Explain different types of cursors. What are the
disadvantages of cursors? How can you avoid cursors?
Cursors allow row-by-row processing of the result sets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.
Disadvantages of cursors: Each time you fetch a row from the cursor,
it results in a network roundtrip, where as a normal SELECT query
makes only one roundtrip, however large the result set is. Cursors are
also costly because they require more resources and temporary storage
(results in more IO operations). Further, there are restrictions on
the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of
cursors. Here is an example:
If you have to give a flat hike to your employees using the following
criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each
employee's salary and update his salary according to the above
formula. But the same can be achieved by multiple update statements or
can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to
call a stored procedure when a column in a particular row meets
certain condition. You don't have to use cursors for this. This can be
achieved using WHILE loop, as long as there is a unique key to
identify each row. For examples of using WHILE loop for row by row
processing, check out the 'My code library' section of my site or
search for WHILE.
91. Dynamic Cursors?
Suppose, I have a dynamic cursor attached to table in a database. I
have another means by which I will modify the table. What do you
think will the values in the cursor be?
Dynamic cursors reflect all changes made to the rows in their result
set when scrolling through the cursor. The data values, order, and
membership of the rows in the result set can change on each fetch. All
UPDATE, INSERT, and DELETE statements made by all users are visible
through the cursor. Updates are visible immediately if they are made
through the cursor using either an API function such as SQLSetPos or
the Transact-SQL WHERE CURRENT OF clause. Updates made outside the
cursor are not visible until they are committed, unless the cursor
transaction isolation level is set to read uncommitted.
92. What is DATEPART?
Returns an integer representing the specified datepart of the
specified date.
93. Difference between Delete and Truncate?
TRUNCATE TABLE is functionally identical to DELETE statement with no
WHERE clause: both remove all rows in the table.
(1) But TRUNCATE TABLE is faster and uses fewer system and transaction
log resources than DELETE. The DELETE statement removes rows one at a
time and records an entry in the transaction log for each deleted row.
TRUNCATE TABLE removes the data by deallocating the data pages used to
store the table's data, and only the page deallocations are recorded
in the transaction log.
(2) Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
(3) The counter used by an identity for new rows is reset to the seed
for the column. If you want to retain the identity counter, use DELETE
instead.
Of course, TRUNCATE TABLE can be rolled back.
94. Given a scenario where two operations, Delete Stmt and Truncate
Stmt, where the Delete Statement was successful and the truncate stmt
was failed. – Can u judge why?
95. What are global variables? Tell me some of them?
Transact-SQL global variables are a form of function and are now
referred to as functions.
ABS - Returns the absolute, positive value of the given numeric
expression.
SUM
AVG
AND
96. What is DDL?
Data definition language (DDL) statements are SQL statements that
support the definition or declaration of database objects (for
example, CREATE TABLE, DROP TABLE, and ALTER TABLE).
You can use the ADO Command object to issue DDL statements. To
differentiate DDL statements from a table or stored procedure name,
set the CommandType property of the Command object to adCmdText.
Because executing DDL queries with this method does not generate any
recordsets, there is no need for a Recordset object.
97. What is DML?
Data Manipulation Language (DML), which is used to select, insert,
update, and delete data in the objects defined using DDL
98. What are keys in RDBMS? What is a primary key/ foreign key?
There are two kinds of keys.
A primary key is a set of columns from a table that are guaranteed to
have unique values for each row of that table.
Foreign keys are attributes of one table that have matching values in
a primary key in another table, allowing for relationships between
tables.
99. What is the difference between Primary Key and Unique Key?
Both primary key and unique key enforce uniqueness of the column on
which they are defined. But by default primary key creates a clustered
index on the column, where are unique creates a nonclustered index by
default. Another major difference is that, primary key doesn't allow
NULLs, but unique key allows one NULL only.
100. Define candidate key, alternate key, composite key?
A candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table. If the
table has more than one candidate key, one of them will become the
primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called
composite key.
101. What is the Referential Integrity?
Referential integrity refers to the consistency that must be
maintained between primary and foreign keys, i.e. every foreign key
value must have a corresponding primary key value.
102. What are defaults? Is there a column to which a default can't be
bound?
A default is a value that will be used by a column, if no value is
supplied to that column while inserting data. IDENTITY columns and
timestamp columns can't have defaults bound to them.
103. What is Query optimization? How is tuning a performance of query
done?
104. What is the use of trace utility?
105. What is the use of shell commands? xp_cmdshell
Executes a given command string as an operating-system command shell
and returns any output as rows of text. Grants nonadministrative users
permissions to execute xp_cmdshell.
106. What is use of shrink database?
Microsoft® SQL Server 2000 allows each file within a database to be
shrunk to remove unused pages. Both data and transaction log files can
be shrunk.
107. If the performance of the query suddenly decreased where you will
check?
108. What is execution plan?
109. What is a pass-through query?
Microsoft® SQL Server 2000 sends pass-through queries as
un-interpreted query strings to an OLE DB data source. The query must
be in a syntax the OLE DB data source will accept. A Transact-SQL
statement uses the results from a pass-through query as though it is a
regular table reference.
This example uses a pass-through query to retrieve a result set from a
Microsoft Access version of the Northwind sample database.
SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0',
'c:\northwind.mdb';'admin'; '',
'SELECT CustomerID, CompanyName
FROM Customers
WHERE Region = ''WA'' ')
110. How do you differentiate Local and Global Temporary table?
You can create local and global temporary tables. Local temporary
tables are visible only in the current session; global temporary
tables are visible to all sessions. Prefix local temporary table names
with single number sign (#table_name), and prefix global temporary
table names with a double number sign (##table_name). SQL statements
reference the temporary table using the value specified for table_name
in the CREATE TABLE statement:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
111. How the Exists keyword works in SQL Server?
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE exists
(SELECT *
FROM publishers
WHERE
authors.city = publishers.city)
When a subquery is introduced with the keyword EXISTS, it functions as
an existence test. The WHERE clause of the outer query tests for the
existence of rows returned by the subquery. The subquery does not
actually produce any data; it returns a value of TRUE or FALSE.
112. ANY?
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
(SELECT city
FROM publishers)
113. to select date part only
SELECT CONVERT(char(10),GetDate(),101)
--to select time part only
SELECT right(GetDate(),7)
114. How can I send a message to user from the SQL Server?
You can use the xp_cmdshell extended stored procedure to run net send
command. This is the example to send the 'Hello' message to JOHN:
EXEC master..xp_cmdshell "net send JOHN 'Hello'"
To get net send message on the Windows 9x machines, you should run the
WinPopup utility. You can place WinPopup in the Startup group under
Program Files.
115. What is normalization? Explain different levels of normalization?
Explain Third normalization form with an example?
The process of refining tables, keys, columns, and relationships to
create an efficient database is called normalization. This should
eliminates unnecessary duplication and provides a rapid search path to
all necessary information.
Some of the benefits of normalization are:
• Data integrity (because there is no redundant, neglected data)
• Optimized queries (because normalized tables produce rapid,
efficient joins)
• Faster index creation and sorting (because the tables have fewer
columns)
• Faster UPDATE performance (because there are fewer indexes per table)
• Improved concurrency resolution (because table locks will affect
less data)
• Eliminate redundancy
There are a few rules for database normalization. Each rule is called
a "normal form." If the first rule is observed, the database is said
to be in "first normal form." If the first three rules are observed,
the database is considered to be in "third normal form." Although
other levels of normalization are possible, third normal form is
considered the highest level necessary for most applications.
First Normal Form (1NF)
• Eliminate repeating groups in individual tables
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data.
For example, to track an inventory item that may come from two
possible sources, an inventory record may contain fields for Vendor
Code 1 and Vendor Code 2. But what happens when you add a third
vendor? Adding a field is not the answer; it requires program and
table modifications and does not smoothly accommodate a dynamic number
of vendors. Instead, place all vendor information in a separate table
called Vendors, then link inventory to vendors with an item number
key, or vendors to inventory with a vendor code key.
Another Example
Subordinate1 Subordinate2 Subordinate3 Subordinate4
Bob Jim Mary Beth
Mary Mike Jason Carol Mark
Jim Alan
Eliminate duplicative columns from the same table. Clearly, the
Subordinate1-Subordinate4 columns are duplicative. What happens when
we need to add or remove a subordinate?
Subordinate
Bob Jim
Bob Mary
Bob Beth
Mary Mike
Mary Jason
Mary Carol
Mary Mark
Jim Alan
Second Normal Form (2NF)
• Create separate tables for sets of values that apply to multiple
records.
• Relate these tables with a foreign key.
Records should not depend on anything other than a table's primary key
(a compound key, if necessary).
For example, consider a customer's address in an accounting system.
The address is needed by the Customers table, but also by the Orders,
Shipping, Invoices, Accounts Receivable, and Collections tables.
Instead of storing the customer's address as a separate entry in each
of these tables, store it in one place, either in the Customers table
or in a separate Addresses table.
Another Example:
CustNum FirstName LastName Address City State ZIP
1 John Doe 12 Main Street Sea Cliff NY 11579
2 Alan Johnson 82 Evergreen Tr Sea Cliff NY 11579
A brief look at this table reveals a small amount of redundant data.
We're storing the "Sea
twice each. Additionally, if the ZIP code for Sea Cliff were to
change, we'd need to make that change in many places throughout the
database. Our new table (let's call it ZIPs) might look like this:
33157
Third Normal Form (3NF)
• Eliminate fields that do not depend on the key.
Values in a record that are not part of that record's key do not
belong in the table. In general, any time the contents of a group of
fields may apply to more than a single record in the table, consider
placing those fields in a separate table.
For example, in an Employee Recruitment table, a candidate's
university name and address may be included. But you need a complete
list of universities for group mailings. If university information is
stored in the Candidates table, there is no way to list universities
with no current candidates. Create a separate Universities table and
link it to the Candidates table with a university code key.
Another Example :
Order Number Customer Number Unit Price Quantity Total
1 241 $10 2 $20
2 842 $9 20 $180
The total can be derived by multiplying the unit price by the
quantity, therefore it's not fully dependent upon the primary key. We
must remove it from the table to comply with the third normal form:
Order Number Customer Number Unit Price Quantity
1 241 $10 2
2 842 $9 20
http://databases.about.com/library/weekly/aa091601a.htm
Domain/key normal form (DKNF). A key uniquely identifies each row in a
table. A domain is the set of permissible values for an attribute. By
enforcing key and domain restrictions, the database is assured of
being freed from modification anomalies. DKNF is the normalization
level that most designers aim to achieve.
**
Remember, these normalization guidelines are cumulative. For a
database to be in 2NF, it must first fulfill all the criteria of a 1NF
database.
116. If a database is normalized by 3 NF then how many number of
tables it should contain in minimum? How many minimum if 2NF and 1 NF?
117. What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process of
normalization. It's the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.
118. How can I randomly sort query results?
To randomly order rows, or to return x number of randomly chosen rows,
you can use the
will get same value. You can use an ORDER BY clause to sort the rows
by the result from the NEWID function, as the following code shows:
SELECT *
FROM Northwind..Orders
ORDER BY NEWID()
119. sp_who
Provides information about current Microsoft® SQL Server™ users and
processes. The information returned can be filtered to return only
those processes that are not idle.
120. Have you worked on Dynamic SQL? How will You handled " (Double
Quotes) in Dynamic SQL?
121. How to find dependents of a table?
Verify dependencies with sp_depends before dropping an object
122. What is the difference between a CONSTRAINT AND RULE?
Rules are a backward-compatibility feature that perform some of the
same functions as CHECK constraints. CHECK constraints are the
preferred, standard way to restrict the values in a column. CHECK
constraints are also more concise than rules; there can only be one
rule applied to a column, but multiple CHECK constraints can be
applied. CHECK constraints are specified as part of the CREATE TABLE
statement, while rules are created as separate objects and then bound
to the column.
123. How to call a COM dll from SQL Server 2000?
sp_OACreate - Creates an instance of the OLE object on an instance of
Microsoft® SQL Server
Syntax
sp_OACreate progid, clsid,
objecttoken OUTPUT
[ , context ]
context - Specifies the execution context in which the newly created
OLE object runs. If specified, this value must be one of the following:
1 = In-process (.dll) OLE server only
4 = Local (.exe) OLE server only
5 = Both in-process and local OLE server allowed
Examples
A. Use Prog ID - This example creates a SQL-DMO SQLServer object by
using its ProgID.
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
B. Use CLSID - This example creates a SQL-DMO SQLServer object by
using its CLSID.
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate '{00026BA1-0000-0000-C000-000000000046}',
@object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
124. Difference between sysusers and syslogins?
sysusers - Contains one row for each Microsoft® Windows user, Windows
group, Microsoft SQL Server™ user, or SQL Server role in the database.
syslogins - Contains one row for each login account.
125. What is the row size in SQL Server 2000?
8060 bytes.
126. How will you find structure of table, all tables/views in one db,
all dbs?
sp_helpdb - will give list of all databases
sp_helpdb pubs - will give details about database pubs. .mdf, .ldf
file locations, size of database.
select * from information_schema.tables where table_type='base table'
OR
SELECT * FROM sysobjects WHERE type = 'U' - lists all tables under
current database
***
127. What is English query?
128. B-tree indexes or doubly-linked lists?
129. What is the system function to get the current user's user id?
USER_ID(). Also check out other system functions like USER_NAME(),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
130. What are the series of steps that happen on execution of a query
in a Query Analyzer?
1) Syntax checking 2) Parsing 3) Execution plan
131. Which event (Check constraints, Foreign Key, Rule, trigger,
Primary key check) will be performed last for integrity check?
Identity Insert Check
Nullability constraint
Data type check
Instead of trigger
Primary key
Check constraint
Foreign key
DML Execution (update statements)
After Trigger
**
132. How will you show many to many relation in sql?
Create 3rd table with 2 columns which having one to many relation to
these tables.
For More SQL SERVER Frequently Asked Interview Questions
Subscribe to:
Posts (Atom)
|
|
|
Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog. |
|