Jean Paul's Blog

There are 2 types of People in the World, One who Likes SharePoint and..

  • Microsoft MVP

  • MindCracker MVP

  • CodeProject MVP

  • eBook on SharePoint 2010

  • eBook on Design Patterns

  • eBook on Windows Azure

  • NLayers Framework @ CodePlex

  • MSDN Forums

  • .Net vs. Java

    Due to Public Demand

Posts Tagged ‘sql server’

SQL Server–Create User / Domain User using SQL

Posted by JP on March 23, 2012

CREATE LOGIN user1WITH PASSWORD = ’MyPassword’  USE TheDbName CREATE USER user1FOR LOGIN user1;
GO
CREATE LOGIN [Domain\user1] FROM WINDOWS;
            USE TheDbName CREATE USER user1 FOR LOGIN [Domain\user1]
Advertisements

Posted in C# | Tagged: , | Leave a Comment »

Sql Server – Transact Sql to make a column unique

Posted by JP on April 23, 2011

ALTER TABLE TableName
ADD CONSTRAINT NewConstraintName
UNIQUE(ColumnName)

Posted in SQL Server | Tagged: , , | Leave a Comment »

How to shrink Sql Server Database?

Posted by JP on April 21, 2011

The Enterprise Management studio provides an option to shrink the database.

 

Step 1: Open Enterprise Management Studio (i am using sql server 2008) and select database, right click and choose Tasks>Shrink

 

image

Step 2: In the appearing dialogue press Ok to start the shrink process.

image

Posted in SQL Server | Tagged: , , | Leave a Comment »

Automatic History using Triggers

Posted by JP on February 5, 2011

I would like to take a moment to explain the following problem I am facing:

I do have a table named Equity which contains the company name and the current PE Ratio. 

image

The PE Ratio will be updated daily or weekly based on the wish of user through a web application in ASP.NET.  When the process runs for many years, there will be many updating happening to the same record. 

Problem

Our problem is to get the historical PE Ratio values, so that we can find out the maximum and minimum values for decision making.

High Cost Solution

One of the solutions, we can prefer is while updating the record from our web application, take a copy of the record and insert it into another table. 

This involves changing the associated business logic layer in c# or changing the stored procedure.  I would say  this is more time consuming and lot of code we have to write to write to manage the new table if schema changes are involved.

Simple Solution

We can use database triggers to achieve the same.  The solution steps are as following.  Belive me, this will take only 5 minutes to do.

1. Create a new History Table with same structure

2. Create a Trigger for UPDATE operation

As our History table is not involved in any business logic currently, we can use the Trigger to solve the problem and move off.

Pre-requisites

First, we have to create our original Equity table.  The script is given below.  The script was generated from Sql Server 2005.

CREATE TABLE [dbo].[Equity](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PERatio] [float] NULL,
    [ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_Equity] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Create the History Table

We can create the EquityHistory table using the following script.  Please note that there is an additional column HistoryId which keeps track of the History record as there will be multiple history records for each original table record.

CREATE TABLE [dbo].[EquityHistory](
    [HistoryId] [int] IDENTITY(1,1) NOT NULL,
    [Id] [int],
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PERatio] [float] NULL,
    [ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_EquityHistory] PRIMARY KEY CLUSTERED
