Sql Server 2000/2005/2008

Findout SQL server Job notification details

2012-01-12T03:11:00.001-08:00

How to findout all the notification details which are configured in the server, like there are some jobs which are not sending notifications for successive / failure actions, so we need to fix that. By identifying each and everyone manually it takes a long time when there are no. of jobs, so following query will help to identify the notification details.

select a.name, case when a.enabled =1 then 'Enabled' else 'Disabled' end Status, case when notify_level_email = 1 then 'Job success' when notify_level_email = 2 then 'Job failure' end NotifyEmail, b.name
from sysjobs a left outer join (Select * from Sysoperators ) b on a.notify_email_operator_id = b.id
union all
select a.name,case when a.enabled =1 then 'Enabled' else 'Disabled' end Status, case when notify_level_page = 1 then 'Job success' when notify_level_page = 2 then 'Job failure' end NotifyPage, c.name
from sysjobs a left outer join (Select * from Sysoperators ) c on a.notify_page_operator_id = c.id order by a.name

How to fix Orphan users in Sqlserver

2011-09-27T02:39:00.000-07:00

The orphan users can be fixed by executing sp_change_users_login procedure. The syntax would be

Syntax :
sp_change_users_login 'update_one', 'Username','UserName'

SQL SERVER 2008 R2 FEATURES (CONSOLIDATED)

2011-01-27T06:46:00.000-08:00

A Document
on
Sql Server 2008 R2 Features
Microsoft SQL Server 2008 R2 is the latest release of SQL Server. This documentation explains about SQL Server 2008 R2 and its features. The “R2” tag indicates that this is an immediate release of SQL Server. The Sql server 2008 R2 having features which helps to Developer and DBAs both. In addition to new features, there are two new editions as well, SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse.

Following are the new features, connected features and enhancements in existing features introduced in Sql Server 2008 R2.

Sql server 2008 R2 StreamInsight

New in SQL Server 2008 R2 is component called StreamInsight. This interesting component allows streaming data to be analyzed on the fly. Meaning the data is processed directly from the source stream prior to being saved in a SQL Server table. This could be extremely handy if you’re running a real time system and need to analyze data but can’t afford the latency of a committed write to a table first. Examples usually cited for this application include stock trading streams, click stream web analytics, and industrial process controls. Multiple input streams can be simultaneously monitored.

Sql server 2008 R2 Master Data Services

Master Data Services (MDS) is both a concept and a product. The concept of a Master Data Service is that there is a central data gate keeper of core business data. Data items such as customer billing addresses, employee/customer names, and product names should be centrally managed so that all consuming applications have the same information. The Microsoft example given is a company that has a customer address record in the customer table but a different address in the mailing table. A Master Data Service application would ensure that all tables would have only one correct address. While an MDS can be a homegrown application, SQL Server 2008 R2 includes an application and an interface to manage the central data.

SQL Server Report Builder 3.0 for SQL Server 2008 R2
Report Builder 3.0 introduces additional visualizations including maps, sparklines and databars which can help produce new insights well beyond what can be achieved with standard tables and charts. The Report Part Gallery is also included in this release - taking self-service reporting to new heights by enabling users to re-use existing report parts as building blocks for creating new reports in a matter of minutes with a “grab and go” experience. Additionally, users will experience significant performance improvements with enhancements to the ability to use Report Builder in server mode. This allows for much faster report processing with caching of datasets on the report server when toggling between design and preview modes.

SQL Server 2008 R2 Reporting Services Add-in for SharePoint Technologies 2010 The Microsoft SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint Technologies 2010 allows you to integrate your reporting environment with SharePoint to experience the benefits of using the collaborative environment provided by SharePoint. Once you install the Reporting Services Add-in and configure your servers for integration, you can publish Reporting Services content to a SharePoint library and then view and manage those documents directly from a SharePoint site.

SQL Server 2008 R2 Policies Microsoft SQL Server 2008 R2 Policies are examples of how you can take advantage of Policy Based Management. These policies will help you follow some of the SQL Server best practices and avoid common pitfalls. For more information, please see Administering Servers by Using Policy Based Management in SQL Server 2008 R2 Books Online.

Sql server 2008 R2 Data-Tier Application
A Data-Tier Application (abbreviated as DAC –no idea what the C stands for, and not to be confused with the Windows Data Access Components also abbreviated as DAC ) is an object that stores all the needed database information for a project, such as login, tables, and procedures into one package that can be consumed by Visual Studio. By creating a Data-Tier Application, a SQL Server package version could be saved with each Visual Studio build of your application. This would allow application code builds to be married to a database build in an easily managed way.

Unicode Compression in Sql server 2008 R2
SQL Server 2008 R2 uses a new algorithm known as Simple Compression Scheme for Unicode storage. This reduces the amount of disk spaced used by Unicode characters. This new format happens automatically and is managed by the SQL Server engine so no programming changes are required of the DBA.

SQL Server Utility in Sql server 2008 R2
The new SQL Server Utility is a repository object for centrally controlling multiple SQL Server instances. Performance data and configuration policies can be stored in a single Utility. The Utility also includes an Explorer tool where multi-server dashboards can be created.

Sql server 2008 R2 Multi Server Dashboards
While the SQL Server Management Studio could always connection to multiple servers, each was managed independently with no central view of all of them. Now with SQL Server 2008 R2, Dashboards showing combined server data can be created.

SQL Server Compact 3.5 SP2 SQL Server Compact 3.5 SP2 is an embedded database that allows developers to build robust applications for Windows desktops and mobile devices. The download contains the files for installing SQL Server Compact 3.5 SP 2 on Windows desktop.

SQL Server Compact 3.5 SP2 For Windows Mobile SQL Server Compact 3.5 SP2 is an embedded database that allows developers to build robust applications for Windows desktops and mobile devices. The download contains the files for installing SQL Server Compact 3.5 SP 2 on Windows desktop.

SQL Server Compact 3.5 SP2 Server Tools SQL Server Compact 3.5 SP2 is an embedded database that allows developers to build robust applications for Windows desktops and mobile devices. The download contains the files for installing SQL Server Compact 3.5 SP 2 on Windows desktop.

SQL Server Compact 3.5 SP2 Books On-line SQL Server Compact 3.5 SP2 is an embedded database that allows developers to build robust applications for Windows desktops and mobile devices. The download contains the files for installing SQL Server Compact 3.5 SP 2 on Windows desktop.

SQL Server JDBC Driver 3.0 In our continued commitment to interoperability, Microsoft has released a new Java Database Connectivity (JDBC) driver. The SQL Server JDBC Driver 3.0 download is available to all SQL Server users at no additional charge, and provides access to SQL Server 2008 R2 , SQL Server 2008, SQL Server 2005 and SQL Server 2000 from any Java application, application server, or Java-enabled applet. This is a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5. This release of the JDBC Driver is JDBC 4.0 compliant and runs on the Java Development Kit (JDK) version 5.0 or later. It has been tested against major application servers including IBM WebSphere, and SAP NetWeaver.

Connector 1.1 for SAP BW for SQL Server 2008 R2 The Microsoft Connector for SAP BW is a set of managed components for transferring data to or from an SAP NetWeaver BW version 7.0 system. The component is designed to be used with the Enterprise and Developer editions of SQL Server 2008 R2 Integration Services. To install the component, run the platform-specific installer for x86, x64, or Itanium computers respectively. For more information see the Readme and the installation topic in the Help file.

System CLR Types for SQL Server 2008 R2 The SQL Server System CLR Types package contains the components implementing the geometry, geography, and hierarchy id types in SQL Server 2008 R2. This component can be installed separately from the server to allow client applications to use these types outside of the server.

SQL Server 2008 R2 Remote Blob Store The SQL Server Remote Blob Store is a method for storing blobs of unstructured data in an external Content Addressable data store. The component consists of a client-side DLL that is linked into a user application, as well as a set of stored procedures to be installed on SQL Server. Run the self-extracting download package to create an installation folder. The setup program contained there will install RBS on X86, X64, and Itanium-based computers.

SQL Server 2008 R2 Books On-line Microsoft SQL Server 2008 R2 Books Online is the primary documentation for SQL Server. Visit the SQL Server 2008 Books Online page on the Microsoft Download Center.

SQL Server 2008 R2 Upgrade Advisor Microsoft SQL Server 2008 R2 Upgrade Advisor analyzes instances of SQL Server 2000, SQL Server 2005 and SQL Server 2008 in preparation for upgrading to SQL Server 2008 R2. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.

SQL Server 2008 R2 Native Client Microsoft SQL Server 2008 R2 Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 R2 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 R2 features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

OLEDB Provider for DB2 The Microsoft OLE DB Provider for DB2 Version 3.0 offers a set of technologies and tools for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2008 R2 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the data provider with Integration Services, Analysis Services, Replication, Reporting Services, and DistributedQuery Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the Version 3.0 provider and tools on x86, x64, and IA64 computers. Read the installation guide and release notes for more information.

SQL Server 2008 R2 Command Line Utilities The SQLCMD utility allows users to connect to, send Transact-SQL batches from, and output rowset information from SQL Server 7.0, SQL Server 2000, SQL Server 2005, and SQL Server 2008 and 2008 R2 instances. The bcp utility bulk copies data between an instance of Microsoft SQL Server 2008 R2 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

SQL Server Service Broker External Activator for SQL Server 2008 R2 The Microsoft SQL Server 2008 R2 Service Broker External Activator is an extension of the internal activation feature in SQL Server 2008 R2 that lets you move the logic for receiving and processing Service Broker messages from the Database Engine service to an application executable that runs outside SQL Server. By doing this, cpu-intensive or long-duration tasks can be offloaded out of SQL Server to an application executable, possibly in another computer. The application executable can also run under a different Windows account from the Database Engine process. This gives administrators additional control over the resources that the application can access. Run the self-extracting download package to create an installation folder. Read Books Online for more information. The single setup program will install the service on x86, x64, and IA64 computers. Read the documentation for more information

Windows PowerShell Extensions for SQL Server 2008 R2 The Microsoft Windows PowerShell Extensions for SQL Server2008 R2 includes a provider and a set of cmdlets that enable administrators and developers to build PowerShell scripts for managing instances of SQL Server. The SQL Server PowerShell Provider delivers a simple mechanism for navigating SQL Server instances that is similar to file system paths. PowerShell scripts can then use the SQL Server Management Objects to administer the instances. The SQL Server cmdlets support operations such as executing Transact-SQL scripts or evaluating SQL Server policies.

