SQL Server - Select a subset of data in SELECT query

Select a subset of data in SELECT query

DECLARE @times int
SET @times = 2

SELECT field1, field2
   FROM (SELECT field1, field2, row_number() OVER(PARTITION BY field1 ORDER BY field1 DESC) AS rowNumber FROM Table) AS t
WHERE t.rowNumber <= @times;

SQL - Syntax for SQL sentences to INSERT INTO and SELECT

Syntax for: SQL sentences to INSERT INTO and SELECT

All columns

INSERT INTO table_name1
    SELECT * FROM table_name2;

Some Columns

INSERT INTO table_name1 (column_name1, column_name2)
    SELECT column_name1, column_name2 FROM table_name2;

SQL Server - Use a string that contain single quotes '

How to use a string that contain single quotes '

Problem: Incorrect syntax near ' +


Solution: duplicate the single quotes '' ''


DECLARE @sql nvarchar(max)

SET @sql =
    'SELECT        employee,         FLastName + ''  '' +SLastName + ''  '' + FName + ''  '' +SName + ''  ''
        AS Employee_name 
    '
Note: This example not contains the validation with CASE WHEN IS NULL.

SQL Server - select LOCKOUT/ACCOUNTDISABLE from a user in OPENQUERY LDAP (Active Directory)

SELECT *
  FROM OPENQUERY
               (ADSI,'SELECT  userAccountControl, sAMAccountName
                FROM ''LDAP://DC=domain,DC=com'' where objectClass = ''User'' ')
 WHERE (userAccountControl & 2 <> 0 or userAccountControl & 16 <> 0)

More info https://support.microsoft.com/en-us/kb/305144

SQL Server - Saving changes is not permitted (Alter Table)

Message:
 
Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

 

Solution:

1. Open SQL Server Management Studio.
2. Clic Tools menu: Options.
3. In the navigation pane of the Options window, click Designers.
4. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK

More info: https://support.microsoft.com/en-us/kb/956176