Monday, April 11, 2011

Where SQL server user names and passwords are stored in SQL server?


They get stored in System Catalog Views sys.server_principals and sys.sql_logins




If we have multiple AFTER Triggers on table how can we define the sequence of the triggers ?

sp_settriggerorder
sp_setorder
None

What is the maximum Storage Size of the Varchar Datatype?

4000 Bytes
7000 Bytes
8000 Bytes
1000 Bytes

What are the two system stored procedures for creating the lnked server?

sp_addlinkedserver,sp_addlinkedsrvlogin
sp_addlinkedserver,sp_addlogin
sp_addlinkedsrv,sp_addlinkedsrvlogin
addlinkedsrv,sp_addsrvlogin

How can you find out the sqlserver last restarted date?

SQL Query for sqlserver last restarted date.


Select Answer:

select create_date from sys.databases where name='master'
select create_date from sys.databases where name='tempdb'
select create_date from sys.databases where name='model'
select create_date from sys.databases where name='msdb'

What is Full form of DMV?

Data Management version
Dynmaic Management version
Dynamic Management View
Data Management view

Which function is used for Identifying the numeric value?

isnumeric
isnumber
is_number
is_numeric

How do you find out how many tables created today in particular database in SQL?


Select Answer:

select count(*) from sys.tables where create_date=getdate()
select count(*) from sys.tables where create_date=convert(varchar,getdate(),101) (Your selection was wrong)
select count(*) from sys.tables where convert(varchar,create_date,101)=getdate()
select count(*) from sys.tables where convert(varchar,create_date,101)=convert(varchar,getdate(),101)

What is the difference between a Subquery and a Join?

Subquery will work without having relation between tables, where as join will not work
Subquery will be used for more than one result set where Join is used for one result set
Join can be used for multiple tables, Where as Sub query cannot be used for multiple tables
None of the above
All of the above

Which one of is following is a System defined stored proecdure in SQL Server?

sp_test
sp_myuser
sp_who5
sp_spaceused
sp_gettriggers

What type Index is available in Sql server?


Search index
Clustered Index
Normalized index
All of the above
none of the above

What are the types of joins available in SQL Server?


Inner join, Outer join, Cross join, Loop join
Cross Join, Inner Cross Join, Left outer Join, Right Outer Join
Inner Join, Outer Join, Cross Join, Inner Cross Join
Inner Join Outer Join, Left join, half join
Inner Join ,Outer Join, Cross Join, Self Join

Declare @datalength varchar(50); set @datalength=' Dot net spider ' select datalength(@datalength),len(@datalength) What is the output for the followi


1)15,15
2)17,17
3)15,17
4)17,15
4)17,16

What is the use of UPDATE_STATISTICS in SQL?

To Remove the indexes after modifying huge data
To update the indexes after modifying huge data
To Create new indexes after modifying huge data

How much level Stored Procedure nesting is possible in SQL 2008?

28
30
32
None

What is a Linked SQL Server?

It is concept to add a sql server to another sql server
It is used to create SP from one SQL Server to another
It is used to fetch data from two SQL Servers
All of the above
None of the above

What is the use of sp_CONFIGURE command?

Select Answer:

It is used to configure a table
It is used to configure a row
It is used to configure a stored procedure
It is used to configure a server-level settings
All of the above

What is policy management in SQL Server?

Select Answer:

To define a policy for a Function
To define a policy for a Stored procedure
To define a policy for a enterprise
All of the above
None of the above

SQL Server User names and password are stored in?

Select Answer:

sys.server_authentication and sys.sql_users
sys.server_authentication and sys.sql_logins
sys.server_policies and sys.sql_users
sys.server_management and sys.sql_users
sys.server_principals and sys.sql_logins

What is a Sparse columns in SQL Server?


Select Answer:

It is a special column which will allow null values be default
It is a special column which will save space in Database
It is used to arrange the columns in a logical order
All of the above
None of the above

What is advantage of Response.Buffer()

Select Answer:

This controls the flow of data from the server to the user's browser. When the buffer is set to true, which it is by default, data isn't sent to the browser until the server completely processes the page. When the buffer is set to false, data is sent to th
Use this when the buffer is set to true and you want to get rid of everything processed up to where the Clear() is.
BOTH
NONE

Whats is new in SQL Server 2008 Top operators?

TOP operators can be used in Create and Modify statements
TOP operators can be used in Insert, Update and Delete Statements
TOP operators can be used in DDL and as well as DML
None of the above

Which is not true about CTE?

Select Answer:

It is used for Temporary results
It is similar to Derived table
It can be used in Multiple SQL statements
It can be used in Single SQL statements
It will be available for the duration of the query

What is the use of MERGE statement in SQL Server 2008?


Select Answer:

