Installing and Configuring SQL Server 2005

  • In order to change collation settings, reinstall SQL Server 2005 on a server with the same collation settings as another server.
  • In order to install SQL Server 2005 on a computer on which SQL Server 2000 is already installed, install SQL Server 2005 as a named instance.
  • Members of db_access role can assign permissions to other users to enable them to access and query the database.
  • RAID-0, also known as disk striping, is made up of a disk set in which data is divided into blocks and spread equally in each disk. It provides best performance because data read and data write operations are not limited to a single disk, but to a set of disks. It does not provide data redundancy. Data once lost cannot be recovered.
  • The Service Broker can be attached to a database by using the FOR ATTACH clause of the CREATE DATABASE statement.
  • If a snapshot of a database is present on the same computer on which the database is present, the database cannot be deleted.
  • In order to connect to the DAC, specify ADMIN: server_name in the Connect to the Server dialog box.
  • The login information stored in the master database.
  • In order to secure data that is data transmitted over a network, set the value of the ForceEncryption option on a server to Yes.
  • In order to enable a user to connect to the database, a DBA should add the user to that database.
  • The db_ddladmin fixed database role allows its members to add, modify, or drop objects in the database. Members of the db_ddladmin role can issue DDL statements (e.g. CREATE, ALTER, and DROP) in the database.
  • Member of db_ddladmin role have the permission to execute the T-SQL statements.
  • The sysadmin role allows a user to view the security log.
  • Continue reading

Optimizing the performance of database servers and databases

  • Memory: Pages/sec counter is used to detect whether or not the memory bottleneck is caused by I/O overhead.
  • Disk I/O bottleneck can be monitored by Physical Disk: % Disk Time and Physical Disk: Avg Disk Queue Length counters.
  • The System Monitor tool is used to know the cause of slow authentication.
  • The SQLServer: Buffer Manager performance object is used to check the amount of RAM used by a computer.
  • The Physical Disk: %Disk Time counter measures the pressure on a physical hard disk array.
  • Add more RAM if the reading of Memory: Pages/sec counter is high.
  • Reduce the Maximum Worker Thread configuration settings if the value of System: Processor Queue Length counter is high.
  • Continue reading

Retrieving data using the SQL SELECT statement and using single row functions to customize the output

  • /*…*/ and — are used to put comments in an Oracle program.
  • The CREATE TABLE does not end immediately with a semicolon, and a column name cannot start with a numeric value. However, a numeric value can be used in between a column name.
  • Name of a table cannot start with @ special character.
  • Knowing about the GROUP BY clause.

Using sub queries and set operators to solve queries, as well as DDL statements to create and manage tables

  • + (unary), – (unary), *, /, ||, =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN, NOT, AND, OR is the correct order of operators precedence.
  • Continue reading