How would I find the second largest salary from the employee table? [closed]
Try this: SELECT max(salary) FROM emptable WHERE salary < (SELECT max(salary) FROM emptable);
sql-server-2005
Finding blocking/locking queries in MS SQL (mssql)
You may find this query useful: SELECT * FROM sys.dm_exec_requests WHERE DB_NAME(database_id) = ‘YourDBName’ AND blocking_session_id <> 0 To get the query itself use this one: SELECT text,* FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE DB_NAME(database_id) = ‘YourDBName’ AND blocking_session_id <> 0
SQL Server ON DELETE Trigger
CREATE TRIGGER sampleTrigger ON database1.dbo.table1 FOR DELETE AS DELETE FROM database2.dbo.table2 WHERE bar = 4 AND ID IN(SELECT deleted.id FROM deleted) GO
Table Valued Function Killing My Query Performance
Returning a Table Variable will make it a multi-statement table valued function and can be bad for performance due to the fact that it’s treated like a table except there are no statistics available for SQL Server to base a good execution plan on – so it will estimate the function as returning a very … Read more
Is it possible to use Aggregate function in a Select statment without using Group By clause?
All columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY Good: SELECT col1, col2, col3, MAX(col4) … GROUP BY col1, col2, col3 Also good: SELECT col1, col2, col3, MAX(col4) … GROUP BY col1, col2, col3, col5, col6 No other columns = no GROUP BY needed SELECT … Read more
How to get the last record per group in SQL
You can use a ranking function and a common table expression. WITH e AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ApplicationId ORDER BY CONVERT(datetime, [Date], 101) DESC, [Time] DESC ) AS Recency FROM [Event] ) SELECT * FROM e WHERE Recency = 1
Read only access to stored procedure contents
You can grant them the VIEW DEFINITION privilege to those procs. See here for what this privilege does. You can apply VIEW DEFINITION at different scopes: Server Database Schema Individual entities (e.g. a proc, a function, a view) You can also use a query to generate a script for many procs. So if you have … Read more
INSERT INTO TABLE from comma separated varchar-list
Something like this should work: INSERT INTO #IMEIS (imei) VALUES (‘val1’), (‘val2’), … UPDATE: Apparently this syntax is only available starting on SQL Server 2008.
How to add a column with a default value to an existing table in SQL Server?
Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} WITH VALUES Example: ALTER TABLE SomeTable ADD SomeCol Bit NULL –Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol –When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)–Optional Default-Constraint. WITH VALUES –Add if Column is Nullable and you want the Default Value for Existing Records. Notes: … Read more
Why doesn’t VFP .NET OLEdb provider work in 64 bit Windows?
Have you tried changing the target CPU to x86 instead of “Any CPU” in the advanced compiler options? I know that this solves some problems with other OLEDB providers by forcing the use of the 32-bit version.