We have a table `TemplateItem` that has a column `ListIndex` that stores the order in which we want the items to show in the UI. This table is self-referencing / hierarchical. The query requirement is that the column `ListIndex` has to start from 0 and be sequential for each parent / child(ren) relationship. The query I came up with is as follows: ``` SELECT Id, ParentId, Sub.NewIndex AS ListIndex FROM TemplateItem JOIN ( SELECT Id, ROW_NUMBER() OVER ( PARTITION BY ParentId ORDER BY ListIndex ) - 1 AS NewIndex FROM TemplateItem ) AS Sub ON TemplateItem.Id = Sub.Id ``` ( unnecessary bits of query removed for readability ) This works perfectly fine in SQL Server 2008 R2. However, I now need to use this same functionality in SQL Server Compact Edition 4.0 but, as you may already know, it will fail. It does so because there is no `ROW_NUMBER()` in SQL Server Compact Edition 4.0. How can I accomplish this same functionality?

I have installed SQL Server 2012 Enterprise Edition. I can use SQL Server Data Tools to add databases and tables and query tables etc. Next, I used Visual Studio to try out a tutorial on creating a website with a database. The database type created was an SQL Server Compact Local Database. This database doesn't turn up in the list of databases in SQL Server Data Tools. How do I view compact local databases? (I want to be able to view the data in the DB, add rows using a GUI, and run SQL queries within the tool.) Or are they different products, in which case does Microsoft have an equivalent tool to SQL Server Data Tools for SQL Server Compact databases?

I have a database, with one main table: Project, and about 10 other tables (Entity1, Entity2, etc.) Each of the different entities can belong to one or many projects. I know I can have tables like ProjectEntity1, ProjectEntity2, ProjectEntity3, to store relationships, but that gets old. Are there any issues with creating a ProjectContents table, which would hold projectid, entityid, and entitytype? (Entitytype would be name of the source table.) (Currently, this is in SQL Server Compact 4.0, as a standalone solution. It may never evolve past that.) EDIT: More details - It's yet another world builder type desktop application, where I can dream up/generate stuff - worlds, cultures, languages, religions, alien species, characters, plots - and then assign them to a full blown project if I want. So, a project can have many entities of varying types, and an entity can belong to many projects, or none at all. Right now, all CRUD/Reads are done with C# LINQ, and unless I switch to SQL Express or something else, I don't see the need for stored procedures. At least until I hit some performance bottlenecks.

We have an application that runs on netbooks. It currently uses a SQL Server CE 3.5 database, and we are thinking of upgrading to SQL Server Express. Just wonder if there are any benchmarks to compare the performance of SQL Server CE and SQL Server Express? Would it be too intensive for an atom netbook? We are using SQL Server CE 3.5 and we are thinking of moving to SQL Server Express 2012. UPDATE: Why are we wanting to upgrade? Because we will now be using merge replication with the SQL Server Express database. Also we don't want to limit ourselves. We might use stored procedures and triggers on the database because with the replication solution we will be redesigning parts of our system.

I'm creating a Windows desktop application and want to include a database. I've chosen SQL Server Compact 4 but when I download it there are two versions (32-bit and 64-bit). My development machine is 64-bit, so do I download this version? Or, do I download the version that I expect users to run (which, I guess, is most likely to be 32-bit). If I download and work with the 64-bit, when I release the database, will it cause errors on 32-bit machines? Thank you.