Files / File Groups / Table Spaces / Schemas in SQL Server 2005 (Architecture) !!

Posted on 2007-10-05
Last Modified: 2012-05-05

Can you please exaplin me the architecture / building blocks for SQL Server 2005 (Like Files / Pages / File Groups / Table Spaces / Schemas etc) to know how SQL Server was built ? This will help me a lot to understand the basics of SQL Server to know how it is working...just curious.

Question by:kishore_peddi
    LVL 16

    Accepted Solution

    this is a really good explaination for Files and File Groups....
    The SQL Server database page size is 8 KB. Each page contains a header with fields such as Page Number, Object Id, LSN, Index Id, Torn bits, and Types. The actual row data is located on the remaining portion of the page. The internal database structures track the allocation state of the data pages in the database.

    Page Number
    A page number is a value from 0 through ((Max File Size/8 KB)-1). The page number multiplied by 8 KB provides the offset in the file to the first byte in the page.

    A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement. For instance, you might create a schema for sales-related data by using the following T-SQL:


    Once created, you can add objects, such as tables or stored procedures, to the schema. To do so, you must prefix them with the schema name:

    CREATE TABLE Sales.SalesPeople
        SalesPersonId INT,
        SalesPersonName VARCHAR(50)

    Just as you might expect, you can access an object by referencing it by schema name:

    SELECT *
    FROM Sales.SalesPeople

    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Check books online and the book 'Inside SQL Server "

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    SQL 2000:  Shrink LDF file 10 26
    SQL help 5 41
    How to check data in sql table 11 28
    SQL Encryption question 2 32
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now