Happy Republic Day

Find the SQL Server Service Startup Time

Find the SQL Server Service Startup Time

 

Many times for collecting or analyzing performance counter from the different DMV we need to collect the data of many days, so at that it is necessary to find the SQL Server Service startup time. We can find it as below.

 

1)       Every time when SQL Server restarts, It creates the “TempDB” so from the TempDB creation time you can find SQL Server startup time.

SELECT CREAT_DATE FROM SYS.DATABASES WHERE NAME = ‘TEMPDB’

2)       If you error log is not flushed you can use the XP_ReadErrorlog and from there you can find the SQL Server startup time

3)       We can also find it using sys.dm_exec_requests  DMV for startup time

4)       We can also find it from Master..SysProcesses system table by analyzing the login time for the System Processes (for i.e. LAZYWRITER)

Execute As in SQL Server 2005

When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. This will help database administrator to check the different user permissions. `Execute As` is also very helpful when a user wants to execute a stored procedure in the context of another user.

Syntax
Execute as user = 'SQL_JShah'
select * from CompanyProducts.Products

How to recover root password in MySQL?

1. As Linux system root user stop the database process: /etc/init.d/mysql stop
   (or: service mysql stop)


2. Start MySQL in safe mode and skip the use of the "grant tables": /usr/bin/mysqld_safe --user=mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --datadir=/var/lib/mysql --skip-grant-tables --skip-networking &


3. Reset the MySQL root password: mysqladmin -u root flush-privileges password newpassword


4. Stop MySQL running in safe mode: kill `cat /var/run/mysqld/mysqld.pid`


5. Start MySQL: /etc/init.d/mysql start


6. The new MySQL root password can now be used: mysql -u root -p
   Respond withthe password: newpassword

Oralce Interview Questions - Year 2009 Part - II

1. Give one method for transferring a table from one schema to another: There are several possible methods, export-import, CREATE TABLE... AS SELECT, or COPY

 

2. What is the purpose of the IMPORT option IGNORE? What is it?s default setting The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the tables data will be inserted. The default value is N.

 

3. You have a rollback segment in a version 7.2 database that has expanded beyond optimal, how can it be restored to optimal

Use the ALTER TABLESPACE ..... SHRINK command.

 

4. If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why

       The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement (only data dictionary objects and the system rollback segment should be in SYSTEM).

 

 

5. What are some of the Oracle provided packages that DBAs should be aware of

Oracle provides a number of packages in the form of the DBMS_ packages owned by the SYS user. The packages used by DBAs may include: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT. They may also try to answer with the UTL*.SQL or CAT*.SQL series of SQL procedures. These can be viewed as extra credit but aren?t part of the answer

 

6. What happens if the constraint name is left out of a constraint clause?

The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.

 

7. What happens if a tablespace clause is left off of a primary key constraint clause?

This result in the index that is automatically generated being placed in then users default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.

 

8. What is the proper method for disabling and re-enabling a primary key constraint?

You use the ALTER TABLE command for both. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause for primary keys.

 

9. What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause

The index is created in the user’s default tablespace and all sizing information is lost. Oracle doesn’t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.

 

10.(On UNIX) When should more than one DB writer process be used? How many should be used

If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter.

 

11. You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not

You can’t use hot backup without being in archivelog mode. So no, you couldn?t recover.

 

12. What causes the "snapshot too old" error? How can this be prevented or mitigated

This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents.

 

13. How can you tell if a database object is invalid?

By checking the status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.

 

14. A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check

You need to check that the user has specified the full name of the object (select empid from scott.emp; instead of select empid from emp;) or has a synonym that balls to the object (create synonym emp for scott.emp;)

 

15. A developer is trying to create a view and the database won?t let him. He has the "DEVELOPER" role which has the "CREATE VIEW" system privilege and SELECT grants on the tables he is using, what is the problem

You need to verify the developer has direct grants on all tables used in the view. You can’t create a stored object with grants given through views.

 

16. If you have an example table, what is the best way to get sizing data for the production table implementation?

The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the average row length and other pertinent data for the calculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows.

 

17. How can you find out how many users are currently logged into the database? How can you find their operating system id

      There are several ways. One is to look at the v$session or v$process views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a "ps -ef|grep oracle|wc -l? Command, but this only works against a single instance installation.

 

      18. A user selects from a sequence and gets back two values, his select is:

SELECT pk_seq.nextval FROM dual;What is the problem

Somehow two values have been inserted into the dual table. This table is a single row, single column table that should only have one value in it.

 

19. How can you determine if an index needs to be dropped and rebuilt

Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn’t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio

BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN are nearing 0.3.

 

Oracle Interview Questions - Year 2009 - Part I

1. Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery

In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

3. How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.

4. Explain the difference between a data block, an extent and a segment?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

5. Give two examples of how you might determine the structure of the table DEPT?
Use the describe command or use the dbms_metadata.get_ddl package.

6. Where would you look for errors from the database engine?
In the alert log

7. Compare and contrast TRUNCATE and DELETE for a table.
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

8. Give the reasoning behind using an index.
Faster access to data blocks in a table.

9. Give the two types of tables involved in producing a star schema and the type of data they hold.
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

10. What type of index should you use on a fact table?
A Bitmap index

11. Give two examples of referential integrity constraints.
A primary key and a foreign key are the example of refe

12. A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any ball in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any ball in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

14. What command would you use to create a backup control file?
Alter database backup control file to trace.

15. Give the stages of instance startup to a usable state where normal users may access it.
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened

16. What column differentiates the V$ views to the GV$ views and how?
The INST_ID column which indicates the instance in a RAC environment the information came from.

17. How would you go about generating an EXPLAIN plan?
Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql

18. How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

19. Explain an ORA-01555
you get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

MySQL and Linux commands

Where are the MySQL binaries stored? Find the location? : which mysql

 

Find the version of MySQL: mysql -V

 

Start the MySQL server: /usr/bin/mysqld_safe --user=mysql & or /etc/init.d/mysql start

 

Validate MySQL process has started: ps -afe | grep mysqld

 

Validate MySQL server is up and running: mysql> SHOW VARIABLES LIKE "%version%";

 

Find the columns of the table in MySQL: mysql> SHOW COLUMNS FROM dept;

 

Find the table related information: SHOW TABLE STATUS LIKE 'dept' \G

 

Display all tables: Mysql> show tables

 

Display all databases: Myql> show databases

 

Display tables from the non current database: Mysql> SHOW TABLES FROM database_name;

 

List the filed of the tables

mySQL> describe tableName

 

Backup/Dump all databases

[mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

 

Backup/Dump particular database

[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

 

Backup/Dump a table from a database

[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

 

Restore database (or database table) from backup

[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

 

 

 

 

 

How to decide Primary Key column data type in MySQL? Expectation with the Primary Key column

Decide the Primary Key column is one of the important tasks. Typically, the Primary Key is must mitigate the below properties.

 

(1) Uniquely identify each row in a table

(2) Can be referenced by another column/row (foreign key)

(3) provide a "natural" sequencing of the data (alphabetical, numeric, and chronological)

(4) Partition the data horizontally for performance

 

If your interest is either "unique identifier" or "natural sequencing", you will be using whatever data type fits for the rows natural identifier. So that pretty much answers the question, and should be your first choice.

 

Remember that a primary key must be immutable -- never changes -- so using a natural key may not be possible. In that case, a surrogate key becomes a necessity.

 

A surrogate key will probably break your "natural sequencing", since the data will be indexed on the surrogate key value, not on the natural values. This doesn't mean to avoid it, but only to recognize that condition and address it in your design (an index, for example, restores the sequencing but adds overhead to update/delete/insert operations).

 

Also, if you are looking at this as a performance problem, you may want to substitute an integer data type as a "surrogate key" for the natural primary key.

 

If you use a surrogate key, use an integer that is sized to the native size of the platform (32-bit, 64-bit, 128-bit integer) up to the size needed to support uniqueness.

 

I would avoid integers which increment monotonically (by 1) -- this presents a "bottleneck" in the processing, making it complicated to run multiple, parallel, concurrent insertions. If you're running an OLTP system, that bottleneck will limit your performance and scalability.

 

So ultimately to decide the primary key data type we need to project the data growth, number of insertion, number of selection and performance issues.

 

For the normal table you can use primary key datatype as BIG INT with AUTO_INCREMENT option.

SQL Server Execution Context and Security

What is Execution Context?

Whenever User log on or connects to SQL Server, it will create the User Session. Whenever that user executes the statement SQL Server uses that session’s user id, permission, password to execute the query. That session is called execution context.

 

Execution context is represented by a pair of security tokens: a login token and a user token. The tokens identify the primary and secondary principals against which permissions are checked and the source used to authenticate the token. A login connecting to an instance of SQL Server has one login token and one or more user tokens, depending on the number of databases to which the account has access.

 

What is Principals in SQL Server 2005?

Principals are the individuals, groups, and processes that can request SQL Server resources. Principals are categorized by their scope. Every principal has security identifier

 

-> Windows level

-> SQL Server level

-> Database level 

Windows-level principals

• Windows Domain Login


• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User


• Database Role


• Application Role

What are SQL Server Securables?

SQL Server Securables contains three scopes, which are used to assign permissions to users. The securables are nested and each securable contains various other securables. The securable scopes are as follows:

·         Server: It includes server roles, logins, etc.

·         Database: It includes database users, application roles, database roles, etc.

·         Schema: It includes various database objects such as tables, views, procedures, etc.

The securables are used to assign permissions to the users based on scope and the tasks assigned. The issues related to the connectivity to databases, accessing database objects, etc., can be resolved by granting or denying the permissions to the users.

 

What is the use of the Public database role in SQL Server?

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable. 

Explain Certificate based SQL Server Logins/Principals?

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

• ##MS_SQLResourceSigningCertificate##


• ##MS_SQLReplicationSigningCertificate##


• ##MS_SQLAuthenticatorCertificate##


• ##MS_AgentSigningCertificate##


• ##MS_PolicyEventProcessingLogin##


• ##MS_PolicySigningCertificate##


• ##MS_PolicyTsqlExecutionLogin##

 

 

 

SQL Server Interview Questions 2K9 - Part - I

Question: What is COMMIT & ROLLBACK statement in SQL ?
Answer: Commit statement helps in termination of the current transaction and does all the changes that occur in transaction persistent and this also commits all the changes to the database. COMMIT we can also use in store procedure.
ROLLBACK do the same thing just terminate the current transaction but one another thing is that the changes made to database are ROLLBACK to the database.

Question:-What is difference between OSQL and Query Analyzer ?
Answer:-Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool.OSQL have not ability like query analyzer to analyze queries and show static on speed of execution and other useful thing about OSQL is that its helps in scheduling.

Question: What is SQL?

Answer: The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. QL is Non-Procedural language. It allows the user to concentrate on specifying what data is required rather than concentrating on the how to get it.

 

The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

Question: What is DTS in SQL Server?
Answer: If a organization is big then it is also there that there is multiple option to store data some people are using EXCEL some are using ACCESS and some of they are using SQL SERVER and in some other format also but there a problem is arise that how to merge that data into one format there is different tool are there for doing this function. One of product of SQL SERVER-2000 DTS helps in this problem it provides a set of tool from that tool we can customize are database according to our need DTSRun is a command-prompt utility used to execute existing DTS packages.

Question: What is the difference between SQL and Pl/Sql?
Answer: We can get modify, Retrieve by single command or statement in SQL but PL/SQL process all SQL statements one at a time. With PL/SQL, an entire block of statements process in a single command line. sql is structured query language ,various queries are used to handle the database in a simplified manner. While pl/sql is procedural language contains various types of variable, functions and procedures and other major difference is Sql as the name suggest it is just structured query language whereas PLSQL is a combination of Programming language & SQL.

Question: Can You explain integration between SQL Server 2005 and Visual Studio 2005?
Answer: This integration provide wider range of development with the help of CLR for database server. Because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net. The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is use in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors

Question: What are Checkpoint in SQL Server?
Answer: When we done operation on SQL SERVER that is not committed directly to the database. All operation must be logged in to Transaction Log files after that they should be done on to the main database. Checkpoint are the point which alert Sql Server to save all the data to main database if no check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER. When we stop the SQL Server it will take long time because Checkpoint is also fired.

Question: What is the difference between UNION ALL Statement and UNION?
Answer:- The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION, the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Question: Write some disadvantage of Cursor?
Answer:- Cursor plays there row quite nicely but although there are some disadvantage of Cursor .
Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query gernate output and after that cursor goes one by one so roundtrip happen.Another disadvange of cursor are ther are too costly because they require lot of resources and temporary storage so network is quite busy.

Question: What is Log Shipping and its purpose?
Answer: In Log Shipping the transactional log file from one server is automatically updated in backup database on the other server and in the case when one server fails the other server will have the same DB and we can use this as the DDR(disaster recovery) plan.

Question: What are the null values in SQL SERVER?
Answer: Before understand the null values we have some overview about what the value is. Value is the actual data stored in a particular field of particular record. But what is done when there is no value in the field. That value is something like <null>.Nulls present missing information. We can also call null propagation.

Question: What is difference between OSQL and Query Analyzer?
Answer: Both are same for functioning but there is a little difference OSQL is command line tool which execute query and display the result same a Query Analyzer do but Query Analyzer is graphical.OSQL have not ability like Query Analyzer to analyze queries and show statistics on speed of execution .And other useful thing about OSQL is that its helps in scheduling which is done in Query Analyzer with the help of JOB.

 

Question: Write a Role of Sql Server 2005 in XML Web Services?
Answer:- SQL Server 2005 create a standard method for getting the database engine using SOAP via HTTP. By this method, we can send SOAP/HTTP requests to SQL Server for executing T-SQL batch statements, stored procedures, extended stored procedures, and scalar-valued user-defined functions may be with or without parameters.

Question: What are the different types of Locks ?
Answer: There are three main types of locks that SQL Server
(1)Shared locks are used for operations that does not allow to change or update data, such as a SELECT statement.
(2)Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
(3)Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

Question: What is 'Write-ahead log' in Sql Server 2000?
Answer: Before understanding it we must have an idea about the transaction log files. These files are the files which hold the data for change in database.
Now we explain when we are doing some Sql Server 2000 query or any Sql query like Sql insert query, delete sql query, update sql query and change the data in sql server database it cannot change the database directly to table .Sql server extracts the data that is modified by sql server 2000 query or by sql query and places it in memory. Once data is stores in memory user can make changes to that a log file is generated this log file is generated in every five minutes of transaction is done. After this sql server writes changes to database with the help of transaction log files. This is called Write-ahead log.

Question: What do u mean by Extents and types of Extents ?
Answer: An Extent is a collection of 8 sequential pages to hold database from becoming fragmented. Fragment means these pages relates to same table of database these also holds in indexing. To avoid for fragmentation Sql Server assign space to table in extents. So that the Sql Server keep up to date data in extents. Because these pages are continuously one after another. There are usually two types of extends:-Uniform and Mixed.
Uniform means when extent is own by a single object means all collection of 8 ages hold by a single extent is called uniform.
Mixed mean when more then one object is comes in extents is known as mixed extents.

Question: What is different in Rules and Constraints?
Answer: Rules and Constraints are similar in functionality but there is little difference between them. Rules are used for backward compatibility. One the most exclusive difference is that we can bind rules to a data types whereas constraints are bound only to columns. So we can create our own data type with the help of Rules and get the input according to that.

Question: What Is Database?
Answer: A database is similar to a data file in that it is a storage place for data. Like a data file, a database does not present information directly to a user; the user runs an application that accesses data from the database and presents it to the user in an understandable format. Database systems are more powerful than data files in that data is more highly organized. In a well-designed database, there are no duplicate pieces of data that the user or application must update at the same time. Related pieces of data are grouped together in a single structure or record, and relationships can be defined between these structures and records. When working with data files, an application must be coded to work with the specific structure of each data file. In contrast, a database contains a catalog that applications use to determine how data is organized. Generic database applications can use the catalog to present users with data from different databases dynamically, without being tied to a specific data format. A database typically has two main parts: first, the files holding the physical database and second, the database management system (DBMS) software that applications use to access data. The DBMS is responsible for enforcing the database structure, including: · maintaining relationships between data in the database. Ensuring that data is stored correctly and that the rules defining data relationships are not violated. · Recovering all data to a point of known consistency in case of system failures.

Question: what is Relational Database?
Answer: Although there are different ways to organize data in a database, relational databases are one of the most effective. Relational database systems are an application of mathematical set theory to the problem of effectively organizing data. In a relational database, data is collected into tables (called relations in relational theory). A table represents some class of objects that are important to an organization. For example, a company may have a database with a table for employees, another table for customers, and another for stores. Each table is built of columns and rows (called attributes and tuples in relational theory). Each column represents some attribute of the object represented by the table. For example, an Employee table would typically have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title. Each row represents an instance of the object represented by the table. For example, one row in the Employee table represents the employee who has employee ID 12345. When organizing data into tables, you can usually find many different ways to define tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively.

Question: What are Data Integrity and its categories?
Answer: Enforcing data integrity ensures the quality of the data in the database. For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company. Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into these categories:
1) Entity integrity
2) Domain integrity
3) Referential integrity
4) User-defined integrity
Entity Integrity: Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).
Domain Integrity: Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT efinitions, NOT NULL definitions, and rules).
Referential Integrity: Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft® SQL Server™ 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database. When you enforce referential integrity, SQL Server prevents users from:
· Adding records to a related table if there is no associated record in the primary table.
· Changing values in a primary table that result in orphaned records in a related table.
· Deleting records from a primary table if there are matching related records.
For example, with the sales and titles tables in the pubs database, referential integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.
User-Defined: Integrity User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

