Connect to Sql Server 2005 Express for Window SharePoint Service

If you wanna to connect to the sql server 2005 express after u install window share point to your window server 2003 and when u try to connect to the ServerName\Microsoft##SSEE, it return you error.
Actualy you can't connect to WSS database remotely because network connectivity is disabled by default. ( or without SQL Server Agent Services not installed)

You can use below as ur server name like the image below, it will let you to connect to your share point database

\\.\pipe\mssql$microsoft##ssee\sql\query

The Hibernate Query Language - HQL

NHibernate is equipped with an extremely powerful query language that looks very much like SQL. Hibernate Query Language (HQL) is fully object-oriented, understanding notions like inheritence, polymorphism and association.

The simplest possible Hibernate query is of the form:
- "From Employee" - (similar to SQL "Select * From Employeetbl"
* Employee is a mapping class
if you map the table Employeetbl to a mapping class, so you can use the HQL to select from the mapping file.

Example of Mapping Class for NHibernate
<class table="Employeetbl" name="Employee" lazy="false">
<id type="string" column="Employeeid" name="Employeeid">
<generator class="increment">
</id>
<property type="string" column="EmployeeName" name="EmployeeName"></property>
</class>

NHibernate

NHibernate is a port of Hibernate Core for Java to the .NET Framework. It handles persisting plain .NET objects to and from an underlying relational database. Given an XML description of your entities and relationships, NHibernate automatically generates SQL for loading and storing the objects. Optionally, you can describe your mapping metadata with attributes in your source code.

NHibernate uses the following ways to retrieve objects from the database:

• Hibernate Query Language (HQL)
• Query By Criteria (QBC) and Query BY Example (QBE) using Criteria API
• Native SQL queries
  

NHibernate can work on these Database

• Microsoft SQL Server 2005/2000
• Oracle
• Microsoft Access
• Firebird
• PostgreSQL
• DB2 UDB
• MySQL
• SQLite

NHibernate

SQL Server Express Edition Comparison Table

Express Edition Products for SQL Server Compared
Feature	SQL Server 2005 Express Edition	SQL Server 2005 Express Edition with Advanced Services	SQL Server 2005 Express Edition Toolkit
Database Engine	x	x
Client Components	x	x	x
Full Text Search		x
Reporting Services		x
Management Studio Express		x	x
Business Intelligence Developer Studio			x

Comparation between the SQL Server Database Between:

1. SQL Server 2005 Express Edition

2. SQL Server 2005 Express Edition with Advanced Services

3. SQL Server 2005 Express Edition Toolkit

SQL Server 2005 Management Studio Express

Free to download, free to redistribute, free to embed, and easy for new developers to use immediately, a simple but powerful development environment for building data-driven applications.

The advanced query optimization engine of SQL Server 2005 delivers fast performance with full-text search. Load databases easily through the XCopy feature. Native XML support ensures that your Web environment can easily interoperate with other applications through Web services.

SQL Server 2005 Reporting Services provides a complete, server-based platform for creating, managing, and delivering traditional and interactive reports. Easily create sophisticated reports with tables, charts, and graphics, and then embed them in applications.

Configure SQL Server Express to dynamically tune database parameters to respond to changing usage characteristics. SQL Server Express delivers user updates directly using Microsoft Update, easily plugs in to most enterprise management environments, and supports either silent installs or GUI-based setup and installations.

SQL Server Express Edition

List of DBCC in SQL SERVER 2005

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases.
Example, maintenance, validation task and status checks.

DBCC CHECKALLOC - Check consistency of disk allocation.
DBCC CHECKCATALOG - Check catalog consistency
DBCC CHECKCONSTRAINTS - Check integrity of table constraints.
DBCC CHECKDB - Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP - Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT - Check identity value for a table.
DBCC CHECKTABLE - Check integrity of a table or indexed view.
DBCC CLEANTABLE - Reclaim space from dropped variable-length columns.
DBCC dllname - Unload a DLL from memory.
DBCC DROPCLEANBUFFERS - Remove all clean buffers from the buffer pool.
DBCC HELP - Help for DBCC commands.
DBCC INPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC OPENTRAN - Display information about recent transactions.
DBCC OUTPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC PROCCACHE - Display information about the procedure cache
DBCC SHOW_STATISTICS - Display the current distribution statistics
DBCC SHRINKDATABASE - Shrink the size of the database data and log files.
DBCC SHRINKFILE - Shrink or empty a database data or log file.
DBCC SQLPERF - Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE - Enable or Disable trace flags
DBCC UPDATEUSAGE - Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS - Return the SET options currently active
DBCC deprecated commands

New Feature in MySQL 6.0

1. The new Falcon transactional storage engine.

2. Support for additional Unicode character sets: utf16, utf32, and 4-byte utf8. These character sets support supplementary Unicode characters; that is, characters outside the Basic Multilingual Plane (BMP).

3. BACKUP DATABASE and RESTORE statements for backup and restore operations.

4. Optimizer enhancements for faster subqueries and joins, including batched index access of table rows for sequences of disjoint ranges by the MyISAM and InnoDB storage engines.

5.The syntax for the LOCK TABLES statement has been extended to support transactional table locks that do not commit transactions automatically. Following LOCK TABLES ... IN SHARE MODE or LOCK TABLES ... IN EXCLUSIVE MODE, you can access tables not mentioned in the LOCK TABLES statement. You can also issue LOCK TABLES statements that acquire transactional locks many times in succession, adding additional tables to the locked set, and without unlocking any tables that were locked previously. When using LOCK TABLES with IN SHARE MODE or IN EXCLUSIVE MODE, tables are not unlocked until the transaction ends.
Transactional locks acquired with LOCK TABLES are released when the transaction ends, either explicitly with COMMIT or ROLLBACK, or implicitly due to a statement that causes implicit commit or because the connection ends.

6. Support for extended comments for tables, columns, and indexes.

More details, visit here http://dev.mysql.com/doc/refman/6.0/en/mysql-nutshell.html

SQLyog

SQLyog GUI is the most powerful MySQL manager and admin tool, combining the features of MySQL Query Browser, Administrator, phpMyAdmin and various other MySQL Front Ends and MySQL clients in a single intuitive interface.

http://www.webyog.com/faq/index.html

LINQ to SQL in C#

This article provides an introduction to employing LINQ to SQL within a Windows Forms application.
Article will address the incorporation of LINQ to SQL into a win forms project, how to use LINQ to SQL to select, insert, update, and delete data, and how to use LINQ to SQL to execute stored procedures.

The article shows some simple examples of LINQ to SQL; from it you can see how easy it is to query against single and related tables and to write filtered queries, execute stored procedures, perform aggregation, and how to insert, update, and delete records from the database.


DataClasses1DataContext dc = new DataClasses1DataContext();
var q =
from a in dc.GetTable()
where a.CustomerID.StartsWith("A")
orderby a.OrderDate ascending
select a;
dataGridView1.DataSource = q;

click here for full article

LINQ tutorials convert a Linq Query resultset to DataTable object

This LINQ tutorials convert a Linq Query resultset to DataTable object.
The tutorials attached with the source code

full article, click here

LINQ Begginer Tutorials

In this tutorial, we looked at the current state of today's data access story. Then we looked at how LINQ and the new language features in C# 3.0 solve these issues by providing us with a consistent set of Standard Query Operators that we can use to query any collection that implements IEnumerable. In this installment, we only focused on in-memory collections of data in order to avoid the confusion that most people have when mixing LINQ with DLinq and XLinq, but rest assured these will be covered in the future installments of this article. Furthermore, because LINQ is just a set of methods that adhere to the naming conventions for the Standard Query Operators, anybody can implement their own LINQ based collections for accessing any other type of data.

More Details, please visit the link

Relational database management system (RDBMS)

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational model.

A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.

Not be enough virtual memory - SQL Server

For each updated database, SQL Server allocates at least one 64-KB block for use in formatting log records before they are written to disk. This allocation occurs when the first log record is generated for the database, such as during an INSERT, UPDATE, or DELETE statement. Depending on the activity and the size of the generated log records, subsequent modifications might trigger the allocation of additional 64-KB allocations. SQL Server 7.0 will allocate no more than three 54-KB blocks. In SQL Server 2000, the upper number of allocations for each database is a function of the number of processors that SQL Server is configured to use.

Solution
Use the -g startup parameter to leave additional, unreserved virtual memory available for these database allocations. The -g parameter is documented in the Readme.txt of the SQL Server 7.0 service pack, and in SQL Server 2000 Books Online. The "More Information" section in this article includes the settings that Microsoft recommends you use to determine the appropriate value for this setting.

Sql Server 2005 - Twelve Tips For Optimizing Query Performance

1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level

Original Article

LINQ - .NET Language Integrated Query

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it.

LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.

Developers can use LINQ with any data source. They can express efficient query behavior in their programming language of choice, optionally transform/shape data query results into whatever format they want, and then easily manipulate the results. LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring support when writing LINQ code.

LINQ supports a very rich extensibility model that facilitates the creation of very efficient domain-specific operators for data sources. The "Orcas" version of the .NET Framework ships with built-in libraries that enable LINQ support against Objects, XML, and Databases.

For More details about the LINQ :
Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
Part 6: Retrieving Data Using Stored Procedures
Part 7: Updating our Database using Stored Procedures
Part 8: Executing Custom SQL Expressions
Part 9: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the control)

