Tag Info

Hot answers tagged

4

Generally, you can always wrap a query as subquery if you don't want to output all rows: SELECT title FROM (SELECT ...) sub; But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented). For the case at hand, it must be mentioned that you are using the additional module ...


2

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example: INSERT INTO Contacts (number, ...


2

Your best bet will be to use SSIS or BULK INSERT. There are various performance improvements that you can do when using them and they are very well documented in The Data Loading Performance Guide. At SSIS level, you can look into below things to speed up data read and data load : Fast Parse Option along with its limitations. Use the SQL Server Native ...


2

You need conditional aggregation using sum(case): select cat_id, sum(case when status = 1 then 1 else 0 end) as published, -- only count status 1 sum(case when status = 0 then 1 else 0 end) as unpublished,-- only count status 0 count(*) as totals from tab group by cat_id Edit: My crystal ball was apparently broken, but now it's fixed :-) If ...


1

Are strings in table columns represented as bit patterns or Unicode? Computers only deal with 1's and 0's (i.e. binary); datatypes indicate how to interpret that info. Would the queries perform faster if the column in the table was designed to have the year at the left of the name in the strings that are stored? (assuming the year component of the ...


1

WITHOUT INDEXING SELECT * FROM actor_info WHERE film_info LIKE '%Animation%' OR film_info LIKE '%Documentary%'; or SELECT * FROM actor_info WHERE LOCATE('Animation',film_info)>0 OR LOCATE('Documentary',film_info)>0; WITH INDEXING Create a FULLTEXT index on film_info ALTER TABLE actor_info ADD FULLTEXT (film_info); Query using the FULLTEXT ...


1

Are you just looking for distinct Agents that have at least one null invoice and at least one non-null invoice? SELECT [AC].[Agent] FROM [AgentCommTMP] AS [AC] GROUP BY [AC].[Agent] HAVING Count(CASE WHEN [AC].[InvoiceID] IS NULL THEN 1 ELSE Null END) > 0 AND Count(CASE WHEN [AC].[InvoiceID] IS NULL THEN Null ELSE 1 END) > 0


1

Granting schema modification is not, and should not be, the same as granting DML rights. Grant the user the exact rights that user needs, including 'SELECT', etc, as necessary. See https://msdn.microsoft.com/en-us/library/ms178569.aspx for details.



Only top voted, non community-wiki answers of a minimum length are eligible