Question: SQL Server runs on which TCP/IP port and From where can you change the default port?
Answer: SQL Server runs on port 1433 but we can also change it for better security and From the network Utility TCP/IP properties -->Port number.both on client and the server.

Question: What is the use of DBCC commands?
Answer: DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

Question: What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Answer: Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Question: When do you use SQL Profiler?
Answer: SQL Profiler utility allows us to basically track Connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc.

Question: Can you explain the role of each service?
Answer: SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintenance, Backups DTC - Is for linking and connecting to other SQL Servers.

Question: What is Normalization ?
Answer: The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can impair the performance of the entire system.

Normalizing a logical database design involves using formal methods to separate the data into multiple, related tables. A greater number of narrow tables (with fewer columns) is characteristic of a normalized database. A few wide tables (with more columns) is characteristic of an nonnomalized database. Reasonable normalization often improves performance. When useful indexes are available, the Microsoft® SQL Server™ 2000 query optimizer is efficient at selecting rapid, efficient joins between tables.

Some of the benefits of normalization include:
·Faster sorting and index creation.
·A larger number of clustered indexes. For more information, Narrower and more compact indexes.
·Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements.
·Fewer null values and less opportunity for inconsistency, which increase database compactness.

As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance. Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables.

Sometimes the logical database design is already fixed and total redesign is not feasible. Even then, however, it might be possible to normalize a large table selectively into several smaller tables. If the database is accessed through stored procedures, this schema change could take place without affecting applications. If not, it might be possible to create a view that hides the schema change from the applications.

