General SQL Questions

Q. What's the difference between a primary key and a unique key?
Ans - Both primary key and unique enforce uniqueness of the column on whichthey are defined. But by default primary key creates a clustered indexon the column, where are unique creates a nonclustered index bydefault. Another major difference is that, primary key doesn't allowNULLs, but unique key allows one NULL only.

Q . What are "user defined datatypes" ?
Ans - User defined datatypes is a good feature provided by SQL. User defined datatypes let you extend the base SQL Server datatypes byproviding a descriptive name, and format to the database. Take forexample, in your database, there is a column called Flight_Num whichappears in many tables. In all these tables it should be varchar(8).In this case you could create a user defined datatype calledFlight_num_type of varchar(8) and use it across all your tables.

Q. Define candidate key, alternate key, composite key.
Ans - A candidate key is one that can identify each row of a table uniquely.Generally a candidate key becomes the primary key of the table. If thetable has more than one candidate key, one of them will become theprimary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is calledcomposite key.

Q. What is a transaction and what are ACID properties?
Ans - A transaction is a logical unit of work in which, all the steps mustbe performed or none. ACID stands for Atomicity, Consistency,Isolation, Durability. These are the properties of a transaction. Formore information and explanation of these properties, see SQL Serverbooks online or any RDBMS fundamentals text book.

Q. What's the maximum size of a row?
Ans - 8060 bytes.

Just answer a simple question - What is the maximum number of columns per table?
Loking forward for your comments.

SQL Server database security blunders

The database is a favorite target of hackers. That's why leaving the security of MS SQL Server is a bad idea, said Newman, who is co-author of Oracle Security Handbook as well as CTO and co-founder of Application Security Inc. New York-based Application Security is a security solutions and services provider.Newman has seen bundles of SQL Server database security blunders in his field work for Application Security. In an interview with SearchWindowsManageability.com, he lists the 10 most common mistakes and offers tips for avoiding them. Below I am mentioning 5 of them

Blunder No. 1: Only using standard security measures"Some people just use the SQL Server standard security capabilities, wherein you simply set up a login ID and a password that the database maintains," said Newman. That's a mistake. The most important security process for SQL Server is integrating the server's standard security functions with Windows account security measures. In fact, he said, Microsoft strongly encourages SQL Server users to integrate SQL Server security with Windows account security because SQL Server doesn't manage passwords that well.There are a lot of vulnerabilities associated with using only SQL Server security options. If you do use standard security, passwords end up written in log files or somewhere in the registry. That can lead to compromised security. Also, there is no facility for expiring passwords or requiring strong passwords. Unfortunately, using integrated security may not be an option if your users do not belong to the domain or must access the database through a firewall.
Blunder No. 2: Leaving default public permissions 'on'By default, SQL Server allows all users to select from tables such as "syslogins," which contain sensitive information such as the list of logins. By default, anyone could select from that table, and that's not really a good idea. It gives others a list of login names they could use to hack your database. SQL Server's system tables have public permissions, and it's a good idea to remove them to prevent people from getting that sensitive information.
Blunder No. 3: Setting an easy-to-guess password for the system administrator loginThe system administrator (SA) login is your main account on the server. "That's a powerful login and a common target. A SQL Server administrator needs to set a very strong password on it," said Newman. "Even better, lock that account and create secondary accounts for system administration, and use them instead of the SA login." If every database administrator uses the SA login, it's difficult to create an audit trail and know who did what. It's better to add users who have SA privileges, so you can track their actions individually.
Blunder No. 4: Allowing guest users in databasesSQL Server typically has multiple databases within it. If a database has a user name's guest in it, logins with access to other databases will be allowed to access this database as the guest user without official access permissions."It's a good idea never to allow guest users in your database unless that's what you intended," said Newman.Some databases within SQL Server -- such as the MSDB database -- come by default with a guest user account. "It's a good idea to remove that guest user," said Newman.The database administrator can't remove the guest user from some SQL Server databases, including master and tempDB, because of the nature of the databases. "If you can remove the guest user, however, you should, especially when sensitive information is at risk," Newman said.Now, some databases administrators intentionally set up guest user accounts because they want to broaden users' accessibility to various databases within SQL Server. That's an option, but it's a risky one, Newman said.
Blunder No. 5: Failing to enforce strong password policiesStrong passwords contain at least eight characters and both letters and numbers. Additionally, they include both upper- and lower-case characters. Require people to change their passwords every 90 days."If you are using standard security, this is hard to enforce," said Newman. Administrators can enforce these policies by educating users.