It is used to perform multiple DDL operations
It is used to perform multiple DML operations
It is used to perform Only Insert and Delete operations
It will be processed more than one

Which of the following is not true about Merge statement?


It is used to merge INSERT, UPDATE and DELETE
Merged data will be processed at once
Semi colon is not mantatory after the merge statement
All of the Above
None of the above

Which clause is necessary to have an ORDER BY clause in a View?

SELECT
DISTINCT
TOP

How many non-clustered index can be created in a Sql Server 2008 table?




Select Answer:

249
255
999

What is a Collation in SQL Server?

It is used to compare different data types in SQL server
It is used to define rules for comapring 2 different data types in SQL Server
It is used to define rules to compare and how the data is stored
All of the above
None of the Abive

Which is not true about the Sub Query in SQL Server?




Select Answer:

A subquery is a select query which is nested within another T-SQL statement
A subquery will always return a single result
A subquery can be used with GROUP BY, HAVING
A subquery can be used as a parameter to a function call

Can you call procedure in functions?



Select Answer:

Yes
No
We cant say

How many nested Transaction can possible in SQL SERVER?




Select Answer:

1
2
16
32
None of these

Why Normalization is used?




Select Answer:

To minimize redundancy
Easy maintenance and up keeping of the data
Both 1 and 2
None of these

Is it possible to call procedure inside a function.


Select Answer:

Yes
No
I dont know

What is true about Local Temporary table in SQL Server 2008?




Select Answer:

It remains permanently in Database
It remains only for the particular session
It remains only for the duration of a connection
None of the Above

What is Not true about Global Temporary Table?




Select Answer:

It remains permanently in Database
It remains only for the particular session
Its rows exist only within the given connection
Table Definition will remain in the Database

What is the use of STUFF function in SQL Server 2008?



Select Answer:

It is to insert a string between strings
It is used to overwrite the existing string
It is used to overwrite the existing characters
All of the above

Which are the new Data types introduced in SQL Server 2008?

Select Answer:

Datetime Geography and Time (Your selection was wrong)
Geometry, Time and Date
Geometry, Time, Date, Datetime and DateTime2
All of the above

What is the advantage of using DATETIME2 data type in SQL Server 2008?


It is used to Convert date to Time
It is used to cover a wide range of years
It is used to convert the time to fraction
All of the above

Can we use Sql express edition 2008 on dot net windows application?


You can use this windows application.

Use server uthentication with id and password.

Then select users and grant permission

Second Option:

You can use the Sql Server 2008 for the database that is in the shared drive, you need to provide the authentication information.

Microsoft SQL Server SQL Server 2008

SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SQL Server Management Studio combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.

SQL Server Management Studio combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager, included in previous releases of SQL Server, into a single environment. In addition, SQL Server Management Studio works with all components of SQL Server such as Reporting Services, Integration Services, and SQL Server Compact 3.5 SP1. Developers get a familiar experience, and database administrators get a single comprehensive utility that combines easy-to-use graphical tools with rich scripting capabilities.

How to use Management Studio ?

The left side panel shows a tree structure. The first node says "(local)\SQL2005". Here, I have connected to an instance SQL Server called "SQL2005" on the local computer. Since I have SQL Server 2000 and SQL Server 2005 installed on my computer, I used the name "SQL2005" when I installed SQL 2005.

When you expand the server name, you will see few nodes depending on the number of features you have installed:

1. Databases
2. Security
3. Server Objects
4. Replication
5. Management
6. Notification Services
etc.

The first node "Databases" is the one that you will use most of the time. This node includes all the databases created in this instance of the database server. The security node allows you to manage user accounts and permissions.

configuring and Connecting to SQL Server Database using Management Studio

As you learned in one of the previous chapters, SQL Management Studio can be used to manage multiple instances of SQL Server. However, when you open the Management Studio, it will prompt you to specify the login information to any one of the instances.
Server Type

The first dropdown in the above screenshot represents the "Server Type". Depending on the number of features you have installed, there will be various options here. When you are trying to connect to the database server, you have to choose "Database Engine".

Server Name

Second dropdown is to select the database server name. If you are connecting to the SQL Server installed on local computer, you can use one of the following (assuming DevServer1 as the computer name and 192.168.2.100 as IP Address):

(local)
. (dot)
localhost
127.0.0.1
DevServer1
192.168.2.100

The first 4 options are used to represent the local computer. If you are connecting to an SQL Server installed on a different computer, you can use only IP Address or Computer name.

If you are connecting to a named instance of SQL Server instead of the default instance, then you must append a backslash and instance name to the above mentioned names. Example: 192.168.2.100\SQLInstance2

Authentication

This dropdown gives two options:

1. Windows Authentication
2. SQL Server Authentication

