Archive for the ‘Database’ Category
How to Calculate Running Totals, Subtotals and Grand Total Without a Cursor
Posted by: admin in MS SQL Server, SQL Express Edition, SQL Server 2008 on August 13th, 2011
If you have ever had the need to show detailed data of individual transactions and also keep a running total, subtotals, and grand total columns at the same time, but were not exactly sure how to tackle the problem then this article might help. In this article I will show you a few different techniques for calculating and summing information on multiple rows without using a cursor. The techniques I will show you will just use a basic SELECT statement. Of course, the calculations of the running total, subtotals and grand total will be done using other SQL clauses and functions like SUM and CASE.
Sample Data Used by Examples
Prior to showing you my examples, I will first need to establish a set of test data, which all my examples will use. My test data will consist of an “Orders” table with the following format:
create table Orders (OrderID int identity, OrderAmt Decimal(8,2), OrderDate SmallDatetime)
I’ve populated this test Orders table with the following set of records:
OrderID OrderAmt OrderDate ----------- ---------- -------------------- 1 10.50 2003-10-11 08:00:00 2 11.50 2003-10-11 10:00:00 3 1.25 2003-10-11 12:00:00 4 100.57 2003-10-12 09:00:00 5 19.99 2003-10-12 11:00:00 6 47.14 2003-10-13 10:00:00 7 10.08 2003-10-13 12:00:00 8 7.50 2003-10-13 19:00:00 9 9.50 2003-10-13 21:00:00
All my examples will be using this table to produce the running totals, sub totals, and grand total reports shown below. Basically this table contains a number of different orders that where created over time. Each order has an ID (OrderID) which uniquely identifies each record, an order amount (OrderAmt) that holds a decimal amount for the order, and a timestamp (OrderDate) that identifies when the order was placed.
Running Total On Each Record
This first example will display a simple method of calculating the running total of the OrderAmt. The calculated running total will be displayed along with each record in the Orders table. The “Running Total” column will be created with a simple SELECT statement and a correlated sub query. The correlated sub query is the part of the statement that does the heavy lifting to produce the running total.
select OrderId, OrderDate, O.OrderAmt
,(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID)
'Running Total'
from Orders O
When I run this query against my Orders table I get the following results:
OrderId OrderDate OrderAmt Running Total ----------- -------------------- ---------- ------------- 1 2003-10-11 08:00:00 10.50 10.50 2 2003-10-11 10:00:00 11.50 22.00 3 2003-10-11 12:00:00 1.25 23.25 4 2003-10-12 09:00:00 100.57 123.82 5 2003-10-12 11:00:00 19.99 143.81 6 2003-10-13 10:00:00 47.14 190.95 7 2003-10-13 12:00:00 10.08 201.03 8 2003-10-13 19:00:00 7.50 208.53 9 2003-10-13 21:00:00 9.50 218.03
As you can see, there is a “Running Total” column that displays the running total along with the other column information associated with each Orders table record. This running total column is calculated, by summing up the OrderAmt for all Orders where the OrderID is less than or equal to the OrderID of the current ID being displayed.
Running Total for Each OrderDate
This example is similar to the one above, but I will calculate a running total for each record, but only if the OrderDate for the records are on the same date. Once the OrderDate is for a different day, then a new running total will be started and accumulated for the new day. Here is the code to accomplish this:
select O.OrderId,
convert(char(10),O.OrderDate,101) as 'Order Date',
O.OrderAmt,
(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID and
convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
'Running Total'
from Orders O
order by OrderID
Here are the results returned from the query using my sample Orders Table:
OrderId Order Date OrderAmt Running Total ----------- ---------- ---------- --------------- 1 10/11/2003 10.50 10.50 2 10/11/2003 11.50 22.00 3 10/11/2003 1.25 23.25 4 10/12/2003 100.57 100.57 5 10/12/2003 19.99 120.56 6 10/13/2003 47.14 47.14 7 10/13/2003 10.08 57.22 8 10/13/2003 7.50 64.72 9 10/13/2003 9.50 74.22
Note that the “Running Total” starts out with a value of 10.50, and then becomes 22.00, and finally becomes 23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But when OrderID 4 is displayed the running total is reset, and the running total starts over again. This is because OrderID 4 has a different date for its OrderDate, then OrderID 1, 2, and 3. Calculating this running total for each unique date is once again accomplished by using a correlated sub query, although an extra WHERE condition is required, which identified that the OrderDate’s on different records need to be the same day. This WHERE condition is accomplished by using the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.
Running Totals With Subtotals and Grand totals
In this example, I will calculate a single sub totals for all Orders that were created on the same day and a Grand Total for all Orders. This will be done using a CASE clause in the SELECT statement. Here is my example.
select O.OrderID,convert(char(10),O.OrderDate,101) 'Order Date',O.OrderAmt,
case when OrderID = (select top 1 OrderId from Orders
where convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101)
order by OrderID desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders
where OrderID <= O.OrderID
and convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
else ' ' end as 'Sub Total',
case when OrderID = (select top 1 OrderId from Orders
order by OrderDate desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders)
else ' ' end as 'Grand Total'
from Orders O
order by OrderID
Output from the SELECT statement looks like this:
OrderID Order Date OrderAmt Sub Total Grand Total ----------- ---------- ---------- ---------- ----------- 1 10/11/2003 10.50 2 10/11/2003 11.50 3 10/11/2003 1.25 23.25 4 10/12/2003 100.57 5 10/12/2003 19.99 120.56 6 10/13/2003 47.14 7 10/13/2003 10.08 8 10/13/2003 7.50 9 10/13/2003 9.50 74.22 218.03
In this example the first CASE statement controls the printing of the “Sub Total’ column. As you can see, the sub total is printed only on the last order of the day, which is determined by using a correlated sub query. The second CASE statement prints the “Grand Total”, which is only printed along with the very last order. Each of these CASE statements uses the TOP clause to determine which OrderID is the correct order for which to print out the “Grand Total”.
Conclusion
Hopefully these examples will help you understand different methods that can be used to calculate running totals, sub totals, and a grand total. As you can see you don’t need to use a cursor to calculate these different totals. With the creative use of correlated sub queries and other SELECT clauses like CASE you can easily create all these different totals. Next time you need to calculate totals consider using one of these non-cursor based solutions.
Posted by: admin in Database, MS SQL Server, SQL Express Edition, SQL Server 2008 on August 12th, 2011
Top 10 SQL Server Counters for Monitoring SQL Server Performance
Do you have a list of SQL Server Counters you review when monitoring your SQL Server environment? Counters allow you a method to measure current performance, as well as performance over time. Identifying the metrics you like to use to measure SQL Server performance and collecting them over time gives you a quick and easy way to identify SQL Server problems, as well as graph your performance trend over time.
Below is my top 10 list of SQL Server counters in no particular order. For each counter I have described what it is, and in some cases I have described the ideal value of these counters. This list should give you a starting point for developing the metrics you want to use to measure database performance in your SQL Server environment.
1. SQLServer: Buffer Manager: Buffer cache hit ratio
The buffer cache hit ratio counter represents how often SQL Server is able to find data pages in its buffer cache when a query needs a data page. The higher this number the better, because it means SQL Server was able to get data for queries out of memory instead of reading from disk. You want this number to be as close to 100 as possible. Having this counter at 100 means that 100% of the time SQL Server has found the needed data pages in memory. A low buffer cache hit ratio could indicate a memory problem.
2. SQLServer: Buffer Manager: Page life expectancy
The page life expectancy counter measures how long pages stay in the buffer cache in seconds. The longer a page stays in memory, the more likely SQL Server will not need to read from disk to resolve a query. You should watch this counter over time to determine a baseline for what is normal in your database environment. Some say anything below 300 (or 5 minutes) means you might need additional memory.
3. SQLServer: SQL Statistics: Batch Requests/Sec
Batch Requests/Sec measures the number of batches SQL Server is receiving per second. This counter is a good indicator of how much activity is being processed by your SQL Server box. The higher the number, the more queries are being executed on your box. Like many counters, there is no single number that can be used universally to indicate your machine is too busy. Today’s machines are getting more and more powerful all the time and therefore can process more batch requests per second. You should review this counter over time to determine a baseline number for your environment.
4. SQLServer: SQL Statistics: SQL Compilations/Sec
The SQL Compilations/Sec measure the number of times SQL Server compiles an execution plan per second. Compiling an execution plan is a resource-intensive operation. Compilations/Sec should be compared with the number of Batch Requests/Sec to get an indication of whether or not complications might be hurting your performance. To do that, divide the number of batch requests by the number of compiles per second to give you a ratio of the number of batches executed per compile. Ideally you want to have one compile per every 10 batch requests.
5. SQLServer: SQL Statistics: SQL Re-Compilations/Sec
When the execution plan is invalidated due to some significant event, SQL Server will re-compile it. The Re-compilations/Sec counter measures the number of time a re-compile event was triggered per second. Re-compiles, like compiles, are expensive operations so you want to minimize the number of re-compiles. Ideally you want to keep this counter less than 10% of the number of Compilations/Sec.
6. SQLServer: General Statistics: User Connections
The user connections counter identifies the number of different users that are connected to SQL Server at the time the sample was taken. You need to watch this counter over time to understand your baseline user connection numbers. Once you have some idea of your high and low water marks during normal usage of your system, you can then look for times when this counter exceeds the high and low marks. If the value of this counter goes down and the load on the system is the same, then you might have a bottleneck that is not allowing your server to handle the normal load. Keep in mind though that this counter value might go down just because less people are using your SQL Server instance.
7. SQLServer: Locks: Lock Waits / Sec: _Total
In order for SQL Server to manage concurrent users on the system, SQL Server needs to lock resources from time to time. The lock waits per second counter tracks the number of times per second that SQL Server is not able to retain a lock right away for a resource. Ideally you don’t want any request to wait for a lock. Therefore you want to keep this counter at zero, or close to zero at all times.
8. SQLServer: Access Methods: Page Splits / Sec
This counter measures the number of times SQL Server had to split a page when updating or inserting data per second. Page splits are expensive, and cause your table to perform more poorly due to fragmentation. Therefore, the fewer page splits you have the better your system will perform. Ideally this counter should be less than 20% of the batch requests per second.
9. SQLServer: General Statistic: Processes Block
The processes blocked counter identifies the number of blocked processes. When one process is blocking another process, the blocked process cannot move forward with its execution plan until the resource that is causing it to wait is freed up. Ideally you don’t want to see any blocked processes. When processes are being blocked you should investigate.
10. SQLServer: Buffer Manager: Checkpoint Pages / Sec
SQL Tuning and SQL Optimization Tips
Posted by: admin in Database, MS SQL Server, SQL Express Edition, SQL Server 2008 on August 11th, 2011
SQL Tuning/SQL Optimization Techniques:
1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than ‘*’.
For Example: Write the query as
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of:
SELECT * FROM student_details;
2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as
SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;
Instead of:
SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';
3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';
Instead of:
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';
4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)
Instead of:
Select * from product p
where product_id IN
(select product_id from order_items
5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);
Instead of:
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;
6) Try to use UNION ALL in place of UNION.
For Example: Write the query as
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
Instead of:
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;
7) Be careful while using conditions in WHERE clause.
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);
Write the query as
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)
Write the query as
SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';
Instead of:
SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';
Use non-column expression on one side of the query because it will be processed earlier.
Write the query as
SELECT id, name, salary
FROM employee
WHERE salary < 25000;
Instead of:
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE age > 10;
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;
8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as
SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';
Instead of:
SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';
9) To store large binary objects, first place them in the file system and add the file path in the database.
10) To write queries which provide efficient performance follow the general SQL standard rules.
a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb
How To: Find / Check if the index is exists
Posted by: admin in Database, MS SQL Server, SQL Express Edition, SQL Server 2008 on May 11th, 2011
IF EXISTS(select * from sys.indexes where object_id = OBJECT_ID(‘TableName’) AND name=’IndexName’)
print ‘Exists’
or
exec sp_helpindex ‘TableName’
Could not allocate space for object ‘dbo.SORT temporary run storage
Posted by: admin in MS SQL Server, SQL Server 2008 on April 26th, 2011
This error happens when you do not have enough disk space.
CHARINDEX vs PATINDEX
Posted by: admin in SQL Server String Functions on April 15th, 2011
Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.
Example of CHARINDEX:
USE AdventureWorks;
GO
SELECT CHARINDEX(‘ensure’, DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
Examples of PATINDEX:
USE AdventureWorks;
GO
SELECT PATINDEX(‘%ensure%’,DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
Source
Sql Server Basic
Posted by: admin in Database, MS Access, MS SQL Server, SQL Express Edition, SQL Server 2008, Sql Server Notes on February 23rd, 2011
Concurrent Connections – 32,767
Storage – 524, 272 TB per Database
# of Database – 32, 767
# of of Tables per Database – 2,147,483,647
# of Columns per Table – 1024
How to find the Index fields of table in SQL Server
Posted by: admin in Constraint, Database on August 26th, 2010
Below is the query on finding the Index fields of the table
Returns Primary key
-
select * from sys.objects where parent_object_id = object_id(N'TABLE_1')
Returns All
-
SELECT * FROM SYS.index_columns where object_id= object_id(N'TABLE_1')
Operand type clash: nvarchar(max) is incompatible with image
This error usually occurs when you are adding / updating a record on the database table with image/binary columns, and the value of that image column is null and you did not specify the data type of the parameter.
To resolve this issue you need to explicitly specify the datatype of that image/binary column because the default data type of the Sql parameter is varchar(string)
SQL Express setup parameters
Posted by: admin in SQL Express Edition on June 21st, 2010
; IMPORTANT: THIS IS A TEMPLATE .INI FILE FOR THE SQL SERVER 2005 UNATTENDED INSTALLATION,
; MODIFICATION TO THIS FILE IS REQUIRED TO USE DURING SETUP.
; User must fill in the required information for installation and comment out or delete any non-related information.
; To comment out a line, preface it with a semicolon ‘;’. Items in angled brackets ‘<>’ should be replaced with information without brackets. To determine the information required by your installation, read the descriptions that are provided for each parameter or refer to the SQL Server Books Online help for additional information.
; To use this .ini file, use the /settings switch on the SQL Server 2005 Setup.exe command line.
; For example: start /wait setup.exe /qb /settings c:\template.ini
; SECURITY NOTE: If you create a template.ini file that contains logins and passwords, store the file in a secured location after use.
;——————————————————————–
;——————————————————————–
; Command line examples:
; /qn is quiet mode, no GUI is displayed.
; /qb is quiet mode, basic GUI information is displayed, no user interaction is required. Use /qb for troubleshooting command line problems.
; 1a. New Default instance installation: (Only applies to SQL Server Express Edition)
; start /wait setup.exe /qn ADDLOCAL=ALL SQLBROWSERACCOUNT=
;1b. New Default instance installation: (Applies to all other SQL Server Editions)
; i) Install Database Services instance, Replication, connectivity and SQLXML components:
; start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER ADDLOCAL=SQL_Engine,SQL_Replication,Client_Components,Connectivity,SQLXML SAPWD=
; ii) Install Analysis Services and Analysis Services sample database:
; start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER ADDLOCAL=Analysis_Server,AnalysisDataFiles,SQL_AdventureWorksASSamples SAPWD=
; iii) Install only connectivity and SQLXML components:
; start /wait setup.exe /qb ADDLOCAL=Client_Components,Connectivity,SQLXML
; 2. New Named cluster instance installation: (This example only installs SQL Server Database Services)
; start /wait setup.exe /qn VS=
; 3. Default Standalone instance Maintenance (add/remove features):
; start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER ADDLOCAL=SQL_FullText REMOVE=SQL_Replication
; 4. Uninstall Default Standalone installation: (This removes all features of default SQL Server)
; start /wait setup.exe /qb REMOVE=ALL INSTANCENAME=MSSQLSERVER
; 5. Add nodes to cluster: (This adds new SQL Server Database Services feature to specified node)
; start /wait setup.exe /qn VS=
; 6. Remove nodes from cluster: (This removes SQL Server Database Services feature from specified node)
; start /wait setup.exe /qn VS=
; 7. Uninstall a Default cluster instance: (This removes SQL Server Database Services from the cluster)
; start /wait setup.exe /qn VS=
; 8. Rebuild system databases:
; start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1
; 9. Repair SQL Server 2005 Management Studio shortcuts:
; start /wait setup.exe /qb REINSTALL=SQL_Tools90 REINSTALLMODE=S
; 10. Repair Full-Text Search Engine registry keys:
; start /wait setup.exe /qb REINSTALL=SQL_FullText INSTANCENAME=MSSQLSERVER REINSTALLMODE=M
; 11. Repair All files, registry keys and shortcuts for a default instance:
; start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REBUILDDATABASE=1 REINSTALL=All SAPWD=
; 12. Rebuild system databases on cluster:
; start /wait setup.exe /qn VS=
;——————————————————————–
;——————————————————————–
; The following line is REQUIRED when using a settings file.
[Options]
;——————————————————————–
; If USERNAME or COMPANYNAME are not specified, the default operating system username and company name values for the server are used.
; Note: If names contain spaces, surround the names with quotes.
USERNAME=
COMPANYNAME=
;——————————————————————–
;——————————————————————–
; PIDKEY specifies the Product Identification Key.
; Usage: PIDKEY=ABCDE12345FGHIJ67890KLMNO (This is not an actual key value.)
; NOTE: PIDKEY is not required for SQL Server Express Edition.
; NOTE: Do not include “-” in the PIDKEY.
;PIDKEY=
;——————————————————————–
; INSTALLSQLDIR specifies the location for the instance specific binary files.
; Default location is: %ProgramFiles%\Microsoft SQL Server\
; To use the default path, do not specify the following parameter.
; NOTE: INSTALLSQLDIR is REQUIRED for clustered installations and must point to a local
; drive for which the drive letter exists on all nodes in the cluster definition.
; NOTE: If there is a space in a specified path, surround the path with quotes
; and end every path with a ‘\’.
INSTALLSQLDIR=
;——————————————————————–
; INSTALLSQLSHAREDDIR specifies a custom location for Integration Services
; Notification Services, Client Components, SQL Server Books Online and Samples.
; To use the default path, do not specify the following parameter. Default path is %ProgramFiles%\Microsoft SQL Server\
; If Tools components are already installed on the computer, this parameter is ignored.
; NOTE: If there is a space in a specified path, surround the path with quotes
; and end every path with a ‘\’.
; The installation paths will be:
;
;
;
INSTALLSQLSHAREDDIR=
;——————————————————————–
; INSTALLSQLDATADIR specifies the location for the SQL Server data files.
; Default: INSTALLSQLDIR\Data\
; To use the default path, do not specify the following parameter.
; NOTE: INSTALLSQLDATADIR is REQUIRED for clustered installations and must point to a
; shared drive which is a member of the cluster group specified for installation.
; NOTE: If there is a space in a specified path, surround the path with quotes
; and end every path with a ‘\’.
INSTALLSQLDATADIR=
;——————————————————————–
; INSTALLASDATADIR specifies the location for the Analysis Server Data Files.
; Default: INSTALLSQLDIR\Data\
; If Analysis Server is already installed on the computer, this parameter is ignored.
; To use the default path, do not specify the following parameter.
; NOTE: Do not use with a SQL Server Express installation.
; NOTE: If there is a space in a specified path, surround the path with quotes
; and end every path with a ‘\’.
INSTALLASDATADIR=
;——————————————————————–
; ADDLOCAL specifies which components to install. If ADDLOCAL is not
; specified, setup will fail.
; To install all components specify ‘ADDLOCAL=ALL’ on the command line.
; ADDLOCAL Rules:
; Features are case sensitive.
; To use ADDLOCAL, provide a comma-delimited list with no spaces of the
; features to install.
; Selecting a parent feature only installs the parent feature, not the parent and the child.
; For example: SQL_Engine is the parent feature, SQL_Data_Files is the child feature.
; Installing the child feature automatically installs the parent feature.
; Removing the parent feature removes the parent and all child features. See BOL for more information on this topic.
; You can also use ADDLOCAL to add components in maintenance mode.
; ————————-
; List of features and their ADDLOCAL properties for
; Developer, Enterprise, Evaluation, and Standard editions.
; For more information on the feature list supported per editions refer SQL Server Books Online.
; SQL Server Database Services SQL_Engine
; Data Files SQL_Data_Files
; Replication SQL_Replication
; Full-Text Search Engine SQL_FullText
; Analysis Server Analysis_Server
; Data Files AnalysisDataFiles
; Reporting Services RS_Server
; Report Manager RS_Web_Interface
; Notification Services Notification_Services
; Engine Components NS_Engine
; Client Components NS_Client
; Integration Services SQL_DTS
; Client Components* Client_Components
; Connectivity Components Connectivity
; Management Tools SQL_Tools90
; Business Intelligence Development Studio SQL_WarehouseDevWorkbench
; Software Development Kit SDK
; SQLXML Client Features SQLXML
; Legacy Components Tools_Legacy
; Documentation and Samples* SQL_Documentation
; SQL Server Books Online SQL_BooksOnline
; Sample Databases SQL_DatabaseSamples
; AdventureWorks Sample OLTP SQL_AdventureWorksSamples
; AdventureWorksDW Sample Data Warehouse SQL_AdventureWorksDWSamples
; AdventureWorks Sample OLAP SQL_AdventureWorksASSamples
; Sample Code and Applications SQL_Samples
; ————————-
; List of features and their ADDLOCAL properties for Workgroup edition.
; SQL Server Database Services SQL_Engine
; Data Files SQL_Data_Files
; Replication SQL_Replication
; Full-Text Search Engine SQL_FullText
; Reporting Services RS_Server
; Report Manager RS_Web_Interface
; Integration Services SQL_DTS
; Client Components* Client_Components
; Connectivity Components Connectivity
; Management Tools SQL_Tools90
; Software Development Kit SDK
; SQLXML Client Features SQLXML
; Legacy Components Tools_Legacy
; Documentation and Samples* SQL_Documentation
; SQL Server Books Online SQL_BooksOnline
; Sample Databases SQL_DatabaseSamples
; AdventureWorks Sample OLTP SQL_AdventureWorksSamples
; AdventureWorksDW Sample Data Warehouse SQL_AdventureWorksDWSamples
; AdventureWorks Sample OLAP SQL_AdventureWorksASSamples
; Sample Code and Applications SQL_Samples
;NOTE: (*) If setup is being performed using CD. Installing the Client Components or Documentation and Samples requires Disc 2. However, if you use the /qn switch to suppress the Setup user interface, Setup will fail to prompt for Disc 2, and return Error 70379 (“Please insert Disc 2″) in the Setup log file.
; ——————————————————————-
; List of features and their ADDLOCAL properties for Express edition.
; SQL Server Database Services SQL_Engine
; Data Files SQL_Data_Files
; Replication SQL_Replication
; Client Components Client_Components
; Connectivity Components Connectivity
; Software Development Kit SDK
;——————————————————————–
; List of features and their ADDLOCAL properties for Express Edition with Advanced Services.
; SQL Server Database Services SQL_Engine
; Data Files SQL_Data_Files
; Replication SQL_Replication
; Full-Text Search Engine SQL_FullText
; Reporting Services RS_Server
; Report Manager RS_Web_Interface
; Client Components Client_Components
; Connectivity Components Connectivity
; Software Development Kit SDK
; Management Studio Express SQL_SSMSEE
; ——————————————————————-
; List of features and their ADDLOCAL properties for Express Edition Toolkit.
; Client Components Client_Components
; Connectivity Components Connectivity
; Software Development Kit SDK
; Business Intelligence Development Studio SQL_WarehouseDevWorkbench
; Management Studio Express SQL_SSMSEE
; ——————————————————————-
; REMOVE specifies which components to remove.
; To remove instance aware components like SQL Server, Reporting Services, Analysis Server, etc
; the REMOVE command needs to be used in conjunction with the INSTANCENAME parameter.
; For example, to remove the instance aware component AND any non-instance aware components
; like Client_Components, you would specify the following:
; REMOVE=All INSTANCENAME=MSSQLSERVER
; NOTE: Don’t use if you have several instances installed and you still want to use client tools.
; To remove all non-instance aware components like Client_Components and SQL_DTS, specify
; REMOVE=ALL
; When REMOVE is specified for a new installation, components listed after
; ‘REMOVE=’ will NOT be included for installation. For example to install every
; component except for Reporting Services components during a new installation specify
; the following:
; ADDLOCAL=All REMOVE=RS_Server,RS_Web_Interface
; REMOVE can be used during maintenance to remove components from an existing
; installation. For example, to remove Full-Text search specify the following:
; REMOVE=SQL_FullText
; NOTE: Removing the parent feature removes both the parent and the child feature.
; See SQL Server Books Online for more information on this topic.
ADDLOCAL=
REMOVE=
;——————————————————————–
; INSTANCENAME is REQUIRED; for a default instance use MSSQLSERVER
; For shared components (IS, NS and client components) INSTANCENAME is not required.
INSTANCENAME=
;——————————————————————–
; The UPGRADE property specifies which product to upgrade.
; The allowed values are SQL_Engine, Analysis_Server, RS_Server, SQL_DTS, Notification_Services, Client_Components
; If the UPGRADE option is specified for the values (SQL_Engine, Analysis_Server, RS_Server), the INSTANCENAME parameter
; is REQUIRED to define which instance to upgrade.
; For example, to upgrade a default instance of Database Services
; use the following command line:
; UPGRADE=SQL_Engine INSTANCENAME=MSSQLSERVER
; To upgrade a named instance of SQL in which the SQL Service
; is running under a domain account, and you would like to
; add the client components and tools, run this command:
; UPGRADE=SQL_Engine INSTANCENAME=
; SQLACCOUNT=
; ADDLOCAL=Client_Components
UPGRADE=
;——————————————————————–
; The UPGRADEUSER and UPGRADEPWD properties specify the SQL login used by setup
; to connect to upgrade SQL database engine.
; If the properties are not specified NT authentication is used.
; NOTE: To upgrade to SQL Server Express editions NT authentication has to be used.
UPGRADEUSER=
UPGRADEPWD=
;——————————————————————–
; Use the SKUUPGRADE parameter when upgrading from one edition of SQL Server 2005 to another edition of SQL Server 2005.
; Important: If you use the SKUUPGRADE parameter, Setup will remove all hotfix and service pack updates from the SQL
; Server instance being upgraded. Once the edition upgrade is complete, you must reapply all hotfix and service pack updates.
; To perform an edition upgrade of a default instance of SQL Server 2005 (SQL Engine only) via unattended Setup, run
; the following command using either /qb or /qn:
; start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine SKUUPGRADE=1 /qb
SKUUPGRADE=
;——————————————————————–
; The RSUPGRADEDATABASEACCOUNT and RSUPGRADEPASSWORD properties specify the Reporting Services database account and Reporting Services upgrade password respectively.
; For example to upgrade Default instance of Reporting Services and Database Services use the following command:
;/qb ADDLOCAL=All RSCONFIGURATION=Default RSSQLLOCAL=1 RSACCOUNT=
RSUPGRADEDATABASEACCOUNT=
RSUPGRADEPASSWORD=
;——————————————————————–
; SKIPUPGRADEBLOCKERS switch can be used during unattended upgrade setup. To enable this switch Specify 1 and to disable do not use this switch.
; NOTE: During upgrade if this switch is used, Upgrade Blockers checks will run, however blocked results will be skipped.
; If Upgrade Blockers identifies blocked results, using this switch may result into failed setup.
;SKIPUPGRADEBLOCKERS=1
;——————————————————————–
; SAVESYSDB can be used during uninstall to not delete system databases.
; If SAVESYSDB is set to 1 during uninstall, the system databases are left behind in the SQL data directory.
SAVESYSDB=
;——————————————————————–
; USESYSDB specifies the root path to the data directory of the system databases during upgrade.
; For example: If the system databases were installed to “D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA”, use SAVESYSDB during uninstall to save the system databases and then use USESYSDB=”D:\Microsoft SQL Server\MSSQL.1\MSSQL” during installation of a new SQL Server instance to reuse the saved system databases.
USESYSDB=
;——————————————————————–
; The services for SQL Server and Analysis Server are set auto start. To use the *ACCOUNT settings
; make sure to specify the DOMAIN, e.g. SQLACCOUNT=DOMAINNAME\ACCOUNT
; NOTE: When installing SQL_Engine 3 accounts are REQUIRED: SQLACCOUNT, AGTACCOUNT and SQLBROWSERACCOUNT.
; SQLACCOUNT Examples:
; SQLACCOUNT=
; SQLACCOUNT=”NT AUTHORITY\SYSTEM”
; SQLACCOUNT=”NT AUTHORITY\NETWORK SERVICE”
; SQLACCOUNT=”NT AUTHORITY\LOCAL SERVICE”
; Note: To install localized versions of SQL Server, refer to the Localized Service Names table in the SQL Server Books Online topic “Setting Up Windows Service Accounts”
; Note that if SQLBrowser is already installed, SQLBROWSERACCOUNT and SQLBROWSERPASSWORD are ignored.
SQLBROWSERACCOUNT=
SQLBROWSERPASSWORD=
SQLACCOUNT=
SQLPASSWORD=
AGTACCOUNT=
AGTPASSWORD=
ASACCOUNT=
ASPASSWORD=
RSACCOUNT=
RSPASSWORD=
;——————————————————————–
; To use the *AUTOSTART features, specify 1 to start automatically or 0 to start manually.
; NOTE: If you decide to start SQL Agent automatically, then SQL is also started automatically.
; If *AUTOSTART is not specified on the command line, startup is set to manual.
SQLBROWSERAUTOSTART=0
SQLAUTOSTART=0
AGTAUTOSTART=0
ASAUTOSTART=0
RSAUTOSTART=0
;——————————————————————–
; To use Mixed mode authentication, use SECURITYMODE=SQL.
; If SECURITYMODE=SQL is not specified, then Windows Authentication only will be used by default.
; If you use SQL Authentication, you are REQUIRED to provide a strong system administrator (SA) password.
; If you use Windows Authentication, the SA account will be disabled.
; To set SA password specify SAPWD.
SECURITYMODE=
SAPWD=
;——————————————————————–
; SQLCOLLATION specifies the collation for Database Services.
; ASCOLLATION specifies the collation for Analysis Server.
; See SQL Server Books Online for more information on collation options.
SQLCOLLATION=
ASCOLLATION=
;——————————————————————–
; Information for rebuilding system databases
; When system databases are corrupted, they need to be brought back to the original installed state. To rebuild the system databases set REBUILDDATABASE=1.
; This option is only available in unattended setup; either option /qb or /qn must be used
; When system databases are rebuilt it removes all database objects and data in master, model and msdb.
; In order to rebuild system databases new sa password is REQUIRED and system collation can be provided. In addition, the following parameters need to be provided: SQLACCOUNT, SQLPASSWORD, SQLAGTACCOUNT, SQLAGTPASSWORD, SQLBROWSERACCOUNT, SQLBROWSERPASSWORD
REBUILDDATABASE=
;——————————————————————–
;REINSTALLMODE is used to repair installed components. The supported values are:
;O – Reinstall if file is missing, or an older version is present.
;M – Rewrite machine specific reg keys under HKLM
;U – Rewrite user specific reg keys under HKCU
;S – Reinstall all shortcuts
;When using REINSTALLMODE you are REQUIRED to use REINSTALL
;REINSTALL uses the same values as ADDLOCAL.
;This option is only available in unattended setup either option /qb or /qn must be used.
;For example, to repair the Management Studio shortcut in case you accidentally delete it,
;run REINSTALL=SQL_Tools90 REINSTALLMODE=S where “S” will repair only shortcuts.
; For example:
; REINSTALLMODE=S
; REINSTALLMODE=OMUS
REINSTALLMODE=
;——————————————————————–
; RSCONFIGURATION specifies the Reporting Services installation option.
; The installation options can be either “FilesOnly” or “Default”.
; The “FilesOnly” option will only install the files without configuring the Reporting Services.
; The “Default” option will install the files and configure the Reporting Services.
; If you specify RSCONFIGURATION=Default, you must set RSSQLLOCAL=1
; NOTE: If no option is specified the default is “FilesOnly”.
; For example:
; RSCONFIGURATION=Default
; RSCONFIGURATION=FilesOnly
RSCONFIGURATION=
;——————————————————————–
; The RSUPGRADEDATABASEACCOUNT and RSUPGRADEPASSWORD are used for upgrading a report service.
; The account is used to connect to SQL instance that hosts RS databases. If it is not specified
; NT authenticatuion is used.
RSUPGRADEDATABASEACCOUNT=
RSUPGRADEPASSWORD=
;——————————————————————–
; SAMPLEDATABASESERVER specifies the SQL Server instance where the sample
; databases will be attached. The parameter is used when AdventureWorks Samples or
; AdventureWorksDW Samples features are being installed.
; Sample databases need to be attached to an existing local SQL instance that is the same
; version as sample databases.
; For example: SAMPLEDATABASESERVER=
SAMPLEDATABASESERVER=
;——————————————————————–
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On
; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2
DISABLENETWORKPROTOCOLS=
;——————————————————————–
; If you enable ERRORREPORTING, SQL Server, SQL Server Agent and Analysis Server will be configured to automatically send a report to Microsoft if a fatal error occurs.
; Microsoft uses error reports to improve SQL Server functionality, and treats all information as confidential.
; To enable error reporting specify ERRORREPORTING=1
; By default ERRORREPORTING=0
ERRORREPORTING=
;——————————————————————–
; If you enable SQMREPORTING, SQL Server Products will be configured to automatically
; send periodic reports to Microsoft on feature usage. These reports are used to better understand
; how our customers use our products: which SQL services are installed, which features are being used,
; database deployment, use, and maintenance patterns, etc. This will help us determine how to improve reliability and how to better target resources in ; future offerings.
; To enable error reporting specify SQMREPORTING=1;
; By default SQMREPORTING=0
SQMREPORTING=
;——————————————————————–
; The ENABLERANU switch is used to configure Run as Normal User (User Instances).
; Set ENABLERANU = 0 to turn User Instances off.
; Set ENABLERANU = 1 (default) to turn User Instances on.
; Note: User Instances only apply to SQL Server Express SKUs.
ENABLERANU=
;——————————————————————–
; The ADDUSERASADMIN switch is used to add the setup user to the
; SQL Server 2005 System Administrator role. Members of the
; System Administrator role can perform any activity in the server.
; Set ADDUSERASADMIN=1 to add the setup user to the SQL Server System Administrator role
ADDUSERASADMIN=
;——————————————————————–
; ADMINPASSWORD parameter is used for cluster setup.
; Specify the password for the logged on user account.
; This account must be an administrator on the remote node(s).
ADMINPASSWORD=
;——————————————————————–
; The following parameters are required for cluster installations. To run a
; cluster installation, you must be on a cluster and specify /qn or /qb in the command line.
; INSTALLSQLDIR – The drive letter must exist on both machines.
; INSTALLSQLDATADIR – The shared drive letter must exist in the disk group used in the GROUP parameter.
; SQLACCOUNT,SQLPASSWORD,AGTACCOUNT,AGTPASSWORD – These must all be used.
; INSTANCENAME – For a default instance use MSSQLSERVER, see BOL for instance name rules.
; SAPWD – Strong SA Password (required for SQL Authentication)
;——————————————————————–
; REQUIRED for cluster setup – Specify the name of the virtual server.
; The name cannot exceed 15 characters and follows the same rules as for a computer name.
VS=
;——————————————————————–
; REQUIRED for cluster setup – Specify the server that needs to be installed as a cluster
; Use SQL_Engine for SQL and Analysis_Server for Analysis Server
INSTALLVS=
;——————————————————————–
; REQUIRED for cluster setup – Specify the IP addresses; provide one entry for each network adapter.
; NOTE: You must specify both address and network name.
; Format: IP=”address,network name”
; Example: IP=”xxx.xxx.xxx.xxx,Local Area Connection”
IP=
;——————————————————————–
; REQUIRED for cluster setup – Specify the Cluster group.
; This is the group that contains the disk to which the shared data files are
; to be written, and that will contain SQL Server resources.
; Example: GROUP=”Group 2″
GROUP=
;——————————————————————–
; REQUIRED for cluster setup – Specify which nodes you want in your Virtual Server.
; The nodes must exist in the cluster. For a new installation, the node that owns the disk group must be included in the node list.
; Example: ADDNODE=”node1,node2″
ADDNODE=
;——————————————————————–
; Use this to remove nodes from an existing cluster (REQUIRED ONLY when performing node maintenance)
;REMOVENODE=
;——————————————————————–
;In order to install SQL Server 2005 as a failover cluster you must create domain local groups before starting the installation.
;For each clustered service in the SQL Server instance you are installing, provide the domain name and group name.
;The groups must be specified in the format of DomainName\GroupName
;DomainName\GroupName selections must exist at the time you run SQL Server Setup.
;The account under which SQL Server Setup is running must have privileges to add accounts to the domain groups.
SQLCLUSTERGROUP=
; REQUIRED for clustered setup if SQL Engine is installed
ASCLUSTERGROUP=
; REQUIRED for clustered setup if Analysis Services is installed
AGTCLUSTERGROUP=
; REQUIRED for clustered setup if SQL Agent is installed
FTSCLUSTERGROUP=
; REQUIRED for clustered setup if Full Text Search is installed
; Example; SQLCLUSTERGROUP=”MYDOMIAN\MYSQLDOMAINGROUP” AGTCLUSTERGROUP=”MYDOMIAN\MYAGTDOMAINGROUP”