SQL Server 2008 R2 Shared Management Objects The SQL Server Management Objects (SMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services. This object model will work with SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.
Note: Microsoft SQL Server 2008 R2 Management Objects Collection requires Microsoft Core XML Services (MSXML) 6.0, Microsoft SQL Server Native Client, and Microsoft SQL Server System CLR Types. These are available on this page.

SQL Server 2008 R2 ADOMD.NET ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 R2 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.

Analysis Services OLE DB Provider for SQL Server 2008 R2 The Analysis Services OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 R2 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.

SQL Server 2008 R2 Analysis Management Objects Analysis Management Objects (AMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer Analysis Services objects.

SQL Server Driver for PHP 1.1 The SQL Server Driver for PHP 1.1 is a PHP extension that allows for accessing data in all Editions of SQL Server 2005, SQL Server 2008,and SQL Server 2008 R2 (including Express Editions) from within PHP scripts. The driver provides a procedural interface for accessing data and makes use of PHP features, including PHP streams to read and write large objects. The SQL Server Driver for PHP relies on the Microsoft SQL Server Native Client to communicate with SQL Server.SQL Server Native Client can be downloaded on this Feature Pack page.

SQL Server Migration Assistant Microsoft SQL Server Migration Assistant (SSMA) is a family of tools that dramatically cut the effort, cost, and risk of migrating from Oracle, Sybase, MySQL or Access to any edition of SQL Server 2008 R2 or SQL Server 2008 or SQL Server 2005. SSMA for MySQL and SSMA for Access products also support simple and direct migration to SQL Azure. SSMA provides an assessment of migration efforts as well as automates schema and data migration.

SQL Server 2008 R2 Best Practices Analyzer SQL Server 2008 R2 Best Practices Analyzer is an analysis tool that validates your system configuration and execution against a recommended set of best practices developed by SQL Server Engineering and Customer Support.

Optimize Hardware ResourcesThis is a great new feature for database administrators as it will provide a real time insight into Server Utilization, Policy Violations etc. This feature will help organizations to strictly apply organization wide policies across servers thereby helping them maintain a healthy system.

Manage Efficiently at Scale
This feature will help database administrator to gain insight into growing applications and databases thereby helping them to ensure better management of database servers.

Enhance Collaboration Across Development and IT
Database Application development will be more closely integrated with Visual Studio 2010 which will help to ensure higher quality during the application development along with easier deployments and better handling of changes over time.

Build Robust Analytical Applications
Using Microsoft Office Excel 2010 you can build robust analytical applications which will allow in-memory, column oriented processing engine to allow users to interactively explore and perform complex calculations on millions of data at lightening speeds. Using Microsoft Excel 2010 you can easily integrate data from multiple sources such as corporate databases, spreadsheets and external data sources.

Support for Geospatial Visualization
Microsoft SQL Server 2008 R2 will provide support for geospatial visualization including mapping, routing, and custom shapes. It will also support SQL Spatial and will also provide integration with Microsoft Virtual Earth tiles.

Sql server 2008 R2 Edition wise Information :
Standard Edition: Now with Backup Compression
SQL Server 2008 introduced backup compression, but it was only available in Enterprise Edition. At the time, Enterprise Edition cost around $20,000 more per processor than Standard Edition, so companies couldn’t justify upgrading to Enterprise Edition just to get backup compression. Companies had to need Enterprise for multiple features in order to stomach the price. If all a DBA needed was compression, they could buy backup compression software much cheaper than the price of Enterprise Edition.
In SQL 2008 R2, even Standard Edition gets backup compression. That’s a game-changer, and I’d expect to see smaller companies that do backup compression – and nothing else – to start falling by the wayside.
In addition, Standard can now be a managed instance – it can be managed by some of the slick multi-server-management tools coming down the pike like the Utility Control Point (read my SQL 2008 R2 Utility review). It can’t be the management server itself – it can’t be a Utility Control Point – but at least we can manage Standard. It’s good to see that Microsoft recognizes all servers need to be managed, not just the expensive ones. Big thumbs up there.

Enterprise Edition: CPU LimitsIn Enterprise, Microsoft giveth and Microsoft taketh away. SQL 2008 R2′s BI tools include a new Master Data Services tool. It’s targeted at enterprises with data warehouses that need to manage incoming data from lots of different sources, and that data isn’t always clean or correct. MDS helps make sure data follows business rules. This isn’t a common need for OLTP systems, so it’s only included in Enterprise, not Standard. Makes sense.
A little less easy to stomach, however, is a new set of caps on Enterprise Edition. The current SQL 2008 comparison page shows that Enterprise has no licensing limit on memory or the number of CPU sockets. SQL 2008 R2 Enterprise Edition is capped at 8 CPU sockets, and there’s a memory cap as well, but I haven’t been able to track down a public page showing the cap. The only hint is the SQL 2008 R2 edition comparison page, which notes that Datacenter Edition (more on that in a second) is licensed for “memory limits up to OS maximum.” If that wasn’t a unique selling point, it shouldn’t be included in the feature list.
The more expensive Enterprise can act as the management server (Utility Control Point) for up to 25 instances. However, that doesn’t mean you need to buy one Enterprise per 24-25 Standard servers, and then manage them in pools – there’s an app edition for that.

Datacenter Edition: For, Well, Datacenters
The new Datacenter Edition picks up where Enterprise now runs out of gas. It supports more than 8 sockets, up to 256 cores, and all the memory you can afford. Or can’t afford, for that matter.
If you’re going to manage over 25 instances with the Utility Control Point stuff, Datacenter Edition can manage “more than 25 instances” according to Microsoft’s edition comparison page. I like how they worded that – they didn’t say “unlimited instances,” because there will be performance impacts associated with using Utility Control Points. The performance data collections gather a lot of data, and storing it for hundreds of instances will take some pretty high performance hardware.

Parallel Data Warehouse Edition: Sold with Hardware Only
The big new fella in town getting all the press is the artist formerly known as Project Madison, formerly known as DATAllegro. It’s a scale-out data warehouse appliance, but you won’t find this appliance at Home Depot. This version of SQL Server is sold in reference architecture hardware packages from Bull, Dell, HP, EMC, and IBM. Write one check, and you get a complete soup-to-nuts data warehouse storage engine that includes everything from the servers, SAN, configuration, and training.
I had the chance to talk with Microsoft’s Val Fontama, and I’ll post more details of that interview next week, but I have to share one quick snippet. I asked what happens when a Parallel Data Warehouse system starts to have performance issues, and he explained that the DBA will need to call in specialized Parallel engineers. You won’t be popping open this rack and installing another drawer of hard drives yourself or adding additional commodity hardware boxes to scale out your datacenter. It’s more of a sealed solution than something you have to build yourself.
I have mixed feelings about this – as a guy who loves hardware, I want to dive under the hood. However, as a guy who’s managed data warehouses, I know it’s one heck of an ugly skillset to learn on the job, and when data gets into the 5-10 terabyte range, you can’t afford to make configuration mistakes.

New Index features in Sql server 2005 & 2008

2010-12-27T05:56:00.000-08:00

1. Indexes in SQL Server 2005: Index features:

INCLUDE (column [,... n ] )

 Specifies the nonkey columns to be added to the leaf level of the nonclustered index.
 The maximum number of included nonkey columns is 1,023 columns; the minimum number is 1 column.
 Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and nonkey columns.
 All data types are allowed except text, ntext, and image.

Index arguments

ONLINE = {ON|OFF}
 Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation.
 The default is OFF.
 Online index operations are available only in SQL Server 2005 Enterprise Edition.

ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to proceed. At the start of the operation, a Shared (S) lock is held on the source object for a very short period of time. At the end of the operation, for a short period of time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

ALLOW_ROW_LOCKS = {ON | OFF}

 Specifies whether row locks are allowed.
 The default is ON.

ON

 Row locks are allowed when accessing the index.
 The Database Engine determines when row locks are used.

OFF
 Row locks are not used.

ALLOW_PAGE_LOCKS = {ON | OFF}

 Specifies whether page locks are allowed.
 The default is ON.

ON
 Page locks are allowed when accessing the index.
 The Database Engine determines when page locks are used.

OFF
 Page locks are not used.

MAXDOP = max_degree_of_parallelism

 Overrides the max degree of parallelism configuration option for the duration of the index operation.
 Use MAXDOP to limit the number of processors used in a parallel plan execution.
 The maximum is 64 processors.
 The below table shows the possible max_degree_of_parallelism and its description.

Max_degree_of_parallelism Description
1 Suppresses parallel plan generation.
>1 Restricts the max.no of processors used in a parallel index operation to the specified number
0(Default) Uses the actual number of processors

2. Indexes in SQL Server 2008: Index features:

WHERE

 Creates a filtered index by specifying which rows to include in the index.
 The filtered index must be a nonclustered index on a table.
 Filtered indexes do not apply to XML indexes and full-text indexes.
 Filtered indexes do not allow the IGNORE_DUP_KEY option.
 The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators.

FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | NULL}

 Specifies the placement of FILESTREAM data for the table when a clustered index is created.
 The FILESTREAM_ON clause allows FILESTREAM data to be moved to a different FILESTREAM filegroup or partition scheme.
 filestream_filegroup_name is the name of a FILESTREAM filegroup.
 The filegroup must have one file defined for the filegroup otherwise an error is raised.
 If the table is partitioned, the FILESTREAM_ON clause must be included and must specify a partition scheme of FILESTREAM filegroups that uses the same partition function and partition columns as the partition scheme for the table. Otherwise, an error is raised.
 If the table is not partitioned, the FILESTREAM column cannot be partitioned. FILESTREAM data for the table must be stored in a single filegroup that is specified in the FILESTREAM_ON clause.
 FILESTREAM_ON NULL can be specified in a CREATE INDEX statement if a clustered index is being created and the table does not contain a FILESTREAM column.

Index arguments

DATA_COMPRESSION
 Specifies the data compression option for the specified index, partition number, or range of partitions. The options are as follows:
NONE
 Index or specified partitions are not compressed.
ROW
 Index or specified partitions are compressed by using row compression.
PAGE
 Index or specified partitions are compressed by using page compression.

FIND LIST OF JOBS AND THEIR SCHEDULE WITH STEP WISE

2010-12-11T03:11:00.000-08:00

SELECT SJ.NAME, SJS.STEP_ID,SJS.STEP_NAME,SJS.COMMAND,
SJ.DESCRIPTION,
(SELECT NAME FROM MASTER.DBO.SYSLOGINS WHERE SID IN (SJ.OWNER_SID)) JOB_OWNER,
SJ.DATE_CREATED,SJ.DATE_MODIFIED, SJS.DATABASE_NAME, case when d.freq_type = 8 then 'Weekly' when d.freq_type = 4 then 'Daily' else 'None' end Schedule,
d.Freq_Subday_Interval Interval_in_Minutes,
case when len(d.active_start_time) = 5 then convert(varchar,left(d.active_start_time,1)) + ':' + convert(varchar,left(right(d.active_start_time,4),2)) + ':' + convert(varchar,right (d.active_start_time,2))
when len(d.active_start_time) = 4 then '00' + ':'+convert(varchar,left(d.active_start_time,2)) + ':'+convert(varchar,right(d.active_start_time,2))
when len(d.active_start_time) = 3 then '00:0' + convert(varchar,left(d.active_start_time,1)) + ':'+convert(varchar,right(d.active_start_time,2))
when len(d.active_start_time) = 2 then '00:00:' + convert(varchar,left(d.active_start_time,2))
when len(d.active_start_time) = 1 then '00:00:0' + convert(varchar,left(d.active_start_time,1))
else
convert(varchar,left(d.active_start_time,2)) + ':' + convert(varchar,left(right(d.active_start_time,4),2)) + ':' + convert(varchar,right (d.active_start_time,2)) End [Scheduled_Time]
FROM SYSJOBS SJ INNER JOIN SYSJOBSTEPS SJS
ON SJ.JOB_ID = SJS.JOB_ID
LEFT OUTER JOIN SYSJOBSCHEDULES SJSC ON SJ.JOB_ID = SJSC.JOB_ID
LEFT OUTER JOIN SYSSCHEDULES d ON SJSC.SCHEDULE_ID = d.SCHEDULE_ID
ORDER BY NAME

Sql Monitoring - Job Failure information

2010-10-14T08:37:00.000-07:00

A common requiremnet in DBA Environment to findout what are the jobs got failure in the servers. The jobs use to perform for various activities like Mainteance, Performance Related, Projects related, Backups/restore, making Disaster recovery techniques etc. If the job got failure and no solution has been provided for that, there could be some problem in regular activities.

Following query helps to findout what are the jobs got failure and by getting the information solution can be provided. If required this can be created as procedure in MSDB to simplify the task.

----------------------------------------------------------------------------------
Create Proc Pr_MonitorFailure as
Begin
select b.name Job,
case when len(a.run_time) = 5 then convert(varchar,left(a.run_time,1)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2))
when len(a.run_time) = 4 then '00' + ':'+convert(varchar,left(a.run_time,2)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 3 then '00:0' + convert(varchar,left(a.run_time,1)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 2 then '00:00:' + convert(varchar,left(a.run_time,2))
when len(a.run_time) = 1 then '00:00:0' + convert(varchar,left(a.run_time,1))
else
convert(varchar,left(a.run_time,2)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2)) End [Last Run Datetime],
--run_duration,
ISNULL(SUBSTRING(CONVERT(varchar(7),run_duration+1000000),2,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),4,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),6,2),'') AS [Last Run Duration], Message,
case when run_status = 0 then 'Fail' else 'Other Reason' end run_status from
Sysjobhistory a inner join Sysjobs b on a.job_id = b.job_id
where run_status <> 1 and message not like '%The Job was invoked%'
End
----------------------------------------------------------------------------------

Basic information about Joins in Sql server

2010-09-14T05:17:00.000-07:00

Joins are really important for writing queries in any database language. If you take SQL Server there are few joins available which can help to construct sql statemetns. The Joins are combination of keywords Intersect, Union, Union all which we have learnt during our X standards. Join can be matched to 2 or more tables (usually), you can match single table also using join i.e., called Self join.

In Sql server we have mainly
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join
Self Join

For best understanding create following tables and insert the data in sample database. So that you can understand very easily how the join condition results.

----------------------------
Create table Table1 (id int, name varchar(10))
Create table Table2 (id int, Name varchar(10))

insert into table1 (id, name)
values (1,'A')
insert into table1 (id, name)
values (2,'AB')
insert into table1 (id, name)
values (3,'ABC')

insert into table2 (id, name)
values (1,'A')
insert into table2 (id, name)
values (2,'AB')

----------------------------
JOINS :
Inner join : A Intersect B; it means the data results which are available in both the tables.
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A inner join Table2 B on a.id = b.id

Left outer Join : A union B : Whatever the data available in A table and the matching data from B Table
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Left outer join Table2 B on a.id = b.id

Right outer Join : A union B : Whatever the data available in B table and the matching data from A Table
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Right outer join Table2 B on a.id = b.id

Full outer Join : All the data from A and B tables
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Full outer join Table2 B on a.id = b.id

Cross Join : A X B ; for each row in a multiply into all rows in B. Like wise for all the rows.
SELECT a.id, a.Name,b.id, b.Name from
Table1 A cross join Table2 B

Self Join : By using self join we can match the same table for different columns.
SELECT a.id, a.Name,b.id, b.Name from
Table1 A join Table1 B on a.id = b.id

Reindex all tables which are highly fragmented in the database

2010-07-12T07:41:00.000-07:00

A common database maintenance activity, we need to Reindex database tables to maintain less fragmentation. For performing this we can identify each and every object which has fragmentation reached our expectation level; instead I have designed 2 procedures to findout the fragmented tables and perform the reindex on all indexes available in identified tables.

Hope this will reduce your maintenance time of writing queries for reindexing.
You can choose MSDB to create this proc and change the content to refer actual databases

-- Identifying Fragmentation of Tables (1)

Create Proc Pr_View_Defragmentation (@database sysname, @Frag int)
as
Declare @dbid int
select @dbid = dbid from sys.sysdatabases where name = @database
select distinct xtype, name,object_name(object_id),* from sys.dm_db_index_physical_stats (@dbid,NULL,NULL,NULL,'SAMPLED')
a inner join sys.sysobjects b on a.object_id = id where xtype = 'u' and name not like '%sys%'
and avg_fragmentation_in_percent >= @frag

-- Perform Reindexing to decrease the Fragmentation (2)

Create Proc Pr_Alter_index (@database sysname, @frag int) as
--DECLARE @DATABASE SYSNAME
--DECLARE @FRAG INT
Declare @a int
Declare @b int
Declare @sql varchar(1000)
Declare @Table table(id int identity(1,1),Object varchar(100))
Declare @sql1 varchar(100)
--set @database = 'DATABASE'
--set @frag = 10
SEt @a = 1
select @B = count(DISTINCT object_id) from sys.dm_db_index_physical_stats (db_id(@database),NULL,NULL,NULL,'SAMPLED')
a inner join sys.sysobjects b on a.object_id = id where xtype = 'u' and name not like '%sys%'
and avg_fragmentation_in_percent >= @frag
insert into @table(object)
Select distinct object_name(object_id) from sys.dm_db_index_physical_stats (db_id(@database),NULL,NULL,NULL,'SAMPLED')
a inner join sys.sysobjects b on a.object_id = id where xtype = 'u' and name not like '%sys%'
and avg_fragmentation_in_percent >= @frag
while @a < @b
Begin
select @SQL1 = OBJECT FROM @TABLE where id = @a SET @SQL = 'ALTER INDEX ALL ON ' + @database + '..' + @sql1 + ' REBUILD WITH (ONLINE = ON)'
exec (@sql)
set @a = @a+1
End

exec pr_View_Defragmentation database, fragmentation percentage
exec Pr_Alter_index database,fragmentation percentage

Eg : exec pr_View_Defragmentation 'abcd', 70

Maintain all jobs with a default owner account in Sql server

2010-06-10T03:21:00.000-07:00

This is common requirement in DBA environment, like all created jobs in the server should run with specified username. Normally in production environment user will not perform the job manually and if it is on schedule also it should run with some service account. When creating a job it would be created with the default user who creates the job and there may be chances also to forget changing the job owner.

This query will helps to perform all jobs should be under default owner account.

declare @username varchar(100)
set @username = 'sa'
update sysjobs set owner_sid =
(select sid from master.dbo.syslogins where name = @username)
where name in (job_a, job_b,...... job_n)

* you can change the parameters as per your requirement

Findout Database Restoration Details

2010-06-07T03:24:00.000-07:00

I got confusion that have I restored my database using latest back or yet to be restored ? B'cos I have no. of databases in different environments and requires to be restored with the latest backup arrived from client dbs.

To findout these details in the database like when it was restored either full database (or) only filegroup or part of some files restored, It can be achieved in following way :
USE MSDB
GO

select BS.user_name,
destination_database_name Database_name,
restore_date Date,
BS.database_name Actual_Database,
BS.server_name,
BS.name,
physical_name,
backup_start_date,
BF.backup_size
from RestoreHistory RH inner join BackupSet BS on RH.backup_set_id = BS.backup_set_id
inner join BackupFile BF on BF.backup_set_id = BS.backup_set_id
order by RH.Restore_Date

* Database_name and Actual_Database both are same but Actual_Database refers the database which you have restored. If the database is renamed after restoration also it shows the actual name when it was restored.

How PERFMON (Windows tool) helps DBA to identify the Performance Issues and take necessary solutions

2010-05-25T03:58:00.000-07:00

In SQL Server DBA Environment, there are several ways to improve the performance of SQL Server Database Applications such as Query Execution Plans, Sql Profiler, DTA (Database Tuning Advisor), Server Level properties, Object level improvements (Indexes, statistics, other maintenance stuff) and Windows Level Applications. I mean the database performance can be identified in 3 levels i.e., Server Level (Operating Systems, Networking Protocols); Database Level (Sql Server Databsae Engine, SQL Server) ; Object Level (Objects within the database).

So if we are facing the performance degrade then we need to check up at all levels for taking necessary actions. Currently I will discuss about the Server Level Tool PERFMON which is very useful to identify the sever levels activities, based on the identified results we can take appropriate action to improve the performance. It can be Hardware, Memory (or) System changes.

PERFMON : PERFMON is a windows inbuilt tool which can provide the workload of the resources running in the system. It can be used to find out Windows resources data as well as SQL Server resources.

Main Benefits Of The Tool :
• Understand your workload and its effect on your system's resources.
• Observe changes and trends in workloads and resource usage so you can plan for future upgrades.
• Test configuration changes or other tuning efforts by monitoring the results.

System Monitor and Performance Logs and Alerts provide detailed data about the resources used by specific components of the operating system and by programs that have been designed to collect performance data.

Choosing the data to monitor :
Start by monitoring the activity of the following components in order:
• Memory
• Processors
• Disks
• Network

Following counters can be helpful to trace the data

1:
Component : Disk
Performance aspect being monitored : Usage
Counters to monitor :
Physical Disk\Disk Reads/sec, Physical Disk\Disk Writes/sec, LogicalDisk\% Free Space, Interpret the % Disk Time counter carefully. Because the _Total instance of this counter may not accurately reflect utilization on multiple-disk systems, it is important to use the % Idle Time counter as well. Note that these counters cannot display a value exceeding 100%.

2 :
Component : Disk
Performance aspect being monitored : Hindrances
Counters to Monitor : Physical Disk\Avg. Disk Queue Length (all instances)

3:
Component : Memory
Performance aspect being monitored : Usage
Counters to Monitor : Memory\Available Bytes, Memory\Cache Bytes

4:
Component : Memory
Performance aspect being monitored : Hindrances
Counters to Monitor : Memory\Pages/sec, Memory\Page Reads/sec, Memory\Transition Faults/sec, Memory\Pool Paged Bytes, Memory\Pool Nonpaged Bytes.
Although not specifically Memory object counters, the following are also useful for memory analysis: Paging File\% Usage object (all instances), Cache\Data Map Hits %, Server\Pool Paged Bytes and Server\Pool Nonpaged Bytes

5:
Component : Network
Performance aspect being monitored : Throughput
Counters to Monitor : Protocol transmission counters (varies with networking protocol); for TCP/IP: Network Interface\Bytes total/sec, Network Interface\ Packets/sec, Server\Bytes Total/sec, or Server\Bytes Transmitted/sec and Server\Bytes Received/sec

6:
Component : Processor
Performance aspect being monitored : Usage
Counters to Monitor : Processor\% Processor Time (all instances)

7:
Component : Processor
Performance aspect being monitored : Hindrances
Counters to Monitor : System\Processor Queue Length (all instances),
Processor\ Interrupts/sec, System\Context switches/sec

How to Create and perform :
1. Go to RUN and type PERFMON then Enter
2. Double-click Performance Logs and Alerts, and then double-click Counter Logs. Any existing logs will be listed in the details pane. A green icon indicates that a log is running; a red icon indicates that a log has been stopped.
3. Right-click a blank area of the details pane, and click New Log Settings.
4. In Name, type the name of the log, and then click OK.
5. On the General tab, click Add Objects and select the performance objects you want to add, or click Add Counters to select the individual counters you want to log.

Configure the other setings as you required. It can be run for certain time period i.e, 10 hours, 1 day, 1 week to identify how the processes are running in the system. The data can be saved as .CSV or text files. When you get the data you can make a charts using Excel and it can be understandable what necessary action to be taken for improving performance.

Understand how shrinking a LOG file works :

2010-05-20T04:08:00.000-07:00

Understand how shrinking a LOG file works :

Many of them faced while shrinking a log file in the database, after applying the DBCC
Shrinkfile option also the files size not reduced. What was the structure behind, how it works.

For an example you have a database consists of data and log files, which log file size is growing abnormally exceeding hard drive size. So you need to reduce the file to accommodate within the available space. So maximum people would perform following options to decrease the file size.

1) Either truncating log file directly then shrink
Backup log 'DATABASE' with truncate_only
DBCC shrinkdatabase ('database',10) (or)
DBCC shrinkfile ('logfile')

2) or Applying the shrinkdatabase option directly on the database.

3) or Keeping Full / Log backup jobs in the server with some time intervals and shrinking the database.

Whether these above options are sufficient to perform the maintenance of Log file in the server. No, these can be manageable upto some extent only, if you can follow the above activities with slight changes you can expect 100% results from it.

What are the drawbacks in above processes :

1) If you truncate directly your log file then shrink the database, you can have reduced log file, but if you need to restore your database using the log backup.......... no backup available

2) Applying shrink option directly doesn't give you much impact

3) Keeping Log backup and perform shrink option will give you the good result but it should happend sequentially.

The Process behind log backup and shrink :
1) I have database consists of log file size 100Mb and Used space in the log file is 94 MB.
This can be find out using DBCC sqlperf(logspace).
2) I have performed DBCC shrinkfile ('log file') for size decrease ; but it increased instead of decrease. Why bcos the log file having the logs and for shrinking it written on the top of it, it increased. Use DBCC sqlperf(logspace) to check the details.
3) Now I have performed backup log file then performed the shrink option. It reduced to the initial size and my drive is free now.

Additional options to findout :

A) Run following query
DBCC loginfo
If the status in the below table is 2 then it is waiting for the backup
else it is 0 then it can be shrinked

result :
Field FileSize Startoffset FseqNo STatus Parity CreateLSN
2 2555904 8192 98682 2 64 0
2 2555904 2564096 98683 2 128 0
2 2555904 5120000 98685 2 64 0
2 2809856 7675904 98684 2 128 0
2 253952 10485760 98686 2 64 98685000000407400016
2 253952 10739712 98687 2 64 98685000000407400016
2 253952 10993664 98688 2 64 98685000000407400016

b) Run following query whether the backup is pending or not
select log_reuse_wait_desc from sys.databases where name = 'database'

if the result is 'LOG_BACKUP' then waiting for backup
if the result if 'NOTHING' then it can be shrinked

Note : In Sql Server 2008 there is no truncating log backup option, instead you can alter the recovery mode into simple then shrink. Again change the mode into normal.

SQL Server 2008 Certification Details

2010-04-29T05:16:00.000-07:00

Useful information about Sql Server 2008 Certification Exams : (Click on the image to read)

Log file Maintenance in SQL SERVER 2000/2005 & 2008 :

2010-04-13T02:25:00.000-07:00

In earlier version of SQL Server if the database log is full then by truncating the Log file, we can free up the database log file space. For an example you have allocated 5 GB for log file and it crosses the limit after certain period. As a result it doesn't allows any database transactions and receives continuous errors. In SQL server 2005 and 2000 we can free up the space by using following commands.

USE DatabaseName
* Database will be your user database.

BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
* it will truncate all the contents from log file

DBCC SHRINKFILE (Log File)
* It will shrink the file into its intial size.

But the above process doesn't work in SQL server 2008. These features are discontinued from the latest version and for achieving this requirement you need to work on following method.

First Alter the database from Full recovery mode to Simple recovery mode, then Shrink the database and again change the mode into full.

ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
* This will change the database mode into simple

DBCC SHRINKFILE (Log File)
* It will shrink the file into its intial size.

ALTER DATABASE DatabaseName SET RECOVERY FULL
* This will change the database mode into full

The above process can be done using SQL Server Management studio also.

To check up the database and its file details you can use system procs sp_helpdb, sp_helpdb databasename.

Partition a Table for better performance in Sql Server 2005 / 2008

2010-03-18T03:47:00.000-07:00

In earlier versions we didn't have partitioning a table option to distribute the data across multiple file groups. This was introduced with Sql server 2005 including other valuable features. Partitioning can be performed with help of Partition Function, Partition Scheme.

Partition is nothing like distributing the data across multiple file groups, as we all know if we share the data into multiple drives automatically performance of the database will be increased. Let assume we have a big table comprises million of records, when fetching the data it took too long time. So, for increasing the performance of the table we can implement Partitioning concept and achieve the better performance.

HOW TO PARTITION A TABLE :
Requirement : A table is to be defined with multiple columns, out of which we need to fetch the data based on column "Name". If you select data based on Name there would be many records with different names comprising letters from A-Z. So by using following method, the data can be distributed into different file groups.

Step 1 : As a first step you need to create a Partition function :
CREATE PARTITION FUNCTION NameFunc (nvarchar(30))
AS RANGE RIGHT FOR VALUES ('H', 'N')

Step 2 : Need to create partition scheme based on the partition function :

CREATE PARTITION SCHEME NameScheme
AS PARTITION NameFunc TO (fg1, fg2, fg3)

Step 3 : Create a table based on Function and Scheme

CREATE TABLE dbo.Employees
(EmpID int, Name nvarchar(30))
ON NameScheme (Name)

Explanation :
In the first step 1, 'H', 'N' values defines
A-H first group,
H-N second group,
N-Z Third group

