SQL Server CE

Topics

• What's New in SQL Server Compact Edition
• Feature Improvements to Enhance Reliability and Performance
• Synchronization
• Storage Engine
• Query Processor
• Integration with SQL Server 2005
• Integration with Microsoft Visual Studio 2005
• Advantage of SQL SERVER CE
• Disadvantage of SQL SERVER CE
• Connection strings for SQL Server Compact Edition
• Difference Between SQL Server Compact Edition (CE) and SQL Server Express Edition

Scheduling a SQL Server PowerShell script using the Task Scheduler

Problem

Even if your SQL Server edition does not include the SQL Server Agent service (if you use SQL Express, for instance), you can still automate administration tasks without using any 3rd party products. In this tip I'll describe how to schedule a PowerShell script with the Task Scheduler MMC snap-in. This applies to Windows 2008 Server, Windows 2008 Server R2 and Windows 7.

Solution

Following is the step by step process to setup Task Scheduler to run a PowerShell script. In this example I show how you can backup a database, but this can be applied to any SQL Server task you want to automate and have scheduled on a regular basis.
1. There are several ways to start the Task Scheduler, such as:

• Go to Start ---> Control Panel ---> Administrative Tools ---> Task Scheduler
• Go to Start ---> Run ---> Taskschd.msc
• Go to the Configuration Node of the Server Manager (Windows Server 2008, 2008 R2) or to Computer Manager ---> System Tools (Windows 7 Pro) and click on Task Scheduler

Server Side Pagination

The paging of a large database resultset in Web applications is a well known problem. In short, you don't want all the results from your query to be displayed on a single Web page, so some sort of paged display is more appropriate. While it was not an easy task in the old ASP, the DataGrid control in the ASP.NET simplifies this to a few lines of code. So, the paging is easy in ASP.NET, but the default behavior of the DataGrid is that all resulting records from your query will be fetched from SQL server to the ASP.NET application. If your query returns a million records this will cause some serious performance issues (if you need convincing, try executing such a query in your web application and see the memory consumption of the aspnet_wp.exe in the task manager). That's why a custom paging solution is required where desired behavior is to fetch only the rows from the current page.

Here I provide some query which provide solution for custom pagination. It helps to improve your search result for your filter data.

Code

NULLIF (Transact-SQL)

Yesterday I found one more function of sql server. NULLIF which returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF function is available from Sql Server 2005.



Syntax :



NULLIF ( expression , expression )



Example :

The following example creates a budgets table to show a department (dept) its current budget (current_year) and its previous budget (previous_year). For the current year, NULL is used for departments with budgets that have not changed from the previous year, and 0
is used for budgets that have not yet been determined. To find out the
average of only those departments that receive a budget and to include
the budget value from the previous year (use the previous_year value, where the current_year is NULL), combine the NULLIF and COALESCE functions.

ISNULLIF Example