What are the MS SQL Server settings?
- Like most other SQL Servers, MSQL can be configured in so many different ways.
- The MSSQL configuration will dictate how your programs and databases behave. For example, the collation will dictate the sorts and all the searches.
- Microsoft provides a stored procedure that retrieves that information, so you can configure your own programs to match.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Server info
attribute_id attribute_name attribute_value
------------ ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 DBMS_NAME Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server Yukon - 9.00.3042
10 OWNER_TERM owner
11 TABLE_TERM table
12 MAX_OWNER_NAME_LENGTH 128
13 TABLE_LENGTH 128
14 MAX_QUAL_LENGTH 128
15 COLUMN_LENGTH 128
16 IDENTIFIER_CASE MIXED
17 TX_ISOLATION 2
18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52
19 SAVEPOINT_SUPPORT Y
20 MULTI_RESULT_SETS Y
22 ACCESSIBLE_TABLES Y
100 USERID_LENGTH 128
101 QUALIFIER_TERM database
102 NAMED_TRANSACTIONS Y
103 SPROC_AS_LANGUAGE Y
104 ACCESSIBLE_SPROC Y
105 MAX_INDEX_COLS 16
106 RENAME_TABLE Y
107 RENAME_COLUMN Y
108 DROP_COLUMN Y
109 INCREASE_COLUMN_LENGTH Y
110 DDL_IN_TRANSACTION Y
111 DESCENDING_INDEXES Y
112 SP_RENAME Y
113 REMOTE_SPROC Y
500 SYS_SPROC_VERSION 9.00.3042
(29 row(s) affected)
- All these settings a server wide.
- Changing any of these settings will affect all databases running on this SQL server.
- To change the global settings, you can use SP_CONFIGURE.
Server info for only one setting
- sp_server_info is most often used to retrieve the sort order.
EXEC SP_SERVER_INFO 18
go
attribute_id attribute_name attribute_value
------------ ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52
(1 row(s) affected)
