Connection to the SQL Sever2008 database failed to connect to the database engine problem resolution

Problems that arise:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or could not be accessed. Verify that the instance name is correct, and that SQL Server is configured to allow remote connections. (provider: Named pipe provider, ERROR:40-Unable to open connection to SQL Server) (Microsoft SQL Server, error: 2)

Reason:

MSSQLSERVER (Microsoft database server) has not been opened, so can not enter, so there is not found or access to the server, the solution is to open the Microsoft server

Solution:

Starting with the program->microsoft SQL Server, Configuration tool->sql Server Configuration Manager, select SQL Server service in SQL Servers (MSSQLSERVER), and double-click the row to talk about A window, click on the "service" to enter, the "boot mode" manually changed to Automatic (automatically can log on to SQL every time into the service status)

Expand your knowledge and enter into understanding SQL Sever 2008:

Reprinted from yeewe.com Partial abstracts:

In today's data world, companies are going to be successful and evolving, and they need to position their primary data trend vision. Microsoft's vision of a data platform will help companies meet the needs of data explosion and next-generation data-driven applications. Microsoft will continue to invest in and develop the following key areas to support their data platform vision: mission-critical enterprise data platforms, dynamic development, relational data, and business intelligence.

Learn about the Microsoft Data Platform Vision

And how SQLServer2008 meets the needs of next-generation data-driven applications.

Microsoft Data Platform Vision

Many factors cause information storage to explode. With new types of information, examples and digital video, and sensor information obtained from RFID tags, the number of digital information in the company is growing sharply. The development of normalization and globalization requires the security and availability of information storage. At the same time, the cost of disk storage is significantly reduced, allowing the company to store more data for each dollar invested. The user must quickly find the relevant information in a large amount of data. In addition, they want to use this information on any device and plan to use it every day, such as MicrosoftOffice system applications. The increasing governance of data explosion and user expectations has created many challenges for the company.

Microsoft%26amp;reg; The data platform Vision provides a solution to meet these needs, the solution is that the company can store and govern many data types, including XML, e-mail, time/calendar, files, documents, geography, etc. It also provides a rich set of services to interact with the data: Search, query, data analysis, reporting, data integration, and powerful synchronization capabilities. Users can access information from the creation to the archive on any device, from the desktop to the mobile device.

SQLServer2008 new Features

SQLServer2008 appears on the Microsoft Data Platform vision because it enables companies to run their most critical applications while reducing the cost of managing data infrastructure and sending information to all users.

This platform has the following features:

%26middot, trusted%26mdash;%26mdash, allows companies to run their most critical applications with high security, reliability, and scalability.

%26middot; Efficient%26mdash;%26mdash enables companies to reduce the time and cost of developing and managing their data infrastructures.

%26middot Intelligent%26mdash;%26mdash provides a comprehensive platform to send observations and information to your users when they need them.

One, can be trusted

In today's data-driven world, companies need to continue to access their data. SQLSERVER2008 provides powerful security features, reliability, and scalability for key mission applications.

(a) Protection of your information

On the basis of past SQLServer2005, SQLSERVER2008 has made the following improvements to enhance and extend its security:

Simple Data encryption

SQLServer2008 can encrypt the entire database, data files, and log files without having to alter the application. Encryption enables companies to meet regulatory and extreme concerns about data privacy requirements. The benefits of simple data encryption include searching for encrypted data using any scope or fuzzy query, enhancing data security to prevent unauthorized user access, and data encryption. These can be done without changing the existing application.

FOREIGN key governance

SQLSERVER2008 provides a comprehensive solution for encryption and key governance. To continually meet the need for greater security in data center information, companies invest in suppliers to govern security keys within the company. SQLSERVER2008 provides a good support for this requirement by supporting third-party key governance and hardware security Module (HSM) products.

Enhanced the Review

Use SQLServer2008 to review your data operations, which improves compliance and security. The review includes not only all the information for data modification, but also information about when to read the data. SQLServer2008 has features such as enhanced review configuration and governance in the server, which enables companies to meet a variety of regulatory requirements. SQLServer2008 can also define a review specification for each database, so a review configuration can be made separately for each database. A review configuration for the specified object makes the review perform better and the configuration more flexible.