(
    [HistoryId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Create the Trigger

The following Trigger script will do all the magic involved.  It says that create a trigger on table Equity whenever UPDATE statement is used.  The last line says that get the record from table inserted and insert it into the EquityHistory table

CREATE TRIGGER Equity_Update_Trigger
ON Equity
FOR UPDATE
AS
INSERT INTO EquityHistory SELECT * FROM inserted

The table inserted  is a pre-defined table insided triggers.  It will give the current record structure while using with INSERT/UPDATE triggers.

Testing the Whole

You can try inserting a new record to the original Equity table.  The corresponding record will get automatically inserted in the EquityHistory table.

Now, try changing the PERatio from the first able.  You can see a new record gets added to the history table.

image

Attachment

The attachment file contains queries to create table, insert data, select data.  You can try running the file against an sql server database.

Posted in SQL Server | Tagged: , , , | Leave a Comment »

How to create Linked Server in Sql Server?

Posted by JP on November 11, 2010

Open the Management Studio and in the Server Objects > Linked Servers, right click select New Linked Server menu.

image

In the appearing screen enter the actual remoteserver alias name as “RS”

image

Enter Product Name=’’, Data source=[YourRemoteServerInstanceName], Catalog=[DbName]

and press Ok button.

In the security tab enter map the sa user like following screen.

Note: If you are using another user to connect to the current server, use that user name for mapping.

 

image

Press Ok to complete the form.

Testing the Server

You can type any query with the prefix REMOTESERVER to access the tables.

SELECT * FROM RS.Database.dbo.Table

If you can see the results,  Congratulations!  You have done with linked servers

In case of error try the following

1. Change the Security Property (previous screen) “Be made using the security context:”

2. Set the Remote login and password in the textboxes.

3. Retry the query

Creating backup of table in remote server to local

SELECT * INTO new_table_name FROM RS.Database.dbo.Table

Posted in C# | Tagged: , | Leave a Comment »

Sql Server :: Cool Sqls

Posted by JP on November 11, 2010

Some useful sqls I am sharing here.

Description Sql
Get Row Number of any table
SELECT ROW_NUMBER() OVER (ORDER BY Name ASC) AS ROWID, * FROM  TableX
Delete Duplicate Records

WITH CTE (Col1,Col2, DuplicateCount)

AS

(

SELECT Col1,Col2,

ROW_NUMBER() OVER(PARTITION BY Col1,Col2 ORDER BY Col1,Col2) AS DuplicateCount

FROM Table1

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

Create Copy of a table

SELECT *

INTO new_table_name [IN externaldatabase]

FROM old_tablename

Create Linked Server

exec sp_addlinkedserver @server = ‘servername‘, @srvproduct = ”, @provider = ‘sqloledb’,

@datasrc = ‘serverhostname‘, @catalog = ‘dbname

Copy table from linked server to local server SELECT * INTO NewTable FROM RS.Db.dbo.RemoteTable
Transactions

BEGIN TRAN;

    SELECT * FROM Address

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    COMMIT TRAN;

    RETURN;

ERR_HANDLER:

PRINT ‘Unexpected error occurred!’

ROLLBACK TRAN

Identity Insert Enable SET IDENTITY_INSERT YourTable ON

Some Cool Sql Server Links

Insert Query Generator

http://www.codeproject.com/KB/database/InsertGeneratorPack.aspx

Posted in C# | Tagged: , , | Leave a Comment »

The system tables in Sql Server

Posted by JP on October 28, 2010

We can use the sys.tables and sys.objects to find metadata of tables and columns.

Display all User Tables

select * from sys.tables – displays all user tables

Display all Columns in a Table

select * from sys.columns where object_id in
(select object_id from sys.tables where name=’MyTable)

Posted in C# | Tagged: , | Leave a Comment »

Using xml column in sql server with XSD enabled

Posted by JP on October 18, 2010

For a long time I was wondering about why the xml type is needed in sql server.  Following are a few number of advantages on using xml column types which I found implemented in industry.

  • We can store entire xml content in the column and later query on the xml tags
  • We can store application properties like Company Name, Address etc. as xml tags and with the advantage of extensibility
  • We can reduce the number of relational tables with the power of extensibility on adding new xml elements

In this article I am trying to show how xml data types can be used in sql server along with xsd (xml schema definition)

Scenario

We need to create a Customer table having Id and Name of the customer and the order created by him.  An order contains multiple items and at a time the customer is having only one order.

The default implementation would look like:

Customer OrderHeader OrderDetail
Id Id Id
Name OrderDate Item
  CustomerId Quantity

 

We can implement the same using xml type as:

Customer
Id
Name
Orders (XML data type)

 

Step1: Creating the table

You can create the table using Sql Server Management Studio.  The Sql Server version which I am using is 2005.

image

Once created the table like above, you can try inserting data into it.

INSERT INTO Customer(Id, Name, Orders)

 

VALUES (

1,

‘Joe C’,

N‘<Orders></Orders>’

);

If you are able to execute the query successfully.  Congratulations!  That seems to be a valid the table creation.

Step2: Create the XSD

The above table design is good to proceed but not the best!  The user can store any xml in the column that would be difficult for us to parse back.  So we need to ensure that only the right format of xml can be stored in the column.  We can achieve the above using XSD enabling.

The XSD is an xml format definition which says what should be the xml data stored in.  If an attempt to save an invalid format the database will throw exception.

For creating an xsd the following sql can be used. (Please remember to delete the previous records from the table)

CREATE XML SCHEMA COLLECTION OrdersSchema AS

<schema xmlns="http://www.w3.org/2001/XMLSchema"&gt;

  <element name="Orders">

    <complexType>

      <sequence>

        <element name="Order" minOccurs="0">

                  <complexType>

                        <attribute name="Id" type="int" use="required" />

                        <attribute name="Item" type="int" use="required" />

                        <attribute name="Quantity" type="float" use="required" />

                  </complexType>

        </element>

      </sequence>

    </complexType>

  </element>

</schema>’

GO

After running the query successfully, you can ensure the schema was created in sql server by expanding the Programmability tree.

image

Step3: Set the XSD to our xml column

Open the Customer table in modify mode and select the ‘Orders’ column and se the ‘OrdersSchema’ to the Schema Collection property of it.

image

Save the table so that now onwards the XSD validation is enabled.

Testing the Table

You can test the above table by using following query:

DELETE FROM customer

 

INSERT INTO Customer(Id, Name, Orders) VALUES (1, ‘Joe C’,N‘<Orders><Order Id="1" Item="Laptop" Quantity="10"></Order></Orders>’);

INSERT INTO Customer(Id, Name, Orders) VALUES (2, ‘Dane K’,N‘<Orders><Order Id="2" Item="Pen Drive" Quantity="500"></Order></Orders>’);

INSERT INTO Customer(Id, Name, Orders) VALUES (3, ‘Mark V’,N‘<Orders><Order Id="3" Item="Mobile Phone" Quantity="30"></Order></Orders>’);

 

If you get the following results, then You are good.

(3 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

You can see the existing records by the query:

SELECT * FROM Customer;

image

Testing with a wrong Xml

You can check whether the xml schema validation is working by trying the following query which contains an “InvalidAttribute”

INSERT INTO Customer(Id, Name, Orders) VALUES (100, ‘Joe C’,N‘<Orders><Order InvalidAttribute="Value" Id="1" Item="Laptop" Quantity="10"></Order></Orders>’) 

Posted in C# | Tagged: , , , | Leave a Comment »