MCTS - Microsoft SQL Server 2008, Database Development - 70-433 - Objective List Part 1 (Implementing Tables and Views)

• Create and alter tables
  
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • Understanding Tables
    
    • Table Basics
    • Data Integrity Basics
    • Special Table Types
  • Designing Tables
  • Creating and Modifying Tables
  • Viewing a Table
• Computed Columns
  
  • Computed Columns
  • COLUMNPROPERTY
  • How to: Specify Computed Columns
  • How to use computed columns to improve query performance
• Persisted Columns
  
  • Look at Computed Columns (PERSISTED KEYWORD)
• Schemas
  
  • User-Schema Separation
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • GRANT
  • GRANT SCHEMA Premission
  • DENY
  • DENY SCHEMA Premission
  • REVOKE
  • REVOKE SCHEMA Premission
  • How to: Create a Database Schema
• Scripts to deploy changes to multiple environments
  
  • How to: Build a Database Project to Generate a Compiled Schema (.dbschema) File
  • How to: Deploy Changes to New or Existing Databases
  • How to: Configure Properties for Deployment Details
  • An Overview of Database Build and Deployment
  • An Overview of Database Project Settings
  • Troubleshooting Database Project, Build, and Deployment Issues
• Create and alter views
  
  • CREATE VIEW
  • ALTER VIEW
  • DROP VIEW
  • ENCRYPTION - look at CREATE VIEW
  • sys.syscomments
  • SCHEMABINDING - look at CREATE VIEW
  • WITH CHECK OPTION - look at CREATE VIEW
• Create and alter indexes
  
  • CREATE INDEX
  • CREATE SPATIAL INDEX
  • ALTER INDEX
  • DROP INDEX
  • CREATE XML INDEX
  • XML Indexes in SQL Server 2005
  • Undestanding Indexes
  • Designing Indexes
  • Tables and Index Data Structures Architecture
  • General Index Design Guidelines
  • Determining Index Disk Space Requirements
  • Filtered Index Design Guidelines
  • Index with Included Columns
  • Unique Index Design Guidlines
  • Clustered Index Design Guidlines
  • Clustered Index Structures
  • Nonclustered Index Design Guidlines
  • Nonclustered Index Structures
  • Heap Structures
  • Optimizing Indexes
    
    • Reorganizing and Rebuilding Indexes
    • Fill Factor
  • CREATE STATISTICS
  • Using Statistics to Improve Query Performance
  • Designing Indexed Views
  • Creating Indexed Views
• Create and modify constraints
  
  • Data Integrity Basics
    
    • Constraints
    • Rules
    • Defaults
  • Enforcing Data Integrity
    
    • PRIMARY KEY Constraints
    • FOREIGN KEY Constraints
    • Cascading Referential Integrity Constraints
    • UNIQUE Constraints
    • CHECK Constraints
    • DEFAULT Constraints
    • Allowing Null Values
  • Creating and Modifying PRIMARY KEY Constraints
  • Creating and Modifying FOREIGN KEY Constraints
  • Creating and Modifying UNIQUE Constraints
  • Creating and Modifying CHECK Constraints
  • Creating and Modifying DEFAULT Definitions
  • Creating and Modifying Identifier Columns
  • Disabling Indexes
    
    • Guidelines for Disabling Indexes and Constraints
    • Guidelines for Enabling Indexes and Constraints
  • SET IDENTITY_INSERT (Transact-SQL)
• Implement Data Types
  
  • FILESTREAM Overview
  • FILESTREAM Storage in SQL Server 2008
  • Getting Started with FILESTREAM Storage
  • How to: Enable FILESTREAM
  • How to: Create a FILESTREAM-Enabled Database
  • How to: Create a Table for Storing FILESTREAM Data
  • Using FILESTREAM Storage in Client Applications
  • Using FILESTREAM with Other SQL Server Features
  • FILESTREAM Best Practices
  • Spatial Data Support In SQL Server 2008
  • Working with Spatial Data (Database Engine)
    
    • Types of Spatial Data
    • Spatial Reference Identifiers (SRIDs)
    • Getting Started with the geometry Data Type
    • Getting Started with the geography Data Type
  • Designing and Implementing Structured Storage (Database Engine)
  • Designing and Implementing Semistructured Storage (Database Engine)
  • Designing and Implementing Spatial Storage (Database Engine)
  • Using SQL Server Collations
  • Selecting a SQL Server Collation
• Implement partitioning solutions
  
  • Partitioning
  • Partitioned Tables and Indexes
    
    • Understanding Partitioning
      
      • Partitioned Table and Index Concepts
    • Designing Partitioned Tables and Indexes
      
      • Planning Guidelines for Partitioned Tables and Indexes
      • Designing Partitions to Manage Subsets of Data
      • Designing Partitions to Improve Query Performance
      • Special Guidelines for Partitioned Indexes
    • Implementing Partitioned Tables and Indexes
      
      • Creating Partitioned Tables and Indexes
      • Modifying Partitioned Tables and Indexes
      • Querying Data and Metadata from Partitioned Tables and Indexes
  • CREATE PARTITION FUNCTION (Transact-SQL)
  • CREATE PARTITION SCHEME (Transact-SQL)
  • ALTER PARTITION FUNCTION (Transact-SQL)
  • ALTER PARTITION SCHEME (Transact-SQL)
  • MERGE
  • Creating Distributed Partitioned Views
  • Modifying Data in Partitioned Views
  • Resolving Distributed Partitioned Views
  • Using Partitioned Views
  • Linking Servers

Convert object to byte array and vice versa

Convert an object to byte array

private byte[] ObjectToByteArray(Object obj)
{
    if(obj == null)
        return null;
    BinaryFormatter bf = new BinaryFormatter();
    MemoryStream ms = new MemoryStream();
    bf.Serialize(ms, obj);
    return ms.ToArray();
}

Convert a byte array to object

private Object ByteArrayToObject(byte[] arrBytes)
{
    MemoryStream memStream = new MemoryStream();
    BinaryFormatter binForm = new BinaryFormatter();
    memStream.Write(arrBytes, 0, arrBytes.Length);
    memStream.Seek(0, SeekOrigin.Begin);
    Object obj = (Object) binForm.Deserialize(memStream);
    return obj;
}

New article on CodeProject.com: SQL Server Database Comparison Tool

Before a couple of days I have posted new article on CodeProject.com.
This new article is about comparison of databases and how to perform comparison of two databases using Server Management Objects in C#.
More information you can find here.

Synchronized RichTextBox objects (or how to scroll 2 RichTextBox objects together)

In my previous posts I have described, how to create synchronized (or linked) ListBox and ListView objects. Now I continue in investigation in this area and I have found another interesting example which describes synchronization of two RichTextBox objects. First step is to create class which derives from RichTextBox class.

public class SynchronizedScrollRichTextBox : System.Windows.Forms.RichTextBox
{
    public event vScrollEventHandler vScroll;
    public delegate void vScrollEventHandler(System.Windows.Forms.Message message);

    public const int WM_VSCROLL = 0x115;

    protected override void WndProc(ref System.Windows.Forms.Message msg)
    {
        if (msg.Msg == WM_VSCROLL)
        {
            if (vScroll != null)
            {
                vScroll(msg);
            }
        }
        base.WndProc(ref msg);
    }

    public void PubWndProc(ref System.Windows.Forms.Message msg)
    {
        base.WndProc(ref msg);
    }
}

I have created two SynchronizedScrollRichTextBox controls and placed them on the form. Then I have added following code (vScroll event):

private void synchronizedScrollRichTextBox1_vScroll(Message message)
{
    message.HWnd = synchronizedScrollRichTextBox2.Handle;
    synchronizedScrollRichTextBox2.PubWndProc(ref message);
}

Here you can find original post.

Synchronized ListView objects (or how to scroll 2 ListView objects together)

In my previous post I have described how to synchronize two ListBox objects. Here I will show, how to synchronize two ListView objects.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;

namespace SyncLists
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.listView1.View = View.Details;
            this.listView2.View = View.Details;
            for (int j = 0; j < 10; j++)
            {
                this.listView1.Columns.Add("c" + j.ToString());
                this.listView2.Columns.Add("c" + j.ToString());
            }
            for (int j = 0; j < 300; j++)
            {
                ListViewItem item = new ListViewItem("item" + j.ToString());
                for (int k = 1; k < 10; k++)
                {
                    item.SubItems.Add("sub item" + k.ToString());
                }
                this.listView1.Items.Add(item);
                this.listView2.Items.Add(item.Clone() as ListViewItem);
            }

            this.listView1.Scroll += new ListViewEx.ScrollHandler(listView1_Scroll);
        }

        [DllImport("user32.dll")]
        static extern IntPtr SendMessage(IntPtr hWnd, int Msg, IntPtr wParam, IntPtr lParam);
        [DllImport("user32.dll")]
        static extern int SetScrollPos(IntPtr hWnd, int nBar, int nPos, bool bRedraw);

        private const int WM_HSCROLL = 0x114;
        private const int WM_VSCROLL = 0x115;
        private const int SBS_HORZ = 0;
        private const int SBS_VERT = 1;

        void listView1_Scroll(object sender, MyScrollEventArgs e)
        {
            ListViewEx listVw = sender as ListViewEx;

            Int16 hi = (Int16)((int)e.WParam >> 16);//position
            Int16 lo = (Int16)e.WParam;//scroll type

            if (e.Orientation == ScrollOrientation.VerticalScroll)
            {
                if (lo == 5) //SB_THUMBTRACK
                {
                    if (SetScrollPos(this.listView2.Handle, SBS_VERT, hi, true) != 0)
                    {
                        SendMessage(this.listView2.Handle, WM_VSCROLL,
                          (IntPtr)(4 + 0x10000 * hi), IntPtr.Zero);
                    }
                }
                else
                {
                    SendMessage(this.listView2.Handle, WM_VSCROLL, e.WParam, IntPtr.Zero);
                }
            }
            if (e.Orientation == ScrollOrientation.HorizontalScroll)
            {
                if (lo == 5)//SB_THUMBTRACK
                {
                    SetScrollPos(this.listView2.Handle, SBS_HORZ, hi, true);
                    SendMessage(this.listView2.Handle, WM_HSCROLL,
                       (IntPtr)(4 + 0x10000 * hi), IntPtr.Zero);
                }
                else
                {
                    SendMessage(this.listView2.Handle, WM_HSCROLL, e.WParam, IntPtr.Zero);
                }
            }
        }

    }

    public class ListViewEx : ListView
    {
        public delegate void ScrollHandler(object sender, MyScrollEventArgs e);
        public event ScrollHandler Scroll;
        public void OnScroll(MyScrollEventArgs e)
        {
            if (Scroll != null)
            {
                Scroll(this, e);
            }
        }

        private const int WM_HSCROLL = 0x114;
        private const int WM_VSCROLL = 0x115;

        protected override void WndProc(ref Message m)
        {
            if (m.Msg == WM_VSCROLL)
            {
                MyScrollEventArgs e = new MyScrollEventArgs();
                e.WParam = m.WParam;
                e.Orientation = ScrollOrientation.VerticalScroll;
                OnScroll(e);
            }
            if (m.Msg == WM_HSCROLL)
            {
                MyScrollEventArgs e = new MyScrollEventArgs();
                e.WParam = m.WParam;
                e.Orientation = ScrollOrientation.HorizontalScroll;
                OnScroll(e);
            }
            base.WndProc(ref m);
        }
    }

    public class MyScrollEventArgs : EventArgs
    {
        private ScrollOrientation orientation;
        private IntPtr wParam;

        public IntPtr WParam
        {
            get { return wParam; }
            set { wParam = value; }
        }

        public ScrollOrientation Orientation
        {
            get { return orientation; }
            set { orientation = value; }
        }
    }
}

Synchronized ListBox objects (or how to scroll 2 ListBox objects together)

Before couple of days I had a task where I must synchronize two ListBox objects. I have fount out, that ListBox object doesn't have any property which allow to accomplish this. After a few hours of googling I found code of class that inherited from ListBox class. Here is the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.ComponentModel;
using System.Runtime.InteropServices;
namespace SyncLists
{
    class SyncListBox : System.Windows.Forms.ListBox
    {
        [Category("Action")]

