SQL Server: Limit (MySQL) equal Function in SQL Server

SQL Server: Limit (MySQL) equal Function in SQL Server

In MySQL, Limit is a powerful function used with SELECT query to return rows BUT within given range. We commonly need it when returning only required rows to application according to paging range. For example to return rows from 11 to 15 we will use following query in MySQL

SELECT * FROM MyTable LIMIT 11, 5

In SQL Server, same functionality can be achieved with three different ways.

1.       With Derived Table (Most inefficient way, but applicable to all versions of SQL Server)

SELECT  *

      FROM    ( SELECT    *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
          FROM      MyTable ) DerivedTable
       WHERE   RowNum >= 11
        AND RowNum <= 11 + ( 5 - 1 )

2.       With CTE Common Table Expression (Applicable to SQL Server 2005/2008/2012)
   ;
     WITH   CTE
          AS ( SELECT   *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
               FROM     MyTable)
    SELECT  *
    FROM    CTE
    WHERE   RowNum >= 11
            AND RowNum <= 11 + ( 5 - 1 )

3.       With OFFSET_ROW FETCH (Applicable to SQL Server 2012 Only)
        Here “OFFSET” means, how many rows to skip, “ROWS FETCH NEXT” means, how many rows to skip

 SELECT * FROM MyTable  

        OFFSET 10 
        ROWS FETCH NEXT 5 ROWS ONLY;

ref:

http://www.connectsql.com/2012/07/sql-server-limit-mysql-equal-function.html

Create a Full Database Backup (SQL Server)

Examples (Transact-SQL)

 The following example backs up the complete 
    AdventureWorks2012
   database to disk, by using FORMAT to create a new media set.

USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2012';
GO

ref:

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

LocalDB: Where is My Database?

As I mentioned in the previous post LocalDB handles database files differently than regular SQL Server.
The regular SQL Server assumes that it is actively managed by a DBA. The DBA carefully configures SQL Server during the installation and afterwards. The DBA decides where SQL Server binaries are installed. The DBA also decides where the system database files are located. The location of the system databases also becomes the default location for all user databases.
LocalDB is different. It's built to be used by developers, not DBAs. And we have heard developers saying loud and clear that they just want to focus on their databases and database code. Therefore our primary design goal was to eliminate all the server configuration and management that is getting in their way. As a result LocalDB stores all the system databases deep inside the "hidden" AppData folder in the user profile. For example, after playing with the Automatic Instance in the previous post, I find this folder in my user profile:

This folder constitutes my Automatic LocalDB Instance. Any good DBA will explain in great and painful details what these files are, but if you are a developer this is the first and the last time you will look at them. Which is great, but left us with an interesting question. If the location of system databases is hidden from the developer, what should the default location for the databases created by the developer be? Creating user databases in a hidden folder didn't seem like the right design. We considered My Documents too, but that could give the impression that SQL Server databases are portable documents. Plus half of our team was using some sort of document synchronization solution, like Live Mesh. Those would quickly destroy the database files and we thought they will only get more popular over time, thus more likely to wreck havoc in the future.
In the end we decided to create the database files in the root of the user profile. On most machines it is located in C:\Users\user-name folder
In the spirit of a scientific approach, let's try it out. Open SSMS and connect to your Automatic Instance:

Then create database foo without specifying the location for its files:

create database foo

Open Windows Explorer and navigate to your profile (typing %USERPROFILE% in the address bar is a nice shortcut). If the database was created successfully there will be new files in this folder, foo.mdf and foo_log.ldf that represent your database. QED.
Given that user profile folder is likely not the best location to store database files, we advise developers creating databases to always specify the location for the database files, like in this T-SQL example:

create database foo on (name='foo', filename='c:\DBs\foo.mdf')

- Krzysztof Kozielczyk


ref:
http://blogs.msdn.com/b/sqlexpress/archive/2011/10/28/localdb-where-is-my-database.aspx

ASP.Net Web Matrix: Change SQL Espress Authentication and Enable SA from the Command Prompt

how to use joomla with sql server express using web matrix2 

  When SQL Server Express is installed with default options it is setup with Integrated Windows Security and the SA (Administrator) account disabled, for example when installed as part of Visual Studio. Additionally, in many cases the SQL Server Management Studio Express is not installed, so that a graphical interface to change to a Mixed Security and enabled SA account does not exist. Many of the ASP.Net templates integrate into ASP.Net Web Matrix like Umbraco and SrewTurn Wiki require this Mixed Security scenario, so here are the steps to change from Integrated to Mixed Security and enable the SA account with a password from the command prompt. This is assuming SQL Server Express 2008 R2, the step 4 key is slightly different with 2008/2005 installs.

1. Launch cmd.exe to enter Command Prompt

2. Run net stop MSSQL$SQLEXPRESS

 3. Run regedit

4. In the Registry Editor change the key HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer\LoginMode value to 2.

 5. Close Registry Editor and return to Command Prompt

6. Run net start MSSQL$SQLEXPRESS

 7. Run SQLCMD –S .\SQLEXPRESS

8. Enter alter login 'sa' enable

9. Enter alter login 'sa' with password ='MyPassword'

10. Close the Command Prompt, you are done

 for sqlexpress 2012 do the following

 8. Enter alter login [sa] enable go

 9. Enter alter login [sa] with password =N'passwordhere' go

10. Close the Command Prompt, you are done now you will be able to install joomla , wordpress , drupal , ets ... with sql server

 references
 http://joeblog.homnick.com/Lists/Posts/Post.aspx?List=db4a6966-2b11-448d-a4ba-1550e74c89ba&ID=65 http://forums.asp.net/t/1861816.aspx/1?SqlServer+Authentication+