What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? 2008-04-04 11:19:37 Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECTstatement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVINGbehaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in aquery. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. Read more:difference
What types of Joins are possible with Sql Server? 2008-04-04 11:19:15 Joins are used in queries to explain how different tables are related. Joins also let you select data froma table depending upon data from another table.Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFTOUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. Read more:types
When is the use of UPDATE_STATISTICS command? 2008-04-04 11:18:54 This command
is basically used when a large processing of data has occurred. If a large amount ofdeletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes totake these changes into account. UPDATE_STATISTICS updates the indexes on these tablesaccordingly.
Difference between Function and Stored Procedure? 2008-04-04 11:18:32 UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where asStored procedures cannot be.UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.Inline UDF's can be though of as views that take parameters and can be used in JOINs and otherRowset operations. Read more:Procedure
What is difference between DELETE & TRUNCATE commands? 2008-04-04 11:18:08 Delete command removes the rows from a table based on the condition that we provide with a WHEREclause. Truncate will actually remove all the rows from a table and there will be no data in the tableafter we run the truncate command.TRUNCATETRUNCATE is faster and uses fewer system and transaction log resources than DELETE.TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only thepage deallocations are recorded in the transaction log.TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexesand so on remain. The counter used by an identity for new rows is reset to the seed for the column.You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.Because TRUNCATE TABLE is not logged, i Read more:difference
What is a NOLOCK? 2008-04-04 11:17:00 Using the NOLOCK query optimiser hint is generally considered good practice in order to improveconcurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks aretaken when data is read. The result is a Dirty Read, which means that another process could beupdating the data at the exact time you are reading it. There are no guarantees that your query willretrieve the most recent data. The advantage to performance is that your reading of data will not blockupdates from taking place, and updates will not block your reading of data. SELECT statements takeShared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, butother processes are blocked from modifying the data. The updates will queue until all the reads havecompleted, a
How to implement one-to-one, one-to-many and many-to-many relationships while 2008-04-04 11:16:17 How to implement one-to-one, one-to-many and many-to-many relationships whiledesigning tables?Ans:One-to-One relationship can be implemented as a single table and rarely as two tables with primaryand foreign key relationships.One-to-Many relationships are implemented by splitting the data into two tables with primary key andforeign key relationships.Many-to-Many relationships are implemented using a junction table with the keys from both the tablesforming the composite primary key of the junction table.
What's the difference between a primary key and a unique key? 2008-04-04 11:15:45 Both primary key and unique
enforce uniqueness of the column on which they are defined. But bydefault primary key creates a clustered index on the column, where are unique creates a nonclusteredindex by default. Another major difference
is that, primary key doesn't allow NULLs, but unique keyallows one NULL only.
What are different type of Collation Sensitivity? 2008-04-04 11:15:15 Case sensitivityA and a, B and b, etc.Accent sensitivitya and á, o and ó, etc.Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated different
ly, it is called Kanasensitive.Width sensitivityWhen a single-byte character (half-width) and the same character when represented as a double-bytecharacter (full-width) are treated differently then it is width sensitive.
What is Collation? 2008-04-04 11:14:47 Collation refers to a set of rules that determine how data is sorted and compared. Character data issorted using rules that define the correct character sequence, with options for specifying casesensitivity,accent marks, kana character types and character width.
What is a Linked Server? 2008-04-04 11:14:27 Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and queryboth the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easyto follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.
What is the use of DBCC commands? 2008-04-04 11:14:06 DBCC stands for database consistency checker. We use these commands to check the consistency ofthe databases, i.e., maintenance, validation task and status checks.E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.DBCC CHECKALLOC - To check that all pages in a db are correctly allocated.DBCC CHECKFILEGROUP - Checks all tables file group for any damage.
What is cursors? 2008-04-04 11:13:19 Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis,instead of the typical SQL commands that operate on all the rows in the set at one time.In order to work with a cursor we need to perform some steps in the following order:Declare cursorOpen cursorFetch row from the cursorProcess fetched rowClose cursorDeallocate cursor
What are primary keys and foreign keys? 2008-04-08 22:18:47 Primary keys are the unique identifiers for each row. They must contain unique values and cannot benull. Due to their importance in relational databases, Primary keys are the most fundamental of all keysand constraints. A table can have only one Primary key.Foreign keys are both a method of ensuring data integrity and a manifestation of the relationshipbetween tables.
What is the basic functions for master, msdb, model, tempdb databases? 2008-04-08 22:18:14 The Master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer this database with care.The msdb database stores information regarding database backups, SQL Agent information, DTSpackages, SQL Server jobs, and some replication information such as for log shipping.The tempdb holds temporary objects such as global and local temporary tables and stored procedures.The model
is essentially a template database used in the creation of any new user database created inthe instance.
How to rebuild Master Database? 2008-04-08 22:17:18 Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the ProgramFiles\Microsoft SQL Server\80\Tools\Binn directory.In the Rebuild Master
dialog box, click Browse.In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or inthe shared network directory from which SQL Server 2000 was installed, and then click OK.Click Settings. In the Collation Settings dialog box, verify or change settings used for the masterdatabase and all other databases.Initially, the default collation settings are shown, but these may not match the collation selected duringsetup. You can select the same settings used during setup or select new collation settings. When done,click OK.In the Rebuild Master dialog box, click Rebuild to start the process. Read more:Database
What is the STUFF function and how does it differ from the REPLACE function? 2008-04-08 22:16:24 STUFF function
to overwrite existing characters. Using this syntax, STUFF(string_expression, start,length, replacement_characters), string_expression is the string that will have characters substituted,start is the starting position, length is the number of characters in the string that are substituted, andreplacement_characters are the new characters interjected into the string.REPLACE function to replace existing characters of all occurance. Using this syntaxREPLACE(string_expression, search_string, replacement_string), where every incidence ofsearch_string found in the string_expression will be replaced with replacement_string. Read more:differ
What does it mean to have quoted_identifier on? What are the implications of having it off? 2008-04-08 22:16:02 When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, andliterals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifierscannot be quoted and must follow all Transact-SQL rules for identifiers.
What are the different types of replication? Explain. 2008-04-08 22:14:52 The SQL Server 2000-supported replication types
are as follows:· Transactional· Snapshot· MergeSnapshot replication distributes data exactly as it appears at a specific moment in time and does notmonitor for updates to the data. Snapshot replication is best used as a method for replicating data thatchanges infrequently or where the most up-to-date values (low latency) are not a requirement. Whensynchronization occurs, the entire snapshot is generated and sent to Subscribers.Transactional replication, an initial snapshot of data is applied at Subscribers, and then when datamodifications are made at the Publisher, the individual transactions are captured and propagated toSubscribers.Merge replication is the process of distributing data from Publisher to Subscribers, allowing thePublisher Read more:different
What is sp_configure commands and set commands? 2008-04-08 22:14:33 Use sp_configure
to display or change server-level settings. To change database-level settings, useALTER DATABASE. To change settings that affect only the current user session, use the SET statement.
What command do we use to rename a db? 2008-04-08 22:14:13 sp_renamedb ‘oldname’ , ‘newname’If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user usingsp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi usermode. Read more:command
What is the difference between a local and a global variable? 2008-04-08 22:13:55 A local temporary table exists only for the duration of a connection or, if defined inside a compoundstatement, for the duration of the compound statement.A global temporary table remains in the database permanently, but the rows exist only within a givenconnection. When connection are closed, the data in the global temporary table disappears. However,the table definition remains with the database for access when database is opened next time. Read more:difference
What is log shipping? 2008-04-08 22:13:34 Log shipping is the process of automating the backup of database and transaction log files on aproduction SQL server, and then restoring them onto a standby server. Enterprise Editions onlysupports log shipping. In log shipping the transactional log file from one server is automatically updatedinto the backup database on the other server. If one server fails, the other server will have the same dbcan be used this as the Disaster Recovery plan. The key feature of log shipping is that is willautomatically backup transaction logs throughout the day and automatically restore them on thestandby server at defined interval.
What is Raiseerror? 2008-04-08 22:13:03 Stored procedures report errors to client applications via the RAISERROR command. RAISERRORdoesn't change the flow of a procedure; it merely displays an error message, sets the @@ERRORautomatic variable, and optionally writes the message to the SQL Server error log and the NTapplication event log.
How do you load large data to the SQL server database? 2008-04-11 08:26:04 BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps toImports a data file into a database table or view in a user-specified format. Read more:large
, SQL
, server
What is BCP? When does it used? 2008-04-11 08:25:43 BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy thestructures same as source to destination.
What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it 2008-04-11 08:25:18 What is a table called, if it does not have neither Cluster nor Non-clusterIndex
? What is itused for?Ans:Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.A heap is a table that does not have a clustered index and, therefore, the pages are not linked bypointers. The IAM pages are the only structures that link the pages in a table together.Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from tableand than do bulk of inserts and to restore those indexes after that.
Recommend Site To A Friend