If you select Windows Authentication, the login and password you used while logging in to your computer will be used to connect to the database server. If you are an administrator on the local computer or if you have configured the database server to use your windows account, you can use this option.

SQL Server Authentication can be used if you have created a user name and password in SQL Server. In that case, you must select this option and specify the user name and password below the drop down.

Which are the SQL Management Studio Editions

SQL Management Studio comes in two flavours:

1. SQL Management Studio

This edition comes with all versions of SQL Server 2005 except SQL Server 2005 Express Edition. This edition has various advanced features like import data from other database systems, export data to other databases etc.

2. SQL Management Studio Express

SQL Management Studio Express is part of SQL Server 2005 Express Advanced edition. You can download Management Studio Express as a separate tool also. Some of the key features like Import data, export data etc are missing in the Express edition of Management Studio.

What is SQL Management Studio?

As you already learned, the core part of SQL Server system is the 'server' which runs as a service. The service is a hidden application that runs in the background and it is hard to deal with this service directly.

SQL Server 2005 comes with a tool called 'Management Studio' which will help you manage your SQL Server. SQL Management Studio allows you to perform various actions on SQL Server including:

1. Create/Delete databases
2. Backup/restore databases
3. Attach/detach databases
4. Design tables and edit data
5. Execute queries against any database

SQL Management studio has a lot more features than explained above. Some of the main functionalists mentioned above are explained in coming chapters.

SQL Management Studio allows you to manage multiple instances of SQL Server installed on same computer or different computers. To manage a specific instance of SQL Server, you have to first connect to the instance using appropriate login and password. You need to install management studio only once even if you install multiple instances of SQL Server.
Subscribe to: Posts (Atom)
What is RDBMS?

In recent years, database management systems (DBMS) have established themselves as the primary means of data storage for information systems ranging from large commercial transaction processing applications to PC-based desktop applications. At the heart of most of today's information systems is a relational database management system (RDBMS). RDBMSs have been the workhorse for data management operations for over a decade and continue to evolve and mature, providing sophisticated storage, retrieval, and distribution functions to enterprise-wide data processing and information management systems. Compared to the file systems, relational database management systems provide organizations with the capability to easily integrate and leverage the massive amounts of operational data into meaningful information systems. The evolution of high-powered database engines such as Oracle7 has fostered the development of advanced "enabling" technologies including client/server, data warehousing, and online analytical processing, all of which comprise the core of today's state-of-the-art information management systems.

Examine the components of the term relational database management system. First, a database is an integrated collection of related data. Given a specific data item, the structure of a database facilitates the access to data related to it, such as a student and all of his registered courses or an employee and his dependents. Next, a relational database is a type of database based in the relational model; non-relational databases commonly use a hierarchical, network, or object-oriented model as their basis. Finally, a relational database management system is the software that manages a relational database. These systems come in several varieties, ranging from single-user desktop systems to full-featured, global, enterprise-wide systems, such as Oracle7.

This blog discusses the basic elements of a relational database management system, the relational database, and the software systems that manage it. Also included is a discussion of nonprocedural data access. If you are a new user to relational database technology, you'll have to change your thinking somewhat when it comes to referencing data nonprocedurally.

The Relational Database Model

Most of the database management systems used by commercial applications today are based on one of three basic models: the hierarchical model, the network model, or the relational model. The following sections describe the various differences and similarities of the models.

Hierarchical and Network Models

The first commercially available database management systems were of the CODASYL type, and many of them are still in use with mainframe-based, COBOL applications. Both network and hierarchical databases are quite complex in that they rely on the use of permanent internal pointers to relate records to each other. For example, in an accounts payable application, a vendor record might contain a physical pointer in its record structure that points to purchase order records. Each purchase order record in turn contains pointers to purchase order line item records.

The process of inserting, updating, and deleting records using these types of databases requires synchronization of the pointers, a task that must be performed by the application. As you might imagine, this pointer maintenance requires a significant amount of application code (usually written in COBOL) that at times can be quite cumbersome.

Elements of the Relational Model

Relational databases rely on the actual attribute values as opposed to internal pointers to link records. Instead of using an internal pointer from the vendor record to purchase order records, you would link the purchase order record to the vendor record using a common attribute from each record, such as the vendor identification number.

Although the concepts of academic theory underlying the relational model are somewhat complex, you should be familiar with are some basic concepts and terminology. Essentially, there are three basic components of the relational model: relational data structures, constraints that govern the organization of the data structures, and operations that are performed on the data structures.

Relational Data Structures

The relational model supports a single, "logical" structure called a relation, a two-dimensional data structure commonly called a table in the "physical" database. Attributes represent the atomic data elements that are related by the relation. For example, the Customer relation might contain such attributes about a customer as the customer number, customer name, region, credit status, and so on.


Key Values and Referential Integrity