SQL IDENTITY_INSERT

SQL IDENTITY_INSERT is allow values to be inserted into the identity column of a table.
Identity column creates a numeric sequence value for you automatic.

It only alow one table in a session can have the IDENTITY_INSERT property set to ON.
If you need to change the table, you need to set the existing table IDENTITY_INSERT back to OFF.

SET IDENTITY_INSERT TableName OFF

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

SET IDENTITY_INSERT TableName ON

SQL Get Rows Number

Below SQL Statement return rows number for each row in a partition.

select R1.CategoryName,
rank = (select count(distinct R2.CategoryName) from categories R2 where R1.CategoryName <= R2.CategoryName) from categories R1 Order by Rank

Below is the result after use the row number sql statement:

DBCC DBREINDEX

When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor.

Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.

The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database

TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.

Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)

Example
DBCC DBREINDEX ('Categories')
DBCC DBREINDEX ('Categories','Categoryname',80)

- It Can improve your database perfomance.

WITH RECOMPILE - Re Compile Execution Query Plan

WITH RECOMPILE is specified SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Ideally, you should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures. However, you can use this option during early development stages of the project where database structure is still evolving and stored procedures should be recompiled as a result of these changes.

Below is the simple example for using With Recompile in creating STORE PROCEDURE
CREATE PROCEDURE sp_MyTable WITH RECOMPILE
AS
Select Column1, column2 from Table
GO

Except the above method, you also can using with Recompile when u Execute the Store procedure in Query Analyzer.

Below is the example for using With Recompile in Query analyzer
EXEC sp_MyTable1 'Parameter1', 'Parameter2' WITH RECOMPILE

This tells SQL Server to throw away the existing query plan or do not cache the previous execution query plan and build another one query plan but only this once.

sp_changedbowner - SQL Change DB Owner

- Changes the owner of the current database.

After sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database.

The owner of the master, model, or tempdb system databases cannot be changed.
To display a list of the valid login values, execute the sp_helplogins stored procedure.
Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users who were previously aliased to dbo to the new database owner.

Only members of the sysadmin fixed server role can execute sp_changedbowner.


Example
This example makes the user Emil the owner of the current database and maps
existing aliases to the old database owner to Emil.

EXEC sp_changedbowner 'Emil'