Insert data into Sql table read from excel sheet. OPENROWSET() is a method for read the data from excel sheet. Before we run this script make sure that 'Testexcel.xls’ is avilable in 'C' Drive.
INSERRT INTO tbL_excel
SELECT *
FROM OPENROWSET
(
‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=\\C\Testexcel.xls’,
‘SELECT * FROM [Sheet1$]‘
)
Note: It is not work for windows 2007 excel sheets. Because those sheet extenction is '.xlsx'
Friday, September 17, 2010
SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
CSV stands for Comma Separated Values.
Create TestTable
USE TestData
GO
CREATE TABLE CSVTest
(
ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME
)
GO
Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO
Create TestTable
USE TestData
GO
CREATE TABLE CSVTest
(
ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME
)
GO
Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO
Thursday, September 16, 2010
Where is ROWID in Sqlserver 2005 like oracle?
The nearest equivilant is the Row_Number() function, but it doesn't work
exactly like the orable version.
Here's an example...
Select firstname,Row_Number() over( order by firstname ) as RowId from mytable Order By Firstname
This will produce the same results. Why is it different? The over part of the command. This specifies the order that the numbers are to be generated. But it's not necessarily the order of the resultset. The order by at the end of the statment does that.
exactly like the orable version.
Here's an example...
Select firstname,Row_Number() over( order by firstname ) as RowId from mytable Order By Firstname
This will produce the same results. Why is it different? The over part of the command. This specifies the order that the numbers are to be generated. But it's not necessarily the order of the resultset. The order by at the end of the statment does that.
Wednesday, September 15, 2010
Restrictions on Updating Data through Views
You can insert, update, and delete rows in a view, subject to the following limitations:
- • If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.
- • You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.
- • All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
- • Text and image columns can't be modified through views.
- • There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.
How to set alias name for column while retrival in sqlserver 2005
Alias name is name which is given by user....
Select Emp_ID, Emp_name from Emp
o/p:
Emp_ID Emp_name
---------------------
101 suresh
102 kumar
If i need to display Emp_ID as Employee Number and Emp_name as EmployeeName, Then we will change our querry as follows
Select Emp_ID AS Employee Number , Emp_name AS EmployeeName from Emp
o/p:
Employee Number EmployeeName
---------------------------------------
101 suresh
102 kumar
Select Emp_ID, Emp_name from Emp
o/p:
Emp_ID Emp_name
---------------------
101 suresh
102 kumar
If i need to display Emp_ID as Employee Number and Emp_name as EmployeeName, Then we will change our querry as follows
Select Emp_ID AS Employee Number , Emp_name AS EmployeeName from Emp
o/p:
Employee Number EmployeeName
---------------------------------------
101 suresh
102 kumar
Compare two counts in where clause in sqlserver 2005
We can compare two counts in where clause of select statement:
Select * from Emp where
(
(select count(*) from dept where ISactive=1) = (select count(*) from dept where Status=1)
)
In this case it fetch records only the counts are equal...
Select * from Emp where
(
(select count(*) from dept where ISactive=1) = (select count(*) from dept where Status=1)
)
In this case it fetch records only the counts are equal...
truncate time part from datetime in sqlserver 2005 or remove time part from datetime
Example:
My date looks like '2010-09-15 16:59:27.797'. If i want to remove time part and i want to display only date part we can use following code snippet.
Declare @test datetime
set @test=getdate()
select convert(varchar(10),@test,101)
After execution of above 3 statements output looks like;
-----------------
09/15/2010
Note: we may pass column name as argument insted of @test(variable).
Ex: select convert(varchar(10),@column_name,101) from my_table
my_table is table and @column_name is name of column that contains datetime values....
My date looks like '2010-09-15 16:59:27.797'. If i want to remove time part and i want to display only date part we can use following code snippet.
Declare @test datetime
set @test=getdate()
select convert(varchar(10),@test,101)
After execution of above 3 statements output looks like;
-----------------
09/15/2010
Note: we may pass column name as argument insted of @test(variable).
Ex: select convert(varchar(10),@column_name,101) from my_table
my_table is table and @column_name is name of column that contains datetime values....
Tuesday, September 14, 2010
Microsoft SQLSERVER DBA InterView Questions
Here is the good collection for Sql server Data Base Administartor Interviews. Download from the following link. Enjoy............
Click here to download.
If any one has questions or any corrections needed feel free to send mail.
Click here to download.
If any one has questions or any corrections needed feel free to send mail.
Tuesday, September 7, 2010
Brief about Select….into statement with an example in Sqlserver 2005
Select into is used to create back up copies of tables. It selects data from one table and inserts into another.
Syntax:
Select * INTO dest_table_name FROM source_table_name
Example: Select data from customer table into customer_backup
Select * INTO customer_backup FROM customer
It can also be used with WHERE clause for a condition.
Select * INTO customer_backup FROM customer WHERE cust_salary > 1000;
Syntax:
Select * INTO dest_table_name FROM source_table_name
Example: Select data from customer table into customer_backup
Select * INTO customer_backup FROM customer
It can also be used with WHERE clause for a condition.
Select * INTO customer_backup FROM customer WHERE cust_salary > 1000;
Brief about Bulk copy with an example.
Answer
Bulk copy utility of SQL allows data to be copied from one data file to another. The data is first exported from the source data file and then imported into the SQL server using the bcp command. It can also be used to transfer data from a table to a file.
Bulk copy utility of SQL allows data to be copied from one data file to another. The data is first exported from the source data file and then imported into the SQL server using the bcp command. It can also be used to transfer data from a table to a file.
What is “Index Tuning Wizard” in sqlserver 2005?
Index Tuning Wizard is a software application that identifies tables which have inefficient indexes. It makes recommendations on how indexes should be built on a database to optimize performance. The recommendations are based on T-SQL commands that the wizard analyzes.
Come up with comments if any one wants to add more for this article... :)
Come up with comments if any one wants to add more for this article... :)
List out what Index Tuning Wizard can do in Sqlserver 2005
- It identifies tables in need of an index change
- Implements recommendations
- Determines how a proposed change might affect performance
- Has the capability to make indexes change immediately and schedule them
- Helps to tune indexes.
What are high availability features in Sql server 2005?
1. Log Shipping
2. Mirroring
3. Replication
4. Failover Clustering
1) Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs.
Usage Scenario: You can cope up with a longer down time. You have limited investments in terms of shared storage, switches, etc.
2) Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs (actually logs are continuously merged in this scenario – no wonder it’s called Mirror ). Additional advantages of Mirroring include support at .NET Framework level (read no switching/routing code – requires ADO.NET 2.0 & higher) plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.
Usage Scenario: You want very less down time and also a cost effective solution in terms of shared storage, switches, etc. Also you are targeting a single database which easily fits in your disks.
3) Replication is used mainly when data centers are distributed geographically. It is used to replicate data from local servers to the main server in the central data center. Important thing to note here is, there are no standby servers. The publisher & subscriber both are active. It has no down time
Usage Scenario: A typical scenario involves syncing local / regional lookup servers for better performance with the main server in data center periodically, or sync with a remote site for disaster recovery.
4) Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. Here’s a good article on adding geo redundancy to a failover cluster setup.
You might want to look at the licensing options of SQL Server, various editions available and how they map to above features
2. Mirroring
3. Replication
4. Failover Clustering
1) Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs.
Usage Scenario: You can cope up with a longer down time. You have limited investments in terms of shared storage, switches, etc.
2) Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs (actually logs are continuously merged in this scenario – no wonder it’s called Mirror ). Additional advantages of Mirroring include support at .NET Framework level (read no switching/routing code – requires ADO.NET 2.0 & higher) plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.
Usage Scenario: You want very less down time and also a cost effective solution in terms of shared storage, switches, etc. Also you are targeting a single database which easily fits in your disks.
3) Replication is used mainly when data centers are distributed geographically. It is used to replicate data from local servers to the main server in the central data center. Important thing to note here is, there are no standby servers. The publisher & subscriber both are active. It has no down time
Usage Scenario: A typical scenario involves syncing local / regional lookup servers for better performance with the main server in data center periodically, or sync with a remote site for disaster recovery.
4) Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. Here’s a good article on adding geo redundancy to a failover cluster setup.
You might want to look at the licensing options of SQL Server, various editions available and how they map to above features
What are some well-known DBCC (Data Base Consistency Check) Commands in Sqlserver 2005?
1. DBCC Shrinkdatabase
2. DBCC SHRINKfile
3. DBCC Showcontig
4. DBCC help
5. DBCC IndexDefrag
6. DBCC CheckDB
7. DBCC CheckFilegroup
8. DBCC OPENTRAN It shows what are all the open transactions in log
9. DBCC LOGINFO It gives you status of virtual logs if status=2 then that is in use
10. DBCC DBREINDEX(‘Table name’,’Index anme’,’Fillfactor’)
11. DBCC CHECKIDENT(‘T_B_P Table anme’, RESEED, 45)—it sets seed to 45
12. DBCC SQLPERF( LOGSPACE) —Used to see all databases log file sizes.
EX: DBCC SQLPERF(LOGSPACE);
GO
2. DBCC SHRINKfile
3. DBCC Showcontig
4. DBCC help
5. DBCC IndexDefrag
6. DBCC CheckDB
7. DBCC CheckFilegroup
8. DBCC OPENTRAN It shows what are all the open transactions in log
9. DBCC LOGINFO It gives you status of virtual logs if status=2 then that is in use
10. DBCC DBREINDEX(‘Table name’,’Index anme’,’Fillfactor’)
11. DBCC CHECKIDENT(‘T_B_P Table anme’, RESEED, 45)—it sets seed to 45
12. DBCC SQLPERF( LOGSPACE) —Used to see all databases log file sizes.
EX: DBCC SQLPERF(LOGSPACE);
GO
How to get top 3 records without using TOP clause in sqlserver 2005?
set rowcount 3
SELECT * FROM dept
Execute the two statements in a single process. But it sets all the values are set to 3 in this process, if you want to change set rowcount 0
SELECT * FROM dept
Execute the two statements in a single process. But it sets all the values are set to 3 in this process, if you want to change set rowcount 0
How do I list all the indexes on a particular table in sqlserver 2005?
USE YourDatabasename;
GO
EXEC sp_helpindex 'Your_Table_Name'
GO
GO
EXEC sp_helpindex 'Your_Table_Name'
GO
How do we list all the statistics for particular table?
USE URDATABASE;
GO
EXEC sp_helpstats 'YOUR_Table_Name'
GO
GO
EXEC sp_helpstats 'YOUR_Table_Name'
GO
What is refreshing in SQLServer?
You most likely refresh a development or test environment frequently with a recent production environment backup. Depending on what you are testing or developing, current data may be critical to ensuring your results are valid. Creating an automated task to restore the database regularly (weekly, daily or even several times a day as needed) will save lots of time and guarantee that you do not miss any necessary restore steps.
At a high level, you should complete the following steps to refresh a test or development environment using backup and restore as the refresh method:
1. Back up the production database: You should already have a scheduled process that creates daily full backups, so use the backup files that are already in place.
2. Copy all the users in test environment to some other table in master data base
2. Kill connections to database: In order to do a restore, there can be no active connections to the database. All connections to the database must be killed before you start the restore.
3. Restore the database: Issue the restore command.
4. Re-link users and logins: Users and logins are associated by SIDs (security identifiers). These may not be the same on your production and test environments, so you will need to re-lid
At a high level, you should complete the following steps to refresh a test or development environment using backup and restore as the refresh method:
1. Back up the production database: You should already have a scheduled process that creates daily full backups, so use the backup files that are already in place.
2. Copy all the users in test environment to some other table in master data base
2. Kill connections to database: In order to do a restore, there can be no active connections to the database. All connections to the database must be killed before you start the restore.
3. Restore the database: Issue the restore command.
4. Re-link users and logins: Users and logins are associated by SIDs (security identifiers). These may not be the same on your production and test environments, so you will need to re-lid
Difference between Sql server 2000 and 2005
SQL Server 2000
SQL Server 2005
Security:Owner = Schema, hard to remove old users at times
Encryption:No options built in, expensive third party options with proprietary skills required to implement properly.
High Availability Clustering or Log Shipping require Enterprise Edition. Expensive hardware.
Scalability:Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.
SQL Server 2005
Security Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.
Encryption Encryption and key management build in.
High Availability Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.
Scalability 4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.
Add article to mirroring?
Sql server 2005 onwords we have good option called mirroring (Heigh avilability) Concept.
By using this option we can maintain/manage same capy of data in another location( either in same server or different server)
Same server: instal two instancess in same machine.
Another server: let configure mirroring in another machine.
In both senarios we need to add an article to the existing mirroring articles.
This can be done in two ways:
1.From query window
use mydb
go
sp_addarticle @publication='mypublication', @article='dbo.test’,
@source_table='test' , @force_invalidate_snapshot=1 it is used for add article for existing one
go
2.From UI ( Wizard )
1. Right click on local publication
2. Select Required publication
3. Right click on desired publication
4. Click on Launch publication monitor
5. Expand plus symblo -- > Choose Properties --> Choose article from the wizard (option 2)
6. Add required table/stored procedure (Article) to the existing articles
7. then take snapshot and apply that snpshot to the subscription
By using this option we can maintain/manage same capy of data in another location( either in same server or different server)
Same server: instal two instancess in same machine.
Another server: let configure mirroring in another machine.
In both senarios we need to add an article to the existing mirroring articles.
This can be done in two ways:
1.From query window
use mydb
go
sp_addarticle @publication='mypublication', @article='dbo.test’,
@source_table='test' , @force_invalidate_snapshot=1 it is used for add article for existing one
go
2.From UI ( Wizard )
1. Right click on local publication
2. Select Required publication
3. Right click on desired publication
4. Click on Launch publication monitor
5. Expand plus symblo -- > Choose Properties --> Choose article from the wizard (option 2)
6. Add required table/stored procedure (Article) to the existing articles
7. then take snapshot and apply that snpshot to the subscription
Killing open transaction in Sql server
In Sql server 2005 we can see open Tranasctions by using DBCC Command
DBCC opentran() Press enter
It Shows open transactions with Tranaction ID(Process ID)
Then Apply kill command
Kill 45 ( here 45 is pocessID)
DBCC opentran() Press enter
It Shows open transactions with Tranaction ID(Process ID)
Then Apply kill command
Kill 45 ( here 45 is pocessID)
Partitioned Views
A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.
Is it possible to update View?
Yes, it is possible but we need to update the data of the base table underlying to the view.
But follow some rules:
But follow some rules:
Restrictions on Updating Data through Views
You can insert, update, and delete rows in a view, subject to the following limitations:
• If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.
• You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.
• All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
• Text and image columns can't be modified through views.
• There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.
• If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.
• You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.
• All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
• Text and image columns can't be modified through views.
• There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.
How to reset identity seed value?
SELECT IDENT_CURRENT('TestTable') IdentityInTable -- shows current identity
Now execute the DBCC CHECKIDENT command to reset the Seed value for the table as
DBCC CHECKIDENT('TestTable', RESEED, 33)—reset identity
check the current Identity Value.
SELECT IDENT_CURRENT('TestTable') IdentityInTable
Now execute the DBCC CHECKIDENT command to reset the Seed value for the table as
DBCC CHECKIDENT('TestTable', RESEED, 33)—reset identity
check the current Identity Value.
SELECT IDENT_CURRENT('TestTable') IdentityInTable
Bulk insertion in sql server 2005?
DECLARE @DocHandle INT
EXEC SP_XML_PREPAREDOCUMENT @DocHandle OUTPUT, @vXMLPayDues
INSERT INTO tbl_4_LLPymnt(lk_tbl_5_LLs_ID, lk_tbl_3_Lctn_ID,
Month,
MM, YYYY,
D_Dt, Pymt_Typ_Ctgry, D_Amt,Current_Status)
SELECT @iLLsID,@v_LocID,
dbo.UDF_GetMonthNameFromDate(DATE),
Month(DATE),Year(DATE),
DATE,24,DUE,1
FROM OPENXML (@DocHandle,'/NewDataSet/Table', 2) WITH
(ID INT,DATE DATETIME,DUE MONEY)
EXEC SP_XML_REMOVEDOCUMENT @DocHandle
EXEC SP_XML_PREPAREDOCUMENT @DocHandle OUTPUT, @vXMLPayDues
INSERT INTO tbl_4_LLPymnt(lk_tbl_5_LLs_ID, lk_tbl_3_Lctn_ID,
Month,
MM, YYYY,
D_Dt, Pymt_Typ_Ctgry, D_Amt,Current_Status)
SELECT @iLLsID,@v_LocID,
dbo.UDF_GetMonthNameFromDate(DATE),
Month(DATE),Year(DATE),
DATE,24,DUE,1
FROM OPENXML (@DocHandle,'/NewDataSet/Table', 2) WITH
(ID INT,DATE DATETIME,DUE MONEY)
EXEC SP_XML_REMOVEDOCUMENT @DocHandle
Is it possible to take system data bases backup?
Yes, it’s possible except to take ‘temp’ data base backup
Importance of the Resource Database
SQL Server 2005 introduced a new read-only, hidden system database named Resource (RDB). The Resource database contains copies of all system objects that are shipped with SQL Server 2005 and SQL Server 2008.
Resource Database Overview
The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database. The Resource Database does not contain any user data or any user metadata. By design, the Resource database is not visible under SQL Server Management Studio’s Object Explorer | Databases | System Databases Node. The physical file names of the Resource database is mssqlsystemresource.mdf and mssqlsystemresource.ldf. The important thing to be noted is that each instance of SQL Server has one and only one associated mssqlsystemresource.mdf & mssqlsystemresource.ldf file and that instances do not share this file. In a clustered environment, the Resource database exists in the \Data folder on a shared disk drive. The ID for the Resource Database will be always 32767. The DBA shouldn’t rename or move the Resource Database file. If the files are renamed or moved from their respective locations then SQL Server will not start. The other important thing to be considered is not to put the Resource Database files in a compressed or encrypted NTFS file system folders as it will hinder the performance and will also possibly prevent upgrades.
Resource Database File Location in SQL Server 2005
In SQL Server 2005 the Resource Database related MDF and LDF files will be available in:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ directory. The important thing to be noted is that the Resource Database related MDF & LDF file need to be available in the same directory where the Master Databases MDF & LDF files are located. By default during the installation of SQL Server 2005 both the Resource and the Master database files will be available in the same \Data directory.
Resource Database File Location in SQL Server 2008
In SQL Server 2008 the Resource Database related MDF and LDF files will be available in: \Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn. The important thing to be noted is that the Resource Database related MDF & LDF file are in the \Binn directory and the Master Databases MDF & LDF files will be located in the : \Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Data directory. In SQL Server 2008 it is not mandatory to keep both the Resource and Master Database files in the same directory.
Advantages of Resource Database
In previous versions of SQL Server whenever service packs are applied all the system objects that are residing within the system and user databases gets updated which makes it very difficult to rollback the changes.
• The only way to rollback the changes is to uninstall the instance of SQL Server and reinstall SQL Server followed by applying any Service Packs or Hotfixes to revert it to the previous version of the SQL Server Instance
• In SQL Server 2005 onwards the changes will be made to the Resource Database, which will indeed reflect the changes in all the system and user database of the particular instance
• If the DBA needs to apply a Service Pack to multiple instances, then the mssqlsystemresource.mdf and mssqlsystemresource.ldf needs to be copied by the DBA to the target instance
• If the DBA needs to rollback the changes performed by the Service Packs then they need to replace the Resource Database related MDF and LDF files with that of the previous backed up version
TSQL Query to determine the version of Resource Database
SELECT SERVERPROPERTY('ResourceVersion')
GO
TSQL Query to determine when the Resource Database was last backed up
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO
Resource Database Overview
The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database. The Resource Database does not contain any user data or any user metadata. By design, the Resource database is not visible under SQL Server Management Studio’s Object Explorer | Databases | System Databases Node. The physical file names of the Resource database is mssqlsystemresource.mdf and mssqlsystemresource.ldf. The important thing to be noted is that each instance of SQL Server has one and only one associated mssqlsystemresource.mdf & mssqlsystemresource.ldf file and that instances do not share this file. In a clustered environment, the Resource database exists in the \Data folder on a shared disk drive. The ID for the Resource Database will be always 32767. The DBA shouldn’t rename or move the Resource Database file. If the files are renamed or moved from their respective locations then SQL Server will not start. The other important thing to be considered is not to put the Resource Database files in a compressed or encrypted NTFS file system folders as it will hinder the performance and will also possibly prevent upgrades.
Resource Database File Location in SQL Server 2005
In SQL Server 2005 the Resource Database related MDF and LDF files will be available in
Resource Database File Location in SQL Server 2008
In SQL Server 2008 the Resource Database related MDF and LDF files will be available in
Advantages of Resource Database
In previous versions of SQL Server whenever service packs are applied all the system objects that are residing within the system and user databases gets updated which makes it very difficult to rollback the changes.
• The only way to rollback the changes is to uninstall the instance of SQL Server and reinstall SQL Server followed by applying any Service Packs or Hotfixes to revert it to the previous version of the SQL Server Instance
• In SQL Server 2005 onwards the changes will be made to the Resource Database, which will indeed reflect the changes in all the system and user database of the particular instance
• If the DBA needs to apply a Service Pack to multiple instances, then the mssqlsystemresource.mdf and mssqlsystemresource.ldf needs to be copied by the DBA to the target instance
• If the DBA needs to rollback the changes performed by the Service Packs then they need to replace the Resource Database related MDF and LDF files with that of the previous backed up version
TSQL Query to determine the version of Resource Database
SELECT SERVERPROPERTY('ResourceVersion')
GO
TSQL Query to determine when the Resource Database was last backed up
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO
difference between Indexes and Statistics in SQL Server
An index is a physically implemented structure in the database (you can read up more in BOL on clustered and non-clustered indexes) whereas statistics are a set of values that help the optimizer during the execution plan formation stages to decide whether to use an index or not. And it is not a 1-1 relationship between indexes and statistics i.e. all indexes have statistics but one can have statistics without an index. And these statistics that do not associate to an index can also help in the formation of the right execution plan. We will cover that in another post as to how that can help. In this post, let’s cover the basics of these statistics.
An index helps the optimizer to find the data during the execution of the statements and statistics help the optimizer to determine which indexes to use.
So, what exactly do statistics contain that make them so useful? Statistics essentially contain two pieces of information:
1) A histogram which contains a sampling of the data values from the index and the distribution of the values in the ranges of data
2) Density groups (collections) over the column (or number of columns) of a table or an indexed view. Density essentially reflects the uniqueness of the values in a particular column.
An index helps the optimizer to find the data during the execution of the statements and statistics help the optimizer to determine which indexes to use.
So, what exactly do statistics contain that make them so useful? Statistics essentially contain two pieces of information:
1) A histogram which contains a sampling of the data values from the index and the distribution of the values in the ranges of data
2) Density groups (collections) over the column (or number of columns) of a table or an indexed view. Density essentially reflects the uniqueness of the values in a particular column.
Monday, September 6, 2010
Subscribe to:
Posts (Atom)