        private const int WM_HSCROLL = 0x114;
        private const int WM_VSCROLL = 0x115;

        public event ScrollEventHandler OnHorizontalScroll;
        public event ScrollEventHandler OnVerticalScroll;


        private const int SB_LINEUP = 0;
        private const int SB_LINELEFT = 0;
        private const int SB_LINEDOWN = 1;
        private const int SB_LINERIGHT = 1;
        private const int SB_PAGEUP = 2;
        private const int SB_PAGELEFT = 2;
        private const int SB_PAGEDOWN = 3;
        private const int SB_PAGERIGHT = 3;
        private const int SB_THUMBPOSITION = 4;
        private const int SB_THUMBTRACK = 5;
        private const int SB_PAGETOP = 6;
        private const int SB_LEFT = 6;
        private const int SB_PAGEBOTTOM = 7;
        private const int SB_RIGHT = 7;
        private const int SB_ENDSCROLL = 8;

        private const int SIF_TRACKPOS = 0x10;
        private const int SIF_RANGE = 0x1;
        private const int SIF_POS = 0x4;
        private const int SIF_PAGE = 0x2;
        private const int SIF_ALL = SIF_RANGE | SIF_PAGE | SIF_POS | SIF_TRACKPOS;

        [DllImport("user32.dll", SetLastError = true)]
        private static extern int GetScrollInfo(
        IntPtr hWnd, int n, ref ScrollInfoStruct lpScrollInfo);


        private struct ScrollInfoStruct
        {
            public int cbSize;
            public int fMask;
            public int nMin;
            public int nMax;
            public int nPage;
            public int nPos;
            public int nTrackPos;
        }

        protected override void WndProc(ref System.Windows.Forms.Message msg)
        {
            if (msg.Msg == WM_HSCROLL)
            {
                if (OnHorizontalScroll != null)
                {
                    ScrollInfoStruct si = new ScrollInfoStruct();
                    si.fMask = SIF_ALL;
                    si.cbSize = Marshal.SizeOf(si);
                    GetScrollInfo(msg.HWnd, 0, ref si);

                    if (msg.WParam.ToInt32() == SB_ENDSCROLL)
                    {
                        ScrollEventArgs sargs = new ScrollEventArgs(
                        ScrollEventType.EndScroll,
                        si.nPos);
                        OnHorizontalScroll(this, sargs);
                    }
                }
            }
            if (msg.Msg == WM_VSCROLL)
            {
                if (OnVerticalScroll != null)
                {
                    ScrollInfoStruct si = new ScrollInfoStruct();
                    si.fMask = SIF_ALL;
                    si.cbSize = Marshal.SizeOf(si);
                    GetScrollInfo(msg.HWnd, 0, ref si);

                    if (msg.WParam.ToInt32() == SB_ENDSCROLL)
                    {
                        ScrollEventArgs sargs = new ScrollEventArgs(
                        ScrollEventType.EndScroll,
                        si.nPos);
                        OnVerticalScroll(this, sargs);
                    }
                }
            }
            base.WndProc(ref msg);
        }


        private void InitializeComponent()
        {
            this.SuspendLayout();
            // 
            // scrolled
            // 
            this.Size = new System.Drawing.Size(120, 95);
            this.ResumeLayout(false);
        }
    }
}

add two SyncListBox objects on the form and add following code:

private void syncListView2_OnVerticalScroll(object sender, ScrollEventArgs e)
{
    syncListView1.TopIndex = syncListView2.TopIndex;
}

private void syncListView1_OnVerticalScroll(object sender, ScrollEventArgs e)
{
    syncListView2.TopIndex = syncListView1.TopIndex;
}

T-SQL - Find which Object resides in which FileGroup

Select Distinct OBJECT_NAME(SI.object_id) AS ObjectName
,OBJECTPROPERTYEX(SI.object_id,'BaseType') AS ObjectType
,FG.Name AS FileGroupName
from sys.indexes SI , sys.filegroups FG
where SI.data_space_id = FG.data_space_id
Order by ObjectName