(ii) Ensuring business sustainability

With SQLServer2008, Microsoft continues to provide the company with the ability to deliver applications that simplify governance and have high reliability.

Improved Database Mirroring

Based on SQLServer2005, SQLSERVER2008 provides a more reliable platform for enhancing database mirroring. The new features include:

%26middot; page Auto fix. SQLServer2008 requests a re-copy of the error page from the mirrored partner machine so that the primary and mirrored computers can transparently fix the 823 and 824 errors on the data page.

%26middot; improved performance. The SQLServer2008 compresses the output log stream to minimize the network bandwidth required for database mirroring.

%26middot; Strengthening of the support

(1) The SQLServer2008 includes a newly added execution counter that enables finer granularity of time spent in different stages of the Database governance system (DATABASEMANAGEMENTSYSTE,DBMS) logging.

(2) SQLServer2008 includes dynamic governance Views (Dynamicmanagementview) and extensions to existing views to show more information about the mirroring session.

Hot Add CPU

To extend existing support in SQL Server to add memory resources online, the hot-add CPU allows the database to scale as needed. In fact, CPU resources can be added to the hardware platform on which SQLServer2008 is located without the need to stop the application.

(iii) Best and predictable system performance

Companies are facing growing pressure to provide predictable responses and to govern the growing volume of data as the number of users grows. SQLSERVER2008 provides a broad collection of features that enable the execution of all workloads on your data platform to be extensible and predictable.

Collection of performance data

Performance tuning and troubleshooting is a time-consuming task for a governance administrator. To provide full executive insight to the administrator, SQLServer2008 has introduced a larger range of data acquisition, a new centralized database for storing performance data, and new reporting and monitoring tools.

Extended Events

SQL Server Extended Events is a general event-handling system for the servers system. The extended event infrastructure is a lightweight mechanism that supports capturing, filtering, and responding to events that occur during server operation. This ability to respond to events enables users to quickly diagnose run-time problems by increasing contextual association data, such as Transactsql to all event call stacks or query plan handles. Event snapping can be output in several different types, including Windows Event Tracing (EVENTTRACINGFORWINDOWS,ETW). When an extended event is output to ETW, the operating system and the application can be associated, which allows for a more comprehensive system trace.

Backup compression

Keeping disk-based backups online is expensive and time-consuming. With SQLServer2008 backup compression, the required disk I/O is reduced, the amount of storage required for online backup is reduced, and the speed of backups is significantly faster.

Data compression

Improved data compression allows data to be stored more efficiently and reduces the storage requirements of the data. Data compression also provides significant performance improvements for large, limited input/output workloads such as data warehouses.

Resource Monitor

SQLServer2008 with the introduction of resource monitors, the company can provide continuous and predictable responses to end users. The Resource Monitor enables the database administrator to define resource limits and priorities for different workloads, which allows concurrent workloads to provide stable performance for the end user.

A stable plan

SQLSERVER2008 provides a new ability to make query plans, providing better query execution stability and guesswork, enabling companies to provide stable query plans in hardware server replacements, server upgrades, and product deployments.

Second, the efficient

SQLSERVER2008 reduces the governance system,. NET architecture and Visualstudio%26amp;reg; The time and cost of TeamSystem enables developers to develop powerful next-generation database applications.

(i) Policy-based governance

As part of Microsoft's efforts to reduce the company's total cost, SQLServer2008 introduced a declarative Governance architecture (DMF), a new policy-based governance framework for the SQL Server database engine. Declarative governance provides the following benefits:

%26middot; compliance with System configuration policies

%26middot; Monitor and prevent changes to the system by creating policies that do not conform to the configuration

%26middot: Reduce the total cost of the company by simplifying governance efforts

%26middot; Using SQL Server Governance suite to find compliance issues

DMF is a policy-based system used to govern one or more SQLServer2008 instances. To use the Dmf,sqlserver policy administrator, you use the SQL Server governance suite to create policies that govern the entities on the server, such as instance, database, and other SQL Server objects. DMF consists of three components: policy governance, policy administrator for policy creation, and explicit governance. The administrator selects one or more objects to govern, and explicitly checks whether the objects conform to the specified policy or explicitly makes them conform to a policy.

