New answers tagged

1

The results from these views do not overlap and together cover 100% of the table. What keeps you from just querying the underlying table? Should be fastest: SELECT x.* FROM cases x JOIN case_clients cacl ON cacl.case_id = x.main_id WHERE cacl.client_id = 12046 ORDER BY x.sort_nr, x.id;


2

What you're asking the DB to do in Query one is: Give me ALL from table A FILTERED Give me ALL from table B FILTERED Give me ALL from table C FILTERED Give me ALL from table D FILTERED And then Union. In the second query you first get all the data, and only after that you do the join and the filter. JOIN and WHERE on a UNION query, which doesn't really ...


0

You are really creating your own authentication methods. Plan A: Use the PAM stuff in newer versions of MySQL. Plan B: Write a database layer where the user, via some UI, asks for something. Then the layer checks your tables to see whether he is allowed to see/change/etc the thing in question. Then it either denies access, or proceeds to generate and ...


1

Something like that could be achieved through dynamic sql, or via using stored procedures instead of views (for selecting) or triggers (for controlling inserts/updates).alter But you can also create the memberview to return values according to the currently logged on user, which is probably more practical: create view memberview as select Dept, Name ...


0

A VIEW is a shorthand for accessing the underlying table(s). If the table(s) are not there, the VIEW won't work. You can reach into other servers via FEDERATED. See also Spider (a 3rd party product). Each has limitations. Master-Slave is predicated that the Slave always contains an exact copy of the Master. (There are exceptions, but your question does ...


4

A less comprehensive answer than Aaron's but the core issue is a cardinality estimation bug with DATEADD when using the datetime2 type: Connect: Incorrect estimate when sysdatetime appear in a dateadd() expression One workaround is to use GETUTCDATE (which returns datetime): WHERE CreatedUtc > CONVERT(datetime2(7), DATEADD(DAY, -365, GETUTCDATE())) ...


5

In some scenarios SQL Server can have really wild estimates for DATEADD/DATEDIFF, depending on what the arguments are and what your actual data looks like. I wrote about this for DATEDIFF when dealing with beginning of the month, and some workarounds, here: Performance Surprises and Assumptions : DATEDIFF But, my typical advice is to just stop using ...


1

Replace dateadd() with datediff() to get an adequate approximate (30%ish). select distinct SessionId from [User].Session -- 1.2M est, 3.0M act. where datediff(day, CreatedUtc, sysutcdatetime()) <= 365 This appears to be a bug similar to MS Connect 630583. Option recompile makes no difference.



Top 50 recent answers are included