Can you explain about buffer cash and log Cache in sql server?
Buffer Cache:Buffer cache is a memory pool in which data pages are read. It performance of the buffer cache is indicated as follows: 95% indicates that pages that were found in the memory are 95% of time. Another 5% is needed for physical disk access. If the value falls below 90%, it is the indication of more physical memory requirement on the server.
Log Caches:Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log caches separately from the buffer cache.
What is a Trace frag? Where do we use it?
Temporary setting of specific server characteristics is done by trace tags. DBCC TRACEON is the command to set the trace flags. Once activated, trace flag will be in effect until the server is restarted. Trace frags are frequently used for diagnosing performance issues.
For example, the trace flag 3205 is used for disabling hard compression for tape drives, when an instance of SQL Server starts.
Describe how to use Linked Server.
MS SQL Server supports the connection to different OLE DB on an ad hoc basis. This persistent connection is referred as Linked Server.
The following are the steps to use Linked Server for any OLE DB. I refer this to use an MS-Excel workbook.
1. Open SQL Server Management Studio in SQL Server 2005
2. Expand Server Objects in Object Explorer.
3. Right-click on Linked Servers. Click on New Linked Server.
4. Select General page in the left pane and
i. Type any name for the linked server in the first text box
ii. Select the Other Data Source option.
iii. Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list.
iv. Type the Excel as the name of the OLE DB data source.
v. Type the full path and file name of the Excel file in Data Source box.
vi. Type the Excel version no. (7.0, 8.0 etc) in the Provider String. Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
vii. To create a linked server click on OK.
i. Type any name for the linked server in the first text box
ii. Select the Other Data Source option.
iii. Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list.
iv. Type the Excel as the name of the OLE DB data source.
v. Type the full path and file name of the Excel file in Data Source box.
vi. Type the Excel version no. (7.0, 8.0 etc) in the Provider String. Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
vii. To create a linked server click on OK.
Explain how to make remote connection in database?
The following is the process to make a remote connection in database:
· Use SQL Server Surface Area Configuration Tool for enabling the remote connection in database.
· Click on Surface Area Configuration for Services and Connections.
· Click on SQLEXPRESS/Database Engine/RemoteConnections
· Select the radio button: Local and Remote Connections and select ‘Using TCP/IP only’ under Local and Remote Connections.
· Click on OK button / Apply button
Explain how to send email from database?
SQL Server has a feature for sending mail. Stored procedures can also be used for sending mail on demand. With SQL Server 2005, MAPI client is not needed for sending mails.
The following is the process for sending emails from database.
- Make sure that the SQL Server Mail account is configured correctly and enable Database Mail.
- Write a script to send an e-mail. The following is the script.
USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'mathew@xyz.com;
@body = ' A warm wish for your future endeavor',
@subject = 'This mail was sent using Database Mail' ;
GO
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'mathew@xyz.com;
@body = ' A warm wish for your future endeavor',
@subject = 'This mail was sent using Database Mail' ;
GO
Difference between cross join and Full outer join
Cross Join :
No join conditions are specified.
Results in pairs of rows.
Results in Cartesian product of two tables.
No join conditions are specified.
Results in pairs of rows.
Results in Cartesian product of two tables.
Full Outer Join:
A combination of both left and right outer joins.
Results in every row from both of the tables , at least once.
Assigns NULL for unmatched fields.
A combination of both left and right outer joins.
Results in every row from both of the tables , at least once.
Assigns NULL for unmatched fields.
Explain the purposes of OPENXML clause sql server stored procedure
OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the RDB. It is also possible to query the data by using OpenXML. The path of the XML element needs to be specified by using ‘xpath’.
The following is a procedure for retrieving xml data:
DECLARE @index int
DECLARE @xmlString varchar(8000)
SET @xmlString ='
Prasanth
9343463943/PhoneNo>
Laasya
9342673212
EXEC sp_xml_preparedocument @index OUTPUT, @xmlString
DECLARE @xmlString varchar(8000)
SET @xmlString ='
EXEC sp_xml_preparedocument @index OUTPUT, @xmlString
SELECT * FROM OPENXML (@index, 'Persons/Person') WITH (id varchar(10), Name varchar(100) 'Name' , PhoneNo varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
The above code snippet results the following:
15201 Prasanth 9343463943
15202 Laasya 9342673212
The above code snippet results the following:
15201 Prasanth 9343463943
15202 Laasya 9342673212
Explain inline variable assignment in sql server 2008 with an example.
Inline variable assignment in SQL Server 2008 enables to – declare, and assign the variables in a ‘single line’.
The traditional Ex:
DECLARE @myVar int
SET @myVar = 5
need to declare the variable and assigning it to the variable to split into 2 lines and use 2 statements - DECLARE and SET.
DECLARE @myVar int
SET @myVar = 5
need to declare the variable and assigning it to the variable to split into 2 lines and use 2 statements - DECLARE and SET.
In SQL Server 2008, as the name suggests ‘inline’, both declaration and assignment can be given in a single line:
DECLARE @myVar int = 5
What is Compound Operators in sql server 2008? Explain with an example
Compound operators feature is enhanced in SQL Server 2008. They are like compound operators in languages like C, C++ etc.
Compound operators are a combination of operator with another operator.
The compound assignment operators are - arithmetic operators - +=,-=,*=, /=, %=, bitwise operators -&=, ^=,|=
Declare @number int
Set @number = 10
--using Compound assignment operator
Set @number+=200
Select @number as MyResult
Go
Set @number = 10
--using Compound assignment operator
Set @number+=200
Select @number as MyResult
Go
SQL Server 2008 introduces automatic auditing. Explain its benefits
Automatic auditing is a true auditing solution for enterprise customers. STL Trace can be used for satisfying several auditing needs. SQL Server Auditing feature offers a number of advantages that attracts and help DBAs with much more easily achievable goals. These include meeting regulatory compliance requirements. These include –
5. Provision for centralized storage of audit logs and integration with system center
6. Better performance that is noticeable
7. Permits free-grained auditing in which an audit can be targeted for some specific actions by a principle against a particular object.
8. Allows the objects of audit to be treated as first class database objects, which mean Transact-SQL DDL statements can create these objects.
9. The database object is controlled by database engine’s permission model and enforcement control.
10. A new level permission is featured in SQL Audit – ALTER ANY SERVER AUDIT- which allows a principle to CREATE, ALTER and DROP an Audit Specification object.
11. A database level permission – ALTER ANY DATABASE AUDIT – is introduced to allow a principle to CREATE, ALTER and DROP a Database specification object..
Explain Compression - row-level and page-level compression in sql server 2008
Data compression is a feature which is used to reduce disk storage space and increase the performance of the query by reducing the I/O operations.
SQL Server 2008 supports two types of compression – Row-level compression and Page-level compression.
A row-level and page-level compression takes place on the metadata.
Page level compression results in persisting certain common data that affects rows in a single location.
The compression takes place into number of bits. For example, the length of ‘varchar’ will be stored in 3 bits.
What is filtered indexes in sql server 2008? Explain its benefits and provide an example
Filtered index in SQL Server 2008 is an index WHERE clause. A filtered index is an optimized non-clustered index. It allows for defining the filter predicate with WHERE clause at the time of creating index. The rows from the filtered index are available in B-Tree will contain the rows which only satisfy the criteria of the filter while creating the index.
The benefits of Filtered indexes are:
· Has only that row which satisfies the filter criteria defined. This results in reducing the storage space need for the index.
· The filtered index statistics are accurate and more compact. The reason is they consider only the tuples / rows in the filtered index and it reduces the cost of overhead of updating the statistics.
· The data modification impact is less by using filtered index. Because it is updated only at the time where the data of the index is impacted.
· The cost of maintenance will be reduced, because only the subset of rows will be considered which rebuilding the index.
Ex: CREATE NONCLUSTERED INDEX FI_Employee_DOJ ON Employee(DOJ)
WHERE DOJ IS NOT NULL
WHERE DOJ IS NOT NULL
In the above example the NOT NULL is the filtered criteria for the index. Employee is the table and DOJ is the column name.
Explain Resource governor in sql server 2008
Resource Governor enables the DBA for managing the work load of SQL Server and critical system resource consumption. The limits of CPU and memory which are the incoming sessions to the SQL Server will be controlled by Resource Governor.
The various scenarios that occur when sudden spike in CPU and memory utilization that result in slow responses for querying requests. The Resource Governor enables the DBA’s to differentiate the workloads and allocates the shared resources which allow the available CPU and memory resources.
Explain Plan freezing in sql server 2008
Plan freezing is a new concept that allows the DBAs to persist plan guides. These guides could be reverted to when the queries either fail or drain the resources after the upgrade. The stability to queries is achieved by Plan Freezing. Several monitoring features for checking when the query is succeeded or failed are included with Plan Freezing.
Explain Table Value Parameters (TVP) in sql server 2008
A user defined tables are allowed between queries using the Table Value Parameters feature of SQL Server 2008. It also supports defining the tables between a client and a server. Querying, joining, inserting values, updating values etc., can be done as is being done with a normal table. Instead of a query taking a long list of parameters, they simple take TVP as a parameter.
For creating TVP, one need to define a user defined type and the columns which the TVP would hold. The following example creates a customer type which holds an id and name.
CREATE TYPE Customer AS TABLE (id int, CustomerName nvarchar(50))
A dummy table is created for persisting the information.
CREATE TABLE Customers (id int, CustomerName nvarchar(50)) GO
CREATE TABLE Customers (id int, CustomerName nvarchar(50)) GO
A procedure can also be created which takes a single parameter as a Table Value Parameter. Data can be inserted into two different tables, however from the outside it is a single object and only a single stored procedure is being called.
CREATE Procedure AddCustomers(@customer Customer READONLY) AS
INSERT INTO Customers SELECT id, CustomerName FROM @customer
GO
INSERT INTO Customers SELECT id, CustomerName FROM @customer
GO
The TVP as parameter must have the READONLY attribute and TVPs are basically temporary tables persisted on the server in tempdb.
Explain Intellisense in sql server 2008
Prior to SQL Server 2008, IntelliSense was available from third-party products. Red-Gate has a pretty good IntelliSense product.
IntelliSense of SQL Server 2008 is ON by default, and can be turn it OFF by using Tools->Options from Management Studio.
The process involves in creating a table object like the following:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [char](150) NULL,
[SaleDate] [date] NULL,
[SalePrice] [money] NULL
)
GO
Only the table exists. Use DML statements like SELECT, INSERT against SalesHistory table. A dropdown list of the fields available would be chosen from the table. This helps in knowing the information about columns easier.
DML statements can also be used with IntelliSense. For example type UPDATE statement against the SalesHistory table. A list of available fields are available for UPDATE statement. Not only for UPDATE also other statements.
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [char](150) NULL,
[SaleDate] [date] NULL,
[SalePrice] [money] NULL
)
GO
Only the table exists. Use DML statements like SELECT, INSERT against SalesHistory table. A dropdown list of the fields available would be chosen from the table. This helps in knowing the information about columns easier.
DML statements can also be used with IntelliSense. For example type UPDATE statement against the SalesHistory table. A list of available fields are available for UPDATE statement. Not only for UPDATE also other statements.
Explain PowerShell included in SQL Server 2008
Windows PowerShell is a new and powerful scripting shell which can be used by developers and administrators for automating server administration and deployment of the application. The PowerShell supports complex logic than the Transact-SQL scripts, which provides the SQL Server administrators the power to build robust administration scripts. Other Microsoft products can be administered by PowerShell. In a nut shell, PowerShell is a common scripting language across different servers.
There are two Windows PowerShell snap-ins provided by SQL Server provides:
SQL Server provider: It enables the navigation mechanism similar to the file system paths. Paths that are similar to file system paths can be built. The process is associated with SQL Server management object model and the other nodes are object model classes based.
Set of cmdlets: These are the commands which are utilized in PowerShell scripts for specifying an action of SQL Server. The actions such as running sqlcmd script which contains Transact-SQL or XQuery statements, are supported by cmdlets.
Explain Declarative Management Framework (DMF) in SQL Server 2008
Declarative Management Framework is a system for managing the instances of SQL Server 2008. It is a policy based system. The database security can be tighten with polity management, automated administration and explicit administration. A policy can be designed for prohibiting the usage of unauthorized applications and the naming conventions on the database are implied for developers.
Various tasks are planned by many DBAs and backing up databases, reviewing events logs, scanning for improper / unauthorized object creations, long running query SPIDs killing are being performed. Lot of tactical and reactionary items on the list of tasks can keep the conscientious DBA busy.
SQL Server will enforce to define the policies by using Management Studio and select certain servers for enforcing the policy. These can be monitored from SSMS, which acts as a central console.
Encryption of entire databases without the need for application changes in sql server 2008. Comment
The demands of regulatory compliance and overall data privacy concern are enabled by encryption in organizations. Searching for encrypted data by using fuzzy searches or range includes secure data from unauthorized users, and data encryption. The existing applications need not be changed by using this concept.
Explain External Key Management in sql server 2008
A comprehensive solution for encryption and key management is provided by SQL Server 2008. The growing need for greater information security within the data centers of enterprises is managed by security keys. This could be done by excellent support given by SQL Server 2008, by supporting third-party keys management and hardware security module products.
Explain Enhanced database mirroring in sql server 2008
Data base mirroring in SQL Server 2008 is enhanced by:
· Automatic Page Repair: The principal is enabled and mirror computers for recovering transparently from 823 and 824 errors on data pages, with a request for a fresh copy of the page that is corrupted from the mirroring partner.
· Improved Performance: The outgoing log stream is compressed by SQL Server 2008 for minimizing the network bandwidth that is required by database mirroring.
· Enhanced Supportability: Additional performance counters for enabling more granular accounting of the time, which is spent across the different stages of DBMS log processing. Dynamic Management Views and extensions of the existing views are included, for exposing additional information that is concerned to mirroring sessions.
Explain Hot Add CPU in sql server 2008
CPUs can dynamically be added to a running system, by using Hot Add CPU feature. New hardware can be added physically and online hardware partitioning logically. A virtualization layer can be used to add this feature virtually.
Hot Add CPU feature allows a database for scaling on demand for extending memory resources added online, The CPU resources can be supported for SQL Server 2008 on hardware platforms that supports, without the need of application downtime
The following are the requirements for Hot Add CPU:
1. Hardware which supports Hot Add CPU
2. Windows Server 2008 Datacenter server of 64-bit or Windows Server 2008 Enterprise Edition for the Itanium-Based System operating system.
3. SQL Server Enterprise.
Explain Extended events in sql server 2008
Extended Events are the enhanced event handling system in SQL Server. It is an architecture that is highly scalable, highly configurable. It allows the DBAs for collecting the required information, could it be little or much, for problem identification or problem trouble shooting.
Data collection which can output to an Event Tracing for Windows target is one of the key features of Extended Events. This allows the correlating data with the data collected from the corresponding operating system with the help of Event Tracing for Windows. Even the wait events could be correlated with the kernel EWT data. This process is done within a single view for isolating the waits for a specific reason.
The events are handled by an engine which is a services and objects collection which allows defining, process and managing event sessions, event data and event sessions respectively.
Explain sql server 2008 backup compression
A compression backup is smaller than uncompressed backup. The backup speed significantly increases because it needs less disk space and I/O operations. The CPU usage is increased and the increased and the additional CPU that is consumed by the process of compression would impact the operations that are running concurrently.
The following processes can be isolated for obtaining a good picture
- Physical disk counters
- Device throughput bytes / second counter of SQL Server Backup Device object
- Backup/Restore throughput / second counter of SQL Server Databases object.
Explain filestream storage of sql server 2008
The complexity of application development and reduces the managing unstructured data cost. The manageability is increased by extending various capabilities which are available only to relational data to non-relational data.
SQL Server 2008 came with 2 new capabilities for persisting BLOB data:
1. FILESTREAM: It is an attribute that can be set on a ‘varbinary’ column for data persistence on the file streams. This enables the benefits from fast streaming capabilities and storage capabilities.
2. Remote BLOB storage: It is a client-side API which reduces the application building complexity and relies on an external persistence for BLOBs and a database for relational data.
SQL Server 2008 will also continue the support for standard BLOB type through the data type ‘varbinary’.
Sparse Columns of sql server 2008. Explain with an example
A column with an optimized storage for null values is known as sparse column. Sparse columns reduce the storage space needs for null values. In a scenario of saving 20 percent to 40 percent of storage space, sparse columns can be considered. They are created using CREATE TABLE or ALTER TABLE statements. Sparse columns can be used with
- Column sets: The statements INSERT, DELETE, UPDATE could be referred the sparse columns by name. The same an also be combined into a single XML Column. This is a column set.
- Filtered index: As there are several null value rows in sparse columns, they are appropriate for filtered indexes. The filtered index on a sparse column can accommodate only the rows / tuples which populate values. The index created with filtered index is small and more efficient index.
A large number of user defined properties can be accessed by using sparse columns and filtered indexes enabled applications such as Share Point Services of windows are efficiently store and access.
TIME data type, datetime2, datetimeoffset data type in sql server 2008. Explain with an example for each
TIME Data type:
TIME data type of SQL Server 2008 allows to exclusively storing the time.
The following is an example of using TIME data type:
DECLARE @dot as TIME
SET @dot = get date()
PRINT @dt
The above script displays the output as HH:MM:SS.SSSSSSS format. The TIME has the data range from 00:00:00.0000000 through 23:59:59.9999999.
SET @dot = get date()
PRINT @dt
The above script displays the output as HH:MM:SS.SSSSSSS format. The TIME has the data range from 00:00:00.0000000 through 23:59:59.9999999.
DATETIME2 Data Type:
DATETIME2 is a data type which returns date/time. It provides larger factional seconds and the year compared to DATETIME data type. There are options to specify the number fractions as per the need. The maximum fraction is 7 and the minimum fraction is 0.
The following is an example of using DATETIME2 data type:
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
PRINT @dt7
The above script displays the date as YYYY-MM-DD HH:MM:SS.SSSSSSS format.
SET @dt7 = Getdate()
PRINT @dt7
The above script displays the date as YYYY-MM-DD HH:MM:SS.SSSSSSS format.
DATETIMEOFFSET Data type:
To store date, time along with time zone, the DATETIMEOFFSET is used. This is important when dealing with date of several countries with various time zones. The clock is based on 24-hour clock.
The following is an example of using DATETIMEOFFSET data type:
DECLARE @dt DATETIMEOFFSET(0)
SET @dt = '2007-10-29 22:50:55 -1:00'
DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1 = '2007-10-29 22:50:55 +5:00'
SELECT DATEDIFF(hh,@dt,@Dt1)
SET @dt = '2007-10-29 22:50:55 -1:00'
DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1 = '2007-10-29 22:50:55 +5:00'
SELECT DATEDIFF(hh,@dt,@Dt1)
Explain spatial data types - geometry and geography in sql server 2008
Location based data can be seamlessly consumed and extended by the enterprises with the comprehensive support of spatial data types.
Geometry data type: This data type supports a flat 2D surface with points of XY coordinates. The points could be on line string, on lines and also mark the polygon edges. There are certain methods like STintersects, STarea, STDistance and STTouch which uses geometry data types.
Geography data type: The similar methods of geometry data type are used in this data type. But the type reflects on curved 2D surfaces. The ST* functions are utilized but the results in the curvature.
DECLARE @gmtry geometry;
SET @gmtry = geometry::STGeomFromText('POINT (3 4)', 0);
DECLARE @grphy geography;
SET @grphy = geography::STGeomFromText('POINT (3 4)', 4326);
Certain observations need to be considered. They are:
SET @gmtry = geometry::STGeomFromText('POINT (3 4)', 0);
DECLARE @grphy geography;
SET @grphy = geography::STGeomFromText('POINT (3 4)', 4326);
Certain observations need to be considered. They are:
- A polygon is tried which is larger than a hemisphere, an ArgumentException is thrown.
- If the returned result is larger than a hemisphere, then a NULL value is returned.
- If the returned result is larger than a hemisphere, then a NULL value is returned.
Describe policy based administration feature of SQL Server 2008
Policy based database administration allows the DBA for managing the instances and objects of SQL Server 2008 across the enterprise by a defined policies that are set. These policies are the rules and regulations which determine the possible ways of what to do and what not to do and the way the violations of policies are enforced and reported. The policies are well defined by using facets and conditions.
Facet: An object which contains the properties which well describes a component.
Condition: A logical expression that is defined on a facet. It is used for identification of acceptable property values of the facet.
Change Data Capture (CDC) feature in sql server 2008. Explain with an example
Change Data Capture is a feature that is used for tracking the changes on a table. The process involves in steps.
Step 1 – Creation of a database
The database name is MyDataBase
USE [master]
GO
The database name is MyDataBase
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDataBase')
DROP DATABASE [MyDataBase]
GO
USE [master]
GO
DROP DATABASE [MyDataBase]
GO
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
CREATE DATABASE [MyDataBase]
GO
Step 2 - Creation of a table in MyDataBase database
USE [MyDataBase]
GO
CREATE DATABASE [MyDataBase]
GO
Step 2 - Creation of a table in MyDataBase database
USE [MyDataBase]
GO
/*** Object: Table [dbo].[MyTable] ***/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDataBase]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDataBase]
GO
CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [MyTable_PK] PRIMARY KEY
GO
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [MyTable_PK] PRIMARY KEY
GO
Step 3 - Enabling Change Data Capture feature.
The Transact SQL command enables the Change Data Capture feature.
After enabling the Change Data Capture, a schema along with objects is created.
USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO
The Transact SQL command enables the Change Data Capture feature.
After enabling the Change Data Capture, a schema along with objects is created.
USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO
Using the cdc the columns, tables, history of ddl commands, index columns and time mappings are created as follows:
cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
Describe how to store and query Spatial Data
Spatial data is stored by using Geometry and Geography data types that are introduced in SQL Server 2008.
Geometry data type is created as follows:
CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO
CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO
The data into the geometry data column is persisted by using the following INSERT command
INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));
The data in the geometry data columns is queried by using the following DECLARE and SELECT statements:
DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;
SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;
SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();
SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();
Describe Entity Data Services in sql server 2008, i.e. Line Of Business (LOB) framework and Entity Query Language (eSQL)
SQL Server 2008 allows objects to be created for high level business like Customers, Parts, Inventory. Instead of returning individual rows and tables, these entities could be used. The E-R model will now match with the SQL model.
MERGE in sql server 2008. Explain with an example
Merge statement allows a single statement for INSERT, DELETE and UPDATE a row that depends on a condition. The target table for certain operations is based on the results of join with a source table. The following example illustrates the use of MERGE.
MERGE InventoryMaster AS invmstr
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON invmstr. InventoryID = src. InventoryID
WHEN MATCHED THEN
UPDATE SET invmstr.Descr = src.Descr
WHEN NOT MATCHED THEN
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON invmstr. InventoryID = src. InventoryID
WHEN MATCHED THEN
UPDATE SET invmstr.Descr = src.Descr
WHEN NOT MATCHED THEN
INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr);.
What is blocking?
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first..
What is Public Role in SQL Server?
Every database has a public role which holds all the default permissions for the users in a database. This role cannot be dropped and cannot have roles or groups assigned. For security reasons, permissions granted to public role should be minimized..
Discuss about SQL Server Login?
SQL server login is used to connect to SQL server. This used when login in through the windows login credentials is not existent. This is possible when SQL Server is configured to operate in Mixed Mode. SQL server login is typically needed when applications are connected to external vendors or for users who don’t have any windows NT/2000 account.
Discuss about Builtin\Administrator.
The built in Administrator Account is basically used during some setup to join some machine in the domain. The built in administrator should be disabled immediately thereafter. For any disaster recovery, the account will be automatically enabled. It should not be used for normal operations.
Failover clustering overview
Failover clustering is mainly used for data availability. Typically in a failover cluster, there are two machines. One machine provides the basic services and the second is available to run the service when the primary system fails. The primary system is monitored periodically to check if it works. This monitoring may be performed by the failover computer or an independent system also called as cluster controller. In an event of failure of primary computer, the failover system takes control.
SQL Server (server-side) supports 3 major elements:
a. Creation of XML fragments: This is done from the relational data using FOR XML to the select query.
b. Ability to shred xml data to be stored in the database.
c. Finally, storing the xml data.
Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of
b. Ability to shred xml data to be stored in the database.
c. Finally, storing the xml data.
Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of
· XML Views: providing bidirectional mapping between XML schemas and relational tables.
· Creation of XML Templates: allows creation of dynamic sections in XML.
Brief about Bulk copy with an example.
Bulk copy utility of SQL allows data to be copied from one data file to another. The data is first exported from the source data file and then imported into the SQL server using the bcp command. It can also be used to transfer data from a table to a file
Describe how bcp command prompt utility is used to import and export data.
The bcp utility is accessed from the command prompt.
Syntax:
bcp {dbtable | query} {in | out | queryout | format} datafile [-n native type] [-c character type] [-S server name] [-U username] [-P password] [-T trusted connection]
bcp {dbtable | query} {in | out | queryout | format} datafile [-n native type] [-c character type] [-S server name] [-U username] [-P password] [-T trusted connection]
It starts with specifying the database or object name. In and out means whether the data needs to be copied into or out of the database. Hence in will be used for importing data while out will be used to export data. Remaining arguments are the basic command line arguments.
Example1: import data of Company database to the employee table
bcp Company.dbo.employee in c:\temp\employee.bcp -c -Sstevenw -Usa –P
bcp Company.dbo.employee in c:\temp\employee.bcp -c -Sstevenw -Usa –P
Example2:Export data of Company database from the employee table
bcp Company.dbo.employee out c:\temp\employee.bcp
bcp Company.dbo.employee out c:\temp\employee.bcp
Describe how bulk insert statement is used to import data.
Bulk Insert is used to copy data from a file into a table or view in a format as specified by the user.
Syntax:
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] FIRSTROW = first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ]
[ [ , ] MAXERRORS = max_errors ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
[ [ , ] TABLOCK ]
[ [ , ] ERRORFILE = 'file_name' ]
)]
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] FIRSTROW = first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ]
[ [ , ] MAXERRORS = max_errors ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
[ [ , ] TABLOCK ]
[ [ , ] ERRORFILE = 'file_name' ]
)]
Example:
BULK INSERT target.txt FROM ‘C:\Dest.txt’ WITH ( FIELDTERMINATOR = ‘ ” , ” ‘)
The FIELDTERMINATOR argument allows you to specify the delimiter used to discern column values.
BULK INSERT target.txt FROM ‘C:\Dest.txt’ WITH ( FIELDTERMINATOR = ‘ ” , ” ‘)
The FIELDTERMINATOR argument allows you to specify the delimiter used to discern column values.
• What kind of comparison operators can be used in a WHERE clause?
Operator | Meaning |
Equal to | |
Greater than | |
Less than | |
Greater than or equal to | |
Less than or equal to | |
Not equal to | |
Not equal to (not SQL-92 standard) | |
Not less than (not SQL-92 standard) | |
Not greater than (not SQL-92 standard) |
• What are the logical operators?
Operator | Meaning |
TRUE if all of a set of comparisons are TRUE. | |
TRUE if both Boolean expressions are TRUE. | |
TRUE if any one of a set of comparisons are TRUE. | |
TRUE if the operand is within a range. | |
TRUE if a subquery contains any rows. | |
TRUE if the operand is equal to one of a list of expressions. | |
TRUE if the operand matches a pattern. | |
Reverses the value of any other Boolean operator. | |
TRUE if either Boolean expression is TRUE. | |
TRUE if some of a set of comparisons are TRUE. |
Data type | Range | Storage |
bigint | -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) | 8 Bytes |
int | -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) | 4 Bytes |
smallint | -2^15 (-32,768) to 2^15-1 (32,767) | 2 Bytes |
tinyint | 0 to 255 | 1 Byte |
•
Print this post
No comments:
Post a Comment