Automatic governance

The policy administrator uses one of the following execution modes to automate the implementation of the policy:

%26middot; Force%26mdash;%26mdash; Use DDL triggers to block policy-violating actions

%26middot; check for changes%26mdash;%26mdash; When a change related to a policy occurs, use event notification to evaluate the policy

%26middot; check schedule%26mdash;%26mdash; Use a SQLServerAgent to work on a regular basis to evaluate a policy

(ii) Improved installation

SQLSERVER2008 provides significant improvements to the service life cycle of SQL Server, which has redesigned the installation, Setup, and configuration architecture. These improvements separate the individual installations on the computer from the configuration of the SQL Server software, which allows companies and software partners to provide recommended installation configurations.

(iii) accelerating the development process

SQL Server provides an integrated development environment and more advanced data extraction, enabling developers to create next-generation data applications while simplifying access to data.

Ado. NET Entity Framework

One trend in database developers is to define advanced business objects, or entities, and then they can match them to tables and fields in the database, and developers use advanced entities such as "customer" or "order" to display the data behind them. Ado. NET Entity Framework enables developers to design relational data with such entities. The design at this extraction level is very efficient and enables developers to take full advantage of Entity relationship modeling.

Language-level Integrated query capabilities

Microsoft's language-level integrated query capabilities (LINQ) enable developers to query data by using a governance programming language such as C # or visualbasic.net instead of SQL statements. LINQ enables seamless and powerful collection-oriented queries that can be written in the. NET framework language to run on ADO (LINQ-to-SQL). NET DataSet (LINQ-to-DataSet), ADO. NET Entity Framework (LINQ-to-entity), and to the Entity Data Service matching vendors. SQLSERVER2008 provides a new LINQ-to-SQL vendor that enables developers to use LINQ directly for SQLServer2008 tables and fields.

CLR integration and ADO-Object Services

Ado. NET's object service layer enables materialized retrieval, change tracking, and the implementation of data as the common language runtime (CLR) for sustainability. Developers use the ADO Entity Framework to program a database by using a CLR object that is governed by ADO. SQLSERVER2008 provides more efficient and optimal support for improving performance and simplifying the development process.

Servicebroker Scalability

SQLServer2008 continued to strengthen the ability of servicebroker.

%26middot; session priority%26mdash;%26mdash; Allows you to configure precedence so that the most important data is first sent and processed.

%26middot; diagnostic tools%26mdash;%26mdash; diagnostic tools Improve your ability to develop, configure, and govern solutions that use servicebroker, such as diagnosing branch loss or incorrectly configured security issues before application deployment.

Improvements to Transact-SQL

SQLSERVER2008 enhances the development experience of Transact-SQL programmers through several key improvements.

%26middot; Tablevalueparameters%26ndash; In many customer scenarios, a collection of values (rows) of a table structure is passed to a stored procedure or function on the server. These values may be used directly for inserting tables or updating tables, or for more complex data operations. A parameter with a value of table provides a simpler way to define a table type and to make the application create, assign, and pass parameters to the table structure into stored procedures and functions.

%26middot; object relevance%26mdash;%26mdash; Improvement of object relevance through the new kinds of viewing and dynamic governance features enable reliable identification of dependencies between objects. Dependency information is always the most up-to-date information about binding schemas and objects that are unbound schemas. This dependency tracks stored procedures, tables, views, functions, triggers, user-defined types, XmlSchema collections, and other objects.

%26middot; Date/time data type%26mdash;%26mdash; SQLServer2008 introduces a new date and time data type:

(1) Date%26mdash;%26mdash; A type that contains only dates

(2) Time%26mdash;%26mdash; A type that contains only time

(3) Datetimeoffset%26ndash; A date/time type that can identify the time zone

(4) Datetime2%26mdash;%26mdash; A date/time type with a more accurate second and year range than the existing datetime type

The new data type enables applications to have separate date and time types, while providing a large range of data for the accuracy of user-defined time values.

(iv) Accidental connection system

