Display only last charecters and replace all other digits will be by "X"

There was a request that confidential numbers should be displayed so that only last four digits are displayed and all other digits will be replaced by "X".
I planned to do it in DB and started to search a method to do it. Initially I thought I will need function but after spending some time I found that there is no need to write any function.
Following is the Query that can be used
SELECT  REPLICATE('X',LEN(AccountNo)-4)+RIGHT(RTRIM(AccountNo),4) as AccountNo,AccountNo as s from Accounts

Indexed view

Views are very useful for fetching data from multiple tables. If data is very huge, then performance of view goes very down. Microsoft has provided now ability to increase performance by indexing view. On MSDN it is explained in great details at http://msdn.microsoft.com/en-us/library/aa933148%28SQL.80%29.aspx

How to create

We will go by example we will create two tables which will have large data.

CREATE TABLE Items(

ItemID INT PRIMARY KEY,

Dsc VARCHAR(20),

)

GO

CREATE TABLE CustOrders(

OrderID BIGINT PRIMARY KEY,

CustNo BIGINT,

ItemID VARCHAR(20),

QTY INT)

GO

CREATE VIEW [dbo].[MyView]

WITH SCHEMABINDING

AS

SELECT dbo.CustOrders.OrderID, dbo.CustOrders.CustNo, dbo.Items.Dsc, dbo.CustOrders.QTY

FROM dbo.CustOrders INNER JOIN

dbo.Items ON dbo.CustOrders.ItemID = dbo.Items.ItemID

GO

Indexed views:

•      Must be created the WITH SCHEMABINDING view option
•      May only refer to base tables in the same database.
•      If there is a GROUP BY clause, the view may not have a HAVING, CUBE, or ROLLUP.
•      May not have an OUTER JOIN clause.
•      May not have a UNION.
•      May not have DISTINCT or TOP clauses
•      May not have full-text predicates such as CONATINSTABLE
•      May not have a ROWSET function such as OPENROWSET
•      May not use derived tables or subqueries.
•      Must be created with ANSI_NULLS ON and QUOTED_IDENTIFIER ON

Delete all data in the database SQL server

Many times there is scenario where you need to delete all of the data in your database, you can do it easily using the MSForEachTable stored procedure. First you need disable referential integrity checks so you can delete data from parent tables.

-- disable referential integrity

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

EXEC sp_MSForEachTable 'DELETE FROM ?'

GO

-- enable referential integrity again

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO