Dynamic SELECT TOP @var In SQL Server
SELECT TOP (@count) * FROM SomeTable This will only work with SQL 2005+
sql
Reset auto increment counter in postgres
If you created the table product with an id column, then the sequence is not simply called product, but rather product_id_seq (that is, ${table}_${column}_seq). This is the ALTER SEQUENCE command you need: ALTER SEQUENCE product_id_seq RESTART WITH 1453 You can see the sequences in your database using the \ds command in psql. If you do … Read more
How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?
For SQL Server: ALTER TABLE TableName DROP COLUMN Column1, Column2; The syntax is DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,…n ] For MySQL: ALTER TABLE TableName DROP COLUMN Column1, DROP COLUMN Column2; or like this1: ALTER TABLE TableName DROP Column1, DROP Column2; 1 The word COLUMN is optional and can … Read more
MySQL Cannot Add Foreign Key Constraint
To find the specific error run this: SHOW ENGINE INNODB STATUS; And look in the LATEST FOREIGN KEY ERROR section. The data type for the child column must match the parent column exactly. For example, since medicalhistory.MedicalHistoryID is an INT, Patient.MedicalHistory also needs to be an INT, not a SMALLINT. Also, you should run the … Read more
Combining “LIKE” and “IN” for SQL Server [duplicate]
Effectively, the IN statement creates a series of OR statements… so SELECT * FROM table WHERE column IN (1, 2, 3) Is effectively SELECT * FROM table WHERE column = 1 OR column = 2 OR column = 3 And sadly, that is the route you’ll have to take with your LIKE statements SELECT * … Read more
How do I obtain a Query Execution Plan in SQL Server?
There are a number of methods of obtaining an execution plan, which one to use will depend on your circumstances. Usually you can use SQL Server Management Studio to get a plan, however if for some reason you can’t run your query in SQL Server Management Studio then you might find it helpful to be … Read more
T-SQL Cast versus Convert
CONVERT is SQL Server specific, CAST is ANSI. CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don’t care about the extended features, use CAST. EDIT: As noted by @beruic and @C-F in the comments below, there is possible loss of precision … Read more
How can I make SQL case sensitive string comparison on MySQL?
The good news is that if you need to make a case-sensitive query, it is very easy to do: SELECT * FROM `table` WHERE BINARY `column` = ‘value’
Simulating group_concat MySQL function in Microsoft SQL Server 2005?
No REAL easy way to do this. Lots of ideas out there, though. Best one I’ve found: SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names FROM information_schema.columns AS extern CROSS APPLY ( SELECT column_name + ‘,’ FROM information_schema.columns AS intern WHERE extern.table_name = intern.table_name FOR XML PATH(”) ) pre_trimmed (column_names) GROUP BY table_name, column_names; Or … Read more
Connect different Windows User in SQL Server Management Studio (2005 or later)
While there’s no way to connect to multiple servers as different users in a single instance of SSMS, what you’re looking for is the following RUNAS syntax: runas /netonly /user:domain\username program.exe When you use the “/netonly” switch, you can log in using remote credentials on a domain that you’re not currently a member of, even … Read more