With mobile devices and active workers, accidental connectivity becomes a way of working. SQLServer2008 introduces a unified synchronization platform that enables consistent synchronization between applications, data storage, and data types. In collaboration with VisualStudio, SQLServer2008 makes it possible to quickly create accidental connection systems through the new Synchronization services provided in ADO and the offline designers in VisualStudio. SQLSERVER2008 provides support for developing cache-based, synchronous-based, and notification-based applications by changing tracking and enabling customers to perform powerful execution with minimal execution consumption.

(v) not just relational data

Applications are using an increasing number of data types, not just those supported by past databases. Based on past strong support for non-relational data, SQLSERVER2008 provides new data types that enable developers and administrators to efficiently store and govern unstructured data, such as documents and pictures. Support for governance of advanced geographic data has also been added. In addition to the new data types, SQLSERVER2008 provides a range of services for different data types, while providing reliability, security, and manageability for the data platform. The next section of this white paper describes some of the benefits of non-relational data storage.

HIERARCHYID

SQLSERVER2008 enables database applications to build tree structures in a way that is more efficient than before. HierarchyID is a new system type that can store the node values displayed in a hierarchical tree. This new type provides a flexible programming model. Executed as a CLR user-defined type (UDT), it provides several effective and useful built-in methods for creating and manipulating hierarchical nodes.

FILESTREAM data

The new Sqlserver2008filestream data type enables large binary data, such as documents and pictures, to be stored directly on an NTFS file system; Documents and pictures remain a major part of the database and maintain transactional consistency.

FILESTREAM enables traditional database-governed large binary data to be stored outside the database as separate files, which can be accessed by using an NTFS streaming API. Using the NTFS streaming API enables normal file operations to be performed efficiently while providing all the rich database services, including security and backup.

Integrated Full-Text search

Integrated full-text indexing allows for seamless conversion between full-text and relational data, while enabling full-text indexes to make high-speed text retrieval of large text fields.

Sparse columns

This feature allows null data to not take up physical space, thus providing a very efficient way to govern the empty data in the database. For example, sparse columns make the object model that typically contains a very large number of NULL values to be stored in a SQLServer2008 database does not take up much space. The sparse column also promised the administrator to create more than 1024 columns of tables.

Large user-defined types

SQLServer2008 removes the 8,000-byte limit on user-defined types, allowing users to significantly expand the size of their UDT.

Geographic information

SQLSERVER2008 provides a wide range of spatial support for consuming, expanding, and using location information in space-based applications.

%26middot; Geographic data type

This feature allows you to store spatial data that complies with industry space standards, such as the Open Geospatial Consortium (OPENGEOSPATIALCONSORTIUM,OGC) plane. This allows developers to implement a "planar earth" solution by storing polygons, dots, and lines associated with the design of planar surfaces and natural plane data such as internal space.

%26middot; Geometry data type

This feature allows you to store geospatial data and perform operations on it. Use a combination of latitude and longitude to define the area of the Earth's surface, combined with geographic data and industry standard ellipsoid (for example, WGS84 for global GPS solutions).

Third, the intelligent

Business Intelligence (BI) continues to be a key area of investment for most companies and an invaluable source of information for users at all levels of the company. SQLSERVER2008 provides a comprehensive platform for you to intelligently provide when users need it.

(i) Integration of any data

Companies continue to invest in business intelligence and data warehousing solutions to derive business value from their data. SQLSERVER2008 provides a comprehensive and scalable data warehousing platform that enables powerful analysis with a single analytics store to meet the needs of thousands of users in a few megabytes of data. The following are some of the advantages of SQLServer2008 in data warehousing.

Data compression

Data capacity in the data warehouse continues to grow as the number of operating systems increases rapidly. Data compression embedded in SQLServer2008 enables companies to store data more efficiently, while also improving performance because of reduced I/O requirements.

Backup compression

Keeping disk-based backups online is expensive and time-consuming. With SQLServer2008 backup compression, the storage required to keep online backups is reduced, and the backup speed is significantly faster because the required disk I/O is reduced.

Partitioned table parallelism

