Using collations eg: if (‘a’=’A’ Collate Latin1_General_CI_AI) print’same 1′ else print ‘different 1’ if (‘a’=’A’ Collate Latin1_General_CS_AI) print’same 2′ else print ‘different 2’ The CS in the collation name indicates Case Sensitive (and CI, Case Insensitive). The AI/AS relates to accent sensitivity. or in your example SUBSTRING(author,1,1) <> LOWER(SUBSTRING(author,1,1)) COLLATE Latin1_General_CS_AI
No you can’t use case and in like this. But you can do SELECT * FROM Product P WHERE @Status=”published” and P.Status IN (1,3) or @Status=”standby” and P.Status IN (2,5,9,6) or @Status=”deleted” and P.Status IN (4,5,8,10) or P.Status IN (1,3) BTW you can reduce that to SELECT * FROM Product P WHERE @Status=”standby” and P.Status … Read more
Use alter table to add a new constraint: alter table foo add constraint check_positive check (the_column > 0); More details and examples are in the manual: http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN70043 Edit Checking for specific values is done in the same way, by using an IN operator: alter table foo add constraint check_positive check (some_code in (‘A’,’B’));
You can use a deadlock graph and gather the information you require from the log file. The only other way I could suggest is digging through the information by using EXEC SP_LOCK (Soon to be deprecated), EXEC SP_WHO2 or the sys.dm_tran_locks table. SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, … Read more
If you want to average date only: SELECT CAST(AVG(CAST(datefield AS INT)) AS DATETIME) FROM dates; And if you want to consider time: SELECT CAST(AVG(CAST(datefield AS FLOAT)) AS DATETIME) FROM dates; See fiddle to test.
Here’s a generic way to transform this update query from SQL-server form to PostgreSQL: UPDATE Users SET bUsrActive = false WHERE ctid IN ( SELECT u.ctid FROM Users u LEFT JOIN Users u2 ON u.sUsrClientCode = u2.sUsrClientCode AND u2.bUsrAdmin = 1 AND u2.bUsrActive = 1 WHERE u.bUsrAdmin = 0 AND u.bUsrActive = 1 AND u2.nkUsr … Read more
Alt + F1 is a good shortcut key for sp_help. sp_helptext is another goodie for getting stored procedure text.
For the field to be able to store unicode characters, you have to use the type nvarchar (or other similar like ntext, nchar). To insert the unicode characters in the database you have to send the text as unicode by using a parameter type like nvarchar / SqlDbType.NVarChar. (For completeness: if you are creating SQL … Read more
You can use: select (Select Count(*) as StockCountA from Table_A where dept=”AAA”) as StockCountA, (Select Count(*) as StockCountB from Table_B where dept=”BBB”) as StockCountB Explanation: you can select single value as a field in a select statement, so you could write something like select x.*, (select Value from Table_Y y) as ValueFromY from Table_X x … Read more
There is no difference. It seems to me that there might be a difference when it comes to performance. Anyone care to elaborate on this? All major engines (that is MySQL, SQL Server, Oracle and PostgreSQL) will merge these predicates on parsing stage, making identical plans from them. Handling of these conditions is more complex … Read more