Persisting a view by creating a clustered index

Starting with a simple table:

 

We create a view to sum all ids:

 

CREATE VIEW vwtest2

AS

SELECT SUM(id) AS sum_id FROM dbo.TABLE1

if we try to create an index: 

 

CREATE   INDEX ixUnique ON vwtest2(sum_id)

it complains saying that the view is not schema bound:

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view ‘vwtest2’ because the view is not schema bound.

 

so we try to schema bind the view and again, it complains because the first index on a view must be a clustered index

DROP VIEW vwtest2

 

CREATE VIEW vwtest2

WITH schemabinding

AS

SELECT SUM(id) AS sum_id FROM dbo.TABLE1

 

CREATE   INDEX ixUnique ON vwtest2(sum_id)

 

 

Msg 1940, Level 16, State 1, Line 1

Cannot create index on view ‘vwtest2’. It does not have a unique clustered index.

 

then, if we try to create the clustered index, we also get an error:

CREATE UNIQUE CLUSTERED INDEX ixClustered ON vwtest2(sum_id)

 

Msg 10138, Level 16, State 1, Line 1

Cannot create index on view ‘TSQL2012.dbo.vwtest2’ because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

the reason is that, since there is an aggregate function, we need a count_big(*) on the query. Either you do that:

 

OPTION 1

DROP VIEW vwtest2

 

CREATE VIEW vwtest2

WITH schemabinding

AS

SELECT SUM(id) AS sum_id, COUNT_BIG(*) AS _count FROM dbo.TABLE1

 

CREATE UNIQUE CLUSTERED INDEX ixClustered ON vwtest2(sum_id)

  

or you remove the aggregation:

 

OPTION 2

 

CREATE VIEW vwtest3

WITH schemabinding

AS

SELECT id FROM dbo.TABLE1

 

CREATE UNIQUE clustered  INDEX ixUnique3 ON vwtest3(id)

Inserting Data through views

How to insert data through a view that access more than one table:

 

1)  Create 2 simple tables and a view that joins both:

 

CREATE TABLE TABLE1

(id INT, NAME VARCHAR(20))

GO

 

CREATE TABLE TABLE2

(id INT, LastNAME VARCHAR(20))

GO

 

 

CREATE VIEW name_lastname AS

SELECT t1.id, t1.NAME, t2.lastname

FROM table1 t1 JOIN table2 t2

ON t1.id = t2.id

GO

 

2)  Create 2 views that access table 1 and 2:

 

CREATE VIEW vwt1

AS SELECT id, name FROM table1

GO

 

CREATE VIEW vwt2

AS SELECT id, LastNAME FROM table2

Go

 

3)  As you can see it’s possible to insert data on TABLE1 and TABLE2 using the individual views:

 

 

INSERT INTO dbo.vwt1 (id, name) VALUES (1, ‘name 1’)

INSERT INTO dbo.vwt2(id, LastNAME) VALUES (1, ‘lastname1’)

 

 

SELECT * FROM name_lastname –OK

 

4)  But, If we try to insert data through the main view, we get an error:

 

 

INSERT INTO dbo.name_lastname (id, NAME, lastname)

VALUES(2,‘name2’, ‘last name 2’)

 

 

Msg 4405, Level 16, State 1, Line 1

View or function ‘dbo.name_lastname’ is not updatable because the modification affects multiple base tables.

 

5)  The way to avoid this is to create a INSEAD OF INSERT trigger on the view and deal with the individual inserts inside the trigger:

 

 

create TRIGGER t_insert_name_lastname

ON name_lastname

INSTEAD OF INSERT

as

begin

  INSERT INTO dbo.TABLE1 (id, NAME) SELECT id,NAME FROM INSERTED

  INSERT INTO dbo.TABLE2 (id, lastname) select id,lastname FROM inserted

End

GO

 

 

INSERT INTO dbo.name_lastname (id, NAME, lastname)

VALUES(2,‘name2’, ‘last name 2’)

GO

  

INSERT INTO dbo.name_lastname

(id, NAME, lastname),

SELECT 3, ‘name 3’, ‘last name 3’

UNION

SELECT 4, ‘name 4’, ‘last name 4’

UNION

SELECT 5, ‘name 5’, ‘last name 5’

 

 

SELECT * FROM name_lastname
 

Random number dataset

Just a proc I had to create to produce a dataset with a date and a random number.

The fun par is that it also generates a random number of rows.

The reason I had to do it is to do a time analysis between sets. Imagine that each execution represents a day and you want to know the difference between today and yesterday.

 

 

CREATE PROCEDURE generateRandomthings

AS

begin

       DECLARE @test TABLE (

       report_date DATETIME,

       _number INT

       )

 

       DECLARE @i int = (SELECT CAST((RAND()*10)+1 AS int))

       DECLARE @date DATETIME = GETDATE()

 

       WHILE @i>0 BEGIN

              INSERT INTO @test (report_date, [_number])

              SELECT @date, (SELECT CAST((RAND()*10)+1 AS int))

              SET @i-= 1

       END

 

       SELECT * FROM @test

END

Show only a few databases on SSMS

This comes from the situation where you have an instance with several databases, maybe all of them almost equally named, like a development server where each user has its own copy of the DB, for example, MY_APPLICATION_DB_USER_01, MY_APPLICATION_DB_USER_02, MY_APPLICATION_DB_USER_03 and so one. It may come a time when you are just interested on your own databases and don’t want to keep looking on a huge list to find it, so how to display only a few databases on the SSMS database list?

 The idea is to deny the user the ability to view all databases and then, make this user db_owner of the databases he is supposed to see. Of course, since a database can have only one owner, with this technique, it will be visible to only to its owner so you might want not to use your regular user (probably your AD one) to do this and create a new SQL User.

The steps are:

1)      Create a “SQL Server Authentication User”, in this example, called just Diego

2)      Right Click on your instance, select Properties -> Permissions

3)      Select your user and deny “view any database”

 

As the name says, this setting prevents the user of seeing any database on the instance. Exceptions are any database that he owns.

4)      Right click the database -> Properties -> Files and change the owner to your user.

Now, you can log in with this user and see only the DBs you actually need to see.

SQL Server 2008 SP1 e CU 2

One of the pre-requisites (or at least it is suggested by MS) to install SharePoint is to have the Cumulative update package 2 for SQL Server 2008 Service Pack 1 installed. When I tried to download it from http://support.microsoft.com/kb/970315 and install, I realized the SQLSERVEDR2008 (which is the name of my instance) checkbox was greyed out and showing the message bellow:

 

The version of SQL Server instance SQLSERVER2008 does not match the version expected by the SQL Server update. The installed SQL Server product version is 10.0.1600.22, and the expected SQL Server version is 10.1.2531.0.

 

To fix it, I download and installed SQL Server 2008 Service Pack 1 from http://www.microsoft.com/en-us/download/details.aspx?id=20302

 

The version is confirmed by the installer:

 

 

And after the SP1 install, the Cumulative Update package 2, the SQLSERVER2008 instance check box was able to be checked.

 

Mining–Time Series Example

http://technet.microsoft.com/en-us/library/ms345287(v=sql.100).aspx

 

CREATE MINING STRUCTURE [Bike Buyer]
(
   [Customer Key] LONG KEY,
   [Age]LONG DISCRETIZED(Automatic,10),
   [Bike Buyer] LONG DISCRETE,
   [Commute Distance] TEXT DISCRETE,
   [Education] TEXT DISCRETE,
   [Gender] TEXT DISCRETE,
   [House Owner Flag] TEXT DISCRETE,
   [Marital Status] TEXT DISCRETE,
   [Number Cars Owned]LONG DISCRETE,
   [Number Children At Home]LONG DISCRETE,
   [Occupation] TEXT DISCRETE,
   [Region] TEXT DISCRETE,
   [Total Children]LONG DISCRETE,
   [Yearly Income] DOUBLE CONTINUOUS
)
WITH HOLDOUT (30 PERCENT or 1000 CASES)

ALTER MINING STRUCTURE [Bike Buyer]
ADD MINING MODEL [Decision Tree]
(
   [Customer Key],
   [Age],
   [Bike Buyer] PREDICT,
   [Commute Distance],
   [Education],
   [Gender],
   [House Owner Flag],
   [Marital Status],
   [Number Cars Owned],
   [Number Children At Home],
   [Occupation],
   [Region],
   [Total Children],
   [Yearly Income]
) USING Microsoft_Decision_Trees
WITH DRILLTHROUGH

ALTER MINING STRUCTURE [Bike Buyer]
ADD MINING MODEL [Clustering]
USING Microsoft_Clustering

INSERT INTO MINING STRUCTURE [Bike Buyer]
(
   [Customer Key],
   [Age],
   [Bike Buyer],
   [Commute Distance],
   [Education],
   [Gender],
   [House Owner Flag],
   [Marital Status],
   [Number Cars Owned],
   [Number Children At Home],
   [Occupation],
   [Region],
   [Total Children],
   [Yearly Income]   
)
OPENQUERY([Adventure Works DW2008],
   'SELECT CustomerKey, Age, BikeBuyer,
         CommuteDistance,EnglishEducation,
         Gender,HouseOwnerFlag,MaritalStatus,
         NumberCarsOwned,NumberChildrenAtHome, 
         EnglishOccupation,Region,TotalChildren,
         YearlyIncome 
    FROM dbo.vTargetMail')

SELECT * FROM [Clustering].CONTENT

SELECT * 
FROM [Decision Tree].CASES

SELECT DISTINCT [Bike Buyer] 
FROM [Decision Tree]


SELECT
   [Bike Buyer] AS Buyer,
   PredictHistogram([Bike Buyer]) AS Statistics
FROM
   [Decision Tree]
NATURAL PREDICTION JOIN
(SELECT 35 AS [Age],
   '5-10 Miles' AS [Commute Distance],
   '1' AS [House Owner Flag],
   2 AS [Number Cars Owned],
   2 AS [Total Children]) AS t

 

http://technet.microsoft.com/en-us/library/cc879270(v=sql.100).aspx

 

CREATE MINING MODEL [Forecasting_MIXED]
     (
    [Reporting Date] DATE KEY TIME,
    [Model Region] TEXT KEY,
    [Quantity] LONG CONTINUOUS PREDICT,
    [Amount] DOUBLE CONTINUOUS PREDICT
    )
USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = 0.8, FORECAST_METHOD = 'MIXED')
WITH DRILLTHROUGH

ALTER MINING STRUCTURE [Forecasting_MIXED_Structure]
ADD MINING MODEL [Forecasting_ARIMA]
   (
   [Reporting Date],
    [Model Region],
    [Quantity] PREDICT,
    [Amount] PREDICT
   )
USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = .08, FORECAST_METHOD = 'ARIMA')
WITH DRILLTHROUGH

ALTER MINING STRUCTURE [Forecasting_MIXED_Structure]
ADD MINING MODEL [Forecasting_ARTXP]
   (
   [Reporting Date],
    [Model Region],
    [Quantity] PREDICT,
    [Amount] PREDICT
   ) 
USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = .08, FORECAST_METHOD = 'ARTXP')
WITH DRILLTHROUGH

INSERT INTO MINING STRUCTURE [Forecasting_MIXED_Structure]
(  
[Reporting Date],[Model Region],[Quantity],[Amount]
)  
OPENQUERY(
[Adventure Works DW2008],
'SELECT [ReportingDate],[ModelRegion],[Quantity],[Amount] FROM vTimeSeries ORDER BY [ReportingDate]'
)

SELECT
[Forecasting_MIXED].[Model Region],
PredictTimeSeries([Forecasting_MIXED].[Quantity],6) AS PredictQty,
PredictTimeSeries ([Forecasting_MIXED].[Amount],6) AS PredictAmt
FROM 
[Forecasting_MIXED]
WHERE [Model Region] = 'M200 Europe' OR
[Model Region] = 'M200 Pacific'


SELECT [Model Region],
PredictTimeSeries([Quantity],6, EXTEND_MODEL_CASES) AS PredictQty
FROM
   [Forecasting_MIXED]
NATURAL PREDICTION JOIN 
(
   SELECT 1 AS [Reporting Date],
  '10' AS [Quantity],
  'M200 Europe' AS [Model Region]
UNION SELECT
  2 AS [Reporting Date],
  15 AS [Quantity],
  'M200 Europe' AS [Model Region]
) AS t
WHERE [Model Region] = 'M200 Europe' OR
[Model Region] = 'M200 Pacific'


SELECT [Model Region],
PredictTimeSeries([Quantity],3,6, EXTEND_MODEL_CASES) AS PredictQty
FROM
   [Forecasting_MIXED]
NATURAL PREDICTION JOIN
( 
   SELECT 1 AS [Reporting Date],
  '10' AS [Quantity],
  'M200 Europe' AS [Model Region]
UNION SELECT
  2 AS [Reporting Date],
  15 AS [Quantity],
  'M200 Europe' AS [Model Region]
) AS t
WHERE [Model Region] = 'M200 Europe'

Locked out of SQL Server

What to do if you get locked out of SQL Server:

I was trying to transfer objects from a SQL Server instance to another using the “Transfer SQL Server Objects Task” and I selected to copy all the security options

When the package was running I got an error saying that I did not have permissions to insert the logins on my destination server. The thing is, the package first deletes all the logins before inserting the new ones (I believe that’s because the DropObjectsFirst option was selected). Result: I got locked out of SQL.

Solution:

If SQL Server is started in single-user mode, any user who has membership in the BUILTIN\Administrators group can connect to SQL Server as a SQL Server administrator. The user can connect regardless of whether the BUILTIN\Administrators group has been granted a server login that is provisioned in the SYSADMIN fixed server role. This behaviour is by design and intended to be used for data recovery scenarios.

In this scenario you are connected as a SQL Server Admin so you have full rights. Remember to give your AD user (which is the same one you are connected at the moment) sysadmin access. If you simply create your user, restart SQL and log in with it, you won’t have access to anything.

A more detailed explanation on the process here: http://technet.microsoft.com/en-us/library/dd207004.aspx

Here is a link on how to start SQL Server on Single User:

Sqlauthority: http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/

TechNet: http://technet.microsoft.com/en-us/library/ms188236.aspx

SQL Server 2012 Certification

(Text from http://www.sqlservercentral.com/)

Forget what you thought you knew about SQL Server certification, as Microsoft has completely redesigned the SQL Server 2012 certification program, making is more difficult, costly, and time-consuming to attain. In addition, whether you like it or not, not only will you need to know how to administer SQL Server (which is of course fully expected), you will also have to become familiar with how SQL Server interacts with the Cloud (Microsoft’s Cloud) and Data Warehousing. If you are not up on the Cloud or Data Warehousing, you will have a lot of preparation work ahead of you. The new exams will become available starting in June 2012.
The most basic SQL Server certification is now called the MCSA: SQL Server, where MCSA stands for Microsoft Certified Solutions Associate, and it requires three tests:

• Exam 461: Querying Microsoft SQL Server 2012

• Exam 462: Administering a Microsoft SQL Server 2012 Database

• Exam 463: Implementing Data Warehouses in Microsoft SQL Server 2012

Once you have attained the MCSA: SQL Server certification, then you can earn either the MCSE: Data Platform, or MCSE: Business Intelligence, where MCSE stands for Microsoft Certified Solutions Expert. Don’t confuse the MCSE designation with the older MCSE designations, which are completely different.
The MCSE: Data Platform requires all of the MCSA exams, plus these two additional exams:

• Exam 464: Developing Microsoft SQL Server 2012 Databases

• Exam 465: Designing Database Solutions for SQL Server 2012

The MCSE: MCSE: Business Intelligence requires all of the MCSA exams, plus these two additional exams:

• Exam 466: Implementing Data Models and Reports with Microsoft SQL Server 2012

• Exam 467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

While none of the above tests include the word “cloud” in them, be sure to review each test’s objectives, as the Cloud is covered in some of them. If you are really into the Cloud, Microsoft offers two different Cloud certifications distinct from the SQL Server certifications.
Microsoft will continue to offer the Microsoft Certified Master (MCM) for SQL Server, but currently it has yet to be updated for SQL Server 2012.

SQL Server tip

Just a quick post in case anyone else is having the same problem that I had.
If you execute a query  like this on a SQL Server 2008 database from a SSMS 2005:

SELECT TOP 10 *
FROM AdventureWorksDW2008.dbo.DimCustomer

and get the following error message:

An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column ‘BirthDate’.  The GetBytes function can only be used on columns of type Text, NText, or Image.

Notice that’s probably because you are connected to the 2008 instance with a 2005 SSMS client.

Apparently, date fields cannot be bound to a SSMS 2005. That’s probably (not sure, just guessing) due to the fact that it is a new data type on SQL Server 2008.

Operador MERGE

* Novo no SQL Server 2008;

* Funciona com uma tabela SOURCE e uma TARGET, fazendo com que a TARGET fique igual a SOURCE;

* Para isso, insere na TARGET as linhas que não existem, atualiza as linhas que estão diferentes e apaga as linhas que não existem na source; Exemplo:

 

TARGET (tabela que será modificada)	SOURCE (tabela de origem)
CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY ) GO INSERT INTO Products VALUES (1, 'Tea', 10.00), (2, 'Coffee', 20.00), (3, 'Muffin', 30.00), (4, 'Biscuit', 40.00) GO	CREATE TABLE UpdatedProducts ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY ) GO INSERT INTO UpdatedProducts VALUES (1, 'Tea', 10.00), (2, 'Coffee', 25.00), (3, 'Muffin', 35.00), (5, 'Pizza', 60.00) GO

 

 

FAZENDO O MERGE

--Sincronizando a tabela MERGE Products AS TARGET USING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID) --Quando encontrou ID igual, faz o update WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate --Quando não encontrou ID igual, faz a inserção WHEN NOT MATCHED BY TARGET THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate) --Quando a linha existe na TARGET e não na SOURCe, apaga a linha WHEN NOT MATCHED BY SOURCE THEN DELETE --$action é uma coluna nvarchar(10) que indica o que foi --feito('INSERT', 'UPDATE', or 'DELETE') em cada linha OUTPUT $action, DELETED.ProductID AS TargetProductID, DELETED.ProductName AS TargetProductName, DELETED.Rate AS TargetRate, INSERTED.ProductID AS SourceProductID, INSERTED.ProductName AS SourceProductName, INSERTED.Rate AS SourceRate; SELECT @@ROWCOUNT; GO

 

Notes

• The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator.
• When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
• At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
• Of course it’s obvious, but just to mention, the person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
• MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
• MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as we did in previous version of SQL Server.
• For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.

 

Artigo originial : http://www.mssqltips.com/tip.asp?tip=1704