the above 3 groups will be stored on fg1, fg2 and fg3 file groups respectively.

Online Indexing in Sql Server 2005

2010-01-06T02:13:00.000-08:00

In SQL Server 2005, the index can be created while Insert/update/delete happening on a table. Without affecting performance degradance to table the index will be created. This concept is called Online indexing in Sql server.

I have taken following sample queries to test online indexing :

Step 1: Create sampe tables to hold the datacreate table Index_Check (id int identity(1,1), name varchar(20),join_date datetime, Amount float)
Create table names (name varchar(50))
insert into names values ('Software company,Bangalore,India')

Step 2 : Executing cursor to insert the data continuously
declare venkat cursor local
for
Select NAME FROM NAMES
open venkat
Declare @aaa VARCHAR(20)
fetch next from venkat into @aaa
while @@fetch_status =0
exec ('insert into Index_Check (name,join_date) select '''+@aaa+''', GETDATE()')
fetch next from venkat into @aaa
deallocate venkat
close venkat

Step 3 : Connect the same server and same table from other connection, try to create cluster / nonclustered online index

Create clustered index IDX_Check on Index_Check(id) with (online=on)
Create nonclustered index IDX_Check1 on Index_Check(id) with (online=on)

Successfully index creates without disturbing the table process.

You can schedule rebuilding index depends on the data usage in the database. While performing rebuild it can be configured to perform online/offline. Online means it doens't impact anything to the table operation and does. But it has some restrictions also like or text data type columns we can't perform online reindexing and etc.

RAID : Understanding of RAID and its functionality

2010-01-04T02:57:00.000-08:00

Understanding of RAID......Redundant Array of Indepenent Disks (RAID)
RAID is a combination of multilple disks to improve the performance and safety of the data. This can be configured using SCSI contollers. SCSI is nothing but a card which can have facility of connect multiple disks. Once configured the RAID, the data is available on ARRAY comprising the available disks. There are mainly 3 terms need to remember while working with RAID levels. Mirroring, Stripped disks and fault tolerance.

There are different RAID levels for various requirements. We can implement the actual RAID level to achieve 100% success.

RAID 0 : It distributes the data across multiple disks. The data retrieving can be faster as the data is dividing into multilpe disks. But this levels doesn't have any data safety, means if one disk fails you will loose the data automatically.

RAID 1 : Mirror the data from one disk to another. Here we have more safety, if one disk fails automatically another disks works which already has mirrored data.

RAID 3 (OR) 4 : This level requires minimum 3 disks including 2 for data distribution and 1 for fault tolerance. Here we have data safety mechanism with full performance including redundant data identification. For this level 1 dedicated disk is required to store redundant data.

RAID 5 : This level also requires 3 or more disks to protect the data in case any one disk fails. Here we dont required dedicated disk for storing redundant data, this will be stored in the combination of all disks. This level has higher data safety allows only one disks to fail. If more than one disks fails then the data will not be available on array.

RAID 6 : Similar to RAID 5, but it requires minimum 4 disks and allows to fail 2 disks. Means 2 disks can fail in this raid level, if more than 2 disks fails than the data wouldn't be available.

There are 2 more new levels introduced i.e....,

RAID 1+0 : Combination of RAID 1 + RAID 0, whatever the features having both levels can incorporate here. But in the sequence like first it mirror the data (safety) then distribute the data across drives (performance).

RAID 0+1 : Combination of RAID 0 + RAID 1, whatever the features having both levels can incorporate here. But in the sequence like first it distribute the data across drives (performance) then mirror the data (safety).

When consider to SQL Server installation mostly we can use RAID 5 for backups of the databases where it requires only 3 disks (less hardware) and high safety with high performance. If the data is too much important and your company can able to spend money like data worth's than any thing, you can suggest RAID 6 level where it comprises 4 disks with dual disk safety.

RAID 1+0 is good for installing Tempdb database where TEMPDB database requires safety of data with good performance.

Some useful Interview questions

2009-11-27T02:13:00.000-08:00

What is materialized view?We can name it materialized view in different ways like Schema Binding view, Structure binding view and materilizaed view. The main use of the view is it acts as a table like hold the schema. For a example normal view is a select query written by combining multiple tables. If we want to improve the performance there is only a way that we can alter the statements in better way. But if consider materialized view, one can create indexes apart from altering a query for better performance. As you understood indexes will give more performance compares with any other option available in sql server.

What is the diff between inner join and union?Inner join retrieves the matched data from 2 or more tables where as union will retrieve distinct data from 2 or more tables. Join is like horizontal result, Union is like vertical result.

What is the diff between master database and resource database? If resource database was corrupted sql server will work or not? How you can repair that?
Master and Resource both are system dbs in sql server. But master is the primary database which will contain all security structures, objects structures and other user database dependent information where as resource will have copy of the users database system tables which helps at the time of upgradation or degradation. The resource purpose is intended in Sql server 2005 is only for Upgradation or degradation of the versions or service packs. If resource is not available, then also Sql server will not work as some dependent services are working based on resource db structured in Sql Server 2005. Eg : Alert Service, Performance related services.

If i need to give permission per column in table how you can give and tell me process?--
Step 1:
Create table NB_Perm (sl int, name varchar(10))

--STep 2 :
Create role NB_role

--Step 3 :
grant select (sl) on nb_perm to NB_role
deny select(sl) on nb_perm to NB_Role

Make your users member of this role.

What is use of built/administrator in sql server?It is a Windows Group defaultly created with installation of SQL Server. The role defaultly assigned is Sysadmin. If we want this group to be there in Sql server we can keep it and create the users under this group. For creating users refer Administrator Tools – Computer Management - Users and Groups.

What is the diff between having clause and where clause?‘Having’ clause can be used when the query consists aggregate functions like count, avg …..
Eg : Select id,count(id) from table having count(id)>5
‘Where’ clause can be used to filter the data in any statement irrespective of conditions.

How you can know who was last using the query?In earlier versions we can find by implementing the trigger. But in Sql server 2008 new concept auditing is available. With help of this we can find out.

What is the diff between physical file and logical file and which one you refer at the time shrink the file?Logical and Physical both are database files where logical file available inside the database structure and physical file available at the destination drive. Logical and physical both are important at the time of backup, restore or any kind of database operation. As you requested I refer the logical file only at the time of shrinking or restoring the db.

What is covering Index and can you give me one example?Covering index is an index which helps to reduce the index size in a database. Like a index will improve the performance and as a disadvantage it occupies some space in your db. Suppose if you want to create index by using more columns then you can refer convering index as best solution. Addition to decreasing the table space it gives good performance also as running the indexes only it requires on the columns. The suggestions from my side is create all int kind of data columns in index list and varchar kind of column in include list.
Eg : Create (cluster/nonclustered) index IndexName on table (main columns) include (other columns)

What is live lock?A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely

Diff between live lock and dead lockDeadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A Live lock also occurs when read transactions monopolize a table or page, forcing a write
transaction to wait indefinitely

Lock escalationLock escalation is the process of escalating a significant number of row level locks to a single table lock. This is trick performed by SQL Server as a way of conserving memory and improving performance. The problem is it works well for smaller systems but can easily throttle performance on larger systems when the activity reaches a certain threshold.

How can rebuild master dbProcess :
1) Shutdown the SQL server and its services.
2) Goto Program Files\Microsoft SQL Server\80\Tools\Binn directory.
3) Open Rebuildm.exe
4) In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
5) Change collation settings if required or leave it default.
6) In the Rebuild Master dialog box, click Rebuild to start the process.
DTS to SSIS migration
We cannot migrate DTS packages from 2000 to 2005 (or) 2008. If it is simple package then we can use it as it is. If it consists vbcode and all it will not convert instead we need to rewritten the package using SSIS. Because SSIS uses .net code.

Types of replication agentsReplication Snapshot Agent:
Replication Log Reader Agent:
Replication Distribution Agent:
Replication Merge Agent:
Replication Queue Reader Agent:

Public/private networkPublic vs. private networks
Computers that are connected to each other create a network. These networks are often configured with "public" Internet Protocol (IP) addresses -- that is, the devices on the network are "visible" to devices outside the network (from the Internet or another network). Networks can also be configured as "private" -- meaning that devices outside the network cannot "see" or communicate directly to them.
Computers on a public network have the advantage (and disadvantage) that they are completely visible to the Internet. As such, they have no boundaries between themselves and the rest of the Internet community. This advantage oftentimes becomes a distinct disadvantage since this visibility can lead to a computer vulnerability exploit -- a.k.a., a "hack" -- if the devices on the public network are not properly secured.

The difference between Master and Resource databases

2009-11-16T05:02:00.001-08:00

The difference between Master and Resource database

Mster db is the mother database for all other databases like it consits of other database information, configurations, file locations, security context and etc. whereas Resource db is a database addition to master consists of database schema and stored procedures which requires to run databases in current instance. Resource db mainly introduced for Upgradation or rollback your installations in Sql server 2005.

Some differences:

Master :
1. Master will have data specific to your instance.

2. The database can be found in your sqlserver installation data folder with the name of master.mdf.

3. For any maintenance activity you need to make the db in single user mode.

Resource :
1. Resource will have schema and stored procedures to run your instance.

2. It is introduced in Sql server 2005 for upgradation purpose. In sql server 2000 if you want to upgrade to a new service pack, you would need to run many log scripts that drop and create system scripts. It is too long process and much time consume. In Sql server 2005 if you install service pack or quick fix a copy of resource database will overrite the old database. With help of this resource db you can easily upgrade or rollback your service packs.

3. The database can be found in your sqlserver installation data folder with the name of mssqlsystemresource.mdf.

4. It is a hidden database, you can't findout it in sysdatabases table. By using serverproperty we can view the information about the resource db.

Query to findout JOBs information for a period of time

2009-09-16T07:34:00.000-07:00

I hope this requirement is very common in DBA work life, as DBA needs to findout how the JOBs are performing currently and for a long time. I too faced the requirement. I have searched most of the pages from google and I couldn't found the solution. Finally I written the following query spending almost 3 hours.

Hopefully it will useful for all SQL Server DBAs.

Syntax :
Select b.Name JobName, a.step_name,a.run_date,run_time,
case when len(a.run_time) = 5 then convert(varchar,left(a.run_time,1)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2))
when len(a.run_time) = 4 then '00' + ':'+convert(varchar,left(a.run_time,2)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 3 then '00:0' + convert(varchar,left(a.run_time,1)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 2 then '00:00:' + convert(varchar,left(a.run_time,2))
when len(a.run_time) = 1 then '00:00:0' + convert(varchar,left(a.run_time,1))
else
convert(varchar,left(a.run_time,2)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2)) End run_time,
run_duration,
ISNULL(SUBSTRING(CONVERT(varchar(7),run_duration+1000000),2,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),4,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),6,2),'') AS [Duration], a.message
from sysjobs b inner join sysjobhistory a on a.job_id = b.job_id
where b.name like '%Reindex%' AND step_name not like 'Notification'
and step_name not like '%Job Outcome%' order by b.Name

For the above query you can add filter conditions as per your requirements. By getting the result of above query you can have an idea of how the jobs are involved and making the analysis you can increase the performance of the server.

Query to findout list of Objects count from all databases in SQL Server :

2009-09-10T04:59:00.000-07:00

This is very useful query on DBA daily work environment. I have struggled to findout the information before. Finally I developed following query to get the information.

Select 'select ('''+name+''') Database_name,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''u'') Table_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''v'') View_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype in (''fn'', ''tn'')) func_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''p'') proc_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''tr'') Trig_Count union all'
from sysdatabases where dbid > 4

Instructions :

* you need to take the result script of above query and execute in other window. And you need to remove 'UNION ALL" from end statement.
* dbid > 4 = it will not show for sysdatabases

Result Query :

select ('TEST') Database_name, (Select count(*) from TEST.dbo.sysobjects where xtype = 'u') Table_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'v') View_Count, (Select count(*) from TEST.dbo.sysobjects where xtype in ('fn', 'tn')) func_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'p') proc_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'tr') Trig_Count

Statistics in Synchronization and Asynchronization mode to gain the performance.

2009-09-10T04:37:00.000-07:00

In SQL Server we have Statistics to improve the performance on tables. Following are some of the concepts about Statistics in Synchronization/Asynchronization mode to gain the performance from tables.

1. Statistics Asynchronization mode was introduced in SQL Server 2005. In earlier versions only synchronizations mode was available. But the default level is synchronization only.

2. Synchronization /Asynchronization options are at database level, not individual object level. There is single option we can enable or disable this for entire database. We can do this by using alter database statement.
Syntax : ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC OFF

3. To checkup this option on existing databases.
Syntax : Select name,is_auto_update_stats_async_on from sys.databases

1 = enabled
0 = disabled

When the setting is off and a statistics update is initiated due to out-of-date statistics in the execution plan, the query must wait until the statistics update is complete before compiling and then returning the result set. When the setting is on, the query does not need to wait as the statistics update are handled by a background process. Mainly it is using to improve the performance on the tables.

So, prior to utilize the statistics aynchronous option at database level, the following process should do at object level.
1. Create statistics on indexed columns. It is suggestable that statistics must create on composite indexed columns.

2. All the created statistics must be updated regularly. When we update the statistics on regular basis, we can gain the performance from tables.

3. This options will work on tables which are having statistics and updating regularly will improve the performance compare to synchronization mode.

Class 8 - Functions in SQL Server

2009-08-06T06:01:00.000-07:00

FUNCTIONS
What is function ?
A function in SQL Server can be defined primary as 2 types. User Defined Functions which is creating by user based on the requirement and System Defined Functions which was already created by Microsoft and provided for ready use.

These can be classified in other ways also :

The Transact-SQL programming language provides three types of functions:
• Rowset functions
Can be used like table references in an SQL statement.
• Aggregate functions
Operate on a collection of values but return a single, summarizing value.
• Scalar functions
Operate on a single value and then return a single value.

Deterministic and Nondeterministic Functions
All functions are deterministic or nondeterministic:
• Deterministic functions always return the same result any time they are called with a specific set of input values.
• Nondeterministic functions may return different results each time they are called with a specific set of input values.

USER DEFINED FUNCTIONS : When we come to User Defined Function again there are 3 types.
1) Scalar Function
2) Table Valued Function
3) Multi Statement Function

Scalar Functions
Scalare functions always returns single resultant value.
Syntax :
CREATE FUNCTION [ owner. ] fn_name ( [ { @parameter [ AS ] data_type }[ ,...n ] ] )
RETURNS data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
Eg :
create function fn_studage (@id int) returns int
as
begin
declare @studage int
select @studage = datediff(year,dob,getdate()) from students where id = @id
return @studage
end

Inline Table-Valued Functions
Inline Table-Valued Functions return a resultset, as opposed to a single scalar value. A table valued function specifies the TABLE keyword in its RETURN clause. Its basic form is :
Syntax :
CREATE FUNCTION [ owner_name. ] fn_name ( [ { @parameter [ AS ] type } [ ,...n ] ])
RETURNS TABLE
[ AS ]
RETURN [ ( ] select-statement [ ) ]

An Inline Table-Valued Function created by this command:
CREATE FUNCTION datesales (@deadline as datetime)
RETURNS TABLE
AS
RETURN ( SELECT *
FROM sales
WHERE ord_date > @deadline)

and called by this sequence:
USE PUBS
GO
select * from datesales('09/13/1994')
will yield the following table:
stor_id ord_num ord_date qty payterms title_id
6380 6871 09/14/94 5 Net 60 BU1032
7067 D4482 09/14/94 10 Net 60 PS2091
7131 N914008 09/14/94 20 Net 30 PS2091
7131 N914014 09/14/94 25 Net 30 MC3021
8042 423LL922 09/14/94 15 ON invoice MC3021
8042 423LL930 09/14/94 10 ON invoice BU1032

Multi-statement Table-Valued Function
The final type of UDF is the Multi-statement Table-Valued Function. This UDS also returns a resultset, like the Inline variety UDF, but with a much more powerful result. The Multi-statement UFD can actually create a temporary table, specifying the fields, their type and characteristics. Now the scope of that temporary table is limited to the UDF in which it was defined, so when the UDF ends, the temporary table evaporates too. However, this can be a tremendously powerful technique to obtain or manipulate data.

The general form for the Multi-statement Table-Valued Function is:
CREATE FUNCTION [ owner_name. ] fn_name ( [ { @parameter [AS] type } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ AS ]
BEGIN
function_body
RETURN
END
The following creates the datesales2 UDF:
CREATE FUNCTION datesales2 (@deadline datetime) RETURNS @table TABLE (stor_id varchar(6) null, ord_num varchar(8) null, ord_date datetime null, qty int, payterms varchar(20), title_id varchar(6))
AS
BEGIN
INSERT @table
SELECT *
FROM sales
WHERE ord_date > @deadline
RETURN
END
When the function is called by this sequence,
USE PUBS
GO
select * from datesales2('09/13/1994')
The following resultset is displayed and available. (It is the same set displayed in the Inline UDF, but created with the temporary table.)
stor_id ord_num ord_date qty payterms title_id
6380 6871 09/14/94 5 Net 60 BU1032
7067 D4482 09/14/94 10 Net 60 PS2091
7131 N914008 09/14/94 20 Net 30 PS2091
7131 N914014 09/14/94 25 Net 30 MC3021
8042 423LL922 09/14/94 15 ON invoice MC3021
8042 423LL930 09/14/94 10 ON invoice BU1032
Now these temporary tables by me combined, joined, with virtually any number created within the Multi-Tabled UDF, giving the user a very powerful tool for calculating and presenting data from the server in a way that truly leverages the effectiveness of writing code in the native language of SQL Server.

SYSTEM DEFINED FUNCTIONS :

FUNCTION EXPLANATION :

Function category Explanation
Configuration Functions : Returns information about the current configuration.
Cursor Functions : Returns information about cursors.
Date and Time Functions : Performs an operation on a date and time input value and returns either a string, numeric, or date and time value.
Mathematical Functions : Performs a calculation based on input values provided as parameters to the function, and returns a numeric value.
Metadata Functions : Returns information about the database and database objects.
Security Functions : Returns information about users and roles.
String Functions : Performs an operation on a string (char or varchar) input value and returns a string or numeric value.
System Functions : Performs operations and returns information about values, objects, and settings in Microsoft® SQL Server™.
System Statistical Functions : Returns statistical information about the system.
Text and Image Functions : Performs an operation on a text or image input values or column, and returns information about the value.

Configuration Functions :
@@REMSERVERReturns the name of the remote Microsoft® SQL Server™ database server as it appears in the login record.
Syntax
@@REMSERVER
Example :
CREATE PROCEDURE check_server
AS
SELECT @@REMSERVER

@@LANGUAGE
@@SERVERNAME

@@SERVICENAME
Example
SELECT @@SERVICENAME
Here is the result set:
------------------------------
MSSQLServer

@@SPID
@@MAX_CONNECTIONS
The actual number of user connections allowed also depends on the version of SQL Server installed and the limitations of your application(s) and hardware.
To reconfigure SQL Server for fewer connections, use sp_configure.
Examples
SELECT @@MAX_CONNECTIONS

@@VERSION

@@NESTLEVEL
Returns the nesting level of the current stored procedure execution (initially 0).

@@DATEFIRST
Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.
Syntax
@@DATEFIRST
Examples
SET DATEFIRST 5
SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'
Here is the result set. Counting from Friday, today (Saturday) is day 2.
1st Day Today
---------------- --------------
5 2

2. Cursor Functions
@@CURSOR_ROWS
@@CURSOR_STATUS
@@FETCH_STATUS

3. Date and Time Functions
Datepart Abbreviations
Year yy, yyyy
Quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

DATEADD
Eg : SELECT DATEADD(day, 21, pubdate) AS timeframe fROM titles

DATEDIFF
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles

DATENAMESELECT DATENAME(month, getdate()) AS 'Month Name' = February

DATEPARTSELECT DATEPART(month, GETDATE()) AS 'Month Number' = 2

DAY SELECT DAY('03/12/1998') AS 'Day Number'

MONTHSELECT MONTH('03/12/1998') AS 'MONTH Number'

YEARSELECT YEAR('03/12/1998') AS 'YEAR Number'

GETDATE()SELECT GETDATE()

4. Mathematical Functions
CEILING Returns the smallest integer greater than, or equal to, the given numeric expression.
Examples
This example shows positive numeric, negative, and zero values with the CEILING function.
SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
GO
Here is the result set:
--------- --------- -------------------------
124.00 -123.00 0.00

FLOORReturns the largest integer less than or equal to the given numeric expression.
Examples
This example shows positive numeric, negative numeric, and currency values with the FLOOR function.
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)
The result is the integer portion of the calculated value in the same data type as numeric_expression.
--------- --------- -----------
123 -124 123.0000

TRIGNOMETRY :
SIN
COS
TAN
COT

5. Metadata Functions
@@PROCIDReturns the stored procedure identifier (ID) of the current procedure
SELECT @@PROCID AS 'ProcID'

OBJECTPROPERTYIF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') = 2
print 'Authors is a table'

DB_IDReturns the database identification (ID) number.

DB_NAMEReturns the database name.
Ex :
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases

OBJECT_ID

6. Security Functions
USER
USER_ID
SUSER_NAME : EG :select SUSER_NAME(0X01)
SUSER_SID : EG : SELECT SUSER_SID('sa')

7. String Functions
LTRIMReturns a character expression after removing leading blanks.
select ltrim(' venkat')

RTRIM
Returns a character string after truncating all trailing blanks.

UPPERReturns a character expression with lowercase character data converted to uppercase.
select upper('venkat')

LOWERReturns a character expression with UPPER character data converted to lowercase.

LENReturns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

LEFTReturns the part of a character string starting at a specified number of characters from the left.
Ex : left(‘venkat’,3)

RIGHTReturns the part of a character string starting at a specified number of characters from the right.
Ex : Right(‘venkat’,3)

REPLACEReplaces all occurrences of the second given string expression in the first string expression with a third expression.
Examples
This example replaces the string cde in abcdefghi with xxx.
SELECT REPLACE('abcdefghicde','cde','xxx')

8. System Functions
CASE
CAST and CONVERTExplicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

CAST : SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'

CONVERT : SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'

ISNULLCOALESCE
CURRENT_USER
HOST_ID
HOST_NAME
Eg : CREATE TABLE Orders
(OrderID INT PRIMARY KEY,
Workstation NCHAR(30) NOT NULL DEFAULT HOST_NAME())

@@IDENTITY
ISNUMERIC
@@row_count
SCOPE_IDENTITY
SERVERPROPERTY
eg : SELECT SERVERPROPERTY('servername'),
SELECT SERVERPROPERTY('productlevel')

SYSTEM_USER

@@TRANCOUNT

USER_NAME

System Statistical Functions
@@CONNECTIONSReturns the number of connections, or attempted connections, since Microsoft® SQL Server™ was last started.

@@PACK_RECEIVEDReturns the number of input packets read from the network by Microsoft® SQL Server™ since last started.

@@CPU_BUSYReturns the time in milliseconds (based on the resolution of the system timer) that the CPU has spent working since Microsoft® SQL Server™ was last started.

@@PACK_SENTReturns the number of output packets written to the network by Microsoft® SQL Server™ since last started.

@@TOTAL_ERRORSReturns the number of disk read/write errors encountered by Microsoft® SQL Server™ since last started.

9. Text and Image FunctionsPATINDEX
TEXTPTR
TEXTVALID

Class 7 - Views in SQL Server

2009-08-06T05:17:00.000-07:00

VIEW IN SQL SERVER 2000
Views are nothing but saved SQL queries. Suppose you have written a query by using multiple SQL statements to view the data. But it may require that watching the data from these tables whenever required. For this you don’t need to write all these queries every time, instead you can save it as a view. View doesn’t contain any data, it is just a query.

CREATE A VIEW
This example creates a view with a simple SELECT statement. A simple view is helpful when a combination of columns is queried frequently.
CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles
GO

JOINS :
Views can be written by using multiple tables with help of joins. There are different joins available in SQL Server. As per the requirement we can choose join to use in the queries for view.

INNER
LEFT OUTER
RIGHT OUTER
FULL
CROSS
SELF
CARTISAN

Eg : Create view ABCD_View
AS
Select a.Col1, a.col2, b.col1, b.col2
from Table_A inner join Table_B
on a.Col1 = b.Col2

Some Arguments about Views in Sql Server 2000 : •
You can create views only in the current database. However, the tables and views referenced by the new view can exist in other databases or even other servers if the view is defined using distributed queries.
• A view can reference a maximum of 1,024 columns.
• A View can be used as a security mechanism like we can given permissions for the users also.
• View names must follow the rules for identifiers and must be unique for each user. Additionally, the name must not be the same as any tables owned by that user.
• You can build views on other views and on procedures that reference views. Microsoft® SQL Server™ 2000 allows views to be nested up to 32 levels.
• You cannot associate rules or DEFAULT definitions with views.
• You cannot associate AFTER triggers with views, only INSTEAD OF triggers.
• The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
• You cannot define full-text index definitions on views.
• You cannot create temporary views, and you cannot create views on temporary tables.
• Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped, unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.
• When a view is created, the name of the view is stored in the sysobjects table.
• If the new table (or view) structure changes, then the view must be dropped and recreated.

View can be createD by using following options for security and performance purposes.

WITH CHECK OPTIONForces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION ensures the data remains visible through the view after the modification is committed.
Eg :
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH CHECK OPTIONGO

WITH ENCRYPTIONIndicates that SQL Server encrypts the system table columns containing the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.
Eg :
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH ENCRYPTION
GO

SCHEMABINDINGBinds the view to the schema. When SCHEMABINDING is specified, the select_statement must include the two-part names (owner.object) of tables, views, or user-defined functions referenced.
Views or tables participating in a view created with the schema binding clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, SQL Server raises an error. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.

Use built-in functions within a view : This example shows a view definition that includes a built-in function. When you use functions, the derived column must include a column name in the CREATE VIEW statement.
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type
GO

Class 6 - Knowlege before writing Queries

2009-07-05T06:10:00.000-07:00

KNOWLEDGE BEFORE WRITING QUERIES

WILDCARD EXAMPLES :
% - V% - VENKAT, VIJAY, VAMSI
- - V-NKAT - VENKAT
[] - [CS]HERYL - CHERYL, SHERYL
[^] - [^C] - SHERYL

FUNCTIONS :
Arithmetic operators
Comparison operators
Logical operators
Assignment operators
String concatenation operators
Unary operators

Arithmetic Operators
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulo (returns the integer remainder of a division)

Comparison Operators
= Equal to
> Greater than
<>= Greater than or equal to
<= Less than or equal to <> Not equal to
! = Not equal to (SQL-89 standard)
! <> Not greater than (SQL-89 standard)

Logical Operators
AND True if both expressions evaluate to true
BETWEEN True if the value is within a specified range
IN True if the result is equal to one in a list
LIKE True if the result matches a pattern
NOT Reverses the value of any other logical operator (such as NOT IN)
OR True if either logical expression evaluates to true
EXISTS True if a subquery (introduced later in this session) returns any Records

---ALL True if all of a set of compared values evaluates to true
---ANY True if any one of a set of compared values evaluates to true
---SOME True if some of a set of compared values evaluates to true

The assignment operator
Transact-SQL only has one assignment operator, and you’ve probably guessed it
already—it’s the equals sign (=). You use it when assigning values to variables or
specifying column headings.

The string concatenation operator
String concatenation is an operation you’ll find yourself performing over and over
again. Luckily, it is very intuitive—T-SQL uses the plus sign (+) to concatenate
strings. You can use it in SELECT statements like the following:
SELECT au_fname + ‘,’ + au_lname FROM authors

Unary Operators
Operator Description
+ The number is positive
- The number is negative

Working with Aggregate Functions
DISTINCT tells the query to ignore duplicate values, and ALL is a default
SUM returns the total of all the values in a numeric field
AVG returns the average of all the values in the numeric column:
COUNT returns the number of records in the group:
COUNT_BIG for big size purpose
MAX returns the highest value in the column:
MIN returns the lowest value in the column:
STDEV finding standar deviation
STDEVP finding standar deviation

CLAUSES :
AS
SET
ORDER BY
GROUP BY
UNION
UNIONALL
HAVING

CASE FUNCTION :
SELECT case when id = 4 then 'VENKAT' ELSE 'VIJAY' END NAME FROM SYSOBJECTS
WHILE : WHILE (SELECT AVG(price) FROM titles) < $30BEGIN UPDATE titles SET price = price * 2 SELECT MAX(price) FROM titles IF (SELECT MAX(price) FROM titles) > $50 BREAK ELSE CONTINUEENDPRINT 'Too much for the market to bear'
IFDECLARE @msg varchar(255)IF (SELECT COUNT(price) FROM titles WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20) > 0 BEGIN SET NOCOUNT ON SET @msg = 'There are several books that are a good value between $10 and $20. These books are: ' PRINT @msg SELECT title FROM titles WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20 ENDELSE BEGIN SET NOCOUNT ON SET @msg = 'There are no books between $10 and $20. You might consider the following books that are under $10.' PRINT @msg SELECT title FROM titles WHERE title_id LIKE 'TC%' AND price <>Inbuilt functions :
GOTO
WAITFOR
RTRIM (select Right of given characters)
LTRIM (select Right of given characters)
Getdate()
Cast
Convert
Isnull
Coleasce
Datediff
Identity

Queries :
SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod' or city = 'Orlando' or division = 'food'

UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod' UNION ALL SELECT employeeID, firstname, lastname FROM names WHERE city = 'Orlando' UNION ALL SELECT employeeID, firstname, lastname FROM names WHERE division = 'food'

GROUP BY
USE Northwind SELECT OrderID FROM [Order Details] WHERE UnitPrice > 10 GROUP BY OrderID
Using a NOT EXISTS
SELECT a.hdr_key FROM hdr_tbl a WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)
Using a NOT IN
SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)

QUERY :
SELECT companyid, plantid, formulaid FROM batchrecords WHERE companyid = '0001' and plantid = '0202' and formulaid = '39988773' OR companyid = '0001' and plantid = '0202'

QUERY WITH VARIABLE :
DECLARE @age int SET @age = "30" DECLARE @service_years int SET @service_years = "10" SELECT employee_id FROM employees WHERE age = @age and service_years = @service_years

................. I will post more................ wait and see.................

Class 5 - Normalization

2009-07-05T06:08:00.000-07:00

Data Models : We can use following models to design our database.
Entity, Hierarchical, Network

DATA MODELING :

1 – 1 Relationship
1 – many
Many – many

1-1 defining with primary – unique key
Students table (studenntID) - Hostel (StudID)

1- many definign Primary – Foreign Key
Students table (StudentID) - Library (StudID)

Many – Many defining with 3 table
Students table (studID) - Subjects (SubjectID) - Third table ( )

NORMALIZATION :

1NF
Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2NF
Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF
Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
BCNF
Boyce-Codd Normal Form - If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF
Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF
Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF
Optimal Normal Form - a model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF
Domain-Key Normal Form - a model free from all modification anomalies.

Class 4 - Constraints

2009-07-05T06:05:00.000-07:00

CONSTRAINTS :
Primary Key, Foreign Key,Unique ,Default Key,Check,Null

Primary key
NEW TABLE : create table abc (sl int identity(1,1), name varchar(100), constraint PK_sl Primary key(Sl))

ON EXISTING : ALTER TABLE ABC ADD CONSTRAINT pk_sL PRIMARY KEY (SL)

DROPPING : ALTER TABLE ABC DROP CONSTRAINT pk_sL

Foreign Key
NEW TABLE : CREATE TABLE ABCD (SL INT, PLACE VARCHAR(100),DISTRICT VARCHAR(50), CITY VARCHAR(50), CONSTRAINT FK_sl$abc$sl Foreign Key (Sl) references abc(Sl))

ON EXISTING : alter table abcd ADD constraint FK_Sl$abc$sl FOREIGN KEY (SL) REFERENCES ABC(SL)

DROPPNG : alter table abcd drop constraint FK_Sl$abc$sl

unique
NEW TABLE : create table abcde (sl int, rollnum int, classname varchar(10),
Constraint UQ_rollnum Unique(rollnum))

ON EXISTING : ALTER TABLE ABCDE ADD CONSTRAINT uq_ROLLNUM UNIQUE CLUSTERED (ROLLNUM)

DROPPING : ALTER TABLE ABCDE DROP CONSTRAINT UQ_ROLLNUM

check
NEW TABLE : CREATE TABLE ABCDEF (SL INT, AMOUNT INT, CONSTRAINT ch_AMOUNT CHECK(AMOUNT>5))

ON EXISTING : ALTER TABLE ABCDEF ADD CONSTRAINT CH_AMOUNT CHECK(AMOUNT>5)

DROPPING : ALTER TABLE ABCDEF DROP CONSTRAINT CH_AMOUNT

default
CREATE TABLE ABCDEFF (SL INT, MARKS INT DEFAULT 35)

Null / not null

Class 3 - Brief about T-SQL

2009-07-05T05:57:00.000-07:00

Brief about TSQL

DDL – DATA DEFINITION LANGUAGE
DML – DATA MANIPULATION LANGUAGE
DCL – DATA CONTROL LANGUAGE
TCL – TRANSACT CONTROL LANGUAGE

DDL – CREATE, ALTER, DROP, TRUNCATE

Eg : CREATE DATABASE SampleDatabase
ON
( NAME = MyDatabase,
FILENAME = ‘C:\program files\MyData\mydatabasefile.mdf’,
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB)
LOG ON
( NAME = MyDatabase_LOG,
FILENAME = ‘C:\program files\MyData\mydatabaselog.ldf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )

Eg :
CREATE TABLE MyTable (
Field1 int PRIMARY KEY,
Field2 char(10) NOT NULL),
Field3 datetime
)

Eg : ALTER DATABASE SampleDatabase
ADD FILE
(
NAME = MyDatabase1,
FILENAME =’c:\program files\MyData\mydatabasefile2.ndf’,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB
)

Eg : ALTER TABLE MyTable ADD Field4 VARCHAR(10) NULL
ALTER TABLE MyTable DROP COLUMN Field4

Eg : DROP DATABASE SampleDatabase

Eg : DROP TABLE MyTable

DML – SELECT, INSERT, UPDATE, DELETE

Eg : _ SELECT @@lNGUAGE displays the name of your SQL Server language.
_ SELECT @@SERVERNAME displays the name of the SQL Server for the current connection.
_ SELECT @@VERSION displays information about Microsoft SQL Server version, build, edition, and so on.
_ SELECT @@TRANCOUNT displays the number of open transactions for the current connection.
_ SELECT @@ERROR displays an error number giving

SELECT :
Select * from [ABCD]

INSERT :
Insert into ABCD [ColumnA, ColumnB] values 5, 6

Insert into ABCD values 5,6

DELETE
Delete from ABCD where ColumnA = 5

UPDATE
Update ABCD set ColumnA = 8 where ColumnA = 5

DCL – GRANT, REVOKE
CREATE LOGIN [venky] WITH PASSWORD=N'111'
CREATE USER [venky] FOR LOGIN [venky]
EXEC sp_addrolemember N'db_owner', N'venky'
DROP LOGIN [venky]

Grant select to venkat
Revoke select to venkat

TCL – COMMIT, ROLLBACK
Commit
Rollback

Class 2 - Data Types

2009-07-05T05:50:00.001-07:00

DATA TYPES :
Exact Numerics
bigint
decimal
int
numeric
smallint
money
tinyint
smallmoney
bit

Approximate Numerics
float
real

Date and Time
datetime
smalldatetime

Character Strings
char
text
varchar

Unicode Character Strings
nchar
ntext
nvarchar

Binary Strings
binary
image
varbinary

Other Data Types
cursor
timestamp
sql_variant
uniqueidentifier
table
xml
See Also
New Datatypes :
Varchar(max)
Nvarchar(max)
Varbinary(max)
xml

Exact-number data types that use integer data.
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

decimal and numeric (Transact-SQL)
Numeric data types that have fixed precision and scale.
decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision. Precision Storage bytes 1 – 9 5 10-19 9 20-28 13 29-38 17 money and smallmoney (Transact-SQL) Data types that represent monetary or currency values. Data type Range Storage money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes smallmoney - 214,748.3648 to 214,748.3647 4 bytes bit (Transact-SQL) An integer data type that can take a value of 1, 0, or NULL. float and real (Transact-SQL) Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Note: The SQL-92 synonym for real is float(24). Data type Range Storage float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Depends on the value of n real - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 Bytes Date and Time (Transact-SQL) Are data types that are used for representing the date and the time of day. datetime and smalldatetime Represent the date and the time of day. Data type Range Accuracy datetime January 1, 1753, through December 31, 9999 3.33 milliseconds smalldatetime January 1, 1900, through June 6, 2079 1 minute char and varchar (Transact-SQL) Are character data types of either fixed length or variable length. char [ ( n ) ] Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The SQL-2003 synonym for char is character. varchar [ ( n max ) ] Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying. nchar and nvarchar (Transact-SQL) Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set. nchar [ ( n ) ] Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The SQL-2003 synonyms for nchar are national char and national character. nvarchar [ ( n max ) ] Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying. ntext, text, and image (Transact-SQL) Important: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-2003 synonym for ntext is national text.

text
Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

image
Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

binary and varbinary (Transact-SQL)
Binary data types of either fixed length or variable length.
binary [ ( n ) ]
Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

varbinary [ ( n max) ]
Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The SQL-2003 synonym for varbinary is binary varying.

cursor (Transact-SQL)
A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are nullable.
The operations that can reference variables and parameters having a cursor data type are:
The DECLARE @local_variable and SET @local_variable statements.
The OPEN, FETCH, CLOSE, and DEALLOCATE cursor statements.
Stored procedure output parameters.
The CURSOR_STATUS function.
The sp_cursor_list, sp_describe_cursor, sp_describe_cursor_tables, and sp_describe_cursor_columns system stored procedures.

Sql_variant (Transact-SQL)
A data type that stores values of various SQL Server 2005-supported data types, except text, ntext, image, timestamp, and sql_variant.
uniqueidentifier (Transact-SQL)
Is a 16-byte GUID.
Remarks
A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:
By using the NEWID function.
By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

xml (Transact-SQL)
Updated: 5 December 2005
Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type. For more information, see xml Data Type.
Select * from systypes

Class 1 - Introduction of SQL Server

2009-07-05T05:34:00.000-07:00

Overview of Database Concepts

A database is a structured collection of records of data. Its group os objects.
The first database management systems were developed in the 1960s. A pioneer in the field was Charles Bachman
First it was in the form of Files.
Later created like Foxpro, Dbase.
The relational model was proposed by E. F. Codd in 1970.
The rules
Rule 0: The system must qualify as relational, as a database, and as a management system.
For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database.
Rule 1: The information rule:
All information in the database is to be represented in one and only one way, namely by values in column positions within rows of tables.
Rule 2: The guaranteed access rule:
All data must be accessible with no ambiguity. This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row.
Rule 3: Systematic treatment of null values:
The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number," in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.
Rule 4: Active online catalog based on the relational model:
The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the database's structure (catalog) using the same query language that they use to access the database's data.
Rule 5: The comprehensive data sublanguage rule:
The system must support at least one relational language that
(a) Has a linear syntax
(b) Can be used both interactively and within application programs,
(c) Supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval), security and integrity constraints, and transaction management operations (begin, commit, and rollback).
Rule 6: The view updating rule:
All views that are theoretically updatable must be updatable by the system.
Rule 7: High-level insert, update, and delete:
The system must support set-at-a-time insert, update, and delete operators. This means that data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.
Rule 8: Physical data independence:
Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.
Rule 9: Logical data independence:
Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.
Rule 10: Integrity independence:
Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.
Rule 11: Distribution independence:
The distribution of portions of the database to various locations should be invisible to users of the database. Existing applications should continue to operate successfully :
(a) when a distributed version of the DBMS is first introduced; and
(b) when existing distributed data are redistributed around the system.
Rule 12: The nonsubversion rule:
If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.

During the 1980s, research activity focused on distributed database systems and database machines.

In the 1990s, attention shifted to object-oriented databases. These had some success in fields where it was necessary to handle more complex data than relational systems could easily cope with, such as spatial databases, engineering data (including software repositories), and multimedia data.

In the 2000s, the fashionable area for innovation is the XML database. As with object databases, this has spawned a new collection of start-up companies, but at the same time the key ideas are being integrated into the established relational products.

Some of DBMS Products :
ADABAS
BerkeleyDB
dBase
IBM DB2
Informix
Microsoft Access
Microsoft SQL Server
MySQL
Oracle Database
Paradox (database)
Sybase
Teradata

MS SQL SERVER

Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. Its primary query language is Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Microsoft and Sybase.
For each databse query languge will use :
Oracle : PL/SQL
Sqlserver : T-Sql

Architecture
The architecture of Microsoft SQL Server is broadly divided into three components: SQLOS which implements the basic services required by SQL Server, including thread scheduling, memory management and I/O management; the Relational Engine, which implements the relational database components including support for databases, tables, queries and stored procedures as well as implementing the type system; and the Protocol Layer which exposes the SQL Server functionality.[1]

SQLOS
SQLOS is the base component in the SQL Server architecture. It implements functions normally associated with the Operating System - thread scheduling, memory management, I/O management, buffer pool management, resource management, synchronization primitives and locking, and deadlock detection.

Relational engine
The Relational engine implements the relational data store using the capabilities provided by SQLOS, which is exposed to this layer via the private SQLOS API.

Protocol layer
Protocol layer implements the external interface to SQL Server.

SQL Server 2005
SQL Server 2005, released in November 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data.

SQL Server 2008
The next version of SQL Server is SQL Server 2008,[29] code-named "Katmai",[30] slated to launch on February 27, 2008 and release (RTM) in Q3 2008.[31][32] The most recent CTP was made available on February 19, 2008. SQL Server 2008 aims[33] to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime. SQL Server 2008 will also include support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them. According to Paul Flessner, senior Vice President, Server Applications, Microsoft Corp., SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc as well as perform search, query, analysis, sharing, and synchronization across all data types.[30]

Tools
SQLCMD
SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt.

Visual Studio
Microsoft Visual Studio includes native support for data programming with Microsoft SQL Server. It can be used to write and debug code to be executed by SQL CLR.
SQL Server Management Studio is a GUI tool included with SQL Server 2005 and later for configuring, managing, and administering all components within Microsoft SQL Server.
Business Intelligence Development Studio
Business Intelligence Development Studio (BIDS) is the IDE from Microsoft used for developing data analysis and Business Intelligence solutions utilizing the Microsoft SQL Server Analysis Services, Reporting Services and Integration Services.

SQL Server Release HistoryVersion Year Release Name Codename

1.0 1989 SQL Server 1.0 -

4.21 1993 SQL Server

6.0 1995 SQL Server

6.5 1996 SQL Server 6.5 Hydra

7.0 1998 SQL Server 7.0 Sphinx-

1999 SQL Server 7.0
OLAP Tools
8.0 2000 SQL Server 2000 Shiloh

8.0 2003 SQL Server 2000 64-bit Edition Liberty

9.0 2005 SQL Server 2005 Yukon

10.0 2008 SQL Server 2008 Katmai

SQL SERVER CLASS

2009-07-05T05:26:00.000-07:00

Hi All,

I have prepared this class material for the learners who are new to SQL Server. By studying this you can gain knowledge in SQL Server. Actually I have take SQL Server 2000 for preparing this class materials, because I felt like it would be better first learn the older versions then come to newer. Hope it will helpful for everyone who are new to SQL Server.

Follow my lession in the Blog :

SQL SERVER 2008 NEW DATA TYPES

2009-07-02T00:31:00.000-07:00

NEW DATA TYPES in SQL Server 2008

• Date and Time: Four new date and time data types have been added, making working with time much easier than it ever has in the past. They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
• Spatial: Two new spatial data types have been added GEOMETRY and GEOGRAPHY which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.
• HIERARCHYID: The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures, such as the organization chart of a business.
• FILESTREAM: FILESTREAM is not a data type as such, but is a variation of the
VARBINARY(MAX) data type that allows unstructured data to be stored in the file system
instead of inside the SQL Server database. Because this option requires a lot of involvement
from both the DBA administration and development side.

…. Working on each data type practical, I will post with examples once I finished.

FIND LIST OF JOBS AND ITS DETAILS IN SQL SERVER

2009-07-01T07:12:00.000-07:00

-- JOBWISE INFORMATION
select distinct a.name JOBNAME, isnull(DESCRIPTION,'No description available') DESCRIPTION,c.Name Category, isnull (b.database_name,'None') DATABASE_NAME, isnull(f.name, 'None') Job_Owner,
case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
isnull (case d.freq_interval
when '1' then 'None'
when '2' then 'Monday'
when '4' then 'Tuesday'
when '8' then 'Wednesday'
when '16' then 'Thursday'
when '32' then 'Friday'
when '64' then 'Saturday'
end,'None') as DAY,
case when active_start_time < 120000 then
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000),4) + ' AM','None') else
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000-12),4) + ' PM','None') END
JOB_start_time,
isnull (convert (varchar,d.Date_Created), 'None') Created_Date from sysjobs a
Inner join sysjobsteps b on
a.job_id = b.job_id
left outer join syscategories c on a.category_id = c.category_id
left outer join master.dbo.syslogins f on a.Owner_sid = f.sid
left outer join sysjobschedules e on e.job_id = a.job_id
left outer join sysschedules d on e.schedule_id = d.schedule_id
order by a.name

--JOB STEP WISE INFORMATION
select distinct a.name JOBNAME, isnull(DESCRIPTION,'No description available') DESCRIPTION,c.Name Category, isnull (b.database_name,'None') DATABASE_NAME, isnull(f.name,'None') Job_Owner,
b.step_id STEP, b.step_name STEPNAME,b.subsystem TYPE,b.command,
case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
isnull (case d.freq_interval
when '1' then 'None'
when '2' then 'Monday'
when '4' then 'Tuesday'
when '8' then 'Wednesday'
when '16' then 'Thursday'
when '32' then 'Friday'
when '64' then 'Saturday'
end,'None') as DAY,
case when active_start_time < 120000 then
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000),4) + ' AM','None') else
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000-12),4) + ' PM','None') END
JOB_start_time,
isnull (convert (varchar,d.Date_Created), 'None') Created_Date from sysjobs a
left outer join sysjobsteps b on
a.job_id = b.job_id
left outer join syscategories c on a.category_id = c.category_id
left outer join master.dbo.syslogins f on a.Owner_sid = f.sid
left outer join sysjobschedules e on e.job_id = a.job_id
left outer join sysschedules d on e.schedule_id = d.schedule_id
order by a.name

MOVING TEMPDB

2009-04-28T22:58:00.000-07:00

MOVING TEMPDB FROM DEFAULT LOCATION TO REQUIRED PATH :

When installa SQL Server all system databases will be intsalled at Progra Files - Microsoft SQL Server - Data folder. But it is suggestable that move Tempdb databsae into another driver for better performance. Moving tempdb is not like normal process, Attach/Detach, Backup/Restore, it can be done in following way.

How to move tempdb

1) Find the tempdb database file details.

sp_helpdb tempdb

2)
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb\tempdb2005.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb\tempdb2005.ldf')

* C:\tempdb is the new location where we want to move the tempdb files.

How to findout list of users and thier permissions in a Database

2009-03-31T04:29:00.000-07:00

I have struggled almost a week to findout the solution. From google also I found different suggestions in various sites, but it doesn't met to my requirement. Finally I got up the solution.

1) SELECT UID, NAME, HASDBACCESS,B.* FROM SYSUSERS A INNER JOIN SYSPERMISSIONS B ON A.UID = B.ID

2) Exec sp_helprotect null,'UserName'

for assigning role to login we can use sys procedures like
sp_addrolemember, sp_change_users_login ........

Mirror the Database using SQL Server 2005

2009-03-24T01:40:00.000-07:00

Mirroing is the new concept introduced in SQL Server 2005. It is very useful to maintain a standby database when disaster occurs. Following is the process how to configure mirroring and test.

1) Create & backup database test_mirror to disk='d:\test_mirror.bkp'

2) backup log test_mirror to disk='d:\test_mirror_log.bkp'

3) move the backup file into destination server

4) Restore the database with norecovery

RESTORE DATABASE [test_mirror] FROM DISK = N'd:\test_mirror.bkp' WITH FILE = 1,

NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO

5) Restore the log with norecovery

RESTORE log [test_mirror] FROM DISK = N'd:\test_mirror_log.bkp' WITH FILE = 1,

NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

6) Configure the mirroring

a) Select test_mirror database right click - go to tasks - select mirror
b) Configure the Security using configure Security button. It will prompt for the

connections and authentication. You can configure different servers or 2 instances for

mirroring.
c) As first step it will ask for Witness server, this is optional. Witness server

can be configured to watch the mirroring from different server.
d) Principal and Mirror servers can be configured using next steps.
e) Select Operating Mode either Asynchronous(high performance) or Synchronous (high protection).
Asynchronous is nothing but changes will happend at principal server first then changes pass to mirror server.
Synchronous is like changes will happend same time at both the servers.

7) create the test table before start the mirroring.

create table test_1(regno numeric)

8) Start the mirroring

9) create the test_2 table after start the mirroring

create table test_2(regno numeric)

10) Create the snapshot copy in destination

CREATE DATABASE test_mirror_copy ON
(
NAME = test_mirror,
FILENAME = 'd:\testmirror.ss'
)
AS SNAPSHOT OF test_mirror

11) In the snapshot copy I can see both test-1 and test_2 tables.

12) Insert data into test_1 table

insert into test_1 values(1)

13) drop the existing snapshot

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'test_mirror_copy'
GO
USE [master]
GO
/****** Object: Database [test_mirror_copy] Script Date: 03/18/2009 05:38:05 ******/
DROP DATABASE [test_mirror_copy]
GO

14) create the new snapshot

CREATE DATABASE test_mirror_copy ON
(
NAME = test_mirror,
FILENAME = 'd:\testmirror.ss'
)
AS SNAPSHOT OF test_mirror

15) So we can able to see one row in test_1 table. It means mirror happening from

principle to mirror server.

Note :
** If you are using SQL server 2005 with Service Pack 2, then no need to configure any trace flags. Otherwise Trace Flag 1400 is required to put in start up Parameters.

- Open Configuration Tools
- Goto Sql Server Configuration Manager
- Goto SQL Server 2005 Services
- then open the sql server service
- goto Advanced
- in startup parameters mention ;-T1400 (T should be capital)
- restart the service to effect the settings

SQL Server 2005 New Features for improving Performance

2009-03-17T05:41:00.000-07:00

TABLE OF CONTENTS

1. SCHEMA BINDING VIEW (Materialized View) :
2. CTE (Common Table Expression) :
3. TABLE PARTITION :
4. OUTPUT Keyword :
5. PIVOT / UNPIVOT :
6. APPLY Operator :
7. RANKING FUNCTIONS :
8. SQL SERVER 2005 INDEX FEATURES :
9. Sp_Executesql

1. SCHEMA BINDING VIEW (Materialized View) :

Schema Binding View is nothing but a view but it will bind the schema like a table. It means the view will hold the data. So we can able to create indexes on that. When index is created on a table automatically it improves the performance.

Benefits of Using Indexed Views :

• It is possible to create a unique clustered index on a view, as well as nonclustered indexes, to improve data access performance on the most complex queries.
• Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
• Tables can be prejoined and the resulting data set stored.
• Combinations of joins or aggregations can be stored.

Considerations : The following considerations should follow while creating SB views ..
• The view, and all tables referenced in the view, must be in the same database and have the same owner.
• The indexed view does not need to contain all the tables referenced in the query to be used by the optimizer.
• A unique clustered index must be created before any other indexes can be created on the view.
• The view must be created using schema binding and any user-defined functions referenced in the view must also be created with the SCHEMABINDING option.
• Additional disk space will be required to hold the data defined by the indexed view.
• DTA (Database Tuning Advisor) tool can be used to findout indexed view requirement. It is a new tool available in SQL 2005 only.

Creating Indexed Views:

The steps required to create an indexed view are critical to the successful implementation of the view.
1. Verify the setting of ANSI_NULLS is correct for all existing tables that will be referenced in the view.
2. Verify ANSI_NULLS is set correctly for the current session as shown in the table below before creating any new tables.
3. Verify ANSI_NULLS and QUOTED_IDENTIFIER are set correctly for the current session as shown in the table below before creating the view.
4. Verify the view definition is deterministic.
5. Create the view using the WITH SCHEMABINDING option.
6. Verify your session's SET options are set correctly as shown in the table below before creating the unique clustered index on the view.
7. Create the unique clustered index on the view.
8. The OBJECTPROPERTY function can be used to check the value of ANSI_NULLS and QUOTED_IDENTIFIER on an existing table or view.

Note The indexed view may contain float and real columns; however, such columns cannot be included in the clustered index key if they are non-persisted computed columns.
GROUP BY Restrictions
If GROUP BY is present, the VIEW definition:
• Must contain COUNT_BIG(*).
• Must not contain HAVING, CUBE, ROLLUP, or GROUPING()
These restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions

Example :

CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*OrderQty) AS SumPrice,
SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice,
COUNT_BIG(*) AS Count, ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID

GO

CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

Go

Can create many non clustered indexes

2. CTE (Common Table Expression) :
A named temporary result set based on a SELECT query. By mentioning WITH CTE Name we can write the query and the result set can be used again in the same query. The main purpose of CTE is we can avoid temporary table.

Advantages :
• Result set can be used in SELECT, INSERT, UPDATE, or DELETE
• Queries with derived tables become more readable
• Provide traversal of recursive hierarchies

Example :

WITH TopSales (SalesPersonID, NumSales) AS
(SELECT SalesPersonID, Count(*)
FROM Sales.SalesOrderHeader GROUP BY SalesPersonId)

SELECT LoginID, NumSales
FROM HumanResources.Employee e INNER JOIN TopSales
ON TopSales.SalesPersonID = e.EmployeeID
ORDER BY NumSales DESC

3. TABLE PARTITION :
Partition Tables
Vertical Table Partitioning :
You can use vertical table partitioning to move infrequently used columns into another table. Moving the infrequently used columns makes the main table narrower and allows more rows to fit on a page.

Horizontal Table Partitioning :
Horizontal table partitioning is a bit more complicated. But when tables that use horizontal table partitioning are designed correctly, you may obtain huge scalability gains. One of the most common scenarios for horizontal table partitioning is to support history or archive databases where partitions can be easily delineated by date. A simple method that you can use to view the data is to use partitioned views in conjunction with check constraints.

How to partition a table using Horizontal Partitioning :
Step 1: Create the partition function
CREATE PARTITION FUNCTION emailPF (nvarchar(30))
AS RANGE RIGHT FOR VALUES ('G', 'N')

Step 2: Create the partition scheme
CREATE PARTITION SCHEME emailPS
AS PARTITION emailPF TO (fg1, fg2, fg3)

Step 3 : Create the partitioned table
CREATE TABLE Sales.CustomerEmail
(CustID int, email nvarchar(30))
ON EMailPS (email)

* fg1,fg2,fg3 are filegroups located in different drives.

4. OUTPUT Keyword :

With help of Output keyword we can avoid multiple declarations and assigning the values. While performing INSERT/Update we can take the value into variable and it can be used in further queries.

Example :

DECLARE @InsertDetails TABLE
(ProductModelID int,
InsertedBy sysname)

INSERT INTO Production.ProductModel(Name, ModifiedDate)
OUTPUT inserted.ProductModel ID, suser_name()
INTO @InsertDetails
VALUES
('Racing Bike', getdate())

SELECT * FROM @InsertDetails

5. PIVOT / UNPIVOT :

PIVOT – converts values to columns :

Cust Prod Qty
Mike Bike 3
Mike Chain 2
Mike Bike 5
Lisa Bike 3
Lisa Chain 3
Lisa Chain 4

SELECT * FROM Sales.Order
PIVOT (SUM(Qty) FOR Prod IN ([Bike],[Chain])) PVT

Cust Bike Chain
Mike 8 2
Lisa 3 7

UNPIVOT – converts columns to values :

Cust Bike Chain
Mike 8 2
Lisa 3 7

SELECT Cust, Prod, Qty
FROM Sales.PivotedOrder
UNPIVOT (Qty FOR Prod IN ([Bike],[Chain])) UnPVT

Cust Prod Qty
Mike Bike 8
Mike Chain 2
Lisa Bike 3
Lisa Chain 7

6. APPLY Operator :

With help of APPLY operator we can join a function with table and get the result. In SQL 2000 then function result need to be inserted in a table, then only we can make a join from that. But using APPLY operator directly joining with table we can get the output.

Advantages : Invokes a table-valued function once per row
 CROSS APPLY – only rows with matching function results
 OUTER APPLY – all rows, regardless of matching function results

Example :

CREATE FUNCTION Sales.MostRecentOrders
(@CustID AS int) RETURNS TABLE AS
RETURN
SELECT TOP(3) SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustID
ORDER BY OrderDate DESC

SELECT Name AS Customer, MR.*
FROM Sales.Store
CROSS APPLY Sales.MostRecentOrders(CustomerID) AS MR

7. RANKING FUNCTIONS :

Function Description
RANK Returns a rank for each row within a specified partition in a result set
DENSE_RANK Returns a consecutive rank for each row within a specified partition in a result set
ROW_NUMBER Returns the ordinal row position of each row in a grouping within a result set
NTILE Divides the rows in each partition of a result set into a specified number of ranks based on a given value.

With help of ranking functions we can find the data sequentially, like we can avoid top and other keywords.
8. SQL SERVER 2005 INDEX FEATURES :

ONLINE INDEXING :
The online index option allows concurrent modifications (updates, deletes, and inserts) to the underlying table or clustered index data and any associated indexes during index data definition language (DDL) execution. For example, while a clustered index is being rebuilt, you can continue to make updates to the underlying data and perform queries against the data.
Eg :
CREATE CLUSTERED INDEX [PK_Employee_EmployeeID] ON [HumanResources].[Employee] ([EmployeeID] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]

ALTER INDEX ALL on HumanResources.Employee REBUILD WITH (ONLINE=ON)

INCLUDE CLAUSE :
With help of include clause, by including non-key columns we can create non-clustered indexes that cover more queries. The Database Engine does not consider non-key columns when calculating the number of index key columns or index key size. Non-key columns can be included in non-clustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. INCLUDE clause to CREATE INDEX for nonkey columns – stored in leaf nodes of the index

Eg :

create unique index XUpersonInterest_firstName_inclInterest
on personInterest(firstName) include (interest)

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)

ALTER INDEX :
Disabling
ALTER INDEX IX_Customer_TerritoryID ON Sales.Customer DISABLE

Rebuilding
ALTER INDEX PK_Customer_CustomerID ON Sales.Customer REBUILD

Reorganizing
ALTER INDEX PK_Customer_CustomerID ON Sales.Customer REORGANIZE

Setting of options
ALTER INDEX PK_Customer_CustomerId ON Sales.Customer SET(...)

Index related considerations for improving performance :
• Create indexes based on use :
Do not create indexes if a table is rarely queried, or if a table does not ever seem to be used by the optimizer. Avoid indexes on bit, text, ntext, or image data types because they are rarely used. Avoid very wide indexes and indexes that are not selective.
• Keep clustered index keys as small as possible.
• Consider range data for clustered indexes.
• Create an index on all foreign keys.
• Create highly selective indexes.
Create indexes that exhibit high selectivity. In other words, create indexes that have many distinct values. For example, an index on a region column may have a small number of distinct values. Therefore, there may not be enough distinct values for the optimizer to use. Another example of an item that may not have enough distinct values is a bit column. Since there are only two values, an index cannot be very selective and as a result, the index may not be used.
• Consider a covering index for often-used, high-impact queries.
• Use multiple narrow indexes rather than a few wide indexes.
• Create composite indexes with the most restrictive column first.
• Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
• Remove unused indexes.
• Use the Index Tuning Wizard to tune the indexes.
• Keep statistics up to date

9. sp_executesql :

This system procedure can be used to execute dynamic sql queries instead of using EXEC keyword. Compare with EXEC keyword it will give the much performance.

Example :

DECLARE @SQLString NVARCHAR(500);

--Set column list. CHAR(13) is a carriage return, line feed
SET @SQLString = N'SELECT FirstName, LastName, JobTitle' + CHAR(13);

-- Set FROM clause with carriage return, line feed.
SET @SQLString = @SQLString + N'FROM HumanResources.vEmployee' + CHAR(13);

--Set WHERE clause.
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%''';

EXEC sp_executesql @SQLString;

* The declared data type should be ntext/nchar/nvarchar

Brief about TSQL (Transact SQL)

2009-03-10T07:36:00.000-07:00

Transact SQL is Structure Query Language which is using by SQL Server. When working with Oracle; PL-SQL can be used. A brief about TSQL query language.........

DDL – DATA DEFINITION LANGUAGE
DML – DATA MANIPULATION LANGUAGE
DCL – DATA CONTROL LANGUAGE
TCL – TRANSACT CONTROL LANGUAGE

DDL – CREATE, ALTER, DROP, TRUNCATE

Eg : CREATE DATABASE SampleDatabase
ON
( NAME = MyDatabase,
FILENAME = ‘C:\program files\MyData\mydatabasefile.mdf’,
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB)
LOG ON
( NAME = MyDatabase_LOG,
FILENAME = ‘C:\program files\MyData\mydatabaselog.ldf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )

Eg :
CREATE TABLE MyTable (
Field1 int PRIMARY KEY,
Field2 char(10) NOT NULL),
Field3 datetime
)

