What are the difference between DDL, DML and DCL commands?
What are the difference between DDL, DML and DCL commands?
SQL can be divided into two parts:
> The Data Manipulation Language (DML)
> The Data Definition Language (DDL).
The query and update commands form the DML part of SQL:
> SELECT - extracts data from a database
> UPDATE - updates data in a database
> DELETE - deletes data from a database
> INSERT INTO - inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are:
> CREATE DATABASE - creates a new database
> ALTER DATABASE - modifies a database
> CREATE TABLE - creates a new table
> ALTER TABLE - modifies a table
> DROP TABLE - deletes a table
> CREATE INDEX - creates an index (search key)
> DROP INDEX - deletes an index
Data Control Language (DCL) statements. Some examples:
> GRANT - gives user\'s access privileges to database
> REVOKE - withdraw access privileges given with the GRANT command
| More interview questions and answers |
|---|
There are many different advantages of using Stored Procedures which is following here : |
Some database systems are very curious about the its must be semicolon at the end of each SQL statement. Semicolon is the very good way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. We are always try to using MS Access and SQL Server 2000 and we don\'t have to put a semicolon after each SQL statement, but some database programs force us to use it. |
8060 bytes. |
Scheduled tasks is the bassically use of the completing task which is schededule by any user who use the system administrator, let user automate processes that run on regular or predictable cycles. user can decide his task for the perticule schedule time such as cube prossessing, for running the during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution. |
A NOT NULL constraint is stands for basically as a name not null it means that the colums are not accpted the null values. Its is used to signifies a domain intigrity as the check constraints. |
A CHECK constraint is bassically a using for limit of the values becouse It can be placed in a columns to tat values. Its used to singnifies a domain integrity. |
What is the STUFF function and how does it differ from the REPLACE function? |
REPLACE function is bassically use for replace existing characters of all occurrences. Its mainly syntax REPLACE, It is using string_expression,search_string,replacement_string, where every incidence of search_string found in the string_expression will be replaced with replacement_string.STUFF function is bassically using for the overwrite existing characters. Its Syntax is STUFF , Its Using for 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,and replacement_characters are the new characters interjected into the string. |
TABLESAMPLE is basscially use for the sampling of the rows in the table which is mainly use for the extracting in the Form Clause. The rows are retrived in the radomlly and its not in a simple order. Its bassically can be based on a percentage of no of rows. its use only that time when sampling of rows is necessary for the application instead of a full result set. |
A PRIMARY KEY is the basically a type of unique identifier which is unique property for any row with in a database table. Primary key is the most important for the unique identification in every table and every row because each table is identify for only with primary key constraints. |
A UNIQUE constraint basically mean is the unique attributes its means no values are duplicate its defines a uniqueness of the valuesina set of columns, The unique key constraints are used to signifies entity integrity as the primary key constraints. |
A FOREIGN KEY is classically a type of primary key it is the normal key for he one table but it is can become a primary key for the other table, The foreign key constraints are using a referential integrity, The foreign key constraints are mainly signifies a referential integrity constraint prevents any actions that can be destroy links between tables with the corresponding data values. Its prevent actions that would leave rows with foreign key values when there are no primary keys with that value. |
The model is the very important for a template database, Its a mainly used in the creation of any new user database created in the instance. |
The tempdb is the basically use for holding the temporary objects such as global and local temporary tables and stored procedures. |
The msdb database is the basically use for stores information regarding database backups,SQL Agent information,DTS packages,SQL Server jobs, and some replication information such as for log shipping. |
Database mirroring and Replication can be work together and they are provide availability for the publication database. Database mirroring basically work in two copies of a single database that are rarlly use on different computers. If we have to given any time for a only one copy of the database is currently available to clients which is called as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database. |
The TOP operator is basically use the new addition of SQL Server 2008 its have a facility provides to accepts the literal values and can be used with INSERT,UPDATE. And DELETES statements.,Its mainly used to be returned by a query from specified no of rows . |
A sparse column is basically a tool of the data base which is use for the decrease physical storage amount which is used in a database.Sparse columns also be use for the reducing work Its reduce the space requirements for null values at the cost of more overhead to retrieve no null values. They are the normal columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve no null values. |
In the new version of Sql Server 2008, SQL Server mainly use as user-defined functions can be created using such CLR languages. Common Language Runtime is a basically runtime environment that manages the execution of program code and provides services such as memory and exception management, debugging and profiling and security. This CLR language support extends not only to user-defined functions,but also to stored procedures and triggers. We can develop such CLR add-ons to SQL Server using Visual Studio 2008. |
Row Function is basically use for the retuning a columns which is type of expressions. which is contain the rows no with in the result set.This is only a number used in the context of the result set, if the result changes, the ROW_NUMBER() will change. |
EXCEPT clause is a basically use on the oracle, It is similar to MINUS Operation which is also use in Oracle. Both are queries are returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields should have the same no and also have a same result sets and same data types. |
Its is basically use for the sharing of the two common data b/w rows of the given page. Its have a many techniques for the compression of the data : |
Dictionary Compression is basically use for the searching . Searching basically the duplicator This Dictionary mainly work is the differentiate between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page. value throughout the page,and stores them to CI |
A pivot table is basically use for the sorting the table, and also doing the work in table like a counting and totaling the data which is stored in one table or spread sheet and its also create the second table for the display the summarized data,It can be turns the value of a specified column into column names, effectively rotating a table. but in case of Unpivote Its a exactly opposite to the pivote. |
Aggregation Function is basically a type of function, which is use for the calculation on the set of values and It is also return the single value , Its also have a HAVING CLAUSE, Which is used along with GROUP BY.for filtering query using aggregate values, Its ignore NULL Values excpt COUNT Function, Its have a many functions which is there : |
SqlCmd is basically a very important for that SQL Server It can be work with two modes : |
Dirty Read is basically a type of error its generate when two operations access in the same time for the example Two operations says,read and write occurs together giving the incurrect or |
File stream is the basically use for the store the large amount of data or objects in the file system which is integrated with the database.It is the SQL Server based applications, Its con be enable for server based applications to store unstructured data such as documents, images, audios, videos etc. In the file system. FILESTREAM basically integrates the SQL Sever Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this data type, the unstructured data is stored in the NTFS file system and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact SQL statements users can insert, update, delete and select the data stored in FILESTREAM enabled tables. |
Explain Active/Active and Active/Passive cluster configurations? |
Many Diff are there : |
denationalization is the reverse process of normalization it is also indicate the name of denenormalisaiotn . It\'s mainly use for the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced. |
There are many different of ways are there which is two different ways to load data in dimension tables. |
Surrogate keys are always integer or numeric. It is useful because the natural primary key can change and this makes updates more difficult .Surrogate key is the basically a type of substitution key for the primary key . It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table.. |
Referential integrity is the basically explain to the consistency that must be maintained between primary and foreign keys. |
A live-lock is a single lock which is basically work on the request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. When four denials are complete then SQL Server detects the situation and refuses further shared locks. |
Deadlock is a basically a name of situation, when two processes are request to the one lock of the one piece of data, attempt to acquire a lock on the others piece. so ofcouse Each process would wait dor the release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user\'s process. |
As like as UPDATE STATISTICS have a many purposes but right now we have a main purpose which is explain here : |
SCOPE_IDENTITY() function is basically use for the Returns that values which is the most recently created identity value for the tables in the current execution scope. |
How do you transfer data from text file to database (other than DTS)? |
We can Transfer the Data from text file to database Using the BCP utility, Where BCP stands for Bulk Copy Program . |
Join in SQL Server is basically puts data from two or more tables into a single result set. |
What is RAID and what are different types of RAID configurations? |
Redundant Array of Inexpensive Disks is abbreviation of RAID, It is mainly use for providing the facility of fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. |
A database management system have a many attributes but here we are mentioned that DBMS is composed of five elements, Which is there : |
What are the main components of Database management systems software. |
The database management system software includes |
In the database many queries generate and executed or Query optimization is the part of the query process in which the database system compares different query strategies and chooses the one with the least expected cost. |
Oracle is the basically a database Its have a many facility, that facilities is one of the name is locking which uses locking mechanisms to protect data from being destroyed by concurrent transactions. |
Oracle has two lock modes : |
The TO_DATE function using the values when we want to be show the timestamps from a character string that has been interpreted using a character template. |
SQL Wild Card is the basically use for the searching operation similarly LIKE function.SQL wildcards can substitute for one or more characters when searching for data in a database. |
SQL can be divided into two parts: |
Truncate is more faster than delete because when we delete the records from the database, database has to perform 2 actions. |
RDBMS stands for Relational Database Management |
normalization have a se veral forms that a database structure can be subject to, each with rules that constrain the database further and each creating what is called a Normal Form. These are, in order: |
Normalization is a process which is the tables in a database are optimized to remove the potential for redundancy. Two main problems may arise if this is not done: |
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database. |
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures. |
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views. |
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance. |
SQL is a basically standard language for accessing and manipulating databases. Its is defines as : |
What are the components of physical database structure of Oracle database? |
Physical components of oracle database are: |
What are the components of logical database structure of Oracle database? |
The Logical Database Structure of an Oracle include : |
A Tablespace is basically a logical storage unit within an Oracle database. It is logical because a table space is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one data file which are physically located in the file system of the server. b/w datafile belongs to exactly one tablespace. Each table index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table\'s or index\' data is stored. There are three types of tablespaces in Oracle: |
Every ORACLE database have a table space which name is SYSTEM . when the database is created that a time its also automatically created. The SYSTEM tablespace always contains the data dictionary tables for the entire database. |
Explain the relationship among database, tablespace and data file. |
Database : Database is the Collection of data is called database. |
Schema basically Pronounce schema, It is the structure of a database system,It described in a formal language supported by the database management system (DBMS). In the RDBMS (Relational database System) , the schema defines the tables, the fields in each table, and the Schemas are generally stored in a data dictionary . Even a schema is defined in text database language , the term is often used to refer to a graphical depiction of the database structure. |
Schema is basically a Associated with each database user . It is a collection of schema objects. Examples of schema objects include tables, views, sequences, synonyms, indexes, clusters, database links, snapshots, procedures, functions, and packages. Schema objects are logical data storage structures. Schema objects don\'t have a one-to-one correspondence to physical files on disk that store their information. Even Oracle stores a schema object logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace\'s datafiles. |
Can objects of the same schema reside in different table spaces? |
Yes we can objects of the same schema reside in different table space. Schema objects can stored in different tablespace and a tablespace can contained one or more schema objects data. |
Yes we can a template hold objects from different schemes. |
The Collection of informations stored in the strutrued format which is in rows and columns formate that is called a table. |
A view is a bassically logical table which makes a change complex query to easy term .We can even create a complex view by joining two tables. |
Of course the View don\'t contain any data. The basic property of a view is to extract a set of data columns to display from a large number. And this is useful in future by just stating the view name instead writing the select statement every time. |
ACID is the properties of the transaction : |
The Primary Key is basically a key that is frequently used to identify a specific row other keys also may exist and all the other values are dependent on that value to be meaningfully identified. A primary key is usually one attribute or column in a relation or table, But can be a combination of attributes. |
Candidate key is basically use for when we added a new attribute in the table, We could also use the combination of other attribute with this new attribute to identify the row, as together they form a Composite Key. Thus we have two potential or Candidate Keys. |
NA |
NULLs are basically for use in database for describe that no value or attributes are exist, |
Relational tables have six properties which is there : |
De-Normalization is basically the process, Which |
A Trigger have a many authorities it can also perform to the INSERT,UPDATE and DELETE operations or logic within itself, And whenever the trigger is fired the |
Linked Servers is basically a concept in SQL Server, Its mainly use we can add the other SQL |
Cursor is basically a database object,which is mainly used by applications to |
Collation is basically a collection to a set of rules that determine how to data is sorted and compared. Character data is sorted with using rules that defined the sequence of correct character, with specifying case sensitivity |
Many Difference are there :> UDF can be used WHERE/HAVING/SELECT sectionanywhere in the SQL statements, But in case ofStored procedures cannot be. > UDF\'s that return tables can be treated asanother rowset. This can be used in JOINs withother tables. Inline UDF\'s can be thought of asviews that take parameters and can be used inJOINs and other Rowset operations. |
What is the Sub-Query?And what is the properties of Sub-Query? |
Sub-queries are bassically defined assub-selects, Its allow a SELECT statement to beexecuted arbitrarily within the body of anotherSQL statement. A sub-query is mainly executed byenclosing in a set of parenthesis. Sub-queriesare generally used to return a single row as anatomic value, They may be used to comparision,Its may be use for compare the values againstmultiple rows with the IN-keyword. A Subquery isa SELECT statement that is nested within anotherT-SQL statement. A Subquery SELECT statement ifexecuted independently of the T-SQL statement, Inwhich it is nested, will return a resultset.Meaning a subquery SELECT statement can standalone and is not depended on the statement inwhich it is nested. A subquery SELECT statementcan return any number of values, and can be foundin, The column list of a SELECT statement,FROM,GROUP BY,HAVING,and/or ORDER BY clauses of aT-SQL statement. A Subquery can also be used as aparameter to a function call. Basically asubquery can be used anywhere an expression canbe used. |
There are diff types of join : |
A cross join is he type of join , That does not have a WHERE clause which is produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is basically equal to the number of rows in the first table multiplied by the number of rows in the second table. For the example we can see when company wants to combine each product with a pricing table to analyze each product at each price. |
Inner Join is a basically use for presentation the tables or format, A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer. |
What do you mean by Outer Join? and define also how many types of Outer Join? |
Outer Join is the basically use for a rows. That join includes rows even if related rows in the joined table are absent is an Outer Join. We can create three different outer join to specify the unmatched rows to be included : |
Self Join is basically use for the editing in the table itself , This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can joined tables which are the same. A self join is rather unique in that it involves a relationship with only one table. For the example is when company has a hierarchical reporting structure whereby one member of staff reports to another. Self Join also use can be Outer Join or Inner Join. |
User Defined Functions is basically signifies UDF, Its allow to define only its own T-SQL functions which is accept 0 or more parameters and return a single scalar data value or a table data type. |
We can a create User-Defined Functions in a many ways they are defined as : |
Identity is a basically called as AutoNumber. It is mainly a column that automatically generates numeric values. When A start and increment value can be set, but most DBA leave these at 1. A identity or GUID column also generates numbers; these type of values cannot be controlled. Identity/GUID columns do not need to be indexed. |
DataWarehousing is the basically mean that It is Subject-oriented, Its a meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together. |
SCD is the basically stands for Slowly Changing Dimensions. Its mainly use for the changing dimensions very slowly because its applies to cases where the attribute for a record varies over time. |
A Clustered index is basically a special type of index,its is mainly use for reorders the way of records in the table which is in the physically stored. Even table can have only one clustered index. the data pages contained on the leaf nodes of the clustered index . |
In The SCD1 mainly use for the create a new record and its also can be replaces the original record. Only one record exist in database - current data. |
A Non Clustered Index is a special type of index, Which Indexes are in the logical order of the index, Its does not match the physical stored order of the rows on disk. The leaf node does not consist of the data pages Which is from non clustered index. Instead, The leaf nodes contain index rows. |
What are the different index configurations a table can have? |
A Table can have Diff type of the index configurations, Which are following as : |
There are many diff types of Collation Sensitivity, Which are following here : |
OLTP stands for Online Transaction Processing Systems. It is mainly use for relational database design for use the discipline of data modeling and generally follow the Code rules of data normalization, It is in order to ensure absolute data integrity. It is Using these rules complex information is broken down into its most simple structures or a table, where all of the individual atomic level elements relate to each other and satisfy the normalization rules. |
Truncate is basically defined as : |
Delete Command basically defines as : |
UPDATE_STATISTICS command is mainly used for the solving a problem when a large processing of data has occurred. If a large amount of deletions wants any modification or when the Bulk Copy into the tables has occurred, that time it has to update the indexes to take these changes into account. Its updates the indexes on these tables accordingly. |
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? |
There are a many differences b/w HAVING CLAUSE and a WHERE CLAUSE : Having Clause is mainly used for the Group By function Its only with the GROUP BY function in a query, But In case of WHERE Clause is using only for rows, Its applied to each row before they are part of the GROUP BY function in a query. Both of Clouses are mainly specify a search condition for a group or an aggregate. But differences are there in the case of HAVING CLAUSE, it can be used only with the SELECT statement. And It is typically used in a GROUP BY clause. Even GROUP BY is not used, HAVING behaves something like a WHERE clause. |
There are many Properties of Sub-Query are there : |
There are many types of SUB-QUERY : |
SQL Profiler is basically a graphical tool, Which is mainly use for allow to system administrators to monitor events in an instance of Microsoft SQL Server. We can capture and save data about each event to a file or SQL Server table to analyze later. For example, we can monitor a production environment to see which stored procedures are hampering performances by executing too slowly. Its mainly Use SQL Profiler to monitor only the events in which we are interested. We can filter the events based on the information we want, when It traces are becoming too large, That\'s why only a subset of the event data is collected.In server many overhead events are added from the Monitor and the monitoring process and because the trace file or trace table to grow very large, Specially when the monitoring process takes place over a long period of time. |
What are the authentication modes in SQL Server? How can it be changed? |
Mainly Two modes are the Authentication Modes in SQL Server they are following as : |
SQL Server Agent is the very important for the Database administrator. Its a very good role or important role in database administrator (DBA) for work on day to day tasks. It is use overlooked as one of the main tools for SQL Server management. Its mainly use of the implementation of tasks for the DBA, with its full function scheduling engine, which allows us to schedule our own jobs and scripts. |
Yes we can stored procedure call itself or recursive stored procedure. Because Stored procedures is basically are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. we can nest stored procedures and managed code references up to 32 levels. T-SQL supports recursion, we can write the stored procedures because of call, Recursion have a techniques or methods of problem solving, Where in the solution is applying o the problems it can be use of subset of problems only. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. |
Log shipping is basically a process of the database and its mainly use for transaction of the lof files on the production SQL Server, And its the process of automating the backup of database. And then restoring them onto a standby server. Enterprise Editions is only supports log shipping. In the case of log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same database and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval. |
What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF? |
When SET QUOTED_IDENTIFIER is OFF, identifiers must follow all T-SQL rules for identifiers and It cannot be quoted , But in case of When SET QUOTED_IDENTIFIER is ON, Identifiers can be defied the boundaries by double quotation marks, And literals must be define the boundaries by single quotation marks. |
Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.A local temporary table is basically a like a using for duration of connections. Its exists only for the duration of a connection. If defined inside a compound statement, for the duration of the compound statement. |
Global temporary tables are distinct within SQL sessions. The table definition is the basically a type of the database for access when database is opened next time. A global temporary table remains in the database permanently, But the rows exist only within a given connection. The data is dispersal in the global temporary table,When connection is closed. |
ER Diagram is stands for entity Relationship Diagrams , It is the basically a major data modelling tool . A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables. and It will also help organize the data in our project into entities and define the relationships between the entities. This process has proved to enable the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner. An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. |
BulkCopy is basically a using for copy, It is a tool used to copy huge amount of data from tables and views. BULK INSERT command helps to import a data file into a database table or view in a user‐specified format.It does not copy the structures same as source to destination. |
UNION is the basically a type of a command which is used for the selection procedure, It is select the related information from the two tables, similarly JOIN command. when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected. |
What is an execution plan? When would you use it? How would you view the execution plan? |
An Execution plan is basically presentation of the map in graphically or textually shows of the dat retrival methods whihc is chosen by the SQL Server abd it can be query optimizer for the stored procedure or ad-hoc query and we can also use this as a tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called \"Show Execution Plan\". If this option is turned on it will display query execution plan in separate window when query is ran again. |
How to implement one-to-one,one-to-many and many-to-many relationships while designing tables? |
Implementations of relationship is there : |
The Master Database is basically use for the database information which all databases are located on the Sqlserver instances and It is the Clue that holds the engine together. Because SQL Server cannot start without a functioning master database, We must administrator this database with care. |
The Resource Database is the basically use for containing the system objects its stand for the read only database,The Resource database does not contain user data or user metadata. System objects are included with SQL Server.SQL Server system objects,such as sys.objects,are physically persisted in the Resource database, but they logically appear in the sys schema of every database. |
Service Broker is the mainly use for developing to integrated SQL Server which is fully into distributed applications, It is basically message-queuing technology in SQL Server, It is the feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable. |
Policy Managements mainly use for configuring the policies and its also use for the managing the policies its policy is SQL Server across the enterprise.and Policy-Based Management is basically configured in the SQL Server Management Studio (SSMS). Its only Navigator to the Object Explorer and Its is also expand the Management node and the Policy Management node, We can see the Policies, and Conditions, and Facets nodes. |
CTE is stands for Common Table Expression. It is like to a derived table in that it is not stored as an object and lasts only for the duration of the query. A Common Table Expression CTE) is a type of expression that can be seem like as a temporary result set which is explain within the execution of a single SQL statement. |
MERGE is a bassically use for the data modification and also check for the data then helps on the updation.It is a new feature of the SQl server, That provides an efficient way to perform multiple DML operations. some time before in Sql server when merge statement is not available in the server, We had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now a days using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once. |
Filtered Index is bassically use for indexing, Its mainly used to improves query performance, It can be reduced mentinence costs and it cn be also reduce index storage costs compared with full-table indexes. Its usually use for Index a portion of rows in a table that means it applies filter on INDEX . When we see an Index created with some where clause then that is actually a FILTERED INDEX. |
CTE advantages are there : > It can be defined, it is used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data. > Its Using CTE for improve the readability and makes maintenance of complex queries easy. > It can be signifies as a functions,stored procedures, triggers or even views. > The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated. |
Synonyms basscially use for the given a alternate name of the database objeats its a mainly use of synonyms, we can alias object names, for the example using the Employee table as Emp. We can also shorten names. Its mainy use for the dealing a two or frour name parts ; for example, shortening server.database.owner.object to object. |
LINQ is an abriviation as Language Integrated Query,Its using .NET languages. Its have a ability for adds query objects,Its have a some basic feacture which is defined as : > Its have a Tools which is create classes That usually called entities, mapped to database tables. > Its Compatibility with LINQ�s standard query operations. |
Isolaion level is bassically comes under the transaction Its signifies the degree, which is transaction should be isolated from resource or data modifications made by other transactions. that tyime Isolation levels are explain that type concurrency side-effects, such as dirty reads or phantom reads, are allowed. |
NOLOCk is the bassically a technique which is useful something for the busy system . Its help on the system which is generally consider good practice to improve the concurrency, When in SELECT statement use whith the NOLOCK hints,when NOLOCK hint is taken then Data is read The result is a Dirty Read, which means that another process could be updating the data at the exact time we are reading it. its not a garunteed for the most of data to be read.in the hints give us a advantages of the performance is that our reading of data will not block updates from taking place, and updates will not block our reading of data. SELECT statements take Shared Read lock, This means that multiple SELECT statements are allowed simultaneous access, But other processes are blocked from modifying the data. The updates will queue until all the readshave completed, and reads requested after the update will wait for the updates to complete. The result to our system is delay blocking. |
RaiseError bassically use for the Error messege.Its generate and initiate error processing in the session. RAISERROR can bassically defined a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned and displays as a server error message to the calling application or to an associated CATCH block of a TRY�CATCH construct. |
Xml is the bassically that type of Datatype which is use for SQL Server Datatype, Its a Xml documents and fragments in a Sql server. Xml bassically a instances that is missing a single top-level element. we can create columns and variables of the xml type and store XML instances in them. The xml data type and associated methods help integrate XML into the relational framework of Server. |
in the new version of SQl Server 2008 are diff type of Data Compressions : > Row Compression > Page Compression > Dictionary Compression |
Row compression changes the format of physical storage of data. It minimize the metadata In SQL SERVE 2008 Data Com column information, length, offsets etc .associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar. |
DBCC is bassically stands for Database conso;e commands for SQlServer. And it is working with T-SQL Programming Language. DBCC commands have a many uses for the performing new tasks which is following here : > It is use as a Tasks that gather and display various types of information. > Validation operations on a database, table, index, catalog, file group or allocation of database pages. > Miscellaneous tasks such as enabling trace flags or removing a DLL form memory . > Maintinence Task on Database , Index or file group. |
Catalouge Views is basssically use for the returing the information, it is used by the SQL Server Database Engine.It is the very importnt general interface catalouge to the matadata and Its provide the most efficient way to obtain ,transform, and present costomised forms of this information .all user available catalouge matadata is exposed through catalouge views. |
Ranking Function is the bassically use for the return value which is the ranking of the each row in the partion. These ranking functins are non Deterministic. there are many diff type of ranking functions : > ROW_NUMBER() OVER([ |
It s the sequential no of the row with in the partition pf the resultset,starting at 1 for the First row in each partition. |
It is bassically Return the value as a rank of each row within the partition of a result set. |
Its retun the value as a rank of rows ith in the partition of the result set, without any gap in the ranking . |
UNIONALL is bassically a command, It is much like a UNION COMMAND, But it is some diff'es like : THis command is select all values. |
A Root Node is bassically use for containing the node Pointers to branch node which can be only one. |
A Branch Node is bassically contain the Pointers for leaf nodes or the other Branch nodes , Which can be two or more. |
A Leaf Node is bassically type of Container which is contain the Index terms and horizontal pointer also for other leaf nodes .Which can be many . |
Lock escalation is the bassically use for the converting a lot of low level locks like a row lock or the level locks into higher level locks which is like table locks. Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. |
Snow Schema is the bassically known as snow flake schema, Its mainly interlinked or may have one to many relationship with other tables. This schema is normalized and results in complex join and very complex query as well as slower results. |
Star Schema is bassically use for got a faster results becouse it is a single fact table with N number of Dimension, which will be linked directly with a fact table. This schema is De-normalized and results in simple join and less complex query as well as faster results. |
SCD3 is bassically use for only the modifcation and create a new data from The original data . One record exist in database-new information are attached with old information in same row. |
SCD2 is mainly use for the add a new record into the customer dimension table. Two records exist in database-current data and previous history data. |
RAID is the bassically stands for Redundant Array of Inexpensive Disks, It is mainly used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. |
DTC is a stands for The Microsoft Distributed Transaction Coordinator (MS DTC), It is bassically a type of 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. |
DTS bassically stands for Data Transformation Services, It is the mainly use for set of graphical tools and programmable objects That we can extract,transform, and consolidate data from disparate sources into single or multiple destinations. |
What are defaults? Is there a column to which a default can't be bound? |
A default is a bassically a type of value whihc is used by the columns, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. |
Collation is basically use for the ordering It is the sort order. There are three types of sort order Whihc is following here : > Dictionary case sensitive, > Dictonary case insensitive and > Binary. |
SQL Profiler is bassically a utility which is allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc.... |
Replication is he bassically using for the copy of the data and move the 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 |
Distributed partitioned views bassically dor providing the facility to the partition tables horizontally across multiple servers.That's why we can scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers. |
there are many types of new data types , Which are signifies here : > Sql_variant data type : This type is a type that allows the storage of data values of different data types. � table data type : This type lets an application store temporary results as a table that we can manipulate by using a select statement or even action queries, just as we can manipulate any standard user table. > Bigint data type : This type is an 8-byte integer type. |
There are many diff are in INSTEAD OF and AFTER Triggers in SQL Server 2000. Which is following here : > INSTEAD OF triggers are bassically work on instead of the INSERT, UPDATE or DELETE triggering action. But in case of �AFTER� triggers are work on after the triggering actions. |
What do you mean by Cascading Referential Integrity Constraints? |
Right now only two new clouses are there, Which is : ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.
The ON DELETE and ON UPDATE clauses have two options:
� NO ACTION : NO ACTION specifies that the deletion/updation fail with an error.
� CASCADE : CASCADE specifies that all the rows with foreign keys pointing to the deleted/updated row are also deleted / updated.
> ON DELETE : Its bassically controls what actions are taken if we attempt to delete a row to which existing foreign keys point.
> ON UPDTE : It is bassically defines the actions that are taken if we attempt to update a candidate key value to which existing foreign keys point. |
Database engine bassically use for the returnind the data as Extensible Markup Language (XML) documents. In The SQL Server 2000, XML Bassically use for the : > insert The value , > update The value , and > delete values , In the database. |
Indexed views is bassically are persistent, It can be significantly improve application performance by eliminating the work that the query processor must perform to resolve the views. It is bassically a views but in some cases Its Unlike standard views,Which views like SQL Server resolves the data-access path dynamically at execution time, The new indexed views feature are there which is following here : > we can store views in the database as like as we can store tables.
|
Stored procedure is bassically have more utilising content Its uses in a every feild of SQL Server, There are four major reasons why we use this procedure which can be beneficial: > It can dramatically increase security. > They can assist you in centralizing your code. > They are executed on the Server's machine. > They are precompiled. |
UNION basically use combine the results , Which results have a two or more queries in to a single result set, Which is consisting of all the rows belonging to all queries in the union. |
t is the bassically defined as a Isolation properties , It is using for the tracsaction, which is A transaction cannot read data that is being modified by another transaction that has not committed. This is the default isolation level in Microsoft SQL Server. |
The Basic diff are there : > Joine is bassically use for the colums but in case of union is does't. > Union is bassically us efor the rows but in case of the Join is does't. > A join selects columns from Two or more tables. A union selects rows. |
A Cartesian product is basically use for the results from a faulty query. It is a row in the results for every combination in the join tables. |
First of all I Would like to tell every one its a Type of Isolation Level , It is the mainly use for the transaction, which transaction can read any data, even if it is being modified by another transaction. This is the least safe isolation level but allows the highest concurrency. |
It is the defined basically a isolation properties and its works on the Isolation level as a Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. Any type of new data can be inserted during a transaction. |
It is the part of the isolation level , Its basically use for the Data read by the current transaction cannot be changed by another transaction until the current transaction finishes. No new data can be inserted that would affect the current transaction. |
Many disadvantages are in Cursor which is defined here : |
Its manly work is Saving all changes made by DML statements. |
Yes we know lots of Databases, we are mentioned there : |
The sequence of SQL statement are processed in the following sequence (DB2): |
in command use suppose u want to choose department then you use in command |
Use primary key If column is like Id or no..Alternate way is you can apply unique constraint on column.. hope u hv cleared ur doubts |