SQL Server Basics

Server

1.1. SQL Server development process
SQL Server is a relational database management system for Microsoft Company, but it has to start with Sybase in terms of its history. SQL Server was developed from the late the 1980s, the earliest originating in the 1987 Sybase SQL Server. SQL Server was originally developed by Microsoft, Sybase, and ashton-tate three companies, and Microsoft, Sybase and Aston-tate in 1988 to migrate the product to OS/2. Aston-tate later withdrew from the development of the product, while Microsoft and Sybase signed a common development agreement, which resulted in the publication of the SQL server,1992 year for Windows NT operating systems and the SQL Server migrated to the Windows NT platform.



After the release of SQL Server 4, Microsoft and Sybase went their separate ways in the development of SQL Server, canceling contracts and developing their own SQL Server. Microsoft focuses on SQL Server development on the Windows NT platform, while Sybase is committed to the development of SQL Server on UNIX platforms. Microsoft SQL Server is described in this article as SQL Server or MS SQL Server for short.



The SQL Server6.0 version is the first version developed entirely by Microsoft Company. Microsoft launched the SQL Server 6.5 version in 1996, followed by a dramatically changed version of version 7.0 in 1998, which has undergone a fundamental change in data storage and database engine. After two years of hard work, Microsoft developed SQL Server 2000 in September 2000, including the Enterprise, standard, development, and personal editions of the four editions. Changes from SQL Server 7.0 to SQL Server 2000 are incremental, not as large as they are from 6.5 to 7.0, but are enhanced on the basis of SQL Server 7.0.
1.2. SQL Server 2000 features 1.2.1. Internet Integration
The SQL Server 2000 database engine provides complete XML support. It also has the scalability, availability, and security features required for data storage components that make up the largest Web site. The SQL Server 2000 program Design model integrates with Windows DNA architecture to develop Web applications, and SQL Server 2000 supports features such as Chinese Query and Microsoft search services, in WEB applications Includes user-friendly queries and powerful search capabilities.
1.2.2. Scalability and Availability
The same database engine can be used on different platforms, from laptops running Microsoft windows®98 to large multiprocessor servers running the Microsoft Windows 2000 Datacenter Edition. SQL Server 2000 Enterprise Edition supports features such as federated servers, indexed views, and large memory support to enable it to upgrade to the required performance levels for the largest Web site.
1.2.3-Enterprise database capabilities
The SQL Server 2000 relational database engine supports the functionality required for today's demanding data-processing environments. The database engine fully protects data integrity while minimizing the overhead of managing thousands of users who concurrently modify the database. SQL Server 2000 distributed queries allow you to reference data from different data sources as if the data were part of a SQL Server 2000 database, while distributed transactions support adequately protecting the integrity of any distributed data update. Replication also allows you to maintain multiple copies of data while ensuring that separate copies of the data remain synchronized. You can copy a set of data to a number of mobile off users, make those users work autonomously, and then merge their modifications back to the publisher.
1.2.4. Easy to install, deploy, and use
SQL Server 2000 includes a series of management and development tools that can improve the process of installing, deploying, Managing, and using SQL Server on multiple sites. SQL Server 2000 also supports standards-based, integrated program design models with Windows DNA, making the use of SQL Server databases and data warehouses a seamless part of generating powerful scalable systems. These features enable you to quickly deliver SQL Server applications, enabling customers to implement these applications with minimal installation and management overhead.
1.2.5. Data Warehouse
A tool in SQL Server 2000 that includes extraction and profiling rollup data for online analytical processing (OLAP). SQL Server also includes tools that you can use to visually design your database and analyze your data through Chinese Query.
Operating systems supported by various versions of 1.3. SQL Server 2000

Operating system for running server software supported by Microsoft SQL Server 2000 version

Operating system

Enterprise Edition

Standard Edition

Personal Edition

Development version

Desktop Engine

SQL Server

Ce

Enterprise Evaluation Edition

Microsoft Windows DataCenter

Support

Support

Support

Support

Support

Vacant

Support

Windows Advanced Server

Support

Support

Support

Support

Support

Vacant

Support

Windows Server

Support

Support

Support

Support

Support

Vacant

Support

Windows Professional

Vacant

Vacant

Support

Support

Support

Vacant

Support

Microsoft Windows NT 4.0 Server Enterprise Edition

Support

Support

Support

Support

Support

Vacant

Support

Windows NT 4.0 Server

Support

Support

Support

Support

Support

Vacant

Support

Windows NT 4.0 Workstation

Vacant

Vacant

Support

Support

Support

Vacant

Support

Microsoft Windows 98

Vacant

Vacant

Support

Vacant

Support

Vacant

Vacant

Microsoft Windows CE

Vacant

Vacant

Vacant

Vacant

Vacant

Support

Vacant


Explains that all client software from SQL Server 2000 versions other than SQL Server Windows CE can be used in any version of Microsoft Windows NT, Microsoft Windows 2000, and Microsoft Run on Windows 98. Only server components, such as the database engine and the Analysis server, must be used on a specific version of the operating system. For example, although SQL Server 2000 Enterprise Edition does not run on Windows Professional, Windows NT Workstation, or Windows 98, you can also use SQL Server 2000 Enterprise CD Install the client software on these operating systems. The Chinese version of SQL Server 2000 does not support the English version of NT 4.0 Enterprise Edition.

All software from SQL Server CE runs exclusively on the Windows CE operating system.
1.4. Microsoft's Future "Soul"-sql Server 2005 is a secret 1.4.1. Killer code "Yukon."
It's been 15 years since Microsoft released SQL Server 1.0 in 1989, and in the 15-year SQL Server, from scratch, from small to large, has gone through the legends of time and time again. It eats into the market share of IBM and Oracle databases, and the next generation of SQL Server is becoming the core of the future of Windows operating systems. Bill Gates, who is visiting China, has repeatedly "seamless computing" at the heart of Yukon, the next generation of "Yukon" database will bring us into what kind of world?
1.4.2. "Soft" pillars of the Internet
In today's network era, data search, data storage, data classification and so on all of these have become the Internet network of the "soft" pillar, and the database system is the most critical of this pillar. If there is no database support, we simply can not find the information we need in Google or Baidu, and can not use fast and convenient e-mail, but said that the network world is completely a large database composition.



According to IDC's latest data, the global database software market appears to be on the rise, with revenues of 2003 reaching $13.6 billion trillion, up from $12.6 billion in 2002. Oracle, IBM and Microsoft currently control 75% of market share. Oracle's market share last year was 39.8%,IBM 31.3%, and Microsoft was 12.1%.



So what is a database? In the university's computer textbooks, the database is interpreted as follows: The database is a specialized system for managing data resources in the computer application system. There are many forms of data, such as text, digital, symbols, graphics, images, and sounds. Data is the object to be processed by all computer systems. One familiar approach is to make documents, the process of processing into a program file, the data involved in accordance with the requirements of the program to organize data files, using program files to call. The data file maintains a certain correspondence with the program file. With the rapid development of computer applications, this document-style approach is inadequate. For example, it makes the data common poor, not easy to transplant, in different files to store a large number of duplicate information, waste storage space, update inconvenience and so on. The database system can solve these problems. The database system does not start with a specific application, rather, it is based on the management of the data itself, which keeps all the data in the database, organizes it scientifically, and uses the database management system as an intermediary to interface with various applications or application systems to make it easy to use the data in the database.



This description is indeed very detailed, but you may look dizzy, in fact, simply said that the database is a group of data after the computer, stored in one or more files, and the management of the database software is called the database management system. A general database system can be divided into databases (database) and Data management system (DB Management System,dbms) two parts, all of which constitute the Internet "soft" pillars of all.



Microsoft SQL Server as one of many database software, after upgrading from 6.5 to 7.0 version, began to gradually become mainstream database software, and SQL Server 2000 proved that the Windows operating system can also afford high-end data applications, As a mainstream database management software for business applications, it breaks the myth that UNIX dominates large database software, and what happens to a new generation of SQL Server 2005?
1.4.3, hit Yukon core secrets.
In the next version of Microsoft SQL Server (code-named Yukon), consider more about the future development of the database and the programming capabilities of SQL Server. Developers within Microsoft have long been aware of the need to introduce more unified programming models in the future and to provide more flexibility for different data models. The unification of programming models means that ordinary data access and manipulation tasks can be done in a variety of ways, such as you can choose to use XML or MICROSOFT.NET framework or Transact-SQL (T-SQL) code.



The result of this program is a new database programming platform, which has been extended in many ways. First, the host. NET Framework the functionality of the common language runtime (CLR) extends the database to the realm of procedural programming and managed code. Secondly. NET Framework hosting integration provides powerful object database capabilities from within SQL Server. Deep support for XML is implemented through a full-featured XML data type that has all the functionality of a relational data type. In addition, server-side support for XML query (XQuery) and XML Schema definition language (XSD) standards has been added. Finally, the SQL Server Yukon contains important enhancements to the T-SQL language.



The history of XML in the SQL Server Yukon actually starts with SQL Server 2000. SQL Server 2000 introduces the ability to return relational data in XML format, loads and splits XML documents, and exposes database objects as xml-based Web services, but Yukon provides more advanced XML query capabilities, The improved Yukon will give full play to all the benefits of XML. Why is XML so critical? In fact, XML has evolved from an initial alternative HTML representation to a line format and is now viewed as a storage format. Persistent storage in XML has aroused widespread concern, and many applications of XML data types have appeared on the Internet. XML itself is a data representation format that spans any system platform, originally used as a document format, and as XML is widely recognized in large enterprises, users begin to use XML to solve difficult business problems, such as data integration. This makes XML, as a data storage format, evolve to this day, and as XML can show the same effect on any platform, XML is becoming a mainstream storage format for databases. Yukon's built-in support for XML will spark a new database technology revolution.



These new programming models and enhanced languages together create a series of programmability that complements and expands the current relational database model. The end result of this architecture is the ability to create more scalable, more reliable, more robust applications, and to improve the productivity of developers. Another result of these models is a new application framework called the SQL Service Broker-a distributed application framework for asynchronous messaging.
1.4.4 of the century Gambling in Yukon Alliance
Before we ramble about a lot of technology application advantages, you may be very curious at this time, why should we introduce such a seemingly high-end application of the database software technology? Maybe now we should solve the mystery.



The richest man on Earth predicted the future of the computer, and he thought that in the future, every ordinary computer would have a big enough super hard disk, and by that time our hard drive would be 80GB, probably 80TB, though just a GB of TB, But that means a 1000 times-fold increase in the size of the hard disk. With the NTFS format of the existing Windows storage hard disk data, it is impossible to cope with such a large hard drive data search capability. To say an image example, if you are using Windows XP at that time in a computer with 100TB hard disk space, it is likely that the time you need to defragment your disk is two days and nights, and if you're looking for a file, you'll have to wait a few hours. It feels like a return to the age of 286.



To address this thorny issue, the next-generation Windows operating system Longhorn decided to adopt a radically different programming model from previous Windows. Its core is Avalon (development code). Avalon is a new GUI library for Windows. The new Longhorn imports new features of indigo (Web Services) and WinFS (file system). Including Avalon, these three new features are called WinFX. WinFX is a new "local" API for Longhorn. Although it is added to be compatible with the current Win32 API, it is generally necessary to use WinFX in order to use the new functionality of Longhorn. WinFX belong to the current. NET framework extension. The classes used in the. NET Framework are also available in the WinFX, and the WinFX support programs run the same mechanism as. NET.



The. NET framework will perform a major version upgrade (Major Versionup) when it supplies SQL Server Yukon, with a specific date expected at the end of 2004. Yukon will run on the. NET framework. class libraries for the. NET framework can be used in the storage process (Stored procedures). The. NET Framework running in Yukon is version 2.0 and will append the multimedia-related classes not currently available in the. NET Framework 1.1. The WinFS uses Yukon engines. In other words, in Longhorn, the file system will use the database engine.



Do you understand this time, the next generation of Windows operating system, the entire file data management will introduce the structure of SQL Server management, then, our computer data query capabilities, data integration capabilities will be greatly improved. Of course, this is a crucial step for the rich to keep talking about "seamless computing," and for Microsoft, the project of integrating database software and operating systems is a big gamble, and if it succeeds, Microsoft will become a database hegemon, but if it fails, This will almost even slow down the next generation of Windows ' normal listing schedule.
1.5. SQL Server vs. Oracle comparison
Microsoft, the world's largest software manufacturer, has taken the top spot in the Windows NT database software market, with Oracle in the NT database market, according to market research from the US.



Oracle launched its own new Oracle software--oracle9i in September 2001 and began oracle9i advocacy in its "Feel Fast,think simple,think smart" slogan, the leader of the database market The--oracle company has also begun to defend the supremacy of the efforts. At this point, the database market began a new round of crowded war. Whether Oracle or Microsoft is one of the world's largest companies, Oracle software and Microsoft SQL Server Software are developed by world-class development teams, and both companies claim that their software is the best, so who is the worst of the two software?



Microsoft has no one to start with, its development process can be summed up as "desktop operating system as the beginning, and then stride into the server software, and now fully move towards the development of Internet software." Entering the database field in 1987, Microsoft SQL Server is now the world's second-largest market share for its products, and the world's software industry leader.



To bring up the database, the first company to think of, is usually oracle. Oracle was founded in 1977, initially a database-specific company designed to build a database management system, and today Oracle has developed many excellent applications and service programs, far beyond its original intention to become the world's second largest software company. Oracle has a leading position in the database field, and in 1984, first transferred the relational database to a desktop computer. Then Oracle's next version, version 5, pioneered the new concepts of distributed databases, client/server architectures, and so on. Oracle's version 6 first-line locking mode and support for Chenduo processing computers ... Oracle8 mainly adds object technology, becomes relational-object database system, Oracle8i adds Internet function. Oracle is currently one of the most widely used relational data systems in the world, with dozens of models of large, medium and small machines.