Eg : ALTER DATABASE SampleDatabase
ADD FILE
(
NAME = MyDatabase1,
FILENAME =’c:\program files\MyData\mydatabasefile2.ndf’,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB
)

Eg : ALTER TABLE MyTable ADD Field4 VARCHAR(10) NULL
ALTER TABLE MyTable DROP COLUMN Field4

Eg : DROP DATABASE SampleDatabase

Eg : DROP TABLE MyTable

DML – SELECT, INSERT, UPDATE, DELETE

SELECT :
Select * from [ABCD]

INSERT :
Insert into ABCD [ColumnA, ColumnB] values 5, 6

Insert into ABCD values 5,6

DELETE
Delete from ABCD where ColumnA = 5

UPDATE
Update ABCD set ColumnA = 8 where ColumnA = 5

DCL – GRANT, REVOKE

CREATE LOGIN [venky] WITH PASSWORD=N'111'
CREATE USER [venky] FOR LOGIN [venky]
EXEC sp_addrolemember N'db_owner', N'venky'
DROP LOGIN [venky]

Grant select to venkat
Revoke select to venkat

TCL – COMMIT, ROLLBACK

Commit
Rollback

CONSTRAINTS IN SQL SERVER

2009-03-10T07:12:00.000-07:00

CONSTRAINTS :
Primary Key, Foreign Key,Unique ,Default Key,Check,Null

primary key
NEW TABLE : create table abc (sl int identity(1,1), name varchar(100), constraint PK_sl Primary key(Sl))

ON EXISTING : ALTER TABLE ABC ADD CONSTRAINT pk_sL PRIMARY KEY (SL)

DROPPING : ALTER TABLE ABC DROP CONSTRAINT pk_sL

Foreign Key
NEW TABLE : CREATE TABLE ABCD (SL INT, PLACE VARCHAR(100),DISTRICT VARCHAR(50), CITY VARCHAR(50), CONSTRAINT FK_sl$abc$sl Foreign Key (Sl) references abc(Sl))

ON EXISTING : alter table abcd ADD constraint FK_Sl$abc$sl FOREIGN KEY (SL) REFERENCES ABC(SL)

DROPPNG : alter table abcd drop constraint FK_Sl$abc$sl

unique
NEW TABLE : create table abcde (sl int, rollnum int, classname varchar(10),
Constraint UQ_rollnum Unique(rollnum))

ON EXISTING : ALTER TABLE ABCDE ADD CONSTRAINT uq_ROLLNUM UNIQUE CLUSTERED (ROLLNUM)

DROPPING : ALTER TABLE ABCDE DROP CONSTRAINT UQ_ROLLNUM

check
NEW TABLE : CREATE TABLE ABCDEF (SL INT, AMOUNT INT, CONSTRAINT ch_AMOUNT CHECK(AMOUNT>5))

ON EXISTING : ALTER TABLE ABCDEF ADD CONSTRAINT CH_AMOUNT CHECK(AMOUNT>5)

DROPPING : ALTER TABLE ABCDEF DROP CONSTRAINT CH_AMOUNT

default
CREATE TABLE ABCDEFF (SL INT, MARKS INT DEFAULT 35)

Null / not null

ABOUT ME

2008-09-25T02:48:00.000-07:00

I have started this blog for sharing knowledge in SQL Server in all aspects like Database Development / Administration / Packages. Thank you for watching my blog and you can keep asking me questions related to SQL Server.

Thanks & regards
Venkat