When to add another lookup table for the same field Example

Table Recipe uses a lookup table for Recipe Category;
Table Ingredient uses a lookup table for Ingredient Category;
They are in a Many-to-Many relationship;
A Recipe Category will not be used as a Ingredient Category or vice-versa;

Question
Should it be separate lookup tables, or can I use one table with an extra column to identify if it's an Ingredient or Recipe Category? Does this affect optimization or query speed?
Roughly 100 Records if a single table; separated tables it's 5 and 95. The table will appear more than two times in the same query.
I tested the ideas in the related Q & A Relational tables with same content, custom solution. It's easier to handle data on the frontend side and housekeeping, but data integrity could be an issue if the database grows.
Clarifications

A recipe can be classified by more than one recipe category
An ingredient can be classified by more than one ingredient category
There are 5 recipe categories in the business domain
There are 95 ingredient categories in the business domain
Three examples of concrete recipe categories are Omivorous, Vegetarian, Vegan.
Three examples of concrete ingredient categories are Cereal, Meat, Fish.

I've been testing around with both solutions but with so few records I don't see any improvement or loss of performance. Also when building the front end, it's easier to work with one model and a column than 2 models but it's a minor issue.
Related Q & A on Stack Overflow: One lookup table or many lookup tables?

Although RecipeCategory and IngredientCategory have very similar names and attributes, they are in fact two different entity types, because each of them (a) carries a specific business domain meaning, (b) has distinct kinds of relationships and (c) entails a particular set of rules.
In this regard, if the intention is to implement a relational database (RDB), it is quite helpful to perform an analysis of the business domain of interest (in order to construct a conceptual model) in terms of entity types (i.e., types or prototypes of entity occurrences), their attributes and interrelationships before thinking in terms of tables, columns and constraints (points that correspond to the logical model). Proceeding in this fashion, it is much easier to capture the meaning of the business domain with accuracy and then reflect it in an actual RDB structure.
Business domain rules
Recipe and RecipeCategory
Let us start working with two entity types: Recipe and RecipeCategory. In the scenario in question, it appears reasonable to affirm that:

A Recipe is classified by zero-one-or-many RecipeCategories
A RecipeCategory classifies zero-one-or-many Recipes

Such situation indicates, yes, that Recipe and RecipeCategory are involved in a many-to-many (M:N) relationship, which implies the existence of an associative entity type, that I am going to call RecipeCategorization.
Ingredient and IngredientCategory
Then, let us deal with Ingredient and IngredientCategory. In this case, we can affirm that:

An Ingredient is grouped by zero-one-or-many IngredientCategories
An IngredientCategory groups zero-one-or-many Ingredients

This means that Recipe and RecipeCategory are connected in another M:N relationship, which entails the existence of another associative entity type, that I denominated IngredientCategorization.
RecipeCategory and IngredientCategory
As discussed above, one can observe that the concrete occurrences of RecipeCategory are meant to be (directly) associated with the specific instances of Recipe, and not with the occurrences of Ingredient. In the same manner, the concrete instances of IngredientCategory are meant to be (directly) connected with the specific occurrences of Ingredient, and not with the instances of Recipe. Therefore, RecipeCategory and IngredientCategory are distinct entity types, and demand their own respective individual considerations.
Recipe and Ingredient
Finally, we can assume that:

A Recipe includes one-to-many Ingredients
An Ingredient is included in zero-one-or-many Recipes

Thus, there is another M:N relationship, this time between Recipe and Ingredient, which reveals the existence of other associative entity type, that I am going to entitle RecipeListing.
Illustrative IDEF1X model
Then, from the aforementioned analysis and consequent formulations, I created the IDEF1X† model shown in Figure 1:

As demonstrated, each entity type is depicted in its corresponding individual box, and is displayed directly related to (i) its own attributes, contained in the respective box, and to (ii) the entity types that apply, by way of the relationship lines.
Of course, there are other indirect relationships that should be derived via the direct connections exposed here.
Logical and physical elements
Once we have analyzed and defined the pertinent types of the things of significance, it is time to determine how to manage them by means of mathematical relations (declared and visualized as tables, if created on a certain SQL database management system), which are composed of domains (portrayed as columns) and tuples (pictured as rows).
As relations are abstract resources, Dr. E. F. Codd —the originator of the relational paradigm— envisioned the utility of representing them in tabular form, so that, e.g., the users and implementers of a RDB can approach them in a more familiar way. In this respect, even though a relational table has a concrete shape, it is still a logical element of a given database, and its components, e.g., columns, rows and constraints are logical as well.
In this regard, it is very important and of vast pragmatical value to distinguish logical from physical elements. For instance, in file systems, a physical record can be made up of zero, one or more fields. In the case of a RDB, the logical elements can be served by one or more physical units (at a lower level of abstraction, then), e.g., indexes, records, pages, extents, etc.
Thus, in accordance with the points detailed above, a table —being a logical level component— does not have fields (which may well be part of the underlying concrete scaffoldings supporting a table declaration, but work at the physical level).
Expository logical SQL-DDL structure
That being said, and based on the IDEF1X model previously presented, both RecipeCategory and IngredientCategory (and the rest of the identified entity types too) require an individual base table that stands for each of them, as exemplified in the following DDL structure:
-- You have to determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient physical implementation settings; e.g.,
-- a good INDEXing strategy based on query tendencies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE RecipeCategory ( -- Plays a ‘look-up’ role.
    RecipeCategoryCode CHAR(2)  NOT NULL,  -- This column can retain the values: ‘O’ for ‘Omnivorous’; ‘VT’ for ‘Vegetarian’; ‘VG’ for ‘Vegan’; etc.
    Name               CHAR(30) NOT NULL,
    Description        CHAR(60) NOT NULL,
    Etcetera           CHAR(30) NOT NULL,
    CreatedDateTime    DATETIME NOT NULL,
    CONSTRAINT RecipeCategory_PK  PRIMARY KEY (RecipeCategoryCode),
    CONSTRAINT RecipeCategory_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT RecipeCategory_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE Recipe (
    RecipeNumber    INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    Description     CHAR(60) NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT Recipe_PK  PRIMARY KEY (RecipeNumber),
    CONSTRAINT Recipe_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT Recipe_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE RecipeCategorization ( -- Represents an associative entity type.
    RecipeNumber       INT      NOT NULL,
    RecipeCategoryCode CHAR(2)  NOT NULL, -- Contains meaningful and readable values.
    Etcetera           CHAR(30) NOT NULL,
    ClassifiedDateTime DATETIME NOT NULL,
    CONSTRAINT RecipeCategorization_PK                   PRIMARY KEY (RecipeNumber, RecipeCategoryCode), -- Composite PK.
    CONSTRAINT RecipeCategorization_to_Recipe_FK         FOREIGN KEY (RecipeNumber)
        REFERENCES Recipe (RecipeNumber),
    CONSTRAINT RecipeCategorization_to_RecipeCategory_FK FOREIGN KEY (RecipeCategoryCode)
        REFERENCES RecipeCategory (RecipeCategoryCode)
);

CREATE TABLE IngredientCategory ( -- Plays a ‘look-up’ role.
    IngredientCategoryNumber INT      NOT NULL,
    Name                     CHAR(30) NOT NULL,
    Description              CHAR(60) NOT NULL,
    Etcetera                 CHAR(30) NOT NULL,
    CreatedDateTime          DATETIME NOT NULL,
    CONSTRAINT IngredientCategory_PK  PRIMARY KEY (IngredientCategoryNumber),
    CONSTRAINT IngredientCategory_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT IngredientCategory_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE Ingredient (
    IngredientNumber INT      NOT NULL,
    Name             CHAR(30) NOT NULL,
    Description      CHAR(60) NOT NULL,
    Etcetera         CHAR(30) NOT NULL,
    CreatedDateTime  DATETIME NOT NULL,
    CONSTRAINT Ingredient_PK  PRIMARY KEY (IngredientNumber),
    CONSTRAINT Ingredient_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT Ingredient_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE IngredientCategorization ( -- Stands for an ssociative entity type.
    IngredientNumber         INT      NOT NULL,
    IngredientCategoryNumber INT      NOT NULL,
    Etcetera                 CHAR(30) NOT NULL,
    GroupedDateTime          DATETIME NOT NULL,
    CONSTRAINT IngredientCategorization_PK                       PRIMARY KEY (IngredientNumber, IngredientCategoryNumber), -- Composite PK.
    CONSTRAINT IngredientCategorization_to_Ingredient_FK         FOREIGN KEY (IngredientNumber)
        REFERENCES Ingredient (IngredientNumber),
    CONSTRAINT IngredientCategorization_to_IngredientCategory_FK FOREIGN KEY (IngredientCategoryNumber)
        REFERENCES IngredientCategory (IngredientCategoryNumber)
);

CREATE TABLE IngredientListing ( -- Denotes an associative entity type
    RecipeNumber     INT      NOT NULL,
    IngredientNumber INT      NOT NULL,
    Etcetera         CHAR(30) NOT NULL,
    IncludedDateTime DATETIME NOT NULL,
    CONSTRAINT IngredientListing_PK               PRIMARY KEY (RecipeNumber, IngredientNumber), -- Composite PK.
    CONSTRAINT IngredientListing_to_Recipe_FK     FOREIGN KEY (RecipeNumber)
        REFERENCES Recipe (RecipeNumber),
    CONSTRAINT IngredientListing_to_Ingredient_FK FOREIGN KEY (IngredientNumber)
        REFERENCES Ingredient (IngredientNumber)
);

--
--

With such structure, you prevent ambiguities and all their logical and pragmatic repercussions. You avoid mixing up

the representation of multiple entity types in a single “shared” table, and
the meaning and intention of each of their attributes (in “shared” columns),

which permits restricting much more easily their corresponding domains of values and the subsequent references through FOREIGN KEY (FK) constraints. The queries and result sets become much more clear because each aspect is approached separately.
This structure, hence, aids by itself in reflecting the business context under consideration with precision, remaining consistent with the characteristics delimited in the conceptual analysis, and guaranteeing that the data (every assertion in the form of a row) complies with the business rules.
Practical considerations regarding the RecipeCategory table
Seing that the RecipeCategory table is supposed to (1) fulfill a look-up role and (2) would hold only a few rows, I consider that it would be very advantageous to declare it with a PRIMARY KEY (PK) constraint on a column that keeps values that are meaningful and, at the same time, physically light and  narrow, i.e., of type CHAR(2) or maybe CHAR(3). So it might comprise, e.g., the rows that follow:

+-——————————————————-+-——————————-+-—————————————————————————-+-————-+-————-+
| RecipeCategoryCode | Name       | Description               | Etc… | Cre… |
+-——————————————————-+-——————————-+-—————————————————————————-+-————-+-————-+
| O                  | Omnivorous | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+
| VT                 | Vegetarian | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+
| VG                 | Vegan      | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+
| F                  | Foo        | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+
| B                  | Bar        | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+

In this manner, when such PK column “migrates” to the RecipeCategorization table as a column with a FK constraint, it will have stable values that maintain their meaning and intention, making the result sets much more readable than, say, an INT value, which definitely can assist in the interpretation of the information obtained through, e.g., derived tables (those fetched back by virtue of a SELECT statement or a VIEW definition).
All this remains in agreement with the spirit of the relational model†† published in 1970 by Dr. Codd, where he included the following relevant note:

Naturally, as with any data put into and retrieved from a computer system, the user will normally make far more effective use of the data if he is aware of its meaning.

Relational databases and application programs
The extract of your question cited below has to do, among other things, with the “link” between (a) the database under discussion and (b) the application programs (apps) that will work along with it:

I tested the ideas in the related Q & A Relational tables with same content, custom solution. It's easier to handle data on the frontend side and housekeeping, but data integrity could be an issue if the database grows.

It is opportune to point out that the data, in its essence and of its nature, is a highly valuable organizational asset; as a result, it must be administered as such. This fundamental resource tends to outlive apps, app development platforms and programming paradigms.
With that, a RDB ought to be an independent (self-protective, self-describing, etc.) software component that is capable of being shared by multiple apps, and it must not be “coupled” —using object oriented programming parlance— with the code of any of these apps.
Consequently, you should (a) manage the data by dint of the instruments provided by the relational theory, the modeling platforms and the SQL sytem of choice, and (b) shape and implement the relevant processes and the graphical user interface with the pertinent app development tools. In this way, all the software components will work harmonically; they will be independent but, at the same time, well interconnected.

Endnote and reference
† Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early relational model works authored by Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
†† Codd, E. F. (June 1970). A Relational Model of Data for Large Shared Data Banks, Communications of the ACM, Volume 13 Issue 6 (pp. 377-387). New York, NY, USA.


Reducing high VLF count I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model.

I ran a full index rebuild at the weekend during which the log file swelled to about 32GB on a 46GB data file with 10MB log autogrowth which I imagine has caused the high VLF count.

From what I understand I should have upped the autogrowth, however this is not typical activity. Typically the .trn files backed up every few hours are 100-200MB so 10MB doesn't seem ridiculous.

So, I think I need to shrink the log file and allow it to grow again in as few growths as possible as scripted here out of hours. Does this sound sensible in this particular situation?

Also, the initial size of the log file is now 33896MB should I reduce this to something more sensible (200MB) after the shrink?

10MB is ridiculous, IMHO. Is your I/O subsystem that slow where limiting growth to such a small increment is necessary? Ideally you should not be observing AutoGrow at all, by sizing the log large enough to handle your largest transactions anyway. And only use AutoGrow as a crutch to protect you from abnormal events, such as this one, in which case it should be rather large.

And no, I would not reduce your log file to 200MB, and I'm not sure how that is "more sensible." Growth events are expensive, particularly with the log file, since the expanded portion of the file has to be zeroed out. You want to size the log file in such a way that you will avoid any predictable growth events. If you will be performing another similar index rebuild in the future, I don't understand what you would gain by shrinking the log file to 200MB in the meantime. Are you going to lease the freed up space out to someone else, then kick them out when your log needs the space again? I would say leave the log at 32GB (shrinking it and expanding it one event, as the script you pointed out suggested) and monitor for AutoGrow events (these are captured in the default trace, so you don't have to set anything up, just look at it once in a while). (On more modern versions, should think about using Extended Events for tracking growth events instead.) This will tell you if you need to consider making it even bigger.

There is no prize for smallest log file - you want it to be large enough to accommodate all of your normal (and abnormal) log activity without having to grow.


Why do I get an error when filtering subSelect? select "masters".*, (select avg (rating)
from "reviews" where "master_id" = "masters"."id") as rating
from "masters" where rating > 3

  [42703] ERROR: column "rating" does not exist

You can't use a column alias on the same level where you defined it. You need to wrap the query:

select *
from (
  select masters.*, (select avg(rating)
                     from reviews
                     where master_id = masters.id) as rating
  from masters
) t
where rating > 3;

A more efficient solution is however, to do the aggregation once for all rows and then join to the result:

select m.*, r.rating
from masters m
  join (
     select master_id, avg(rating)  as rating
     from reviews
     group by master_id
     having avg(rating) > 3
  ) r on r.master_id = m.id;


Query to get the physical file name of the database Im trying to get the physical file name of a database, and when i query sys.database_files, under the physical file name column, you get the whole path (ie. S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ). How can i dynamically extract just the tempdb.mdf off of that string? I was thinking of using SUBSTRING() + CHARINDEX() but cant figure it out since im new to this. CHARINDEX() only searches for a substring from left to right. Is there a function which searches a particular string from right to left? That way i can just search from the last back slash base from my example. Any input would be very much appreciated. Thanks!

You can try this.

@Stringss is a variable to simulate the field you already obtained

DECLARE @String NVARCHAR(MAX)='S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'

SELECT REVERSE(SUBSTRING(REVERSE(@String),0,CHARINDEX('\',REVERSE(@String),0)))


SQL Server 2012 - Deadlock happening daily We have a performance issue with a 3rd-Party application using SQL Server 2012: deadlock happen at least 10 times an hour.

The server is virtual server on VMWare 5.5 with PVSCSI adapter in persisted mode for SQL Server disks, 4 vCPU (that are seen by SQL Server as 2 CPU with 2 cores).
It runs Windows Server 2012.

As we already had the issue, our supplier asked to set MAXDOP to 1 as they experienced problems with parallelism. It didn't solve the situation.

I ran the DBCC TRACEON (1222, -1) to get extended informations about deadlocks and transmit it to the supplier.

Edit: Example deadlock graph

In XML Format: (I was not able to add it to the post)
XML Format

Edit2: Index Maintenance

The following Job runs Ola Hallengren MaintenanceSolution (IndexOptimize) :

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d SAIDBA -Q "EXECUTE maintenance.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationLevel1 = 3,@FragmentationLevel2 = 30,@UpdateStatistics = 'ALL',@OnlyModifiedStatistics = 'Y',@LogToTable = 'Y'" -b

Edit 3: update on the situation

I did some mining on the error log to get an overview of the time line for deadlock occurrences. It happened three to four times more frequently after a software update (120 per day), but there were already 30-40 per day previously.

As the application supplier doubts about our indexing strategy, he wants us to do it.. We will rebuild all the indices..
We will see if it has positive effects...

What else can I do to investigate/troubleshoot/Reduce the number of deadlocks ?

Short answer

Check ERRORLOG for deadlock frequency (is it regular or very new ?)
Ensure Index Maintenance exists and has run successfully.
Setup a trace either with SQL Server Profiler, an Extended Events solution or via Trace Flag 1222.
Analyze the results of the trace:

What queries were involved ?
Which tables/indices are accessed when it happens and how frequently ?

Anything else ?

Deadlocks are "simply" application logic errors. In SQL Server I think you can do the following:

review the application logic;
You can use transaction isolation in snapshot mode SET TRANSACTION ISOLATION LEVEL

See also:

How to Track Down Deadlocks Using SQL Server 2005 Profiler

written by Brad McGehee. I think that he is more authoritative than me. He states:

  ...Deadlocks have been the bane of many a DBA. While rare for a well-designed and written application, deadlocks can be a major problem for—how can I say this delicately?—“less efficient” application code. What is even more frustrating is there is not much the DBA can to do prevent deadlocks, as the burden of preventing them in the first place is on the developers of the application...."


Where to put utility (not administration) stored procedures in SQL Server? Possible Duplicate:
  Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

I have some simple user-defined functions for string manipulations. Most of them are actually CLR functions but not all of them are. I use them from multiple databases on the same server. What is the proper place to put these?

I've been placing them in the master DB so far. Is that a bad idea? If so, why? Is it standard practice to have a utility database that has no data but only progammability objects?

I would put them in a Utility database.

Putting stuff in the master database is something to avoid as it stores information related to the system configuration.  If there is a problem with the master database, your SQL Server will go down.  It's probably ok to put normal stored procedures used for server level troubleshooting like sp_WhoIsActive but I would never put a CLR based procedure in there.


How to convert date and/or time from character string as variable field I have a backup report which looks to see if all databases have been backed up within the last 30 days, and want to insert 'Not all databases backed up in last 30 days' into that field (datetime type) if it is the case that not all databases have been backed up within the last 30 days but i get the error Conversion failed when converting date and/or time from character string.

;WITH CTE AS
(
SELECT
--sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb') AND bus.backup_finish_date > DATEADD(day,-30,GETDATE())
 AND sdb.Name NOT IN ('DBA')
AND sdb.state_desc  = 'ONLINE'
--GROUP BY sdb.Name
)
INSERT INTO Server.Database.Schema.TableName
SELECT @@SERVERNAME  as serverName,
        --DatabaseName as DatabaseName,
        CASE WHEN LastBackupTime IS NULL THEN 'Not all databases backed up in last 30 days' ELSE MIN(LastBackUpTime) END as LastBackUpTime

FROM CTE
GROUP BY LastBackUpTime

The below should return 'Not all databases backed up' if any of the databases haven't been backed up, and 'All databases backed up as of DD/MM/YYYY' if they have (this will be the highest backup date found)

;WITH CTE AS
(
SELECT
--sdb.Name AS DatabaseName,
ISNULL(MAX(backup_finish_date),0) AS LastBackUpTime,
[sdb].[name] AS DatabaseName
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE (bus.type = 'D' OR bus.[database_name] IS NULL)
AND sdb.name NOT IN ('master', 'model', 'msdb', 'tempdb','DBA')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT
    CASE
        WHEN MIN([CTE].[LastBackUpTime]) IS NULL THEN 'Not all databases backed up'
        WHEN MIN([CTE].[LastBackUpTime]) <= DATEADD(dd,-30,GETDATE()) THEN 'Not all databases backed up'
        ELSE 'All databases backed up as of ' + CAST(MAX([CTE].[LastBackUpTime]) AS NVARCHAR(100))
    END
FROM CTE

The script looks for the highest backup date for all non-system (and non-DBA) databases, and returns the datetime equivalent of 0 if there are none (this converts to 1900-01-01 00:00:00.000). We can then check whether the minimum date is in our 30 day region or not.

The actual issue you were experiencing was because your CASE statement returned both a DATETIME data type and an VARCHAR data type - because of data type precedence for a CASE statement SQL server will always choose the data type of the highest precedence as the data type of that field - in this case that was a DATETIME.

To prevent this we simply need to CAST the DATETIME field as a VARCHAR, which prevents the implicit conversion from VARCHAR to DATETIME (lower to higher precedence)


Why and how and where to use user without login I've been trying to find why, how and where to use Users Without Login and I didn't find my exact question or anything that gets closer.
Where I did my research (I also read other posts, saw Youtube Videos):

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9dbd233a-a105-473f-acab-6d8272d5772b/use-of-lt-sql-user-without-login-gt?forum=sqldatabaseengine

https://blog.learningtree.com/sql-server-users-without-logins/

https://blog.sqlauthority.com/2012/10/04/sql-server-importance-of-user-without-login-t-sql-demo-script/

Map a Login to a User that was Created Without Mapping to a Login

https://stackoverflow.com/questions/6468813/assign-a-login-to-a-user-created-without-login-sql-server

I was searching for ways to turn my db more secure and I recently came to know that there's an option to create user without login. I thought it is nice.
But I didn't understand how to use it (not create, use), then after my  research I realized that this user accesses the db while impersonating another user/role.
I thought it would be great to not have a user and somehow access the db.
But when I saw that there's something called execute as I thought that it wasn't useful because why I would execute queries on this "login-less" user as it was another user?
Why I just don't use this user's permission?
Then I thought maybe this user has too much permissions, so I just create a new one with less permissions...
Okay, I don't know why you would create a user without login.
How should this be used?
It's stupid, but I tried to login and failed (sorry, but I'm used to login with users, I never thought on creating a user and not to login, I just know that users login to something, that's where my knowledge is).
So, I don't know why to create this type of user instead of a normal one?
I don't know how (like do I login, do I just type the query and  to use it)?
I don't know where (like, do I need to make an C# app, do I use command...) to use?
My knowledge only takes me till here, I don't have a Senior DBA to ask.
Please explain in simple words if possible.

Imagine that you have somebody running an app, and this requires certain privileges. You do want to be able to log who is doing what and see who are logged in to your SQL Server. So each person need their own login (this can possible be one login created for a domain group).
You don't feel comfortable having these people connecting using SSMS and play around in the database. Since the app might need DELETE permissions on the Customers table (say), then the end-user now can do this from SSMS.
So you create a user without login and have the app use EXECUTE AS as this user. I.e., without EXECUTE AS, the end-user have no privileges at all in the database (i.e., the potential AD group you created a login for and its subsequent user in the database). But they do have privileges when they've done EXECUTE AS.
Is this read security? No, of course not. But perhaps better than either having no traceability or having the end-users being able to play around in the database "directly".
What about application roles? Yes, that can be tighter since they are password protected.
Another usage for this is when you are testing things, using EXECUTE AS USER. You want to play around with something permissions related and it is easier to assign the permissions to a user without login and then do your tests then having to create a login and a user for whatever you want to test.


Derived value not calculating as expected using combination of FK and Triggers (MySQL) I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints.

There are 3 tables involved in this arrangement

responses
------------------------
id_responses
text
n_comments

responses_has_comments
------------------------
id_responses
id_comments

comments
------------------------
id_comments
text

the link table - reviews_has_comments (rhc) exists because comments can be added to several other types of content.

A cascading foreign key constraint on rhc removes rows when a comment or response has been deleted. This is working as expected. I have written a trigger to increment/decrement n_comments on responses when a change occurs in rhc.

The incrementing trigger is working fine, but the decrementing trigger is not. Here are the triggers.

CREATE TRIGGER `inc_n_comments_in_responses`
    AFTER INSERT ON `responses_has_comments` FOR EACH ROW
        BEGIN
            UPDATE responses
            SET n_comments = n_comments + 1
            WHERE id_responses = NEW.id_responses;

        END;
 $$

 CREATE TRIGGER `dec_n_comments_in_responses`
    AFTER DELETE ON `responses_has_comments` FOR EACH ROW
        BEGIN
            UPDATE responses
            SET n_comments = n_comments - 1
        WHERE id_responses = OLD.id_responses;
    END;
$$

Can anyone cast their eye over this and point me in the right direction? As I understand it, the BEFORE and AFTER commands happen before and after the CASCADE.

UPDATE

OK - this is a case of RTFM (although in this instance the manual sucks....)

MySQL documentation on this matter shows that Foreign Key constraints do NOT activate triggers.

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

So - let's look at the options:

1: I can increase the scope of the delete trigger thus:

CREATE TRIGGER `dec_n_comments`
    AFTER DELETE ON `comments` FOR EACH ROW
    BEGIN
        SELECT rshc.id_responses INTO @id_responses
        FROM responses_has_comments AS rshc WHERE id_comments = OLD.id_responses
        IF @id_responses IS NOT NULL THEN
            UPDATE responses
            SET n_comments = n_comments - 1
            WHERE id_responses = @id_responses;
        END IF;
        -- rinse and repeat for all linked "commentable" table data...
    END;
$$

My problem with this is that as the types of content to which comments can be added increases, so to will the size of this IF/ELSE (and it's bigger brother, the UPDATE trigger)

I could possibly remove the multiple IF/ELSE statements this points to
if I write a PROCEDURE to return a placeholder table name and identifier to
use in the subsequent UPDATE clause. This would require an additional field
on the comments table in order to get the relevant table type - but I'm
personally suspicious of referencing table meta data in this way...

I could possibly replace the resource/link/comment paradigm with a one to many
relationship - suffixing each type of comment with it's parent resource: i.e response_comments

Finally - I could just sack this off and keep it in the application - which at the moment
is really looking like the most likely option.

reviews_has_comments (rhc) exists because comments can be added to several other types of content.

If I understand correctly, that is not a justification for responses_has_comments table - you can have several tables with 1-to-many relationships with the same comments table. The only reason for introducing responses_has_comments would be if the same comment can be attached to more than one response, as well as more than one comment being attached to each response, ie it is a many-to-many relationship - I don't think that is what you are trying to achieve.

This does not answer your main question but may sidestep the problem in your updated trigger. Your comments trigger then becomes:

CREATE TRIGGER `dec_n_comments`
    AFTER DELETE ON `comments` FOR EACH ROW
    BEGIN
        UPDATE responses
        SET n_comments = n_comments - 1
        WHERE id_responses = @id_responses;
        -- rinse and repeat for all linked "commentable" table data...
    END;
$$


Does the creation of constraints affect the views that have been created earlier? Does the creation of constraints(foreign key) affect the views that we have created earlier?

create view allreservations (fno, customer) as
SELECT f.flight_number, customer_number
FROM flight f, flight_reservation fr
WHERE f.flight_number = fr.flight_number;

ALTER TABLE <tablename>
ADD FOREIGN KEY (<attribute>)
REFERENCES <tablename>(<attribute>);

No matter what database you are using, you shouldn't find any problem with it, UNLESS you are using the VIEWS to UPDATE\DELETE records (I know is possible in SQL SERVER).

If you are doing that (update\delete on the view), you will get a constraint error once you try to violate the reference.


Cannot open database "dbname" requested by login.The login failed.Login failed for user 'DOMAIN\Windowsusername' I had a database which i dropped using the below command.

USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE dbname;

Now when i refresh the connection in SSMS i get the error message Cannot open database "dbname" requested by login.The login failed.Login failed for user 'DOMAIN\Windowsusername'.Microsoft SQL Server,Error 4060

I remember that the owner for that database was the same login "DOMAIN\Windowsusername" .
So i think now when i connect using the same user login ,it is trying to connect to the dropped database.
Since the database is dropped i cannot change the owner of the db.
How can i get rid of this error.

Any help will be appreciated.

That login probably has the dropped database listed as his/her default database. You just need to assign a different default database.

In SSMS, connect to the server, then go to the Security section, then the Logins section. Find the login in question, right-click, then choose Properties. On the General tab, you'll find a "Default database" field. It probably has the name of the dropped database. Select a database that is still valid, and use the OK button to save your change.


Data for the latest previous effective date sub query Imagine issues with linked

Time entries to track the time spent
Journal entries to track progression evolution

(Actually a part of Redmine schema)
Let's take some sample data:
Issues

id
subject
estimated_hours

1
First issue
4

2
Second issue
8

Time entries

id
issue_id
spent_on
hours

1
1
2020-12-02
1

2
1
2020-12-05
1.5

3
2
2020-12-06
0.5

4
1
2020-12-10
1

5
2
2020-12-10
1

6
2
2020-12-15
2

7
2
2020-12-15
1

Journal

id
issue_id
updated_on
done_ratio

1
1
2020-11-20
0

2
2
2020-11-20
5

3
1
2020-12-01
20

4
1
2020-12-03
40

5
2
2020-12-06
30

6
1
2020-12-07
70

7
1
2020-12-11
100

8
2
2020-12-12
50

Now for each time entry I need to know the latest done ratio.
Here is what I'm expecting

id
subject
spent_on
hours
done_ratio

1
First issue
2020-12-02
1
20

2
First issue
2020-12-05
1.5
40

3
Second issue
2020-12-06
0.5
30

4
First issue
2020-12-10
1
70

5
Second issue
2020-12-10
1
30

6
Second issue
2020-12-15
2
50

7
Second issue
2020-12-15
1
50

I thought I could get that with this query:
SELECT
  tie.id
, iss.subject
, tie.spent_on
, tie.hours
, jou.done_ratio
FROM time_entry AS tie
JOIN issue AS iss ON (tie.issue_id = iss.id)
JOIN (
  SELECT
    issue_id
  , updated_on
  , done_ratio
  FROM journal
  ORDER BY updated_on DESC
) AS jou ON (
  jou.issue_id = tie.issue_id
  AND jou.updated_on <= tie.spent_on
)
GROUP BY tie.id, tie.spent_on
ORDER BY tie.spent_on

But it's returning the first done ratio for every time entry.
Here is a playground to fiddle with the mentioned data
How can I get the done ratio of the latest journal prior to (<=) each time entry ?
I'm using mariadb:10.2.9 here but this should pretty much be standard ANSI SQL

So I'll provide two answers, with the following caveats:

Certain DB engines are bad with correlated subqueries, either in terms of performance or outright lack of support
The row identifier (id) columns carelessly tacked on as a "primary key" will limit performance of both solutions and will likely force a sort, an expensive operation

Given the lack of proper primary keys, the performance may suffer if you are working on a large set of data.  If you're just returning a few hundred/few thousand rows you should be okay on modern hardware.
Methond 1: Correlated Subquery
The most direct way (from a code perspective) is to use a correlated subquery to select the record from journal with the greatest updated_on less than or equal to spent_on from time_entry:
SELECT
  time_entry.id
 ,issue.subject
 ,time_entry.spent_on
 ,time_entry.hours
 ,journal.done_ratio
FROM
  time_entry time_entry
INNER JOIN
  issue issue
    ON issue.id = time_entry.issue_id
LEFT JOIN
  journal journal
    ON journal.issue_id = time_entry.issue_id
         AND journal.updated_on =
           (
             SELECT
               MAX(updated_on)
             FROM
               journal
             WHERE
               issue_id = time_entry.issue_id
                 AND updated_on <= time_entry.spent_on
           )

Method 2: Window Function
You can also use a windowing function to number the rows according to specified criteria, and selecting only the first row.  This forces a sort in all instances.
SELECT
  id
 ,subject
 ,spent_on
 ,hours
 ,done_ratio
FROM
  (
    SELECT
      time_entry.id
     ,issue.subject
     ,time_entry.spent_on
     ,time_entry.hours
     ,journal.done_ratio
     ,ROW_NUMBER() OVER (PARTITION BY time_entry.id ORDER BY journal.updated_on DESC) AS rownum
    FROM
      time_entry time_entry
    INNER JOIN
      issue issue
        ON issue.id = time_entry.issue_id
    LEFT JOIN
      journal journal
        ON journal.issue_id = time_entry.issue_id
             AND journal.updated_on  <= time_entry.spent_on
  ) x
WHERE
  rownum = 1


Is it acceptable to have circular foreign key references\How to avoid them? Is it acceptable to have a circular reference between two tables on the foreign key field?

If not, how can these situations be avoided?

If so, how can data be inserted?

Below is an example of where (in my opinion) a circular reference would be acceptable:

CREATE TABLE Account
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50)
)

CREATE TABLE Contact
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50),
    AccountID INT FOREIGN KEY REFERENCES Account(ID)
)

ALTER TABLE Account ADD PrimaryContactID INT FOREIGN KEY REFERENCES Contact(ID)

Since you are using nullable fields for the foreign keys, you can in fact construct a system that works correctly the way you envision it.  In order to insert rows into the Accounts table you need to have a row present in the Contacts table unless you allow inserts into Accounts with a null PrimaryContactID.  In order to create a contact row without already having an Account row present, you must allow the AccountID column in the Contacts table to be nullable.  This allows Accounts to have no contacts, and allows Contacts to have no account.  Perhaps this is desirable, perhaps not.

Having said that, my personal preference would be to have the following setup:

CREATE TABLE dbo.Accounts
(
    AccountID INT NOT NULL
        CONSTRAINT PK_Accounts
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AccountName VARCHAR(255)
);

CREATE TABLE dbo.Contacts
(
    ContactID INT NOT NULL
        CONSTRAINT PK_Contacts
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ContactName VARCHAR(255)
);

CREATE TABLE dbo.AccountsContactsXRef
(
    AccountsContactsXRefID INT NOT NULL
        CONSTRAINT PK_AccountsContactsXRef
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AccountID INT NOT NULL
        CONSTRAINT FK_AccountsContactsXRef_AccountID
        FOREIGN KEY REFERENCES dbo.Accounts(AccountID)
    , ContactID INT NOT NULL
        CONSTRAINT FK_AccountsContactsXRef_ContactID
        FOREIGN KEY REFERENCES dbo.Contacts(ContactID)
    , IsPrimary BIT NOT NULL
        CONSTRAINT DF_AccountsContactsXRef
        DEFAULT ((0))
    , CONSTRAINT UQ_AccountsContactsXRef_AccountIDContactID
        UNIQUE (AccountID, ContactID)
);

CREATE UNIQUE INDEX IX_AccountsContactsXRef_Primary
ON dbo.AccountsContactsXRef(AccountID, IsPrimary)
WHERE IsPrimary = 1;

This provides the ability to:

Clearly delineate the relationships between contacts and accounts through a cross-reference table the way Pieter recommends in his answer
Maintain referential integrity in a sound, non-circular manner.
Provide for a highly maintainable list of primary contacts through the IX_AccountsContactsXRef_Primary index.  This index contains a filter, so it will only work on platforms that support them.  Since this index is specified with the UNIQUE option, there can only ever be a single primary contact for each account.

For instance, if you want to display a list of all contacts, with a column denoting the "primary" status, showing primary contacts at the top of the list for each Account, you could do:

SELECT A.AccountName
    , C.ContactName
    , XR.IsPrimary
FROM dbo.Accounts A
    INNER JOIN dbo.AccountsContactsXRef XR ON A.AccountID = XR.AccountID
    INNER JOIN dbo.Contacts C ON XR.ContactID = C.ContactID
ORDER BY A.AccountName
    , XR.IsPrimary DESC
    , C.ContactName;

The filtered index prevents insertion of more than a single primary contact per account, whilst simultaneously providing a quick method of return a list of primary contacts.  One could easily imagine another column, IsActive with a non-unique filtered index to maintain a history of contacts per account, even after that contact is no longer associated with the account:

ALTER TABLE dbo.AccountsContactsXRef
ADD IsActive BIT NOT NULL
CONSTRAINT DF_AccountsContactsXRef_IsActive
DEFAULT ((1));

CREATE INDEX IX_AccountsContactsXRef_IsActive
ON dbo.AccountsContactsXRef(IsActive)
WHERE IsActive = 1;


How to drop table with all reference to this table I want to drop a table, and this table has be many table's foreign key.

So I want to drop all those reference (just refernece, not other table) within the drop table process.

Is there any simple SQL, such as:

Drop table table_name with all reference ?

You can use the following:

  The following query will generate all the keys which you have to drop auto, then simply run:

SELECT CONCAT ('alter table ',
               TABLE_SCHEMA,
               '.',
               TABLE_NAME,
               ' DROP FOREIGN KEY ',
               CONSTRAINT_NAME,
               ';')
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
   AND TABLE_NAME = 'YourTableName';


Migrating a database from one PostgreSQL server to another cleanly I have a Rails app server that uses Postgres for the DB backend.  I'm spinning up a new server and I want to get a clean and up to date copy of the database.  I've tried it once so far while the server was in production by doing the following:

As user postgres: pg_dump databasename > databasename.sql
scp the databasename.sql file over to the new server
As user postgres pn the new server enter psql and create the database and alter ownership
As user postgres run psql databasename < databasename.sql

All of this will occur after I shutdown Nginx/Passenger so all sessions will be terminated and no more data can come in/go out of the system.  The only thing running will be postgres which no one can connect to directly.

Does this sound like a reasonable plan to migrate the database from one server to another?  The database is 12MB in size, probably 30 tables, and has maybe 50K records so it's pretty small.

Any advice on how to do this cleanly and to make sure my data is "sane" would be great.

This is a reasonable plan if a planned maintenance of a few minutes up to an hour maybe is no big thing for you.

The database is real small and should be imported within seconds on the new server.

If the downtime is an issue for you, you should consider replicating the server with Slony (http://slony.info/) and then make the replicating slave finally the new master.


How can I move a database from one server to another? How can I move MySQL tables from one physical server to another?

Such as this exact scenario:
I have a MySQL server that uses innodb table and is about 20GB size.

I want to move it to a new server, what's the most efficient way to do this?

My favorite way is to pipe a sqldump command to a sql command. You can do all databases or a specific one.  So, for instance,

mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserverpassword

You can do all databases with

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver

The only problem is when the database is too big and the pipe collapses.   In that case, you can do table by table or any of the other methods mentioned below.


How to Get the SUM Value from Grouped by Last value in a table result I have this query table,

CREATE TABLE BASE_Purchase
(
PurchaseId BIGINT,
PurchaseDate DATE NOT NULL,
SupplierName VARCHAR(255) NOT NULL,
Amount MONEY NOT NULL,
Outstanding MONEY NULL
PRIMARY KEY(PurchaseId)
)

I have the following result set from the table,

Above image result query ↑

SELECT PurchaseId, PurchaseDate, SupplierName, Amount, Outstanding
FROM BASE_Purchase
ORDER BY SupplierName, PurchaseId

I need the above should be result as follow,

After retrieved the above result and i want to get the SUM of each outstanding

Based on your images, something like this should run on SQL Server 2008.

(It's not completely tested because I didn't want to have to type all that data from your screenprints.)

;WITH _cte
AS (
    SELECT PurchaseId
        ,PurchaseDate
        ,SupplierName
        ,Amount
        ,Outstanding
    FROM BASE_Purchase
    )
SELECT PurchaseId
    ,PurchaseDate
    ,SupplierName
    ,Outstanding
FROM _cte c
WHERE PurchaseDate = (
        SELECT max(PurchaseDate)
        FROM _cte
        WHERE SupplierName = c.SupplierName
        )


How do you calculate mysql max_connections variable? How do you calculate mysql max_connections ?

What do you take into consideration ?

Going to post this as an answer, with the relevant information. The basic formulas are:

Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM - Global Buffers) / Thread Buffers

To get the list of buffers and their values:
SHOW VARIABLES LIKE '%buffer%';

Here's a list of the buffers and whether they're Global or Thread:
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size
Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack


Find out date and or number of error logs xp_readerrorlog I have some code to help automate checking the error logs daily on our SQL Server.  This code calls xp_readerrorlog 0 to check the error log, and xp_readerrorlog 0,1 to check the SQL Server log.

Sometimes, however, there is more than one error log I need to scan.

I would like to be able to see, by T-SQL, the date of the log or, barring that, find the number of logs that exist.

I read that we can have up to 99 archive logs, but the server throws the following error message when trying to display an error log that doesn't exist.

"xp_readerrorlog() returned error 2, 'The system cannot find the file specified."

I know the date information and number exists since SSMS displays it( see the image below.)

If you run EXEC sys.sp_enumerrorlogs; it will return one row for each log file, the oldest date in the log file, and the log file size.

Which means you can do something like this:

CREATE TABLE #error_log
(
    log_number INT,
    log_date DATE,
    log_size INT
);
INSERT #error_log ( log_number, log_date, log_size )
EXEC ( 'EXEC sys.sp_enumerrorlogs;' );

SELECT *
FROM   #error_log AS el;

Then set up a loop to grab the max log number, only grab logs after a certain date, or perhaps exclude logs based on size. Some small logs may be uninteresting, and some large logs may take too long to process.


How to safeguard a symmetric key in SQL Server I've got a database in which I need to store the encrypted values for a column. I use the SQL Server encryption format for encryption. The format is as follows:

CREATE MASTER KEY ENCRYPTION BY PASSWORD 'dsagfdsagv418515adsf'

CREATE CERTIFICATE 'CERTIFICATE_NAME'

CREATE SYMMETRIC KEY 'KEY_NAME'

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE 'CERTIFICATE_NAME';

Now I am able to encrypt and decrypt the column, using EncryptByKey and DecryptByKey functions respectively.

Now I see a security threat here in a way, that the SYMMETRIC KEY is visible to DBA and other database Users. They can always decrypt that column using the key any time they want. This is not feasible in my application. Can someone please suggest what can be done to safeguard this key?

I have dealt a lot with PCI and HIPAA recently since we've developed the ApexSQL Audit compliance tool (some details about the tool can be found here), and during a few years of working on the tool and demoing the tool, I was engaged in numerous discussions about the status of DBA in relation to compliance rules. PCI and HIPAA are not developed to deal with "trust" but to eliminate "trust someone" as a category, so PCI DSS is quite strict in that. DBA usually is not the one who is allowed to have access to data related to compliance, and lot of customers are using our tool to place the control of the DBA as well... not because they "don't have a trust" in DBA, but because the specific compliance say/recommend that he shouldn't be trusted. Most of the security breaches are inside jobs, so PCI DSS is there to prevent inside jobs as well

Quite often question was how to prevent DBA to access the data. There are active and passive methods to do that, like segregation of duties between the DBA or attempt to limit the DBA access to some specific tables/views... but so far it seems that passive methods are more efficient. Auditing all user activities and especially activities on objects that stores the sensitive data are the crucial things (including the SELECT)... of course all security related operations (including anything related to keys) must be audited as well as most DDL activities, especially the ones related to triggers and stored procedures must be tracked as they are based on procedural programming language where someone can hide the malicious code that would otherwise be invisible

When somebody want to do something bad, he want to stay unnoticed, and if you establish the auditing of all activities which can be used for some malicious job, and ensure that nothing can go unnoticed, than your passive method in fact became the active method, or better say the pro-active method. If you can be alerted immediately on the desired events, than you can react immediately as well to deal with any security treat and prevent a damage

Just want to state that I'm working for ApexSQL and this is the reason that I mentioned our tool, but there is a bunch of other tools that dealing with compliance and auditing as well


Performance penalty for using thousands of tables in MySQL Background
I'm using WordPress multisite in which each user will have a separate blog. For each blog, additional 15 tables are created. The website will be used by thousands of users. That means that there will be over 15,000 tables which will grow with each user.
Questions

Is there a performance penalty for having this many tables on a single database?
How significant it is compared to a single WordPress installation?

Some day WP will realize that their design of 1 database per user has a scaling problem.  1 user versus 100 users is probably not much difference.  1K-10K begins to show pain, depending on how many are 'active'.

1000 databases means 1000 subdirectories in the filesystem.  That is some burden in the OS.  (10K would be worse.)

15K tables may or may not be an issue.  If, say, 100 blogs are active, then, 1500 tables are active.  That means that you should probably have table_open_cache set to about 1500.  (Plus other settings may need raising.)

But... If you don't have symptoms of performance problems, don't worry.  Perhaps your users are so light-weight that it is not a problem.


How to log which executed SQL statement is deleting records from SQL Server table? I have a SQL Server table where rows randomly get deleted. I want to check what is causing this as I see no issues from the application side.

I did create an after delete trigger and I am able to see which rows are being deleted, but that is still not helping pin-point on why some records are being deleted.

I am wondering if it is possible to log/track which executed SQL statement are deleting the rows from my table.

Please note I am using SQL Server 2016 Express edition.

You can use the help of SQL Profiler ..

Inside the profiler you can choose the events against your server..


Can two sessions create #temp tables with the same name? I am creating a temporary table (#myTable) and using a cursor. Does this create a problem when concurrent users are accessing the cursor through my application? Does it allow me to create separate temp tables with the same name?

Following is the sample code:

Open cursor;
Fetch Next from cursor into @Variable_Temp_Table_Name;
Create table #myTable(pk int)
While @@Fetch_Status = 0
Begin
Fetch Next from cursor into @Variable_Temp_Table_Name;
End

SQL server always append some random number in the end of a temp table name (behind the scenes), when the concurrent users create temp tables in their sessions with the same name, sql server will create multiple temp tables in the tempdb.

I created 3 temp tables called #TempTable in three different sessions in my SSMS, now if I go to the tempdb I can see the temp tables created there with a random (unique) string appended to each temp table's name.


Speed up large database queries I am running a large local database with MySQL and InnoDB. The size is around 30GB for data and 6GB for Keys. The machine has 16GB of RAM.
The table looks like this
symbol VARCHAR(10),
iso_datestamp VARCHAR(14),
-- a lot more columns with values with mostly DECIMAL(26,6)s

I have BTREE indexes on the symbol and the iso_datestamp.
The operations I run are mostly updates which are run like this:

DELETE FROM table WHERE symbol = 'XXX'
INSERT INTO table (columns) VALUES (...)

This needs to be done to avoid data inconsistencies as opposed to just appending data.
I also frequently run analysis as in
SELECT FROM table WHERE symbol in ('XXX', 'YYY', ...) [AND iso_datestamp > '2015...']
Per symbol I have around 3000 entries.
The performance problem: As soon as the table starts to grow over around 10GB, the DELETE and INSERT statements slow down substantially from usually less than 0.4 seconds to well over 1 second even up to 5 seconds and more as the table keeps growing. RAM is not limiting (according to top).
I need the total of DELETE and INSERT to be around 1 second on average (of course faster is also fine)...
I am having 2 ideas right now:

Using hash indexes instead of btree indexes, since I am only doing equality operation
Partitioning the table using the symbol and the range

Are those viable strategies? Do both, just one? Other things I should do to improve the speed?

iso_datestamp to a MySQL TIMESTAMP so that it can be 5 bytes instead of 15.
Use InnoDB, not MyISAM.  (This is critical to some of the items below.)
Use CHARACTER SET ascii (or latin1) unless you need the multibyte utf8/utf8mb4.
PRIMARY KEY(symbol, datestamp)
An auto_increment is a waste of space and indexing.  The above is a 'natural PK' and it provides an efficient index.
PARTITION BY RANGE(TO_DAYS(datestamp)) -- this will significantly speed up deletion of 'old' rows.
With that PK and partitioning, you may not need any secondary indexes, thereby completely eliminating the 6GB you mentioned.  (If you have queries that are not adequately handled, let me know.)
Shoot for no more than about 50 partitions; do not bother with subpartitions.  (See link below)
Use DROP PARTITION for very efficient deleting by date.  (See link below)  If you are deleting by symbol, let me know.
DECIMAL(26,6) takes 12 bytes.  Consider shrinking it and/or moving to an 8-byte DOUBLE.  Caution: Double's ~16 significant digits and binary format may lead to rounding issues.
Consider using a 3-byte MEDIUMINT INT for a symbol_id and normalize the actual symbol name into another (small) table.
Metrics (eg, "alpha") should probably be 4-byte FLOATs.  It's 7 significant digits is overkill for such.
Set innodb_buffer_pool_size to about 70% of available memory (after accounting for apps, web server, etc.)
Insert new data in batches of 100-1000.  This runs literally 10 times as fast as one row at a time.

These changes should help with your various issues.
Currently, performance is falling off a cliff for multiple reasons.  The above tips will delay the cliff for about twice as long, and make the cliff less steep.  In some cases, the cliff is eliminated.
Also, my tips will cut back on the I/O needed -- by clustering, by locality of reference, by better caching, etc.
http://mysql.rjweb.org/doc.php/partitionmaint


sqlsrvr.exe is not using all memory I have no clue what to set up more.
I tried a lot.

My Problem is that on my test Server everything is runing perfectly see

but on my production Server its not! see

I found this on my Production Server:
"Using locked pages for buffer pool"

This is shown in the error log of the SQL Server.
This line only Shows up in the production System.

Both Systems are SQL 2008 R2 Enterprise with WIN2K8 R2 both in 64 bit.

Locked Pages in Memory is not enabled in GPEDIT.MSC

Can someone explain that to me?

AWE allocated pages are not reported by Task Manager. The "Using locked pages for buffer pool" message means the production server is using AWE. Do not trust Task Manager. Use SQL Server own counters: SQL Server, Memory Manager Object, SQL Server, Buffer Manager Object.

Read Fun with Locked Pages, AWE, Task Manager, and the Working Set…


Reporting Services TempDB lock blocking report generation Two days ago one of our reports in Reporting Services started to take much longer to generate.  Its generation time had increased from 4 seconds to 2 minutes.

Investigation found:

The Stored Procedure ran in 1 second and the query plan was acceptable
The report ran as expected in 4 seconds on a dev machine in Visual
Studio

As soon as the report was published to the production server it ran much, much slower.

Using Activity monitor showed that the report was being blocked with a lock "LCK_M_S"

I turned on trace 1222 to check for deadlocks but no deadlocks were reported.

The only information I could get on what was going on was by the report "Top Transactions by Locks Count" for the ReportServer database.  It listed locking transactions on the ReportServerTempDB.

This pattern was repeatable with the affected report for two days, you would run it, see the blocking process in the Activity monitor and the report taking the much longer 2 minutes.

Just as we were looking further into the problem, it suddenly disappeared, nobody in the team had changed anything, code or data, and now the report is back to running in 3-4 seconds.

What happened?

Without dev intervention the report which was repeatedly taking 2 minutes to run and had been for 2-3 days suddenly returns to running in 4 seconds and the blocking process seen in Activity monitor is gone?

A LCK_M_S is a blocking problem not a deadlocking problem. So you are just waiting on something else.  You need to determine what process is blocking you.

So, "it suddenly disappeared" because that block either completed or failed and rolled back.  This is normal.  What sounds abnormal from your notes or your perspective is the length of the block.

In addition to finding the blocking process you could also consider using a different transaction isolation level.  For example, although not without their own problems, you might consider using "READ COMMITTED SNAPSHOT" or "SNAPSHOT" isolation levels for your report.  This would allow it to read the data as of the start of the transaction and would avoid most blocking situations.


How to interpret AlwaysOn event hadr_db_partner_set_sync_state We have a program that listens to the SQL Server AlwaysOn_health event session
for state changes.  When we simulate a secondary server going offline or online, we get the hadr_db_partner_set_sync_state event from SQL Server.  (See below.) When I Google for "hadr_db_partner_set_sync_state sync_state" I get 6 hits and none explain the fields of this event.

{"EventName":"hadr_db_partner_set_sync_state",
 "Fields":
 {"database_id":"95",
 "commit_policy":"WaitForHarden",
 "commit_policy_target":"WaitForHarden",
 "sync_state":"NOT",
 "sync_log_block":"28119150887390",
 "group_id":"070e4751-d4cc-4154-9ff5-d2723838d0e4",
 "replica_id":"8b1c6688-cd6d-4668-b90b-f235121c82b2",
 "ag_database_id":"16e2a407-e136-4755-85b4-9f576d542ed1"
 },
 "Actions":{}
}

Which combination of fields in this event tells us a secondary server has gone offline? And which tells us a server has been restored to online?

If there is some other event we should be watching for, that would be helpful also.

According to How It Works: Always On–When Is My Secondary Failover Ready?, a commit_policy of

DoNothing

is an indication that the secondary is offline.  A commit_policy of

Delay

indicates that the secondary is online, but catching up.  And finally,

WaitForHarden

indicates the secondary is caught up with the current transaction.


Why MySQL Workbench automatically sets NOT NULL for ID column that is PRIMARY KEY? My question is why MySQL Workbench automatically sets NOT NULL for ID column that is PRIMARY KEY? I thought that PRIMARY KEY's can't be NOT NULL by definition? Is it really necessary to set it to NOT NULL?

Explicitly setting the NOT NULL is simply an additional action to make this fact clear also for the non-experienced users. It doesn't hurt and you can safely ignore it.


Arbitrary queries on n:m relationship, including "all" and "any" I'm using postgres >= 9.6. I have tasks, tags and task_tags tables, for a typical n:m relationship between tasks and tags.

I'd like to be able to programmatically build queries against the tasks table that supports queries against the actual fields of tasks, but also on the tags (tag names) of a task.

Queries on the task fields themselves are straight-forward. Queries on the tags for a "does it have tag A?" are also straight-forward. What I am struggling with is coming up with a select/query structure that would allow me to also match things like "does it have tag A and tag B"?

The best I've come up with is a lateral join on a subquery with an array aggregation, and then using array matching functions, e.g.:

SELECT DISTINCT ON (tasks.id) tasks.*
FROM   tasks, LATERAL (SELECT array_agg(tags.name) AS tags
                       FROM   task_tags
                       INNER JOIN tags
                       ON task_tags.tag_id = tags.id
                       WHERE task_tags.task_id = tasks.id
                       GROUP BY task_tags.task_id) tt
WHERE  tt.tags @> array['tag1'::varchar, 'tag3'::varchar];

That way, it should be possible to programmatically build a WHERE clause (using tasks.* and tt.tags) satisfying all of the conditions of the user-provided "query".

However, I'm not sure if this is the best way of doing it - thoughts? Is this query any efficient? Is there any index I could create that would improve it?

Similarly, is there any way at all of making it work with wildcards against the tag names? Normal array matching wouldn't allow that, and solutions I've seen suggest using unnest (or, well, not using arrays in the first place), but then I'd lose the ability of saying "it needs to have both tagA and tagB".

Is there any other way of building a query on these relationships that would allow that kind of "both tagA and tagB" matching?

Is this query any efficient?

No, it is extremely inefficient for multiple reasons.

0. Test schema

Basing my answer on this simplified schema:

CREATE TABLE tag (
  tag_id serial PRIMARY KEY
, tag    text NOT NULL
);

CREATE TABLE task (
  task_id  serial PRIMARY KEY
, task     text NOT NULL
);

CREATE TABLE task_tag (
  task_id    int
, tag_id     int
, PRIMARY KEY (tag_id, task_id)  -- columns in this order
, FOREIGN KEY (task_id) REFERENCES task
, FOREIGN KEY (tag_id)  REFERENCES tag;
);

1. The query is very inefficient

Your original query, adapted to test schema:

SELECT DISTINCT ON (task.task_id) task.*  -- DISTINCT is dead freight
FROM   task, LATERAL (
   SELECT array_agg(tag.tag) AS tags  -- array_agg more expensive than array constructor
   FROM   task_tag
   JOIN   tag ON task_tag.tag_id = tag.tag_id
   WHERE  task_tag.task_id = task.task_id
   GROUP  BY task_tag.task_id  -- redundant noise
   ) tt
WHERE  tt.tags @> array['tag1'::text, 'tag3'::text];  -- or array literal.

What's wrong? For starters:

No reason to add DISTINCT ON. The lateral join only joins to a single row after the aggregation.
An array constructor is cheaper for this simple array aggregation.

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

No need to add GROUP BY, after the WHERE clause already filters one task_id.
Depending on how you call the query, it may be more convenient to pass a single array literal instead of an array constructor.

Equivalent query 1:

SELECT ts.*
FROM   task ts
JOIN   LATERAL (
   SELECT ARRAY (
      SELECT tg.tag
      FROM   task_tag tt
      JOIN   tag      tg USING (tag_id)
      WHERE  tt.task_id = ts.task_id
      ) AS tags
   ) tt ON tt.tags @> '{tag1, tag3}'::text[];

But that's still a waste. The LATERAL does more harm than good while all rows have to be aggregated anyway.

Equivalent query 2:

SELECT ts.*
FROM   task ts
JOIN  (
   SELECT task_id
   FROM   task_tag
   GROUP  BY 1
   HAVING array_agg(tag_id) @> '{1, 3}'::int[];
   ) AS tags USING (task_id);

As you can see, I am not building array of tags, just tag_id. Much more efficient. Resolve your input tags to IDs before you pass them to the query.

Much faster than before but still extremely inefficient for tables of non-trivial size.

2. The whole approach is very inefficient

The above approach cannot use an index. The dynamically generated array does work with indexes. The predicate comes after the aggregation.

2.1 MATERIALIZED VIEW for read-only (or mostly) tables

To make it work with an index you would have to add a MATERIALIZED VIEW with readily aggregated tag arrays (or IDs, preferably) per task_id. And a GIN index on the array column. Only good for read-only (or mostly) tables, since the MV is just a snapshot, quickly outdated with writes to the underlying tables.

Related:

Can PostgreSQL index array columns?

The MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW task_tags_mv AS
SELECT task_id, array_agg(tag_id) AS tag_ids
FROM   task_tag
GROUP  BY 1;

Of course, we work with tag_id again, not raw tags. And for integer arrays there are much faster, specialized operator classes and indexes available from the additional module intarray. Related:

GIN index on smallint[] column not used or error “operator is not unique”

So we use this specialized index:

CREATE INDEX task_tags_mv_arr_idx ON task_tags_mv USING GIN(tag_ids gin__int_ops);

And this query:

SELECT ts.*
FROM   task ts
JOIN   task_tags_mv mv USING (task_id)
WHERE  mv.tag_ids @> '{1, 3}'::int[];  -- uses intarray operator

This is fast, now. And the same setup works for matching ANY tag, using the overlap operator && instead of the contains operator:

...
WHERE  mv.tag_ids && '{1, 3}'::int[];  -- uses intarray operator

2.2 Generic queries

Else you need completely different query techniques that can work with indexes. Yper* pointed to a related question on SO in his comment:

How to filter SQL results in a has-many-through relation

You specific requirement is to make is simple to provide a list of tags to a generic query.

A recursive CTE is one way without dynamic SQL. (There are many others.) Nested in an SQL function with VARIADIC parameter for convenience:

CREATE OR REPLACE FUNCTION f_tasks_with_tags(VARIADIC _tags text[])
  RETURNS SETOF task AS
$func$
   WITH RECURSIVE cte AS (
      SELECT task_id, 1 AS idx
      FROM   task_tag
      WHERE  tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[1])

      UNION
      SELECT task_id, c.idx + 1
      FROM   cte c
      JOIN   task_tag tt USING (task_id)
      WHERE  tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[c.idx + 1])
      )
   SELECT t.*
   FROM   cte  c
   JOIN   task t USING (task_id)
   WHERE  c.idx = array_length(_tags, 1)
$func$  LANGUAGE sql STABLE;

Call:

SELECT * FROM f_tasks_with_tags('tag1', 'tag3'); -- add any number of tags

About VARIADIC (follow links for more):

Passing multiple values in single parameter

Depending on value frequencies, it helps performance (a lot) to pass sparse tags first. It is cheaper to eliminate non-qualifying tasks early.

Another potentially even faster approach: dynamic SQL. Build and execute a query like:

SELECT t.*
FROM  (
   SELECT task_id
   FROM   task_tag t1
   JOIN   task_tag t2 USING (task_id)
-- JOIN   task_tag t3 USING (task_id)
-- more ...
   WHERE  t1.tag_id  = 1
   AND    t2.tag_id  = 3
-- AND    t3.tag_id  = 789
-- more ...
   ) tt
JOIN  task t USING (task_id);

In this query, Postgres can optimize the sequence of joins automatically to evaluate sparse tags first - up to a maximum of join_collapse_limit tables. See:

Postgres JOIN conditions vs WHERE conditions

There are many code examples for building queries dynamically in a plpgsql function on this site. Try a search.

SQL Fiddle (also has intarray module).

dbfiddle here


Check if DISTINCT is really necessary I have a query in mysql as follows.

SELECT DISTINCT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

Now executing the query takes approximately 13 seconds due to the size of the respective tables. However this query is a lot faster (i.e. 2 seconds)

SELECT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

I'm aware that this may result in non-distinct rows but I'm not sure if this situation has been taken care of by the php code on the front end. So I actually want to compare the data of the two result sets both of which result in approximately 83,000 rows if not limited.

Can anyone think of a way to do this in SQL instead of doing it via a php script. I've compared the first 100 rows by eye without any issues but as we all know it only takes one exception at the 101st row to mess everything up. So I need a query that I can execute a few times every so often to see if there are rows that are in one result set that are not in the other.

OK, first things first, talk to the PHP devs to see if this situation is taken care of for you, which would then save a lot of pain and heartache!

Other than that, try with a group by instead of a distinct, see what the performance differences are - it may be that the group by is faster based on the indexes you have on the tables.

With the GROUP BY option, you can add a count(*) then talk to the php devs to ensure they know that if that result is 2 or more, then they need to handle it differently than if the result is a 1.


What storage engine should I use for this MySQL table? I have a table where I store all the urls from my website, and I update them from time to time when a visitor enters.

The primary key is the url itself varchar(255).

I have set it to use InnoDB but I don't know if it would be better to use MyISAM in this case for more speed.

Thanks,
Alex.

When it comes to InnoDB, you should never use big primary keys. Why?

InnoDB has an internal Clustered Index called gen_clust_index. When you are doing INSERTs into an InnoDB table with just a Primary Key, you are building up the gen_clust_index. I would expect this table to bloat very quickly. Your goal should be to record the URL and index it in such a way that the primary is not large and causing potentially make for slow INSERTs. Given the size of the field, I wouldn't even create a secondary index

This table could easily be a MyISAM table. However, in a high-traffic environment, each INSERT into a MyISAM table would produce a full table lock. That's an automatic bottleneck just to record a URL. So, MyISAM is out. Making the table InnoDB allows a high-traffic environment the freedom to INSERT without locking.

I would like to suggest the following approach:

CREATE TABLE url_trail
(
    id int not null auto_increment,
    url_md5 VARCHAR(32),
    url VARCHAR(255),
    PRIMARY KEY (id),
    KEY url_md5 (url_md5),
    KEY url (url (50))
) ENGINE=InnoDB;

Using this type of layout will allow you to index the MD5 o the URL (32 btyes) rather than the 200+ character URL. There is also an optional index on the first 50 characters of the URL. You could use either index or both.

You will have to do INSERTs like this:

INSERT INTO url_trail (url,url_md5) VALUES ('www.yahoo.com',MD5('www.yahoo.com'));

You will have to do searches like this:

SELECT * FROM url_trail WHERE url_md5 = MD5('www.yahoo.com');
SELECT * FROM url_trail WHERE url = 'www.yahoo.com';

The essential thing overall is to not to have bloated primary keys.


How to assign variable as column value I'm new with Oracle. This is something simple that I'm just not getting.

I'm in a situation where I need to split a string with values separated by commas (i.e. 25,27) and "decode" it (example: if 25 = 'Bird. ' and 27 = 'Elephant. ', then 25, 27 should return Bird. Elephant.).

I've followed this and manged to get values I want, but how do I place it into a select statement as a subquery?

The code so far:

declare
    l_input varchar2(50) := '25,27';
    l_count binary_integer;
    l_array dbms_utility.lname_array;
    finalText varchar2(50);
    placeholderText varchar2(50);
begin
    dbms_utility.comma_to_table
    ( list   => regexp_replace(l_input,'(^|,)','\1x'),
    tablen => l_count,
    tab    => l_array
    );

    finalText:= '';
    for i in 1 .. l_count
    loop
        placeholderText := '';
        case substr(l_array(i), 2)
            when '25' then placeholderText:= 'Bird.';
            when '27' then placeholderText:= 'Elephant.';
            else placeholderText:= substr(l_array(i), 2);
        end case;
        finalText:= finalText || ' ' || placeholderText;
    end loop;
    dbms_output.put_line(finalText); --I want this as the column 'value'
end;

What I would like (or something similar):

select
    Id,
    Name,
    (declare
        l_input varchar2(50) := ItemColumValue;
        l_count binary_integer;
        l_array dbms_utility.lname_array;
        finalText varchar2(50);
        placeholderText varchar2(50);
    begin
        dbms_utility.comma_to_table
        ( list   => regexp_replace(l_input,'(^|,)','\1x'),
        tablen => l_count,
        tab    => l_array
        );

        finalText:= '';
        for i in 1 .. l_count
        loop
            placeholderText := '';
            case substr(l_array(i), 2)
                when '25' then placeholderText:= 'Bird.';
                when '27' then placeholderText:= 'Elephant.';
                else placeholderText:= substr(l_array(i), 2);
            end case;
            finalText:= finalText || ' ' || placeholderText;
        end loop;
        dbms_output.put_line(finalText); --I want this as the column 'value'
    end;) as "Some Column Name"
from
    some_table

Is this possible?

Thank you.

Actually DBMS_UTILITY.COMMA_TO_TABLE and DBMS_UTILITY.TABLE_TO_COMMA are a very old procedures. Nowadays people usually prefer this one:

WITH t AS
    (SELECT TRIM(REGEXP_SUBSTR('25,27', '[^,]+', 1, LEVEL)) AS n, LEVEL as pos
    FROM dual
    CONNECT BY REGEXP_SUBSTR('25,27', '[^,]+', 1, LEVEL) IS NOT NULL),
a AS
    (SELECT pos,
        CASE n
            WHEN '25' THEN 'Bird.'
            WHEN '27' THEN 'Elephant.'
        END AS animal
    FROM t)
SELECT LISTAGG(animal, ' ') WITHIN GROUP (ORDER BY pos) AS value
FROM a;


Get max value when joining 3 tables in SQL This is based on a MySQL database.
I'm trying to join 3 tables together and get a MAX value from the third one. I'm trying to get the po.id, for each part.id, that has the latest po.date. I cannot base it on the MAX po.id. It must be based on the MAX po.date, which is what I'm stuck on. However, I need to used the poitem table to determine if the po contains the part.
Table: po
po.id, po.date
Table: poitem
poitem.id (item id that is only used for joining), poitem.partid
Table: part
part.id
This is what I'm trying to end up with:
part.id                po.id
.....                  .....
.....                  .....
.....                  .....

Assuming that  poitem.id is the same as po.id
SELECT
     pa.id,
     pi.id
FROM
    part pa INNER JOIN poitem pi ON pi.partid = pa.id
WHERE pi.id IN (SELECT po.id
                FROM po
                WHERE po.`date` = (SELECT MAX(`date`) FROM po))


Breaking up insert statements with GO I'm trying to build off of this answer How do I run a large script with many inserts without running out of memory?

and break up a query into smaller ones with BEGIN TRANSACTION and then GO's

BEGIN TRANSACTION;

MERGE ghcnd.dbo.us_APCP as target
using
(values
  ('US1CASN0123','2018-03-22','--N','251'),
  ('US1KSGO0013','2018-03-22','--N','41'),
  ('US1WYFM0039','2018-03-22','--N','0'),
  ('US1SCCF0008','2018-03-22','--N','10'))
as source(cell,[date],valueFlag,[value])
on target.cell = source.cell
AND target.date = source.date
when matched then
    update
    set valueFlag = source.valueFlag ,
        value = source.value
when not matched then
    insert (cell,[date],valueFlag,[value])
    values (cell,[date],valueFlag,[value])

COMMIT TRANSACTION; GO

However I get this error

  Msg 102, Level 15, State 1, Line 3
  Incorrect syntax near ')'.
  Msg 102, Level 15, State 1, Line 3
  Incorrect syntax near 'GO'.

PROBLEM NUMERO ONE

GO is the default query batch separator. It is a feature of certain client utilities (mainly SQLCMD and SSMS) and is unknown to the SQL Server engine itself. GO needs to be on a line by itself (optionally followed by only an INT value to cause repetition of that batch) as it is parsed by these client utilities to indicate where to split up what is being submitted to SQL Server.

It cannot be included in any Dynamic SQL as it is, again, unknown to SQL Server. It is also unnecessary to include in Dynamic SQL since each call to EXEC() is its own query batch.

So,

If you are generating a SQL script to exec via SSMS or SQLCMD, then simply put a newline / CRLF between COMMIT TRANSACTION; and GO
If you are generating Dynamic SQL to run each of these blocks via an EXEC(@SQL) call, then simply remove the GO.

PROBLEM NUMERO TWO

After fixing the GO situation, you will find that you still have a syntax error. It is also indicated in that you have two error messages. The problem is that you are missing the semicolon ( ; ) that needs to terminate the MERGE statement. If you try to execute after fixing the GO, you will get the following error:

  Msg 10713, Level 15, State 1, Line XXXXX
  A MERGE statement must be terminated by a semi-colon (;).

Just add a semicolon before the COMMIT TRANSACTION and the whole thing should work. As in:

values (cell,[date],valueFlag,[value]); -- <-- that guy, right there :-)


When creating remote BLOB store is "RBSFilestreamFile" always the name of the file to be added to the FILEGROUP? When creating a remote BLOB store in SQL Server (2008 R2) is "RBSFilestreamFile" always the name of the file when adding it to the FILEGROUP like in this query (this is the name I've seen used in every example I've found online, but I need to know for sure)?

ADD FILE (name = RBSFilestreamFile, filename = 'c:\Blobstore')
    TO FILEGROUP RBSFilestreamProvider

I'm asking because I'm working on an application for restoring SharePoint content databases and need to know if I can hardcode this string into the application.

I know you can create file groups and files with any name you want, but specifically for setting up RBS with SharePoint using SQL 2008's built in FILESTREAM provider are these names an expected convention? i.e. Will it work if I name my FILEGROUP and/or FILE something else?

The filename and filegroup name are not fixed.

SharePoint will use the same name when creating the databases.  However there's nothing that would prevent someone from changing those names to something else if they desired.  I'd query for them from the database backup using RESTORE FILELISTONLY then create the RESTORE DATABASE Command based off that information.


Cycle through values in a table I am trying to find a solution for this problem, I have two tables

Table no 1

| no | ticket |
|----|--------|
| 1  | T1     |
| 2  | T2     |
| 3  | T3     |

Table no 2

| id | CustomerId |
|----|------------|
| 1  | C1         |
| 2  | C2         |

I require a below set

| Tickeid | CustomerId |
|---------|------------|
| T1      | C1         |
| T2      | C2         |
| T3      | C1         |

It basically cycle with ticketid , I started with a cross join on both table but couldn't get the required result

EDIT:1
One change that can happen is the values in table 2 can have more than 2 values
like in table 1 have T1,T2,T3,T4 and table 2 have C1,C2,C3 then I require
T1C1,T2C2,T3C3,T4C1

Again

if table 2 is just C1

then I just Need T1C1.

Based on the recent changes to your question, I've completely rewritten my answer.  There may be more efficient ways of doing this, but here's my solution.  Let me know if I have misunderstood your request.

--set up demo data
declare @Table1 table (no int, ticket varchar(5))
insert into @Table1 (no, ticket) values
(1,'T1'),(2,'T2'),(3,'T3'),(4,'T4'),(5,'T5'),(6,'T6'),(7,'T7'),(8,'T8'),(9,'T9'),(10,'T10'),(11,'T11')

declare @Table2 table (ID int, CustomerId varchar(5))
insert into @Table2 (id, CustomerId) values(1,'C1'),(2,'C2'),(3,'C3'),(4,'C4')

--The actual solution
;
--Table2 row count
WITH Table2RowCount
AS (
    SELECT count(*) AS T2RowCount
    FROM @Table2
    )
--Add row number (sequence) to Table1 rows
    ,Table1AndRowNumber
AS (
    SELECT *
        ,row_number() OVER (
            ORDER BY no
            ) AS sequence
    FROM @Table1
    )
--Add row number (sequence) to Table2 rows
    ,Table2AndRowNumber
AS (
    SELECT *
        ,row_number() OVER (
            ORDER BY id
            ) AS sequence
    FROM @Table2
    )
--Add a grouping column for Table1 using the modulus operator
--This grp column will be used to join up against the sequence column of Table2
    ,Table1Grouped
AS (
    SELECT *
        ,CASE
            WHEN t1arn.sequence % (t2rc.T2RowCount) = 0
                THEN (t2rc.T2RowCount)
            ELSE t1arn.sequence % (t2rc.T2RowCount)
            END AS grp
    FROM Table1AndRowNumber t1arn
    CROSS APPLY Table2RowCount t2rc
    )
SELECT t1g.ticket
    ,t2arn.CustomerId
FROM Table1Grouped t1g
JOIN Table2AndRowNumber t2arn ON t2arn.sequence = t1g.grp

| ticket | CustomerId |
|--------|------------|
| T1     | C1         |
| T2     | C2         |
| T3     | C3         |
| T4     | C4         |
| T5     | C1         |
| T6     | C2         |
| T7     | C3         |
| T8     | C4         |
| T9     | C1         |
| T10    | C2         |
| T11    | C3         |


Why would a SELECT INTO cause an arithmetic overflow? I am doing this

SELECT * INTO table1 FROM Table0

I get an arithmetic overflow on a datetime column (error converting to smalldatetime), however the destinations schema that got created has a datetime column and not a smalldatetime column..

It sounds to me like you have an implicit conversion to smalldatetime, probably in a computed column. If you look at the Estimated Execution Plan, you'll probably see Compute Scalar operations in there. Hit F4 to see the properties, and you should be able to see the calculations that are being done, including any implicit conversions that are being used.


postgresql bgworker SIGTERM handler called twice Im using postgresql 10.1 on Mac.

I have a trouble in terminating a background worker using SIGTERM. In my project, im creating a new background worker using RegisterBackgroundWorker(). i have included a test_function, as a signal handler for SIGTERM. No other signal handlers are defined.

pqsignal(SIGTERM, test_function)

when i issue pkill -15 postgres, i see that the handler is being called twice. Why does it happen? Am i missing anything??

This is because pkill sends the signal to all processes that match the given name, and Postgres is multi-threaded.

Use regular kill with the process ID of the background process that you wish to nuke.


InnoDB undo logs vs history list I was reading about InnoDB undo logs, and in the process, the articles i was reading ended up confusing me on what undo logs are, and how they are different from the history list, or rather the same.

So quoting article one by the MariaDB team, they defined history list as:

  There is also a global history list of the data. When a transaction is
  committed, its history is added to this history list. The order of the
  list is the chronological order of the commits.

Then in the same article, they went on to say this, which now brought even more confusion what the relationship is between undo logs, history list, and views of rows in the apparent undo logs:

  Understanding how the undo log works helps with understanding the
  negative effects long transactions.

The second article [2] by Percona describes the history list as:

  History list length 6 is number of unpurged transactions in undo
  space. It is increased as transactions which have done updates are
  commited and decreased as purge runs.

And lastly, the third article [3] tried to address the confusion by many of what the history list is, but in my opinion, just brought more confusion in distinguishing the undo logs from the history list, and how they both relate to views of rows that innodb transactions us to enforce consistency by MVCC support.Here is how the third article defined the history list:

  The best name for the unit (history list) is undo logs, which are “update undo logs
  for committed transactions”, to quote the source. But an “undo log” is
  a special term with specific meaning in InnoDB. An undo log is a
  single set of atomic changes a transaction makes, which may actually
  modify multiple records.

The first article [1] added this to even confuse me more:

  Understanding how the undo log works helps with understanding the
  negative effects long transactions.

  Long transactions generate several old versions of the rows in the undo log. Those rows will probably be needed for a longer time,
  because other long transactions will need them. Since those
  transactions will generate more modified rows, a sort of combinatory
  explosion can be observed. Thus, the undo log requires more space.
  Transaction may need to read very old versions of the rows in the history list, thus their performance will degrade.

Does the InnoDB undo log contain two types of data, one being views of rows for transaction to use stored as linked-lists, and the other being the history list, which keeps track of committed transaction in the buffer pool (not yet flushed to disk)? Can someone please help make a clear distinction between the undo logs, and how they relate to views of rows (created for running transaction to use) and the history list?

[1] https://mariadb.com/kb/en/library/xtradbinnodb-undo-log/
[2] https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/
[3] https://www.vividcortex.com/blog/2015/07/20/what-is-innodb-history-list-length/

I'll take a swing at this :) My colleagues and I are pouring over how to make a succinct but accurate answer for this;

  on what undo logs are, and how they are different from the history list,

Undo logs are produced when data modifications are made. If a transaction modifies data with an INSERT, UPDATE, DELETE for example it will create discrete undo logs for each operation. Once a transaction is committed MySQL is free to purge the undo logs created in that transaction. There's a one to many relationship between Transaction and Undo Log (one per operation, remember).

My understanding is that the Innodb History List Length is the count of the discrete operations in the Undo Log(s). Undo logs are part of a structure called Rollback Segments and live in the global table space (ibdata1) or in UNDO tablespaces (a recent feature in MySQL 8.0).

  Does the InnoDB undo log contain two types of data, one being views of rows for transaction to use stored as linked-lists, and the other being the history list

No. The undo log will contain instructions on how to revert data to previous state and if other transactions need to see an old version of a row, it will need to use the undo logs (instructions to rollback) in order to honour the scope of it's read view and present previous versions of the data.

There is a background purge operation (analogous to Postgres vacuum) that garbage collects aged undo logs. It may only purge up to the oldest live transaction.

I hope my answer helps you to visualise things a little clearer. If I can tidy up further I'll make edits or if any InnoDB experts wish to chime in, please do so.


Multi-column btree index & INLJ I have three tables:

FILM(ID,TITLE)
ACTOR(ID,NAME,GENDER)
CAST(ACTOR_ID,FILM_ID,ROLE)

Foreign keys: ACTOR_ID, FILM_ID

And I have created a btree on columns (ACTOR_ID,FILM_ID) of CAST table.

Could I use the INLJ (index nested loop join) algorithm to make the following join?

...
FROM FILM,CAST
WHERE FILM.ID = CAST.FILM_ID ;

I very much doubt it will ever do that.  PostgreSQL doesn't yet implement index skip scans, and if it did they would only be effective if there were only a handful of different actors.

It is possible, but not very likely, to use the index for something other than an INLJ.  If the role field is very wide (causing the cast table to be much larger than the index on it), and role is not in your select list, and the table is kept well vacuum, then it might decide to index-only-scan the index rather than seq scan the cast table as part of a merge join or hash join.

(By the way, cast is not a valid table name as it is a reserved word)


Generate create script for all indexes I am working on documenting my databases, and I would like to create a list of all of the indexes in my database.  The reason I want to do this is so that I can track changes to my indexes overtime.  I currently have a spreadsheet with all of the indexes that have changed since i created that spreadsheet, but it doesn't have all of the indexes.  Instead of having to script out each index I would like to be able to just generate the list.  I played with the system views but I wasn't able to figure it out.

How can I generate a list of indexes and the create statement for each index?

I don't seem to have a script like this in my toolbox, but found some others in my search.

Have you already seen this script?

http://www.sqlservercentral.com/scripts/Indexing/31652/

It uses a cursor, which is probably not strictly necessary.  But I typically can clean them up to avoid a cursor by using the FOR XML and potentially a quirky update to concatenate the strings.

Also found these:

http://www.sqlservercentral.com/Forums/Topic796512-391-1.aspx

http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx


How can the size of the notification queue be configured? The documentation for NOTIFY states

The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case

Is it possible to configure the size of the queue?  I couldn't find anything about this in the documentation or in postgresql.conf.

I found this in async.c
 298 #define QUEUE_PAGESIZE              BLCKSZ
     ...
 300
 301 /*
     ...
 310  * The most data we can have in the queue at a time is QUEUE_MAX_PAGE/2
 311  * pages, because more than that would confuse slru.c into thinking there
 312  * was a wraparound condition.  With the default BLCKSZ this means there
 313  * can be up to 8GB of queued-and-not-read data.

This supports the notion that the queue size is not configurable without changes to the source and recompilation, as a_horse_with_no_name commented.


Difference between Owned Schemas and Role Membership for Microsoft SQL Server Are there differences between Owned Schemas and Role Members?

From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked.

However, the Owned Schemas is not compulsory. It really make me wonder why there is a need to have the Owned Schemas in there.

Apples and Oranges. Roles are roles and schemas are schemas. The fact that there is a role called db_accessadmin and also a schema called db_accessadmin does not mean a role is a schema nor that a schema is a role. Roles are security membership containers, a principal is member of a role. Schemas contain database schema bound objects and are owned by a principal. When you create a new user you can choose his default schema, add him to certain roles, and grant him ownership of schemas. Although the actions are separate, the designers of this dialog feel they are somehow related as to merit all that UX real estate (that is questionable, but a completely different topic).