Hot answers tagged

15

A) Scale The middle tier can be scaled easily - hence the web farm concept. Scaling out the DB tier is much more difficult. While some products can do this it is not yet trivial and mainstream. B) Cost Typically web servers are common-or-garden boxes. DB servers, however, tend to be larger, more complex and more resilient. This all translates to "expensive....


4

In SQL Server 2014 & up, new cardinality estimation logic was introduced. From BOL : The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work ...


3

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating ...


3

You cannot refer to an alias defined in the same SELECT list. There are various workarounds. Assuming your query is something like this: select nome, -- various complex expression / subqueries <complex_expression_or_subquery> as Laudos_Expedidos, <complex_expression_or_subquery> as Laudos_Expedidos2 ...


3

When I have a situation where someone (AD group or user) needs to TRUNCATE a table or a set of tables, I write the code in a stored procedure using the WITH EXECUTE AS 'dbo' clause, and grant execute rights to this procedure to the AD group/user who is designated to do the job. For example: CREATE PROCEDURE dbo.TruncateMyTable WITH EXECUTE AS 'dbo' AS ...


3

Contradiction Detection could kick in to make sure only one of the statements is run, and in my simple test it did as long as there was a statement-level recompile hint, but why risk it? For example: USE tempdb GO -- CREATE SCHEMA agg --DROP TABLE agg.DataMin --DROP TABLE agg.DataMedian --DROP TABLE agg.DataWeightedAverage --GO CREATE TABLE agg.DataMin ( ...


2

This is a MyISAM table. It is easy to fix. Login to mysql and run this mysql> REPAIR TABLE mysql.proc; and you should be good to go. BTW this is where stored procedure are stored.


2

We have all been there. It sounds like you don't have a specific question, but just need some help getting started. You need to learn about the sys tables available in SQL Server and the DMV's. They are insanely useful. Ones that will be helpful in your case: sys.procedures - gets you a list of your sprocs sys.object - gets a list of all objects ...


1

You cannot use @variable_name to declare variable. @ can be used only for session variables. Change your code to this, it will work DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE batchsize BIGINT DEFAULT 1; END //


1

You use DECLARE in compound-statement syntax to declare a local variable, cursor, condition or handler. However, your variable @batchsize is a session global variable due to the presence of the @ prefix, and it is an error to attempt to redeclare a global variable. Change the name of your variable from @batchsize to batchsize, and then read up on MySQL ...


1

Callbacks are totally against the idea behind SQL. It's designed as declarative language which queries describe the task and query optimizer builds composition of sorts, index seeks, merges, etc to meet the query. No any row-by-row processing by callbacks. Oracle supports overloading, with restrictions http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/...


1

You can create a mapping table with two columns, one key (a string containing the called procedure name plus arguments if you need that) and one value (an integer containing the number of times the corresponding procedure has been called). The first thing you do in your procedure you want to monitor is to update the mapping table to record the procedure ...


1

The core problem is that your query can potentially match on two different procedures in two different databases that happen to have the same object_id. While there isn't a reliable way to reproduce it on demand, I've seen it enough times to know it is common, and your query needs to protect against it. You can do that with a WHERE clause: WHERE s....


1

Don't do the SELECT MAX thing; you can run into concurrency problems there. If the patterns are simple enough, and have only a single increasing numeric portion, you can probably get away with using an identity column plus a persisted computed column to create the formatted string value: CREATE TABLE IDTest ( row_id int IDENTITY(1,1) NOT NULL, ...


1

This question actually came up 2.5 years ago on StackOverflow, and I posted an Answer there: SQL print line number in comment of dynamically created stored procedure? The relevant portion of that Answer is copied below: You can use TRY / CATCH with a forced error as the CATCH block can return the line number that the error occurred on via the ERROR_LINE(...


1

As DBNull pointed out, you are experiencing this challenge because the Team column is breaking First Normal Form. The values in that column should be atomic. You have two options here. The first is to follow DBNull's recommendation of having the teams registered in their own Team table and having a separate TeamGroup table to store Team Groups. While you're ...


1

If there is a single SQL statement of moderate size (so it fits in a single network packet) then executing that statement from the application or packaging it as an SP and executing the SP will be identical network-wise. However, a pattern I often see is that an application function requires several SQL statements to produce a result. Let's say we are ...



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