New answers tagged

0

For the purpose of this answer, I am assuming your INSERT statement looks something like this: INSERT INTO dbo.vSubCategories (CategoryName, SubCategory) SELECT CategoryName, SubCategory FROM ... /* reading rows either from the CSV directly or from a staging table into which the CSV has been imported */ ; So, the first thing your ...


1

Automating on the Derek's solution, this will change DEFINER to root@localhost and set SQL SECURITY INVOKER (make sure you want that first!) in all views in all databases: mysql -BNe 'show databases' | \ egrep -v '^(information_schema|performance_schema)$' | \ while read DB do mysql -BNe "SELECT CONCAT(\"ALTER DEFINER=\`root\`@\`localhost\` SQL ...


0

The real issue is not nested views in themselves. The real issue is the proliferation of nested views as developers layer additional tweaks on existing views. I have found queries with a nested view 4 layers that actually joined to one of the views in it's definition. Our tendency to take the easy way out rather than analyze and solve a problem is the root ...


2

A recursive view is just syntactic sugar for a recursive CTE. create or replace recursive view foo (name, groupid, parentid) as select name, groupid,parentid from rl.groups where groupid=2 union SELECT groups.name, groups.groupid, groups.parentid FROM rl.groups AS groups, foo WHERE groups.groupid = foo.parentid ; is ...


3

With schemabinding can improve performance in some cases. I couldn't recall all the details, but found an excellent blog post that goes through this in detail: With user defined functions that may need to update data. You might benefit from improved performance that would prevent an Eager Table Spool. SQL Server SCHEMABINDING Edit: Paul White ...


0

Direct answers to your three questions would be; Yes it does. When you replace your view, the lock of view object will be AccessExclusiveLock. Yes, even the 'select' queries that uses that specific view will wait until the 'create or replace' statement is committed. Yes they do. After the "view changer" query is committed the queries that are waiting will ...


1

If all possible filter types (=@d, between @d1 and @d2, day of week in (@dw1,.. @dw7), ...) can be defined at design time then create TVF[s] the way Paul White described. Otherwise you need dynamic sql. Views wouldn't help anyway.


3

If you want to filter rows before the aggregation, I would use an inline table-valued function instead of a view: CREATE FUNCTION dbo.PlaceStatisticsForDate (@DateFilter AS date) RETURNS TABLE AS RETURN SELECT COUNT(PS.TypeID) AS TypeCount, PS.TypeID FROM dbo.PlaceStatistics AS PS WHERE PS.mydatefield = @DateFilter GROUP BY PS.TypeID; ...


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 ...



Top 50 recent answers are included