Question: Can you explain what View is in SQL ?
Answer: View is just a virtual table nothing else which is based or we can say devlop with SQL SELECT query. So we can say that its a real database table (it has columns and rows just like a regular table),but one difference is that real tables store data,but views can’t. View data is generated dynamically when the view is referenced.And view can also references one or more existing database tables or other views. We can say that it is filter of database.

Question: How to get which Process is Blocked in SQL SERVER ?
Answer:- There are two ways to get this  sp_who and sp_who2 . You cannot get any detail about the sp_who2 but its provide more information then the sp_who . And other option from which we can find which process is blocked by other process is by using Enterprise Manager or Management Studio, these two commands work much faster and more efficiently than these GUI-based front-ends.

SQL Server 2005 Schema, Schema Properties, Schema T-SQL

What is Schema in SQL Server 2005? Explain its properties with example?

A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement.

Properties

• Ownership of schemas and schema-scoped securables is transferable.


• Objects can be moved between schemas


• A single schema can contain objects owned by multiple database users.


• Multiple database users can share a single default schema.


• Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases.


• A schema can be owned by any database principal. This includes roles and application roles.


• A database user can be dropped without dropping objects in a corresponding schema.

 

 

Create database SQL2k5

Use SQL2k5

 

-- Created Schema Employee --

Create Schema Employee

 

-- Created table in Employee schema --

Create Table Employee.EmpInfo

(

EmpNo int Primary Key identity(1,1),

EmpName varchar(20)

)

 

-- data insertion --

 

Insert Into Employee.Empinfo Values('Jshah-3')

 

-- Data Selection --

Select * From Employee.Empinfo

 

-- Created another schema HR --

Create Schema HR

 

-- Transfer Objects between Schemas --

ALTER SCHEMA HR

TRANSFER Employee.Empinfo

 

-- Assigning Permission to Schema --

GRANT SELECT ON SCHEMA::HR TO Jshah

 

Download SQL Sever 2005/SQL Server 2008 Samples

You can download Microsoft SQL Server 2005/Microsoft SQL Server 2008 Samples.

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

OR

http://www.codeplex.com/