Attributes are grouped with other attributes based on their dependency on a primary key value. A primary key is an attribute or group of attributes that uniquely identifies a row in a table. A table has only one primary key, and as a rule, every table has one. Because primary key values are used as identifiers, they cannot be null. Using the conventional notation for relations, an attribute is underlined to indicate that it is the primary key of the relation. If a primary key consists of several attributes, each attribute is underlined.

You can have additional attributes in a relation with values that you define as unique to the relation. Unlike primary keys, unique keys can contain null values. In practice, unique keys are used to prevent duplication in the table rather than identify rows. Consider a relation that contains the attribute, United States Social Security Number (SSN). In some rows, this attribute may be null in since not every person has a SSN; however for a row that contains a non-null value for the SSN attribute, the value must be unique to the relation.

Linking one relation to another typically involves an attribute that is common to both relations. The common attributes are usually a primary key from one table and a foreign key from the other. Referential integrity rules dictate that foreign key values in one relation reference the primary key values in another relation. Foreign keys might also reference the primary key of the same relation. Figure illustrates two foreign key relationships.



Oracle and Client/Server


Oracle Corporation's reputation as a database company is firmly established in its full-featured, high-performance RDBMS server. With the database as the cornerstone of its product line, Oracle has evolved into more than just a database company, complementing its RDBMS server with a rich offering of well-integrated products that are designed specifically for distributed processing and client/server applications. As Oracle's database server has evolved to support large-scale enterprise systems for transaction processing and decision support, so too have its other products, to the extent that Oracle can provide a complete solution for client/server application development and deployment. This chapter presents an overview of client/server database systems and the Oracle product architectures that support their implementation.

An Overview of Client/Server Computing

The premise of client/server computing is to distribute the execution of a task among multiple processors in a network. Each processor is dedicated to a specific, focused set of subtasks that it performs best, and the end result is increased overall efficiency and effectiveness of the system as a whole. Splitting the execution of tasks between processors is done through a protocol of service requests; one processor, the client, requests a service from another processor, the server. The most prevalent implementation of client/server processing involves separating the user interface portion of an application from the data access portion.

On the client, or front end, of the typical client/server configuration is a user workstation operating with a Graphical User Interface (GUI) platform, usually Microsoft Windows, Macintosh, or Motif. At the back end of the configuration is a database server, often managed by a UNIX, Netware, Windows NT, or VMS operating system.

Client/server architecture also takes the form of a server-to-server configuration. In this arrangement, one server plays the role of a client, requesting database services from another server. Multiple database servers can look like a single logical database, providing transparent access to data that is spread around the network.

Designing an efficient client/server application is somewhat of a balancing act, the goal of which is to evenly distribute execution of tasks among processors while making optimal use of available resources. Given the increased complexity and processing power required to manage a graphical user interface (GUI) and the increased demands for throughput on database servers and networks, achieving the proper distribution of tasks is challenging. Client/server systems are inherently more difficult to develop and manage than traditional host-based application systems because of the following challenges:

The components of a client/server system are distributed across more varied types of processors. There are many more software components that manage client, network, and server functions, as well as an array of infrastructure layers, all of which must be in place and configured to be compatible with each other.

The complexity of GUI applications far outweighs that of their character-based predecessors. GUIs are capable of presenting much more information to the user and providing many additional navigation paths to elements of the interface.

Troubleshooting performance problems and errors is more difficult because of the increased number of components and layers in the system.

Databases in a Client/Server Architecture

Client/server technologies have changed the look and architecture of application systems in two ways. Not only has the supporting hardware architecture undergone substantial changes, but there have also been significant changes in the approach to designing the application logic of the system.

Prior to the advent of client/server technology, most Oracle applications ran on a single node. Typically, a character-based SQL*Forms application would access a database instance on the same machine with the application and the RDBMS competing for the same CPU and memory resources. Not only was the system responsible for supporting all the database processing, but it was also responsible for executing the application logic. In addition, the system was burdened with all the I/O processing for each terminal on the system; each keystroke and display attribute was controlled by the same processor that processed database requests and application logic.

Client/server systems change this architecture considerably by splitting all of the interface management and much of the application processing from the host system processor and distributing it to the client processor.

Combined with the advances in hardware infrastructure, the increased capabilities of RDBMS servers have also contributed to changes in the application architecture. Prior to the release of Oracle7, Oracle's RDBMS was less sophisticated in its capability to support the processing logic necessary to maintain the integrity of data in the database. For example, primary and foreign key checking and enforcement was performed by the application. As a result, the database was highly reliant on application code for enforcement of business rules and integrity, making application code bulkier and more complex. Figure 2.1 illustrates the differences between traditional host-based applications and client/server applications. Client/server database applications can take advantage of the Oracle7 server features for implementation of some of the application logic.