As a general-purpose database system, Oracle has complete database capabilities, which include storing large amounts of data, defining and operating concurrency control, security control, integrity control, failback, and high-level language interfaces. Oracle is also a distributed database system that supports a wide range of distributed capabilities, especially internet processing. As an application development environment, Oracle provides a user-friendly, full-featured database development tool that users have a good application development environment. Oracle uses the Pl/sql language to provide a variety of operations that are open, executable, scalable, and so on. In particular, Oracle 8i and above support object-oriented features such as support classes, methods, attributes, etc., making Oracle products known as an object/relational database system.
1.5.1. SQL Server is 1.5.1.1 compared to Oracle product features. The openness of database operating platform
Oracle can run on all major platforms, including Windows, and fully supports all industry standards, so customers can leverage a number of third-party applications, tools, gateways, and management utilities. Oracle employs an open policy that allows customers to choose a solution that best suits their specific needs. SQL Server runs only on Windows, and the goal of Microsoft's proprietary strategy is to lock customers into a Windows environment, which can be a double-edged sword, both for the development of SQL Server and its market share. Because SQL Server is tightly bundled on the Windows platform, the openness and reliability of SQL Server can only be further enhanced with the reliability, openness, and performance of the Windows operating system.
1.5.1.2. Performance price ratio
People choose Database products, of course, first consider the performance of database products, but also care about how much money to build and run a set of Oracle or SQL Server database system. The money includes not only the initial purchase of software and hardware, but also maintenance and training costs. Two companies claim to have a better performance price, in fact, Oracle focuses on product reliability and practicality, while Microsoft focuses more on product pricing. Because of the operating system, it is generally considered that SQL Server is less reliable, while Oracle's initial cost is relatively high, especially when considering the factor of tool software, Oracle is more expensive than the free tools software provided by SQL Server. Therefore, when considering what software to use, according to their own business needs and infrastructure to consider synthetically.
1.5.1.3. Tool Software
Making a database easy to install, use, and manage--grouped together as "simple to operate"--is a key factor in reducing costs. Microsoft products have a reputation for ease of use and, in many ways, SQL Server benefits from making it easier to use. SQL Server Enterprise Manager (Enterprise Manager) is an integral part of SQL Server software, providing users with an integrated management console to centrally manage multiple servers. Oracle is also made up of its own Enterprise Manager, whose performance has improved with version improvements, some even exceeding SQL Server Enterprise Manager, but it is difficult to install and some of the better components need to be purchased separately. SQL Server is seamlessly integrated with the Windows operating system, and Microsoft always bundles all the software functions as much as possible, unless the user needs other operational packages for other uses, otherwise the functionality is sufficient. and Oracle companies to Third-party software developers to develop, the cost is high, especially when someone else's software for free, this problem is even more prominent.
1.5.1.4. Database performance
That database is faster? This is actually a difficult question to answer because there are many uncertainties, including processing types, data distribution, and hardware infrastructure. In February 2002, Microsoft announced that SQL Server BATA2 version was running at a maximum of 227,079 transactions per minute on the Windows2000 operating system. Following Oracle's query, Microsoft withdrew the test results and gave a higher test result in October, nearly twice times as many as before. This test is useful in many ways, but it does not explain the operational performance of each user application. Some experience has shown that Oracle databases are superior to SQL Server for long periods of time running a large number of transactions, but SQL Server is better than Oracle databases in terms of cluster technology.
1.5.2. Looking forward to Oracle and SQL Server
Oracle9i has been released, which enhances new features for E-commerce, enhances support for Internet applications, and delivers efficient, reliable, and secure data management capabilities for large data-intensive online transaction processing (OLTP) environments, query-dense data warehouses, and demanding Internet applications.

The next generation of SQL Server 2000 Yukon is expected to be launched this year. Yukon's main enhanced feature is probably clustering, with each server itself doing data processing, managing memory, locking and transaction processing, while keeping the other and its internal connections to the cluster so that one machine in the cluster does not work and does not affect the entire system.

In short, two companies in order to take advantage of the competition, will continue to develop a better function of a more complete database management system. Oracle's advantage is that many companies have invested in the development of related hardware products and software products, and Microsoft itself is a mountain. In the end, the company will win in the fierce competition, let us wait and see, no matter who wins the negative, the benefit is always the user.