My Alesson

what is view in SQL server?

2018-11-18T07:58:00.000-08:00

In designing a partitioning scheme, it must be clear what data belongs to each partition. For example, the data for the Customers table is distributed in three member tables in three server locations: Customers_33 on Server1, Customers_66 on Server2, and Customers_99 on Server3.

A partitioned view on Server1 is defined in the following way:

--Partitioned view as defined on Server1  
CREATE VIEW Customers  
AS  
--Select from local member table.  
SELECT *  
FROM CompanyData.dbo.Customers_33  
UNION ALL  
--Select from member table on Server2.  
SELECT *  
FROM Server2.CompanyData.dbo.Customers_66  
UNION ALL  
--Select from member table on Server3.  
SELECT *  
FROM Server3.CompanyData.dbo.Customers_99;

Generally, a view is said to be a partitioned view if it is of the following form:

SELECT <select_list1>  
FROM T1  
UNION ALL  
SELECT <select_list2>  
FROM T2  
UNION ALL  
...  
SELECT <select_listn>  
FROM Tn;

Conditions for Creating Partitioned Views

The select list
- All columns in the member tables should be selected in the column list of the view definition.
- The columns in the same ordinal position of each select list should be of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.
  
  Also, at least one column (for example <col>) must appear in all the select lists in the same ordinal position. This <col> should be defined in a way that the member tables T1, ..., Tn have CHECK constraints C1, ..., Cn defined on <col>, respectively.
  
  Constraint C1 defined on table T1 must be of the following form:
```
C1 ::= < simple_interval > [ OR < simple_interval > OR ...]  
< simple_interval > :: =   
< col > { < | > | \<= | >= | = < value >}   
| < col > BETWEEN < value1 > AND < value2 >  
| < col > IN ( value_list )  
| < col > { > | >= } < value1 > AND  
< col > { < | <= } < value2 >  
```
- The constraints should be in such a way that any specified value of <col> can satisfy, at most, one of the constraints C1, ..., Cn so that the constraints should form a set of disjointed or nonoverlapping intervals. The column <col> on which the disjointed constraints are defined is called the partitioning column. Note that the partitioning column may have different names in the underlying tables. The constraints should be in an enabled and trusted state for them to meet the previously mentioned conditions of the partitioning column. If the constraints are disabled, re-enable constraint checking by using the CHECK CONSTRAINT constraint_name option of ALTER TABLE, and using the WITH CHECK option to validate them.
  
  The following examples show valid sets of constraints:
```
{ [col < 10], [col between 11 and 20] , [col > 20] }  
{ [col between 11 and 20], [col between 21 and 30], [col between 31 and 100] }  
```
- The same column cannot be used multiple times in the select list.
Partitioning column
- The partitioning column is a part of the PRIMARY KEY of the table.
- It cannot be a computed, identity, default, or timestamp column.
- If there is more than one constraint on the same column in a member table, the Database Engine ignores all the constraints and does not consider them when determining whether the view is a partitioned view. To meet the conditions of the partitioned view, there should be only one partitioning constraint on the partitioning column.
- There are no restrictions on the updatability of the partitioning column.
Member tables, or underlying tables T1, ..., Tn
- The tables can be either local tables or tables from other computers that are running SQL Server that are referenced either through a four-part name or an OPENDATASOURCE- or OPENROWSET-based name. The OPENDATASOURCE and OPENROWSET syntax can specify a table name, but not a pass-through query. For more information, see OPENDATASOURCE (Transact-SQL) and OPENROWSET (Transact-SQL).
  
  If one or more of the member tables are remote, the view is called distributed partitioned view, and additional conditions apply. They are described later in this section.
- The same table cannot appear two times in the set of tables that are being combined with the UNION ALL statement.
- The member tables cannot have indexes created on computed columns in the table.
- The member tables should have all PRIMARY KEY constraints on the same number of columns.
- All member tables in the view should have the same ANSI padding setting. This can be set by using either the user options option in sp_configure or the SET statement.

Conditions for Modifying Data in Partitioned Views

The following restrictions apply to statements that modify data in partitioned views:

The INSERT statement must supply values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow for null values. For those member table columns that have DEFAULT definitions, the statements cannot explicitly use the keyword DEFAULT.
The value being inserted into the partitioning column should satisfy at least one of the underlying constraints; otherwise, the insert action will fail with a constraint violation.
UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause, even if the column has a DEFAULT value defined in the corresponding member table.
Columns in the view that are an identity column in one or more of the member tables cannot be modified by using an INSERT or UPDATE statement.
If one of the member tables contains a timestamp column, the data cannot be modified by using an INSERT or UPDATE statement.
If one of the member tables contains a trigger or an ON UPDATE CASCADE/SET NULL/SET DEFAULT or ON DELETE CASCADE/SET NULL/SET DEFAULT constraint, the view cannot be modified.
INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.
Bulk importing data into a partitioned view is unsupported by bcp or the BULK INSERT and INSERT ... SELECT * FROM OPENROWSET(BULK...) statements. However, you can insert multiple rows into a partitioned view by using the INSERT statement.

Note

To update a partitioned view, the user must have INSERT, UPDATE, and DELETE permissions on the member tables.

Additional Conditions for Distributed Partitioned Views

For distributed partitioned views (when one or more member tables are remote), the following additional conditions apply:

A distributed transaction will be started to guarantee atomicity across all nodes affected by the update.
The XACT_ABORT SET option should be set to ON for INSERT, UPDATE, or DELETE statements to work.
Any columns in remote tables of type smallmoney that are referenced in a partitioned view are mapped as money. Therefore, the corresponding columns (in the same ordinal position in the select list) in the local tables must also be of type money.
Under database compatibility level 110 and higher, any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as smalldatetime. Corresponding columns (in the same ordinal position in the select list) in the local tables must be smalldatetime. This is a change in behavior from earlier versions of SQL Server in which any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as datetime and corresponding columns in local tables must be of type datetime. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).
Any linked server in the partitioned view cannot be a loopback linked server. This is a linked server that points to the same instance of SQL Server.

The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE actions that involve updatable partitioned views and remote tables.

When the member tables and partitioned view definition are in place, the SQL Server query optimizer builds intelligent plans that use queries efficiently to access data from member tables. With the CHECK constraint definitions, the query processor maps the distribution of key values across the member tables. When a user issues a query, the query processor compares the map to the values specified in the WHERE clause, and builds an execution plan with a minimal amount of data transfer between member servers. Therefore, although some member tables may be located in remote servers, the instance of SQL Server resolves distributed queries so that the amount of distributed data that has to be transferred is minimal.

Considerations for Replication

To create partitioned views on member tables that are involved in replication, the following considerations apply:

If the underlying tables are involved in merge replication or transactional replication with updating subscriptions, the uniqueidentifier column should also be included in the select list.

Any INSERT actions into the partitioned view must provide a NEWID() value for the uniqueidentifier column. Any UPDATE actions against the uniqueidentifier column must supply NEWID() as the value because the DEFAULT keyword cannot be used.
The replication of updates made by using the view is the same as when tables are replicated in two different databases: the tables are served by different replication agents and the order of the updates is not guaranteed.

Permissions

Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

Examples

The following examples use the AdventureWorks 2012 or AdventureWorksDW database.

A. Using a simple CREATE VIEW

The following example creates a view by using a simple SELECT statement. A simple view is helpful when a combination of columns is queried frequently. The data from this view comes from the HumanResources.Employee and Person.Person tables of the AdventureWorks2012 database. The data provides name and hire date information for the employees of Adventure Works Cycles. The view could be created for the person in charge of tracking work anniversaries but without giving this person access to all the data in these tables.

CREATE VIEW hiredate_view  
AS   
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate  
FROM HumanResources.Employee e   
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;  
GO

B. Using WITH ENCRYPTION

The following example uses the WITH ENCRYPTION option and shows computed columns, renamed columns, and multiple columns.

Applies to: SQL Server 2008 through SQL Server 2017 and SQL Database.

CREATE VIEW Purchasing.PurchaseOrderReject  
WITH ENCRYPTION  
AS  
SELECT PurchaseOrderID, ReceivedQty, RejectedQty,   
    RejectedQty / ReceivedQty AS RejectRatio, DueDate  
FROM Purchasing.PurchaseOrderDetail  
WHERE RejectedQty / ReceivedQty > 0  
AND DueDate > CONVERT(DATETIME,'20010630',101) ;  
GO

C. Using WITH CHECK OPTION

The following example shows a view named SeattleOnly that references five tables and allows for data modifications to apply only to employees who live in Seattle.

CREATE VIEW dbo.SeattleOnly  
AS  
SELECT p.LastName, p.FirstName, e.JobTitle, a.City, sp.StateProvinceCode  
FROM HumanResources.Employee e  
INNER JOIN Person.Person p  
ON p.BusinessEntityID = e.BusinessEntityID  
    INNER JOIN Person.BusinessEntityAddress bea   
    ON bea.BusinessEntityID = e.BusinessEntityID   
    INNER JOIN Person.Address a   
    ON a.AddressID = bea.AddressID  
    INNER JOIN Person.StateProvince sp   
    ON sp.StateProvinceID = a.StateProvinceID  
WHERE a.City = 'Seattle'  
WITH CHECK OPTION ;  
GO

D. Using built-in functions within a view

The following example shows a view definition that includes a built-in function. When you use functions, you must specify a column name for the derived column.

CREATE VIEW Sales.SalesPersonPerform  
AS  
SELECT TOP (100) SalesPersonID, SUM(TotalDue) AS TotalSales  
FROM Sales.SalesOrderHeader  
WHERE OrderDate > CONVERT(DATETIME,'20001231',101)  
GROUP BY SalesPersonID;  
GO

E. Using partitioned data

The following example uses tables named SUPPLY1, SUPPLY2, SUPPLY3, and SUPPLY4. These tables correspond to the supplier tables from four offices, located in different countries/regions.

--Create the tables and insert the values.  
CREATE TABLE dbo.SUPPLY1 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),  
supplier CHAR(50)  
);  
CREATE TABLE dbo.SUPPLY2 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),  
supplier CHAR(50)  
);  
CREATE TABLE dbo.SUPPLY3 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),  
supplier CHAR(50)  
);  
CREATE TABLE dbo.SUPPLY4 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),  
supplier CHAR(50)  
);  
GO  
--Create the view that combines all supplier tables.  
CREATE VIEW dbo.all_supplier_view  
WITH SCHEMABINDING  
AS  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY1  
UNION ALL  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY2  
UNION ALL  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY3  
UNION ALL  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY4;  
GO
INSERT dbo.SUPPLY1 VALUES ('1', 'CaliforniaCorp'), ('5', 'BraziliaLtd')  
, ('231', 'FarEast'), ('280', 'NZ')  
, ('321', 'EuroGroup'), ('442', 'UKArchip')  
, ('475', 'India'), ('521', 'Afrique');  
GO

Examples: SQL Data Warehouse and Parallel Data Warehouse

F. Creating a simple view

The following example creates a view by selecting only some of the columns from the source table.

CREATE VIEW DimEmployeeBirthDates AS  
SELECT FirstName, LastName, BirthDate   
FROM DimEmployee;

G. Create a view by joining two tables

The following example creates a view by using a SELECT statement with an OUTER JOIN. The results of the join query populate the view.

CREATE VIEW view1  
AS 
SELECT fis.CustomerKey, fis.ProductKey, fis.OrderDateKey, 
  fis.SalesTerritoryKey, dst.SalesTerritoryRegion  
FROM FactInternetSales AS fis   
LEFT OUTER JOIN DimSalesTerritory AS dst   
ON (fis.SalesTerritoryKey=dst.SalesTerritoryKey);

FESTIVALS IN OUR INDIA

2018-08-03T03:27:00.000-07:00

The main religious festivals are deepawali,holi,gangaur ,teej,gogaji,makar sankranti and janmashtami,as the main religion is hindiusm.rajasthan's desert festival is celebrated
with great zest an zeal.This festival is held once year during winter.people of the desert dance and sing ballads of valor,romance and tragedy.There are fairs with snake charmers,puppeteers,acrobats and flok performers . camels play a prominent role in this festival.
THE MOST POPULAR HINDU DEITIES ARE SURYA,KRISHNA & ,RAMA

17 Famous Festivals Of India To Bring Your Life Into A State Of Exuberance

abhay singh

India is a land of festivals, where people from different religions coexist harmoniously. The wide variety of festivals celebrated in India is a true manifestation of its rich culture and traditions. There are many Indian festivals and celebrations, the most exciting of which are mentioned below. While the celebrations happen all over the year, October till January is the time when the country can be seen at its vibrant best.

To give you a fair idea about all the festivals of India in 2018 and their dates, before you begin, here is a piece of information that would prove essential for you!

Diwali – 7th November (Wednesday)
Holi – 2nd March (Friday)
Dussehra – 19th October (Friday)
Navratri – Begins on 10th October (10th Oct Wednesday to 18th Oct Thursday)
Durga Puja – 19th October (Friday)
Janmashtmi – 3rd September (Sunday)
Ganesh Chaturthi – 13th September (Thursday)
Gurupurab – 23rd November (Friday)
Rakshabandhan – 26th August (Sunday)
Eid Ul Fitr – 15th June (Friday)
Bihu – Begins on 14th April (Saturday)
Hemis – Begins on 23rd June (Saturday)
Onam – Begins on 15th August (Wednesday)
Pongal – Begins on 14th January (Sunday)
Christmas – 25th December (Tuesday)
Easter – 1st April (Saturday)
Baisakhi – 14th April (Saturday)

List of Indian Festivals: The widely celebrated religious festivals in the country

Here is a quick list of the most famous festivals of India. Check out these types of festival before you visit the best places of Indian cultural heritage to attend the grand celebrations of these festivals.

1. Diwali: The Grand Festival Of Lights

Further Read: Diwali Celebration In India: A Pocket Guide To The Festival Of Lights

RELIGION

2018-08-03T03:11:00.001-07:00

Rajasthan is home to all the major of india,hindu are the largest in number ,accounting for 87.45% of the population .muslim(10.08%),sikhs(1.27%),jain(1%)and sindhis constitute the remaining of the population.

ARCHITECTURE....

2018-08-03T03:01:00.001-07:00

Rajasthan is famous for its many historical forts ,temple and palaces(havelis),which predominantly drives tourism in the state.

FORTS OF RAJASTHAN

THE FAMOUS RANI PADMANI PALACE.

amber fort,jaipur
bala Qila,alwar
Barmer fort,"lool"llr
chittorghar fort,chittorghar
gagron fort,jhalawar
gugor fort,baran
jaigrah fort,jaipur
jaisalmer fort,jaisalmer
man mahal,pushkar
raj mandir ,banswara
ratan singh place,chittorghar
ramuria haveli,bikaner
udaipure city palace,udiaipur
sukh mahal,bundi
sunheri kothi,sawaimadhopur
umaid bhawan palace,jodhpur

rajsthani_culture

2018-08-03T02:41:00.001-07:00

MUSIC AND DANCE.

A RAJASTHAN FLOK DANCE.

The Ghoomar dance form jodhpure and kalbeliya dancer of jaisalmer have gained international recognition.Folk music is a vital part of rajasthan culture.Kathputali,Bhopa kachchhighori,tejaji path dance etc.are the example of the traditional Rajasthani culture.
ARTS AND CRAFTS Rajasthan is famous for textiles,semi-precious stone and handicrafts,and form traditional and colorful arts.Rajasthan furniture has intricate carving and bright colours.Block prints,tie and dye prints,bagaru prints,zari embroidery are major export product from Rajasthan.The blue pottery of jaipur is particularly noted.

rajasthani arts

Daily positive thoughts for the day?

2018-08-03T01:58:00.002-07:00

positive thoughts can be let you focus on the brighter side of life.A positive person can enjoy happiness and success since he believes that can easily overcome all the obstacles and difficulties that come his way .Not all people happy with positive thinking.In fact, some people consider it a nonsense.Attitude of positive thinking in everything that you do.

smile is every thing in over life.

what is array

2017-11-08T21:44:00.001-08:00

An array refers to a group of data items.where data item must be of similar type. In other words.An array is a group of releted data item's that share a common name .Array hold the similar type data .An array ia a two type first is one dimensional and second is two dimensional.one dimensional is used to store the data items in memory in to as a list.second dimensional array is from form of matrix nd list of table.

how to save a water?

2017-10-27T04:00:00.001-07:00

All human beings thats know if .The water is not on the earth the humsn beings can'not live on the earth.The one men persone is drink 8-ounce glasses of a water a day .the earths can'not live without water,becouse the world is made with 70%water and 3%fresh water on the earths soi may request you to save a water.

What is data type

2017-10-12T09:18:00.001-07:00

Data type. C language is rich in its data types.The variety of data types available allow the programmer to select the type approprite to the needs of the application as well as the machine .The data is like a shot,long,double,float and the ANCI c support three type of data type
1. Primary data type
2.derived data type
3.user-defined data type

What is constants?

2017-10-12T09:06:00.001-07:00

Constant are value that are not change at the time of programe will be excuteded. And the constants are like 1,2,3,4 ...etc.

What is information

2017-10-06T08:44:00.001-07:00

Hey i am Raj and in one line the menning of information is that A collection of data is called information.

What is computer

2017-10-06T08:40:00.001-07:00

A computer is electronic machine that provid communication btw hard wear and user . computer is an electronic machine and they can do work 100 % accuret work then a human being at a time and they can not do any thing without instruction . A computer is a high speed electronic machine .

2017-10-05T07:14:00.001-07:00

This is simple method to you borke your pattern essly in home .my free education.com

What is menu bar

2017-10-05T06:37:00.001-07:00

Menu bar contains all the pull down menu which includes all the various application commands.And there are many example of menu bar like that file .edit view.etc

Free aducation

2017-10-05T06:27:00.001-07:00

Welcome to myfreeeducation world . I hope you like my technical nd knowledge thankyou