Create view in SQL Server CE 3.5

Question

I'm using SQL Server CE as my database.

Can I create a view in SQL Server CE 3.5 ? I tried to create but its saying create view statement not supported.

In my application I have table called Alarm with 12 columns. But I'm always accessing only three columns. So I want to create the view with those three columns.

Will it improve performance?

Answer 1

It appears that SQL Server Compact Edition does indeed not support creation of views.

But if you're selecting only three columns from your table, a view will not help you here at all.

If you have a view AlarmView which is defined as

 CREATE VIEW dbo.AlarmView
 AS
   SELECT Col1, Col2, Col3 FROM dbo.Alarm

then selecting from that view (`SELECT * FROM dbo.AlarmView WHERE ......) essentially becomes

SELECT Col1, Col2, Col3 FROM dbo.Alarm
WHERE ........

so you get the same statement you'd write yourself.

Views are not designed for performance gains mostly (it helps a little bit that using a view, you're limiting the number of columns that are returned in your SELECT) - they're designed for limiting / modelling access to the tables, e.g. you could grant some user SELECT permission on the view but not on the underlying table, so that user would never be able to see / select any of the other columns.