Segmentation enables companies to more effectively manage large, growing data tables, simply dividing them into manageable chunks. SQLServer2008 is built on the advantages of segmentation in SQLServer2005, which improves operational performance for large partitioned tables.

Star join query optimizer

SQLSERVER2008 provides improved query performance for common Data Warehouse scenarios. The star join query optimizer reduces query response time by identifying the Data Warehouse connection mode.

Resource Monitor

SQLServer2008 with the introduction of the Resource Monitor, the company can provide the end user with consistent and predictable response. Resource monitors enable companies to define resource limits and priorities for different workloads, which allows concurrent workloads to provide consistent performance.

Grouping settings

The grouping setting (groupingsets) is an extension of the GROUPBY conditional statement, which allows the user to define multiple groupings in the same query. The grouping setting generates a separate result set that is equivalent to unionall the rows of different groupings, making it easier and faster to aggregate queries and reports.

Capturing change data

With the capture change data, the changes are captured and placed in the change table. It captures the full content of the changes, maintains the consistency of the crosstab, and even works on cross-schema changes. This allows companies to integrate the latest information into the Data warehouse.

Mergesql statements

With the Mergesql statement, developers can more effectively handle the scenario of a data warehouse, such as checking whether a row of data exists and then performing an insert or update.

Scalable Integration Services

The two key advantages of the scalability of integration services are:

%26middot; SQL Server Integration Services (Sqlserverintegrationservices,ssis) pipeline improvements

Data integration packs can now scale more efficiently, use effective resources, and govern the largest enterprise-class workloads. This new design increases the scalability of running time to multiple processors.

%26middot; SSIS Persistent lookups

Performing lookups is the most common extraction, transformation, and loading (ETL) operation. This is especially common in data warehouses when actual records must use lookups to transform business keys into their corresponding substitutions. SSIS enhances the performance of lookups to support large tables.

(b) Send the corresponding report

SQLSERVER2008 provides a scalable business intelligence infrastructure that enables IT staff to use business intelligence across the company to govern reports and analysis of any size and complexity. SQLSERVER2008 enables companies to effectively send appropriate, personal reports to tens of thousands of users in the format and address they want. The number of users receiving Reporting Services has been greatly increased by providing the enterprise reports that users need to be sent interactively. This allows users to gain timely access to information about their own areas of insight, enabling them to make better, faster, more compliant decisions. SQLSERVER2008 enables all users to create, govern, and use reports through the following report improvements:

Enterprise Reporting Engine

With simplified deployment and configuration, reports can be sent more easily within the enterprise. This makes it easy for users to create and share reports of all sizes and complexities.

The new Report Designer

The improved Report Designer can create a wide range of reports so that the company can meet all reporting requirements. Unique display capabilities enable reports to be designed as any structure, while enhanced visualizations further enrich the user experience.

In addition, Reporting Services 2008 enables business users to edit or update existing reports in an environment that can use MicrosoftOffice, regardless of where the report was originally designed, enabling companies to derive more value from existing reports.

Powerful visualization

SQLSERVER2008 expands the visual components available in the report. Visualization tools such as maps, gauges, and charts make reports more user-friendly and understandable.

MicrosoftOffice rendering

SQLSERVER2008 provides a new MicrosoftOffice rendering that allows users to access reports directly from Word. In addition, the existing excel%26amp;reg; renderer is greatly enhanced, and it is used to support functions such as nested data regions, subreports, and merged cells. This allows users to maintain display fidelity and improve the full availability of reports created in MicrosoftOffice apps.

Microsoftsharepoint%26amp;reg; integration

SQLSERVER2008 Reporting Services integrates MicrosoftOfficeSharePointServer2007 and microsoftsharepointservices depth, providing centralized delivery and governance of enterprise reports and other business insights. This gives users access to reports that contain structured and unstructured information that is relevant to the decisions they make directly in the business portal.

(iii) Provide users with comprehensive insight

Timely access to accurate information, allowing users to quickly respond to problems, even very complex problems, is the premise of online analysis processing (ONLINEANALYTICALPROCESSING,OLAP). SQLServer2008 is based on SQLSERVER2005 's powerful OLAP capabilities, providing faster query speeds for all users. This improved performance allows companies to perform very complex analyses with many dimensions and aggregations. This execution speed is combined with the deep integration of the microsoftoffice, enabling SQLServer2008 to gain comprehensive insight for all users. SQL Server Analytics services have the following analysis benefits:

Designed as an extensible

SQLSERVER2008 enhances analytical capabilities and provides more complex calculations and aggregations, making it possible to perform more broadly. The New cube design tool helps users streamline the development of analytics infrastructure so they can build solutions to optimize performance. The bestpracticedesignalerts is embedded in this design, allowing developers to integrate real-time warnings at design time, which optimizes the design. Dimensiondesigner makes it easy to view and edit attribute relationships, and provides several built-in validation of support for ideal-scale designs, while the increased flexibility in data mining structures allows you to create multiple different models, not just filtering data.

Block calculation

Block computing provides a significant improvement in processing performance, allowing users to increase their level depth and computational complexity.

Write back to MOLAP

The new MOLAP-based writeback feature in the SQLSERVER2008 Analysis Service makes it no longer necessary to query the ROLAP partition. This gives the user a stronger use of writeback settings in the analysis application, without the need for OLAP performance.

Resource Monitor

A new Resource Monitor in SQLServer2008 provides a concrete view of resource utilization. With this resource monitor, the database administrator can quickly and easily monitor and control analysis workloads, including identifying which users are running what queries and how long they will run, which allows the administrator to better optimize the use of the server.

Guessing analysis

An improved time-series algorithm expands the guessing capability. This ability to query the data mining structure makes it easy for reports to contain properties derived from the outside of the mining model. The new cross-validation feature compares data in multiple places, sending you reliable results. These improvements in data mining together provide opportunities for better insight and richer information.

Summarize

SQLSERVER2008 provides the technologies and capabilities that companies can rely on to embrace the evolving challenges of managing data and delivering comprehensive insights to users. With a significant advantage in key areas, SQLServer2008 is a trustworthy, efficient and intelligent data platform. SQLServer2008 is a major part of the Microsoft Data Platform vision, designed to meet the current and future needs of governance and usage data.

SQLServer2008 is a major product release that has introduced many new features and key improvements, making it the most powerful and comprehensive version of SQL Server ever made available to date.

This article helps you to fully familiarize yourself with the Sqlplus command.

Remark: Tell Sqlplus that the next text is a comment, not a command.

SETHAEDSEP: Title delimiter identifies a tell Sqlplus

A character that divides a title into two or more lines.

Ttitle: Sets the header title for each page of the report.

Btitle: Sets the footer title for each page of the report.

Column: Gives sqlplus various instructions about the title, format, and processing of the column.

Breakon: Tells Sqlplus to insert spaces between parts of the report, or to break subtotals and grand totals.

Computesum: Make sqlplus calculate subtotal.

SetLineSize: Sets the maximum number of characters for the row of the report.

SetPageSize: Sets the maximum number of rows for the page.

Setnewpage: Sets the number of empty lines between the page and the page.

Spool: Moves a report that is normally displayed on the screen into a file for printing.

/**/marks the beginning and end of a comment within a SQL entry. Similar to remark.

--marks the beginning of the online comment within the SQL entry. Everything that starts the tag at the end of the bank is treated as a comment. Similar to remark.

Setpause: Makes the screen appear to pause between pages and pages.

Save: Saves the SQL query you are creating to a selected file.

Host: Sends commands to the main operating system.

Start or @: tells Sqlplus to execute instructions that have been stored in the file.

Edit: Allows you to quickly leave Sqlplus and enter the editor of your choice.

Define_editor: Tell Sqlplus the name of the editor you selected.

Exit or quit: terminates Sqlplus.

Database Archive of Microsoftsqlserver

This article will tell you how to annotate your own SQL Server, which applies to all SQL Server versions, although SQLServer2005 and later versions contain some clever settings for enhanced data archiving capabilities. Most of the principles involved in this article also apply to other relational databases such as Oracle, MySQL, MicrosoftAccess, and PostgreSQL.

Database Naming conventions

If you are designing a database from scratch, it would be wise to follow the logical data entity naming conventions. Here are some suggestions for naming your objects in a new database.

General Naming conventions

If you can, the database name should only use both literal and data characters. Avoid using hyphens because it can be difficult to write some T-SQL query commands later.

If possible, try to avoid using whitespace in your DB instance, especially when naming tables and columns. Although T-SQL can recognize these entities if enclosed in square brackets, in some cases it can also lead to confusion and code errors. Also, try to avoid using T-SQL reserved words in table and column names, such as month, year, and user.

Table Naming conventions

When we name a table, we should use the name associated with the data stored in the table. For example, a table that stores employee data for an enterprise can be named employees, with a focus on the plural form of nouns, because a table is likely to store more than one employee's information.

Some program developers attach prefixes such as t_ to the table name. These prefixes are useful if the tables refer to the source code of the application, so that the software development team can see at a glance that this is a table, not a view or other referenced entity.

Attaching a prefix (such as studentid_) to a table name that is related to its function can help us categorize the table. SQLSERVER2005 introduces the concept of patterns, which can be categorized into tables. For example, the Studentsenrollments database contains the StudentResources schema, and the related tables (StudentID, studentaddress, Studentcredits, and so on) are listed in this mode in the SQL Server Governance Studio table column.

Foreign key naming conventions

It would be useful to identify what foreign keys (ForeignKeys) are in a data table, so when you name a foreign key, you can add some fk_-like prefixes so that you can see the table's relationship more directly from the table's fields.

Stored Procedure naming conventions

It is meaningful to name the stored procedure according to its purpose. For example, GetUserID, Insertdateofbirth, Updateenrollinfo, and so on, so that the purpose of the stored procedure is at a glance.

Sometimes, you can add suffixes appropriately to represent the input parameters of a stored procedure. For example, the name Getuseridbyusernameandpassword indicates that the stored procedure returns the user ID as an input parameter with a user name and password. This can be used to differentiate stored procedures with similar functionality but with different parameters, and to add other related stored procedures, such as Getuseridbyguid and Getuseridbyapplicantid. The flaw with this naming convention is that the name of the stored procedure becomes very long.

Some developers add prefixes to all stored programs with specific tags. It is popular to add the sp_ prefix to the stored program name. However, this practice is not recommended here for two reasons. First, this has a slight impact on database performance because SQL Server looks for all stored procedures with this prefix in the primary database, although this has little impact on performance, but in advanced and enterprise systems, the impact may be magnified. Second, since Microsoft used this prefix for the system's stored procedures at design time, there is always the possibility that you name your stored procedure the same as the name of a system stored procedure. Also, a new system stored procedure may be introduced in future releases of SQL Server, which is the same as the name of your existing user stored procedure.

If you really want to add a prefix to your stored procedure, it is recommended that you use a prefix such as usp_ or SPROC_. Similarly, you can prefix a function name with FUNC_, which is useful when calling a stored program from the application source code, because it makes it clear to the software development team that this is a call to a stored procedure.

Archiving of SQL stored procedures

Don't forget to archive the T-SQL code for stored procedures and functions. Because simple query code should be self-explanatory, archiving is useful for large query code. The standardized header of a stored procedure is also useful, at least to illustrate the functionality of the stored procedure. If there is no source control system at hand, including the contents of the log changes in the header can also help us to track changes.

Database Object Description

Being able to describe a database object in a way means that you can create a self-archiving database.

Microsoft has introduced a useful feature in SQLServer7.0, which is to add a description (Description) to a table through the table Design window. SQLSERVER2000 enhances this functionality by introducing extended attributes (ExtendedProperties). However, SQLServer2000 's enterprise governance limits the ability to allow attribute editing. However, the SQL Server Governance Studio GUI in SQLServer2005 promised to edit the extended properties. Most objects in a database, such as tables, columns, views, functions, stored procedures, and the database itself, have extensible properties that can be edited. By default, only a single extended property is Ms_description. And, while you can use the Ms_description extension property to add a description to an object, if you don't have a third-party load tool, it's not very useful if you add it.

Original Address Http://www.yeewe.com/w/mssqlserver (thanks for the popularity of the author of the address)