MelvinsWebStuff My Online Notepad

In SQL Server 2008, the TOP 100 PERCENT and ORDER BY is ignored. In SQL Server 2005, the output order isn’t guaranteed.

For example if you created a view like so:

CREATE VIEW vwProduct
AS
SELECT TOP 100 PERCENT lProductID, vsProduct FROM dbo.tblProduct AS tl
ORDER BY lProductID DESC
GO

And tried to select from it expecting the results to be ordered as specified in the view, it’s not going to happen.

-- Select From View
SELECT lProductID, vsProduct
FROM vwProduct

This would return the results ordered ascending not descending:
lProductId  vsProduct
———– ———
1           Widget A
2           Widget B
3           Widget C
4           Widget D
5           Widget E

USE Database --Change to the database you want to use
SELECT name, create_date, modify_date
FROM sys.objects --User defined objects system view
WHERE type = 'P' --Only return stored procedures
AND modify_date > '2009-09-01' --If you aren't looking for a specific data range, take this line out
ORDER BY modify_date DESC

Read sys.objects documentation

Also read documentation on Querying the SQL Server System Catalog, it contains some information on backward compatibility and future releases of SQL Server.

http://halisway.blogspot.com/2006/04/sql-connect-by.html

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#sthref9844

http://www.java2s.com/Code/Oracle/Hierarchical-Query/HierarchicalQueriesSTARTWITHandCONNECTBYPRIORclauses.htm

Changes the name of a user-created object (i.e. table, column, or user-defined data type) in the current database.

Rename a table
This example renames the customers table to custs.

EXEC sp_rename 'customers', 'custs'

Rename a column
This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

The cause of this problem is the SQL server has been configured to operate in “Windows Authentication Mode (Windows Authentication)” and doesn’t allow the use of SQL accounts. This was the default setting in my case, I got the error after installing a new instance of SQL Server 2005.

I was able to solve this problem by changing the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)” to “Mixed Mode (Windows Authentication and SQL Server Authentication)”.

You could use

sp_helpdb [ [ @dbname= ] 'name' ]

Syntax

sp_change_users_login [@Action =] 'action' [,[@UserNamePattern =] 'user']
    [,[@LoginName =] 'login']

Arguments

[@Action =] 'action'

Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.

Auto_Fix

Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.

user must be a valid user in the current database, and login must be NULL, a zero-length string, or not specified.

Report

Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.

user and login must be NULL, a zero-length string, or not specified.

Update_One

Links the specified user in the current database to login. login must already exist. user and login must be specified.

[@UserNamePattern =] 'user'

Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can only be used with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows users.

[@LoginName =] 'login'

Is the name of a SQL Server login. login is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Permissions

Any member of the public role can execute sp_change_users_login with the Report option. Only members of the sysadmin fixed server role can specify the Auto_Fix option. Only members of the sysadmin or db_owner roles can specify the Update_One option.

Examples

A. Show a report of the current user to login mappings

This example produces a report of the users in the current database and their security identifiers.

EXEC sp_change_users_login 'Report'

B. Change the login for a user

This example changes the link between user Mary in the pubs database and the existing login, to the new login NewMary (added with sp_addlogin).

–Add the new login.

USE master
GO

EXEC sp_addlogin 'NewMary'
GO

–Change the user account to link with the ‘NewMary’ login.

USE pubs
GO

EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'

Related Article: http://msdn2.microsoft.com/en-us/library/ms174378.aspx

SELECT t1.columnName, t2.columnName
FROM databaseName.user.table AS t1
INNER JOIN databaseName.user.table AS t2 ON t1.id = t2.id

INSERT INTO MyTable  (FirstCol, SecondCol)
SELECT 'First', 1
UNION ALL
SELECT 'Second', 2
UNION ALL
SELECT 'Third', 3

Here’s a handy query for finding duplicates in a table using the GROUP BY and HAVING statement. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )