Difference between JOIN and INNER JOIN
They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.
sql-server
SQL Server – Best way to get identity of inserted row?
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it’s across scopes. You could get a value from a trigger, instead of your current statement. SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and … Read more
Altering a column: null to not null
First, make all current NULL values disappear: UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL Then, update the table definition to disallow “NULLs”: ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL
Check if table exists in SQL Server
For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version. To check if a table exists use: IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘TheSchema’ AND TABLE_NAME = ‘TheTable’)) BEGIN –Do Stuff END
How can I remove duplicate rows?
Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to keep. Then, just delete everything that didn’t have a row id: DELETE FROM MyTable LEFT OUTER JOIN ( SELECT MIN(RowId) as RowId, Col1, Col2, Col3 FROM MyTable GROUP BY Col1, Col2, Col3 ) as KeepRows … Read more
How can I do an UPDATE statement with JOIN in SQL Server?
Syntax strictly depends on which SQL DBMS you’re using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you’re using a SQL DBMS other … Read more
Find all tables containing column with specified name – MS SQL Server
Search Tables: SELECT c.name AS ‘ColumnName’ ,t.name AS ‘TableName’ FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName; Search Tables and Views: SELECT COLUMN_NAME AS ‘ColumnName’ ,TABLE_NAME AS ‘TableName’ FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName;
Get size of all tables in database
SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, (SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB, CAST(ROUND(((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024.00, 2) … Read more
What is the difference between varchar and nvarchar?
An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk … Read more
How can I delete using INNER JOIN with SQL Server?
You need to specify what table you are deleting from. Here is a version with an alias: DELETE w FROM WorkRecord2 w INNER JOIN Employee e ON EmployeeRun=EmployeeNo WHERE Company = ‘1’ AND Date=”2013-05-06″