How to insert values to a table from 2 foreign keys Hey guys was wanting some help. I want to add the same value for Student_ID that is in the Studentinfo table in the GradeInfo and also wanted to add the same value for CourseNo that is in the CourseInfo table and add it to GradeInfo.  Keep in mind that CourseNo in GradeInfo table is a foreign key from CourseInfo and Student_ID in GradeInfo is a foreign key from CourseInfo.

What I was wanting to know is how do I Insert these values from the foreign keys from other tables? I kept getting an error message of  "foreign key mismatch - "GradeInfo" referencing "CourseInfo"

CREATE TABLE CourseInfo (           /*Created 2nd*/
    CourseNo Text,
    Year NUMERIC,
    Title TEXT,
    PRIMARY KEY (CourseNo, Year)
)

CREATE TABLE GradeInfo (                    /*Created 3rd*/
    Student_ID NUMERIC AUTO_INCREMENT,
    CourseNo Text,
    Pts NUMERIC,
    Grade TEXT,
    FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID)
    FOREIGN KEY (CourseNo) REFERENCES CourseInfo (CourseNo),
    PRIMARY KEY (Student_ID, CourseNo)
)

CREATE TABLE StudentInfo (            /*Created 1st*/
    Name TEXT,
    Student_ID NUMERIC AUTO_INCREMENT,
    Date_of_Birth NUMERIC,
    Gender TEXT,
    Address TEXT,
    Email TEXT,
    Degree TEXT,
    PRIMARY KEY (Student_ID)
)

This is my DML for the tables

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO151", "Databases", "2019")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("ECON130", "Microeconomic Principles", "2019")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("FCOM111", "Government, Law and Business", "2019")

NSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO101", "Foundations of Info Systems", "2019")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO141", "Systems Analysis", "2018")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO151", "Databases & SQL", "2018")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("MGMT101", "Introduction to Management", "2018"),

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("QUAN102", "Statistics for Business", "2018")

This is is already in my table as Data. It works fine.

INSERT INTO StudentInfo (Name, Student_ID, Date_of_Birth, Gender, Address, Email, Degree)
Values ("James bond", "007007", "07-07-1977", "Male", "10 Downing Street", "JB007@gmail.com", "Bachelor of Technology")

This is is already in my table as Data. It works fine.

INSERT INTO GradeInfo(Student_ID, CourseNo, Pts, Grade)
VALUES("7007", "INFO151", "15", "A+")

This is where I am having the problem. The values for student_ID and courseNo are already in the table but still get the error message of "foreign key mismatch - "GradeInfo" referencing "CourseInfo".

Note that Student_ID is a foreign key from StudentInfo and CourseNo is a foreign key from CourseInfo

Any help would be appreciated. Thank you:)

You have two issues:

A typo in your GradeInfo table definition that should be a syntax error, but unfortunately sqlite is all too often too lenient about what it accepts:

FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID)
FOREIGN KEY (CourseNo) REFERENCES CourseInfo (CourseNo),

Notice the lack of comma. (EDIT: This actually works even though it shouldn't! Bug in Sqlite?)
The primary key on CourseInfo is (CourseNo, Year), but your foreign key only references CourseNo. You have multiple rows in CourseInfo with the same value for CourseNo. Which one is supposed to be used as the parent to the foreign key? Add tracking the year too to the table and it works:

  CREATE TABLE GradeInfo (                    /*Created 3rd*/
    Student_ID NUMERIC AUTO_INCREMENT,
    CourseNo Text,
    Year Numeric,
    Pts NUMERIC,
    Grade TEXT,
    FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID),
    FOREIGN KEY (CourseNo, Year) REFERENCES CourseInfo (CourseNo, Year),
    PRIMARY KEY (Student_ID, CourseNo, Year)
    );
INSERT INTO GradeInfo(Student_ID, CourseNo, Year, Pts, Grade)
VALUES(7007, 'INFO151', 2018, 15, 'A+');

Also, there's the double quote vs single quote issue that sticky bit mentioned already, and AUTO_INCREMENT is not a sqlite keyword - you should read this documentation about auto increment and how to use it (If you really need it; you usually don't; an INTEGER PRIMARY KEY is enough).


MySQL replication slave hangs after encountering SET @@SESSION.GTID_NEXT= 'ANONYMOUS'; I recently installed two identical default installations of MySQL 5.7 under Ubuntu Server 16.04, and configured them to do a binary log replication. Until now this has been working fine, but suddenly the replication stops continuing and the slave query thread runs at 100% CPU without doing any work.

After some searching I found that the slave status tells that it is way behind master. Using mysqlbinlog on the binlog file indicated by Relay_Master_Log_File and position Exec_Master_Log_Pos, I found out that the statement executed at this position is:

SET @@SESSION.GTID_NEXT= 'ANONYMOUS';

Somehow the slave hangs when trying to execute this statement, sending the CPU load to 100% (which is how I discovered the situation in the first place).

Except by having the slave skip that statement using SET GLOBAL sql_slave_skip_counter=1 it is unclear to me what is the actual cause of this issue, and how I should solve this.

Any help would be really appreciated!


Does a weak entity always have a weak relationship and total participation in ER diagram? I've found in some videos that say a weak entity has a total participation and weak relationship in er diagram. In other videos it isn't. I couldn't find a clear explanation for this. Please can someone explain this to me.

For example, this states that the weak relationship exists when there is no primary key of the parent entity attached on the weak entity. Is it true?

A weak entity type is one whose primary key contains a relationship to another entity. Total participation in a relationship means that every instance of an entity type must be paired across that relationship to the entity instance at the other end of the relationship. Add in the theoretical requirement that an entity type's primary key must not contain NULLs and you have the solution: a weak entity's key contains a reference to another entity and that reference cannot be NULL i.e. the weak entity has a total participation in that relationship.

In physical terms we can represent the weak entity type's implementation as a table like this:

create table weak_entity
(
  parent_id  not null,
  weak_id    not null,
  other_columns,

  primary key(parent_id, weak_id),

  foreign key(parent_id) references parent(parent_id)
);

Note that parent_id is NOT NULL so weak_entity has total participation in this relationship.

The converse need not be true - the "parent" entity need not have total participation across the same relationship.

In real life some DBMS do allow nullable columns in a key. Then the total participation requirement is relaxed. That's the messy reality of real life, distinct from nice relational theoretical considerations.

Your reference says a weak relationship

  exists when the PK of the related entity does not contain at least one of the PK attributes of the parent entity. For example, if the PK of a COURSE entity is CRS_CODE and the PK of the related CLASS entity is CLASS_CODE, the relationship between COURSE and CLASS is weak.

In other words, the foreign key column(s) in the child (that reference the parent's primary key) must not be part of the child's primary key. This contrasts with the definition of a weak entity (above) which says the parent's key must be part of the child's primary key. The implementation would look something like

create table strong_entity
(
  strong_id    not null,
  other_columns,
  parent_id  not null,

  primary key(strong_id),

  foreign key(parent_id) references parent(parent_id)
);

From this I would say a weak entity must have a strong relationship to that parent and a strong entity must have a weak relationship.

Note that a "child" table can have foreign keys referencing many other "parent" tables. Some of those relationships may be part of the child's primary key (and will be strong relationships) and some may not (and are therefore weak relationships).

As an example consider a Q&A site where a user can comment on many posts and a post can have comments from many users. The PK of entity Comment would likely contain the FKs referencing Post's PK and also the FK referencing User's PK. So Comment is a weak entity (its PK contains FKs) and those relationships are strong. Now imagine Comment also has a relationship to a table of IP addresses. That relationship is not part of the PK of Comment so it is a weak relationship.

Although PK columns should be mandatory there is no such theoretical restriction on FK columns. Therefore the columns implementing a weak relationship can be nullable and we cannot say, in general, whether there must be total participation over a weak relationship.


PostgreSQL: Run EXPLAIN on all queries in pg_stat_activity I am investigating high load on a PostgreSQL database on RDS. As a part of the investigation, I'm running the following query:

SELECT now()-query_start, state, query FROM pg_stat_activity ORDER BY 1 DESC;

This query returns the longest-running queries on the database. Then, I manually copy the query text to another window, and run EXPLAIN on each query to identify longer queries that need indexing.

Is there a way to automatically run EXPLAIN on each query in pg_stat_activity?

It's not exactly what you are looking for, but the auto_explain extension may be as close as you can get.  See auto_explain


What would cause "SELECT permission denied" for an object? This is a work backwards kind of issue, since I can't tell you all of the requirements to cause this to happen.

I'm getting the following error for a table (TABLE01) in one database (DB01), but not a table (TABLE02) in the other (DB02), for a brand new user (USER01), when building a view (VIEW##) and granting SELECT permission on the view for the user.  I can correct it by granting SELECT permission on the table (TABLE01) in the database that's causing the issue (DB01).  But why wouldn't I need to do it in DB02?

CREATE VIEW [DB01].[x].[VIEW01] AS SELECT * FROM [DB01].[dbo].[TABLE01]

GRANT SELECT ON [DB01].[x].[VIEW01] TO [domainx\USER01];

SELECT * FROM [DB01].[x].[VIEW01]; -- throws error below
-- The SELECT permission was denied on the object 'TABLE01', database 'DB01', schema 'x'."

GRANT SELECT ON [DB01].[dbo].[TABLE01] TO [domainx\USER01]; -- fixes error above

BUT IN DB02, I ONLY HAD TO RUN TWO COMMANDS TO GRANT SELECT PERMISSION.  WHY WOULD THIS BE?

CREATE VIEW [DB02].[x].[VIEW02] AS SELECT * FROM [DB02].[dbo].[TABLE02]

GRANT SELECT ON [DB02].[x].[VIEW02] TO [domainx\USER01];

SELECT * FROM [DB02].[x].[VIEW02]; -- works fine; returns results

These symptoms suggest the x schema in DB01 is not owned by dbo, resulting in a broken ownership chain. Permissions are then required on indirectly referenced objects.

To maintain an unbroken chain in DB01 for the query that joins tables in the x and dbo schemas, the x schema must also be owned by dbo. This can be changed with ALTER AUTHORIZATION:

USE DB01;
ALTER AUTHORIZATION ON SCHEMA::x TO dbo;


Oracle returns different results when executing a sql with bind variable or with a fix value The following two statements (strongly simplyfied) returning a different result:

with a fix value for rownmun:

select * from table
 where rownum <= 12;

with bind variable for rownum:

select * from table
 where rownum <= :maxsize;

I know, Orcale is using a different execution plan due to internal statistics, but imho even if the choosen plans are different , the result should be the same.
I'm using a Oracle 10g.

My dba skills are limited, therefore I would appreciate any hint or advice, that helps me to understand this strange behaviour.

The results would not be guarenteed to be the same if the execution plans are different. Without an explicit sub-query ordering the rownum will be based on the order in which Oracle selected the data from the tables / joined tables. If the plans differ on how the tables are joined and in which order, the first 12 values read will be different.

To make this more deterministic, use some kind of explicit ordering on a subquery and then rownum that on the outer query. It would be normal to want the first X records based on some explicit ordering, instead of 12 random ones.


Performance issues with CAST function Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database.

There is currently a table public.users, which has a cast on it to output a record to JSONb.  (e.g. SELECT u::jsonb FROM users u;).

The database has grown in complexity and schemas, which a new schema (think module) has been added that adds a few things to the user accounts (via joins in the schema table).

To make the cast to JSONb consistant across the app, the public.users cast was refashioned to use a cast in a new schema called catalogue.  This way casting public.users will get additional properties from the catalogue schema in a transparent way to the user.  This was done so that the API Server doesn't need to know to use this/that schema, and I can leave my (reused) User API Controller the same.

The new [refashioned] cast returns the cast from catalogue.users, so basically the public's cast function on users call a [SQL] function with the simple SQL command..

CREATE OR REPLACE FUNCTION catalogue.cast_user_jsonb_override(public.user)  RETURNS JSONb STABLE LANGUAGE sql
AS $$
 SELECT u::jsonb FROM catalogue.users u WHERE username = $1.username
$$;

This way the casting public.user to JSONb will utilise the above cast without the user knowing.

However, for some reason calling the cast for public.users is slow, while calling the cast for catalogue.users is fast.

Calling...

EXPLAIN ANALYSE VERBOSE SELECT u::jsonb FROM catalogue.users u;
....
Planning time: 0.046 ms
Execution time: 40.086 ms

while calling...

EXPLAIN ANALYSE VERBOSE SELECT u::jsonb FROM catalogue.users u;
....
Planning time: 0.094 ms
Execution time: 1384.414 ms

or directly...

EXPLAIN ANALYSE VERBOSE SELECT catalogue.cast_users_jsonb_override(u) FROM public.users u;
....
Planning time: 0.101 ms
Execution time: 1325.343 ms

If anything, the catalogue cast should be the slower one as is performs joins and the likes.

The [override] cast function is STABLE to help with performance, but it doesn't seem to do much.

Any ideas as to why calling a function that executes a basic SELECT is slow, while calling the SQL itself is fast? . I haven't tested it in any other postgresql version, and there isn't any priority to do an upgrade to v10 to test as there is currently a deadline on the release.

Put auto_explain into shared_preload_libraries in postgresql.conf and restart PostgreSQL.

Set

auto_explain.log_min_duration = 0
auto_explain.log_nested_statements = on
auto_explain.log_analyze = on
auto_explain.log_buffers = on

in postgresql.conf (and reload).

Then run the slow query again.

This will log all SQL statements and their execution plan in the PostgreSQL log. Examine the plan for the statement run in your function. That should give you a clue.

The most likely explanation would be a missing index on catalogue.users(username).


open mysql on os x backup disk image I have several databases I set up using mamp. Recently, my mac died of old age and I got a new one. Fortunately I have a made of bootable backup of my hard drive every day.  Unfortunately, I cannot boot off of it with my new mac - the image is os x tiger and the new mac won't run tiger, only os x lion.

The upshot is, I have several mysql databases on that backup disk that I need to move to my new machine.  Is there a way of doing this using my current setup running mamp under lion, perhaps open the databases on the backup disk, export the databases and then restarting mamp from the lion databases, and importing what was exported, or something?

Thanks for any ideas!

On a mac, your MySQL installation is located in:

/usr/local/mysql/

This is the folder structure:

$ ls -lah /usr/local/mysql/
total 64
drwxr-xr-x  16 root    wheel   544B Nov 13 15:54 .
drwxrwxr-x  27 root    admin   918B Nov 24 11:42 ..
-rw-r--r--   1 root    wheel    18K Nov 13 15:47 COPYING
-rw-r--r--   1 root    wheel   7.2K Nov 13 15:47 INSTALL-BINARY
-rw-r--r--   1 root    wheel   2.5K Nov 13 15:47 README
drwxr-xr-x  46 root    wheel   1.5K Nov 13 15:54 bin
drwxr-xr-x  18 _mysql  wheel   612B Apr 27 13:37 data
drwxr-xr-x   6 root    wheel   204B Nov 13 15:54 docs
drwxr-xr-x  37 root    wheel   1.2K Nov 13 15:54 include
drwxr-xr-x  12 root    wheel   408B Nov 23 08:58 lib
drwxr-xr-x   5 root    wheel   170B Apr 25 06:48 man
drwxr-xr-x  19 root    wheel   646B Nov 23 08:58 mysql-test
drwxr-xr-x   3 root    wheel   102B Nov 13 15:54 scripts
drwxr-xr-x  32 root    wheel   1.1K Nov 13 15:54 share
drwxr-xr-x  28 root    wheel   952B Nov 13 15:54 sql-bench
drwxr-xr-x  16 root    wheel   544B Nov 13 15:54 support-files

You probably want to copy all of it to your local disk, then install MySQL from a disk image (DMG), backup the installed data folder, copy your backed up data folder contents into /usr/local/mysql/data, permission the folder to mysql, start mysqld and do an mysql_upgrade.

install mysql server from DMG... then...

 sudo mv /usr/local/mysql/data /usr/local/mysql/data_ORIG
 sudo cp /mybackupfolder/mysql/data /usr/local/mysql/data
 sudo chown -Rf mysql:wheel /usr/local/mysql/data
 sudo su -
 nohup /usr/local/mysql/bin/mysqld_safe &
 exit
 /usr/local/mysql/bin/mysql_upgrade -u root -p
 # read output

*Note: if you have a my.cnf, mv it to /etc/my.cnf.ORIG before you start mysqld.


My query fails to segment the results as intended. What am I doing wrong? Update:
Re-phrasing the question.

Below are the results and the desired results. What do I need to do to get the desired results?  See http://sqlfiddle.com/#!2/d0e13c/69

Thank you

SELECT CONCAT('Greater than or equal to ',t.val2,':', count(*)) FROM
(
SELECT
  CASE
    WHEN val>=1000 THEN 1000
    WHEN val>=500 THEN 500
    WHEN val>=400 THEN 400
    WHEN val>=300 THEN 300
    WHEN val>=200 THEN 200
    WHEN val>=100 THEN 100
    WHEN val>=10 THEN 10
    WHEN val>=5 THEN 5
    WHEN val>=2 THEN 2
    WHEN val>=1 THEN 1
  END AS 'val2'
FROM e
) as t
GROUP BY t.val2
ORDER BY t.val2 DESC

Results:

Greater than or equal to 1000:1
Greater than or equal to 500:1
Greater than or equal to 400:2
Greater than or equal to 300:1
Greater than or equal to 200:2
Greater than or equal to 100:3
Greater than or equal to 10:1
Greater than or equal to 5:1
Greater than or equal to 1:2

Desired results:

Greater than or equal to 1000:1
Greater than or equal to 500:2
Greater than or equal to 400:4
Greater than or equal to 300:5
Greater than or equal to 200:7
Greater than or equal to 100:10
Greater than or equal to 10:11
Greater than or equal to 5:12
Greater than or equal to 2:12
Greater than or equal to 1:14

It's possible to convert the CASE options into rows of a temp table, a JOIN with a less or equal condition to the data table will get the rows.

SELECT CONCAT('Greater than or equal to ',a.val,':', count(e.val))
FROM   (Select 1000 val UNION ALL
        Select 500      UNION ALL
        Select 400      UNION ALL
        Select 300      UNION ALL
        Select 200      UNION ALL
        Select 100      UNION ALL
        Select 10       UNION ALL
        Select 5        UNION ALL
        Select 2        UNION ALL
        Select 1       ) a
       LEFT JOIN e ON e.val >= a.val
GROUP BY a.val
ORDER BY a.val  DESC

SQLFiddle demo

Another way doesn't even need the temp table, but will get the results as columns instead of as rows, if you care about the results and not about the format

SELECT CONCAT('Greater than or equal to 1000: '
             , SUM(Case When val >= 1000 Then 1 Else 0 End)) _1000
     , CONCAT('Greater than or equal to  500: '
            , SUM(Case When val >= 500 Then 1 Else 0 End)) _500
     , CONCAT('Greater than or equal to  400: '
            , SUM(Case When val >= 400 Then 1 Else 0 End)) _400
     , CONCAT('Greater than or equal to  300: '
            , SUM(Case When val >= 300 Then 1 Else 0 End)) _300
     , CONCAT('Greater than or equal to  200: '
            , SUM(Case When val >= 200 Then 1 Else 0 End)) _200
     , CONCAT('Greater than or equal to  100: '
            , SUM(Case When val >= 100 Then 1 Else 0 End)) _100
     , CONCAT('Greater than or equal to   10: '
            , SUM(Case When val >= 10 Then 1 Else 0 End)) _10
     , CONCAT('Greater than or equal to    5: '
            , SUM(Case When val >= 5 Then 1 Else 0 End)) _5
     , CONCAT('Greater than or equal to    2: '
            , SUM(Case When val >= 2 Then 1 Else 0 End)) _2
     , CONCAT('Greater than or equal to    1: '
            , SUM(Case When val >= 1 Then 1 Else 0 End)) _1
FROM   e

SQLFiddle demo


How index work in oracle? I want to understand :

How index work in oracle.

How they are stored in the disk.

How they increase the efficiency of a query?

By third point I means how Index contribute to the efficiency of query such as in joins,group by clauses.

One more point is How index behave in Flashback queries.

If a heap-organized table has no indexes, then the database must perform a full table scan to find a value.

Following link provides information on indexes in detail
http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#CNCPT721
here you will find details of various kinds of indexes Oracle supports

If you have query like

Find a,b,c where emp_name = 'Adam Smith'

In case you have an index on column emp_name than instead of searching the entire table the data can be fetched directly. In case the column is not unique multiple rows that have emp_name Adam Smith will be fetched.

On large tables Indexes can improve the performance of queries even 100 times.

Performance improvement depends on the selectivity. In million values in a column if 100% are unique you get maximum selectivity and performance improvement is maximum, but if you have only 2 different values of a column say like 0 or 1 or yes or no you may get some improvement or no improvement or even degradation.

There are different types of Indexes like B-Tree, Bitmap, Bitmap join etc.
Most commonly used indexes are B-Tree.

B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. Indexes are stored as B-Tree on disk

You can find details of B-Tree indexes on the following link
http://en.wikipedia.org/wiki/B_tree_indexing


Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table? Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my question.

Basically I'm working on a new feature for an existing system and am powerless to change the existing schema. So let's say that there currently is a Users table which has a primary key UserID that is of type uniqueidentifier. I want to create a new table called Alerts - related to the Users table in a one-to-many relationship (one User can have many Alerts). Alerts will be regularly queried by this foreign key. I will expect to index this field for performance.

My question is - from what I understand about Indexing strategies, indexing a uniqueidentifier column is (delicately put) not a good idea - so would it be a better idea to have an intermediary table to map these uniqueidentifier keys into a better indexing candidate like an integer?

Users

UserID uniqueidentifier {PK}
Username varchar(50)

Alerts

AlertID int {PK}
UserID uniqueidentifier {FK}
Message varchar(250)
Timestamp datetime

OR

Users

UserID uniqueidentifier {PK}
Username varchar(50)

UserMap

UserKey int identity(1,1) {PK}
UserID uniqueidentifier {FK}

Alerts

AlertID int identity(1,1) {PK}
UserKey int {FK}
Message varchar(250)
Timestamp datetime

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed.  The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit.  If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table.  UserID will be a nonclustered index key on Alerts table.  This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table.  UserId will probably be the clustering key in UserMap too, to make joining more efficient.  UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert.  And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation.  Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of  340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only  4,294,967,296 INTs.  Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt


How to diagnose MSSQL high CPU spike I've had my server CPU spiked to 100% for about 20 minutes. The database was partially unresponsive in certain parts.( i know this because users complained that they cant use certain features, but they could login, and use other areas, only not specific). Before I could check current queries it was gone. I want to see what was the cause of it. Does anyone have some kind of instructions on this? How do I actually get to the root cause of this. How to see what query caused this?  I am using MSSQL
edit:
I found in RING BUFFER LOGS which record id and at what time CPU spiked. Is this something that can bring me to root of issue ?

If you don't have something that captured the queries being executed at the time, all you can do is guess.  In case you're on SQL Server 2016 or higher, and you have Query Store enabled for the affected database, I'll mention that you could track down long-running or slow queries there.  If you don't have query store or some other way of tracking activity after a problem, you might want to look into a monitoring tool, or at least some simpler "home grown" solutions, like logging sp_WhoIsActive to a table.
If the queries that caused this problem are still in the plan cache, you could try running sp_BlitzCache to find the most CPU-intensive queries:
EXEC master.dbo.sp_BlitzCache
    @DatabaseName = N'YourDatabaseName',
    @SortOrder = 'avg cpu';

To be clear, there's no way to know for sure if those queries are the problem ones, but it might be your best bet at this point.
You could also try to narrow down the tables involved (based on which areas of the application or features you said you know were inaccessible).  Once you've done that, you could run sp_BlitzIndex on that table and get a breakdown on how it's being used:
EXEC master.dbo.sp_BlitzIndex
    @DatabaseName = N'YourDatabaseName',
    @SchemaName = N'ProbablyDbo',
    @TableName = N'YourTableName';

That will show you if there are indexes being scanned a lot, or experiencing lock escalation, or long lock waits, etc.  Again, this would all be sort of circumstantial evidence of potential problems.  But it might help.


Exact behaviour of CHANGE_TRACKING AUTO I am trying to understand population options for SQl server's full text indexes. My target is to access whether our currently used option (default = CHANGE_TRACKING AUTO) is the best to use in our case.

I think I have understood perfectly every bit of information in the related MS reference page, except from this devilish line from the subchapter Population based on change tracking/Enable change tracking/Automatic population:

  ...the tracked changes are propagated automatically. The full-text
  index is updated in the background, however, so propagated changes
  might not be reflected immediately in the index.

Nothing in the following reference, however, gives any information about what exactly not immediately means in regards to the exact time the population happens. I have found around queries that can return the last timestamp the population took place; but how can I have an idea of how often and when the system tries to, or indeed executes, the population?

concerning "The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index." from what I can tell and experience it means the change to the full-text is updated as any other clustered index, using a background task and not on the immediate instant that the data was changed and the update was triggered.

Also reading a bit more on the documentation (create fulltext index):
"CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }
Specifies whether changes (updates, deletes or inserts) made to table columns that are covered by the full-text index will be propagated by SQL Server to the full-text index. Data changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking."

This is for the manual option meaning that for the AUTO the reverse can be applied, the change is done by the change tracking event and not immediate.

And here is why (Population based on change tracking):

"Optionally, you can use change tracking to maintain a full-text index after its initial full population. There is a small overhead associated with change tracking because SQL Server maintains a table in which it tracks changes to the base table since the last population. When you use change tracking, SQL Server maintains a record of the rows in the base table or indexed view that have been modified by updates, deletes, or inserts. Data changes made through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking."


SSD for production database I am looking for a solution to [dramatically] improve an enterprise database performance on a system that needs to support thousands of transactions (inserts & large selects) per second. Besides the database/queries optimization, we are also looking for hardware improvements, too.

I need someone with experience in database administration that used SSD drives to store databases. What RAID configuration did you use? RAID1 looks like a good choice maybe combined with another redundancy-focused configuration, also SLC technology would be preferred for SSD drives I suppose.

The questions are: what type of SSD drives are you using (make, model)? How are they organized in RAIDs, what improvements did you achieve and how many (major) issues did you encounter? How much are you worried about the reliability of current SSD drives? Would you recommend using them for database (meaning critical) storage?

Also, what was the approximative price (at the time you implemented the solution, of course) of the implemented solution?

Maybe this post can also provide hystorical data about how SSD+database relationship has evolved over the time.

PS: If important, we are currently using SQL Server 2008 R2. Database size: over 400GB at this time, but my question is not only related to my specifics.

Thanks.

An interesting article found here: http://www.remote-dba.net/t_in_memory_cohesion_ssd.htm

Start by watching Brent Ozar's  introduction into the subject SQL on SSDs: Hot and Crazy Love. Follow up with the extensive and exhaustive benchmarking series from Paul Randal: Benchmarking: Introducing SSDs (Part 1: not overloaded log file array). There are many details, depending on workload type, dat vs. log, placement of tempdb etc etc etc.


Database Design Issues My question consists of three parts:

When can I be sure that my database design is perfect?

Is returning to the database design to change some issues (i.e, adding new column, deleting a column, changing data type, add new table, etc.) considered a bad practice or is it normal?

Are there any websites or books just for training on ERD and normalization? I want a lot of samples, practices, and case studies with recommended answers, to strengthen my skill in database design and avoid the poor database designs I've made.

Note: I don't need books to explain the concepts, what I need is practices, examples, and case studies with recommended answers.

1)  When to be sure that your database design is perfect?
Your design is never perfect because the business logic and amount of data is always changing. Perfect is difficult to define
I've seen systems that were great on deployment but had poor performance after a few years of data were added.  The regrettable trend to treat a database like a black box by some application developers means some databases are deployed with critical tables lacking primary keys or indexes.  Perfect to the CIO because they got the application delivered on time and on budget could be a pain in the butt to the developer/ DBA who has to deal with the problems.

Here are some of indicators I would look for which indicate your design is ready to go
a) Extensive use of primary keys, unique keys, foreign keys, indexes, more so on the larger tables ( I only mention this because I've seen commercial products which lack this)
b) Application logic is duplicated as far as is practical in the database with the use of constraints and default values.  You cannot capture everything but just knowing that there will always be a value for an entry provides peace of mind.
c) Test, test, test:  test from the user perspective on data entry, test from the manager perspective who wants an overview, test from the analyst perspective who wants to see trends
If you find yourself joining 9 large tables which require full table scans in order to find out what work is assigned to a user then maybe you need to reexamine things.
Not everything can be simple but excessive complexity to answer user needs is a hint of trouble to come.
d) do some daydreaming on how the database could and could not be extended.  If I need to add a new property to a unit of work how hard is it?  Can it be done without table changes?  If you are asked to add a new type of work how hard is it.  (Work could be a product, a transaction, a case)
e) people and organizations provide hours of work for me.  How easy is it to create, edit, de-duplicate and report on them?
f) how many user languages are you supporting? What character sets will be required.  If, for example, you intend to support English and Spanish, what happens if the design must be extended to cover French and Italian?

2) Is returning to the data base design to change some issues (like adding new column, delete a column or change data type or add new table or ....) considered as a bad practice or is it normal?

I would say it is normal for an application where the business logic changes frequently or the end user requirements are being added to.


How to create a trigger that update the date and time field and modify of the row I want to create trigger that will update the record. The situation is like:

I have one column named CheckInDateTime and one more named Status.  If the administrator is not able to update the Status within 30 minutes of CheckInDateTime I want to update the Status column of that particular row.

How can I update that particular row?

Create a task in SQL Server Agent that runs a stored procedure once every minute that updates the affected rows.

For instance, something like this might work:

CREATE PROCEDURE UpdateRows
AS
BEGIN
    DECLARE @Cutoff DATETIME;
    SET @Cutoff = DATEADD(mi, -30, GETDATE());
    UPDATE MyTable
    SET Status = 'ForcefullyClosed'
    WHERE CheckInDateTime < @Cutoff AND Status IS NULL;
END;


Estimated vs. Actual rows and multi-column statistics I'm trying to understand multi column indexes and statistics, here is my real world example:

Table: TaskExecutions, size=~1 million rows.
Query SARGS: TaskExecStatusID = 3 AND TaskExecUpdatedDate IS NULL

Distribution:: TaskExecStatusID: 1=160k rows, 2=370k rows, 3=560k rows.
TaskExecUpdatedDate: Null=171k rows.

I have an index on (TaskExecStatusID,TaskExecUpdatedDate),

My problem/question is that the estimated number of rows for the above query is 256, the actual number of rows is 560K.

I want to understand why there is such a big difference between these two numbers?

Stats are up to date, the stats for the index are multi-column stats.  I've tried creating in index/stats on (TaskExecUpdatedDate, TaskExecStatusID), and the estimate/actual does not change.

If I run a DBCC SHOW_Statistics, the density section has both columns in it, the histogram does not. Does SQL server produce a histogram for the combination of columns in multi-column statistics?

dave

You've actually got a few questions in here, so I'll break 'em out individually.

  My problem/question is that the estimated number of rows for the above
  query is 256, the actual number of rows is 560K.  I want to understand
  why there is such a big difference between these two numbers?

In order to answer that question, the first thing we would need is the actual execution plan for the query.  In SQL Server Management Studio, you can get that by clicking Query, Include Actual Execution Plan.  Run the query, click on the Execution Plan tab, and right-click anywhere in the whitespace to click Save Plan As.  Save that, and post it somewhere for people to download and examine.

The next thing we would need is the output from DBCC SHOW_STATISTICS for the stats on that table.  You've hinted at the output, and that's a good start, but the raw output will help us understand exactly what's going on.

  If I run a DBCC SHOW_Statistics, the density section has both columns
  in it, the histogram does not. Does SQL server produce a histogram for
  the combination of columns in multi-column statistics?

No.

  I have an index on (TaskExecStatusID,TaskExecUpdatedDate),

If you frequently use the query in the example (with TaskExecUpdateDate IS NULL), then you might check out filtered indexes.  They're a new feature in SQL Server 2008 that allows you to put a where clause on your index, basically.

http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/


Sp_BlitzCache 678 not writing to table Version 678 from github is displaying results in SSMS query window as expected but not creating a table, using the following commands:

USE DBAResources;
GO
EXEC dba.sp_BlitzCache @Top=10, @OutputDatabaseName = 'DBAResources', @OutputTableName = 'dba.BlitzCacheResults';

BlitzCache is compiled as stored procedure dba.sp_BlitzCache in database DBAResources. Note the use of the non-dbo schema for the stored procedure and the table.

Running on SQL Server 2016 SP 1 Standard.

If you pop open sp_BlitzCache, you should see parameters for:

@OutputDatabaseName NVARCHAR(256) = NULL ,
@OutputSchemaName NVARCHAR(256) = NULL ,
@OutputTableName NVARCHAR(256) = NULL ,

You seem to have tacked the schema name onto the table name. That's probably what's causing the problem.


Sql Server Resource Governor - track IO I know I can use the performance counters to track Resource Governor CPU and Memory Usage.  Is it possibly to track the I/O usage for each work load group?  I haven't seen anything specific or any recommended practices.

Resource Governor does not manage I/O, it is just system resources for CPU and Memory.  Therefore, I don't believe there would be any way to get I/O per workload group.

Quote from BOL on Resource Governor:

  Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use.


Can a hash index become inconsistent for "no reason"? Of course, when i say no reason i mean "apparently" no reason because nothing happened on the server (forced shutdowns, data corruption, deadlocks, etc...).

I ask this because, there was an application which executed a simple select on PostgreSQL:

select * from product where barcode = '9990000088355'

Which returned nothing, but there was a record in the table with barcode = 9990000088355. There was an index specific for this field, and after reindexing it, the problem was solved and the query fetched the record.

So, is there a reason for this issue to happen, and can i do something to prevent this or at least see if there is a problem with other indices of another tables?

FINALLY, after so much time i found the cause of this problem. I actually saw the machine where postgres was running suffering a hard reset, after that, the  exact error happened.
Turns out the index i was using was a HASH index instead of the default B-Tree.
So, looking up on postgres documentation, the first thing i saw was a giant yellow warning explicitly stating about it's unsafety when a crash occurs.
Taken from the docs:

  Caution
  Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash if there were unwritten changes. Also, changes to hash indexes are not replicated over streaming or file-based replication after the initial base backup, so they give wrong answers to queries that subsequently use them. For these reasons, hash index use is presently discouraged.

What i did was simply drop the hash index and create a new one with B-tree algorithm. So far it seems to be the working solution.


is the only way to create a user for an azure database, to use SQL query? Azure databases are contained databases (right?) then I need to use this to create a user:
CREATE USER UserName WITH PASSWORD = 'hardPass';
ALTER ROLE [db_owner] ADD MEMBER UserName ;

is there a way to do this via portal? I found the oficial documentation saying that the only way to add a user is using a query, but maybe the doc is not updated.
Thanks.

Thats correct. Only via the Create User query. The GUI is not supported to create user and permissions for Azure sql database.
You can do that via any client (ssms) or the query editor in portal


Restore database with different filename I have to restore a new back up file daily (as an agent job, after first deleting the existing db) but the file name will vary as it will include the timestamp as well.

How can I achieve this so that the restore operation will identify the new file? The name of the file will be something like this and always in the same directory: AnyLetterGoesHere_TimeStamp.bak

Thanks all

you can create a table variable(or temp table) and insert all the .bak file metadata in that table and then fetch the row with max value of modified time.

DECLARE @FileMeta table (columns nvarchar(200));

INSERT INTO @FileMeta
EXEC sys.xp_cmdshell 'dir D:\FolderName\*.bak';

SELECT * FROM @FileMeta
WHERE columns not like '%volume%'
AND columns is not null and columns not like '%bytes%';

Now you can get the creation date of the file as shown in this link, and Now from this table @FileMeta You can get the row for Max of creation date that will give you the most recent file.


Migrate DTS Jobs to 2008 Server We have been running SQL Server 2000 for years.

Finally, at long last, we have an SQL 2008 Server being shipped in.

We have many DTS jobs on our SQL 2000 Server that need to be migrated to the SQL 2008 Server.

Where can I go to look up how to convert DTS jobs so that they can be run on SQL 2008?

I am a WinForms C# developer that makes calls, builds stored procedures and occasionally creates a new table for our SQL Server, but I am not a Database or Network Administrator by title.

I want to make sure I do this right so that our company has minimal down time.

From our Network Administrator:

  From what I am reading, I will have to do more research, we have to convert all the Data Transformation Services (DTS) packages to SQL Server Integration Services (SSIS). I do not know what this may take but from my understanding, the jobs are written in .Net. If we could migrate them, great, but if not we may have to write new ones for all, which would not be that hard once you have the first one written.

Does this help?

You can import the old DTS packages into SQL Server 2008 using the DTS Migration Wizard. You can find it on the SQL 2008 Management Studio at the following folder: Server -> Management -> Legacy -> Data Transformation Services - Migration Wizard. Point to the current source of the packages and this should help you do your job.

You should be able to find more information here: Support for SQL 2000 DTS packages in SQL 2008 on MSDN.


delete all customers with email ending with example.com with specific DATE period delete all customers with email ending with example.com using mysql with specific DATE period from 9/15/2018 to 9/28/2018

Something like this:

delete customers
where email like '%example.com'
and date_created between '2018-09-15' AND '2018-09-28';

But first, run a select to see if it will delete the correct rows:

select * from customers
where email like '%example.com'
and date_created between '2018-09-15' AND '2018-09-28';


How to join 3 tables in MySQL I’m looking at 3 tables here with:

1 called Users containing a UserID.
UserID is linked by a foreign key to UserID in a table called Orders.
Within Orders there’s a PassengerID linked by a foreign key to a table called Passengers.

Is there anyway I can link these tables to identify the UserID it’s associated OrderID and that OrderIDs associated passengerID.

This should be straightforward, probably something like this:

SELECT u.UserID, o.OrderID, p.PassengerID
FROM Users u
  JOIN Orders o ON u.UserID=o.UserID
  JOIN Passengers p ON o.PassengerID=p.PassengerID
WHERE ...

The WHERE condition, if you want the WHERE clause at all, could be e.g. u.UserID=5 to retrieve only the results for a certain user, or p.PassengerID=4 for a certain passenger.

A slightly shorter way of writing the same query:

SELECT UserID, o.OrderID, PassengerID
FROM Users u
  JOIN Orders o USING(UserID)
  JOIN Passengers p USING(PassengerID)
WHERE ...

Note that here the aliases u and p are not needed in the SELECT clause. USING can only be used because the columns have identical names in the two tables being joined.


Is there a parser for Postgres' auto_explain to make it easier to read and understand? We have a number of Postgres Functions that each call a number of other Postgres Functions. If you simply EXPLAIN ANALYSE a call like SELECT parent_function(), the output is minimal, since it doesn't dive into the calls the parent_function makes.
Thanks to answers like this (Postgres query plan of a UDF invocation written in pgpsql), we discovered auto_explain.

So, we turned this stuff on:

auto_explain.log_min_duration = 0
auto_explain.log_analyze = true
auto_explain.log_verbose = true
auto_explain.log_timing = true
auto_explain.log_nested_statements = true

The good news is that it gave us what we were looking for: A way to see the timing of every nested statement called during a long-running function.
However, the output is very verbose, and it's hard to see the parent-child relationship I'm "used" to seeing in profilers:

some_big_function           1000ms
  --child_function1          800ms
    --child_child_function   600ms
  --child_function2          200ms

Is there a better way to view this log output? Ideally it would group "nested statements" with their parents, showing me, at a glance, where I should look.
We tried pgbadger, but it's not configured to parse auto_explain output, since auto_explain of this verbosity isn't usually running on production servers.

Are there other log parsers (or other techniques) to help profile functions?

SUGGESTION #1
If you are just looking to take an EXPLAIN plan and instantly present it, here is a SQL-Fiddle like site called explain.depesz.com. Just copy-and-paste the explain and hit Submit.
SUGGESTION #2
You could try pgAdminIII which comes with Graphical Explains
See the following links to how to use pgAdmin and other methods

PostgreSQL : Up and Running (Chapter ob pgAdmin)
PostgreSQL Wiki's Using EXPLAIN
READING PGADMIN GRAPHICAL EXPLAIN PLANS
pgAdmin 1.4 online documentation


Allow transparently altering View's underlying table How to create a view that does not prevent altering its underlying table? There's a certain reason why I can't drop and re-create the view every time I need to change a column type or add a new column. This is because the alter script is generated by our ORM when we change the complex type definition and there's a stable deployment procedure I don't want to change.

-- test initialization
drop view if exists test_dep_trk_v;
drop table if exists test_dep_trk;
create table test_dep_trk ( a varchar(10) );

-- the view MUST at all times contain all the columns of the table
create view test_dep_trk_v as select * from test_dep_trk;

-- this statement cannot be changed
alter table test_dep_trk alter column a type varchar(11);

.

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view test_dep_trk_v depends on column "a"
SQL state: 0A000

Looks like this can be done with the event triggers of 9.4. There are some weak points:

pg_trigger_depth() always returns zero (Is this a bug?). This is why I had to use a non-temporary table to prevent recursion.
We need to filter by the altered object name, but there's no way to get it in 9.4. current_query() returns the client's statement even if we're called from a stored procedure.

_

create UNLOGGED table IF NOT EXISTS test_dep_trk_trig_data ( pid integer primary key );

CREATE OR REPLACE FUNCTION test_dep_trk_trig() RETURNS event_trigger AS $$
DECLARE
    v_query text;
BEGIN
  if NOT exists( select * from test_dep_trk_trig_data where pid = pg_backend_pid() ) then
    insert into test_dep_trk_trig_data ( pid ) values ( pg_backend_pid() );
    v_query := lower( current_query() );
    if (v_query like '%test_dep_trk%' and not v_query like '%test_dep_trk_v%') then
      -- RAISE NOTICE '%', v_query;
      drop view if exists test_dep_trk_v;
    end if;
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
  end if;
EXCEPTION
  WHEN others THEN
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
    RAISE;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_dep_trk_trig_end() RETURNS event_trigger AS $$
DECLARE
    v_query text;
BEGIN
  if NOT exists( select * from test_dep_trk_trig_data where pid = pg_backend_pid() ) then
    insert into test_dep_trk_trig_data ( pid ) values ( pg_backend_pid() );
    v_query := lower( current_query() );
    if (v_query like '%test_dep_trk%' and not v_query like '%test_dep_trk_v%') then
      -- RAISE NOTICE '%', v_query;
      create view test_dep_trk_v as select * from test_dep_trk;
    end if;
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
  end if;
EXCEPTION
  WHEN others THEN
    delete from test_dep_trk_trig_data where pid = pg_backend_pid();
    RAISE;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER test_dep_trk_trig ON ddl_command_start EXECUTE PROCEDURE test_dep_trk_trig();
CREATE EVENT TRIGGER test_dep_trk_trig_end ON ddl_command_end EXECUTE PROCEDURE test_dep_trk_trig_end();


i need clarifications on MongoDB I have a few questions, hoping I can get answers.

what does it mean when the Mongodb said:

  Clients can read documents while write operations are in progress

I can see partially updated document? or the document will be under lock and no one can see it until the lock yielded?
I need to know if two operation can update the same document with WiredTiger or MMAP V1? if so, what about reading at the same time of write or update?
If I use $isolate is this enough to be sure that no one could write on the same document?

The locking of documents differs between the WiredTiger and the MMAPv1 storage engine. When using the Wired Tiger storage engine, write operations only hold an exclusive lock at document level. This means that multiple threads can update multiple documents in the same collection at the same time, however, they can NOT update the same document at the same time. In addition to an exclusive lock at document level, WiredTigger also holds intent locks at the global, database and collection levels. These intent locks will not block reading or writing operations,however, when the storage engine detects conflicts between two operations, one will incur a write conflict causing MongoDB to transparently retry that operation.

When using the classical 'MMAPv1` storage engine, a write operation hold an exclusive lock on the entire collection and therefore multiple threads can NOT write to the same collection at the same time, let alone write to the same document at the same time.

The locking does not occur when reading from a document, so two threads can a document at the same time. Regarding $isolated, you need to use this for write operations that affect multiple documents. Setting this flag prevents a write operation from yielding to other reads and writes once the first document is written. For example, if you are running an update query on document 1,2,...100, setting the $isolated flag will prevent document 100 being changed while the update query is updating document 1.


Database Schema for Resume/CV and Separate Sections I am trying to figure out if I am on the right track here.

resumes
id
user_id

resume_sections
id
name
desc

Sections are those that we normally find in resumes, experience, skills, education and so one.

Thanks in advanced!

You are on the right track here.  Somethings to consider:

Users

Likely a User can have multiple resumes.  Job candidates often tailor a resume to a job opening and want to keep all of those.  This means you will need a User table.  Having a user table also gives  you a place to store attributes of the user such as their name.

Sections

A resume has lots of kinds of sections, such as experience, job history, education, and so on.  This means you will want to make you resume sections table a Section Type table.

Thing Types vs. Things

This brings up a common pattern in logical database schema design - the differentiation between kinds of things and the things themselves.  I would recommend you add a Section table related to Resume that holds the actual section of an instance of a resume for a user.

Model Example

Oracle provides a free tool called Oracle Data Modeler which you can use to create a visual model of the table, called an Entity Relationship Diagram (ERD), and then generate your DDL from it.  Here is an example starter model of your database design:

Creating an ERD also helps you start to clarify the requirements.  For example, I made the key to the section table the resume and a sequence number.  This means you could have 2 or more sections on a resume with the same type.  Does this make sense?  Or does it make sense to say there can only be one section of each type per resume?  Second, you want to think about what uniquely identifies a row in each table.  For example, I have used a Resume Number for the key to the resume table.  Wouldn't it make sense to say that each row would be uniquely identified by the User Name and the resume name?  This would prevent the storage of duplicate resumes.

DDL Generation

Once you nail down all the business rules, you can use Oracle Data Modeler to generate your DDL.  While it doesn't support mySQL, you can still generate it and get the base structure and then correct for mySQL specific syntax.  Here is a sample of what I quickly generated from this model:

-- Generated by Oracle SQL Developer Data Modeler 4.1.0.873
--   at:        2015-08-19 09:15:38 EDT
--   site:      Oracle Database 12c
--   type:      Oracle Database 12c

CREATE TABLE "Resume"
  (
    "Resume_Number" NUMBER (10) NOT NULL ,
    "User_Id"       NUMBER (10) NOT NULL ,
    "Name"          VARCHAR2 (100) NOT NULL ,
    "Description"   VARCHAR2 (1000) NOT NULL
  ) ;
ALTER TABLE "Resume" ADD CONSTRAINT "Resume_PK" PRIMARY KEY ( "Resume_Number" )
;

CREATE TABLE "Section"
  (
    "Resume_Number"           NUMBER (10) NOT NULL ,
    "Section_Sequence_Number" NUMBER (5) NOT NULL ,
    "Section_Type_Id"         NUMBER (5) NOT NULL ,
    "Content"                 VARCHAR2 (4000) NOT NULL
  ) ;
ALTER TABLE "Section" ADD CONSTRAINT "Section_PK"
PRIMARY KEY ( "Resume_Number", "Section_Sequence_Number" ) ;

CREATE TABLE "Section_Type"
  (
    "Section_Type_Id" NUMBER (5) NOT NULL ,
    "Name"            VARCHAR2 (100) NOT NULL ,
    "Description"     VARCHAR2 (1000) NOT NULL ,
    "Order_Number"    NUMBER (5) NOT NULL
  ) ;
ALTER TABLE "Section_Type" ADD CONSTRAINT "Section_Type_PK"
PRIMARY KEY ( "Section_Type_Id" ) ;

CREATE TABLE "User"
  (
    "User_Id" NUMBER (10) NOT NULL ,
    "Name"    VARCHAR2 (100) NOT NULL
  ) ;
ALTER TABLE "User" ADD CONSTRAINT "User_PK"
PRIMARY KEY ( "User_Id" ) ;

ALTER TABLE "Resume" ADD CONSTRAINT "Resume_User_FK"
FOREIGN KEY ( "User_Id" )
REFERENCES "User" ( "User_Id" ) ;

ALTER TABLE "Section" ADD CONSTRAINT "Section_Resume_FK"
FOREIGN KEY ( "Resume_Number" )
REFERENCES "Resume" ( "Resume_Number" ) ;

ALTER TABLE "Section" ADD CONSTRAINT "Section_Section_Type_FK"
FOREIGN KEY ( "Section_Type_Id" )
REFERENCES "Section_Type" ( "Section_Type_Id" ) ;

This gives you a good deal of DDL to start with.  A good reference on data modeling is David Hay's Enterprise Model Patterns.  I hope this answer helps you move forward with completing your design!


How to find which SQL server instance is consumimg more CPU on 4 node / 20 instance Windows cluster? I want to know which sql server instance is consuming more CPU on a 4 node active -active - active - active windows cluster. ?

situation: Windows cluster - 4 Node
all active, each node has 5 sql server instances, i want to check the CPU utilization of all the active instances, any help ?

Glenn Berry has a set of DMV scripts, in each of them there is a CPU utilization for the last 256 minutes for the instance (query number 40 for SQL 2016 script). Run this on each instance. And if you have CMS setup correctly, you can run the script against a group of instances at the same time.
the link to Glenn Berry's page:
http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-september-2016/


SQL Server Full-text search for keywords without prefix matching I have the following text in my NVARCHAR column

  "Ich bin eine SuperbProdukt"

I have a full-text search enabled on the column and I can search for it by executing the following query that I wrote :

SELECT FROM MY_TABLE WHERE CONTAINS(Title, ' "Ich*" ')

The query gives me all the result that contains my search criteria, I can also search for other words within the whole text such as "Superb" and get the same record back. However, I am trying to have a search functionality that returns results even when I give keywords that are not the beginning of any words in a phrase, but rather it's in between. For instance, I am expecting to get the same record back when searching for "Produkt"

Is there a way to achieve this in SQL server without using LIKE operation? I heard that CharIndex can do the job, but I have no clue how that can be helpful in my scenario.

P.S: I am using SQL SERVER 2012 on Azure.

Yes, it's possible to achieve what you described using CHARINDEX.
According to this remark:

  If CHARINDEX does not find expressionToFind within expressionToSearch,
  CHARINDEX returns 0.

Knowing that you just have to filter the rows like this:

DECLARE @expressionToFind varchar(50)  = 'an';

SELECT MiddleName
FROM AdventureWorks.Person.Person
WHERE CHARINDEX(@expressionToFind, MiddleName, 1) <> 0;

Here's the result:

As you can see, it brings not only the middle names that begin with an but also the ones with an in the middle of the text.

A little remark regarding this method is that differently from using the Full-Text Search it will scan your table. It might (and almost certainly will) have a big performance impact.


What is disadvantage of disabling lock escalation in SQL Server 2016 Using SQL Sever 2016 at AWS on I3 32 cpu 240GB 2012R2 server.

We have import process that inserts as select 100,000s or rows into table A.  Users when they log in need to update table A.

Import process is is causing lock escalation since it uses more than 5000 locks.  This blocks users from logging in.

We can alter the table to disable lock escalation.

However, what is the cost of doing so.

Is it just that we consume more memory for locks ( we have enough at 200GB) and that is it?  Or are the any other problems.

As Erik mentioned and most experts including Microsoft books online online suggest reducing batch size/change isolation level/rewrite query but not disabling lock escalation.

Ref:

How to resolve blocking problems that are caused by lock escalation
in SQL Server
Lock Escalation (Database Engine)

Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server.

Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail and the following error occurs:

  Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a
  LOCK resource at this time. Rerun your statement when there are fewer
  active users or ask the system administrator to check the SQL Server
  lock and memory configuration.

Escalation Threshold for an Instance of the Database Engine

Whenever the number of locks is greater than the memory threshold for lock escalation, the Database Engine triggers lock escalation. The memory threshold depends on the setting of the locks configuration option:

If the locks option is set to its default setting of 0, then the lock
escalation threshold is reached when the memory used by lock objects
is 24 percent of the memory used by the Database Engine, excluding
AWE memory. The data structure used to represent a lock is
approximately 100 bytes long. This threshold is dynamic because the
Database Engine dynamically acquires and frees memory to adjust for
varying workloads.
If the locks option is a value other than 0, then the lock escalation
threshold is 40 percent (or less if there is a memory pressure) of
the value of the locks option.

Similar question was asked before, answer was not accepted but has some useful information.

drawbacks of disabling lock escalation


Migrating differential backup restoration Good afternoon,

A little back story about what I'm trying to do: I'm migrating a database environment from one SQL server to another. A simple full backup/restore works fine, but there's a possibility that the old environment will still have to be active while I move data to the new environment. Mirroring and log shipping are not options, but there is nothing that is needed to be updated in the database itself for it to work, so I thought I'd run a differential on one and restore it to the migrated database location.

I tried to do this already and found out that I'm already out of the recovery phase after my first full database restoration. I guess my real question is can I do a full backup on my current location database, and a differential on my previous location database and have it work the same as if I restored a full and dif backup from the same location database?

Also, if there are log backups between the times I did a diff backup of the original database, do I need to include them as well?

Please let me know if you need clarification.

Welcome to the forum.  You do not need to do log restores between a FULL and a DIFF backup in SQL Server.  If you do you'll break the LSN (Log Sequence Number) used to mark the delta's between the restores.  A differential backup is all the transactions from the last full backup.  SQL Server notes the last full backup in a data page when it backs up so it knows what data pages have changed.  At that point any DIFF backup from the last FULL backup will work.

Note the newer versions of SQL Server have a 'COPY ONLY' option which will NOT affect your LSN or modified pages since last FULL backup.  So basically don't use COPY ONLY.

Finally you'll want to do a restore with NORECOVERY.  I know this sounds counter intuitive at first but basically you're telling the engine do not play back the log files.

--This restores the full DB but does not replay the open transactions at the time, leaving it consistent for a DIFF or transaction log restore
RESTORE DATABASE DATABASE FROM '<pathofDBBackup>' WITH NORECOVERY;

--This is your DIFF restore and at the end you can recover.
RESTORE DATABASE DATABASE FROM '<pathofDIFFDBBackup>' WITH RECOVERY;

In case you needed more info:

Understanding How Restore and Recovery of Backups Work in SQL Server

The excellent backup internals videos by Paul Randall.

Backup Internals Demo


Can a partitioned table span on multiple files across different file groups? In SQL Server 2008 and above, if a table is partitioned, can it span across multiple files across multiple filegroups?

Thanks

Yes, it can. (BOL reference on Partitioned Tables and Indexes)

  SQL Server supports table and index partitioning. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database.


how to open a corrupted progress v9.1e database How can I open a corrupted PostgreSQL v9.1e database which has data that was added after it was last backed up?

Assuming your tag is right and this is a Progress, not a Postgres (as in the text), database you can try forcing your way in. However, backup beforehand! A forced database will be marked as forced and might never work again.

That aside, force might be a lifeline and the way to enter the database to dump it's content.

Here's a longer article about the force flag (-F).


MYSQL cluster issue: shutdown data node correctly breaks replication I faced issue on mysql cluster (5.7.28), i shuted down properly vm6 (data nodes) and the mysql replication was broken, i'm trying to make the link bettwen the datanode down and the replication broken but i still can't find the reason (below the relevant), is there someone who can help me to find the link
Slave: Got error 4009 'Cluster Failure' from NDB Error_code: 1296 [Warning] Slave: Can't lock file (errno: 157 - Could not connect to storage
engine) Error_code: 1015
Error running query, slave SQL thread aborted. Fix the problem, and
restart the slave SQL thread with "SLAVE START".

In MySQL Cluster the MySQL server that handles the replication slave
will write into the NDB data nodes. Since the NDB Data nodes are
down it isn't possible to write into them and you get the above
mentioned errors. Replication will start up again when the data
nodes are up again and the slave SQL thread is restarted.


Exclude application from postgres logs Currently on one of our dev servers we log everything what happend in database, but lots of traffic in is generated by patroni cluster and is not needed in further investigation. Is possibile to exclude application from postgres logs?

Something like "log from every application except 'patroni' and 'postgres'".

application_name="all -postgres -patroni"

Yes, i know, i can filter it later, but will be nice if this can be done before write anything to disk.

It is easy to do this by user, but not by application name.  Does/can patroni log in as it's own user?

alter user patroni set log_statement to none


Clarification about master slave configuration in mysql We are planning to migrate an existing MySQL database to a new server using the MySQL replication master slave procedure.

Unfortunately this will take a lot of time so we were thinking of doing a dump of the MySQL central db, restoring the dump on the new mysql db, and then setting up the master slave procedure.

Will using the master/slave procedure after restoring the dump of the original database overwrite the data on the new server, thereby wasting our time with the dump/restore process?  Or will the master/slave procedure replicate the data that was added to the old server after the dump took place?

I am using MySQL 5.1 on Debian squeeze.

Yes, it will overwrite the data on the Slave. However, you can setup replication to pick up from the point-in-time of the dump and make it roll all changes since the dump was loaded on the Slave

For this Example, let's assume

IP of the Master is 10.1.1.20
IP of the Slave is 10.1.1.30

Here is what you do

STEP01 : Activate Binary Logging on the Old Server

Step01-a) Add this to /etc/my.cnf on the Master

[mysqld]
server-id=101120
log-bin=mysql-bin

Step01-b) # service mysql restart

After STEP01, you should see mysql-bin.000001 and mysql-bin.index in /var/lib/mysql

STEP02 : Perform mysqldump

On the Master, you can mysqldump the data and record from what point in time it happened.

# service mysql restart --skip-networking --skip-grants
# mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers > MySQLData.sql
# service mysql restart

What this does is record the Master Log File and Position the moment the mysqldump started as a comment. You can visibly see it when you view line 22:

# head -22 MySQLData.sql | tail -1

STEP03 : Load the mysqldump into the Slave

Execute the mysql client loading the mysqldump into the Slave's mysql instance

# mysql -h10.1.1.30 -uroot -p < MySQLData.sql

STEP04 : Create MySQL Replication User on the Master

# mysql -uroot -p -e"GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'10.64.51.%' IDENTIFIED BY 'replpassword'"

STEP05 : Setup the Slave with a Separate Server ID

Add this to /etc/my.cnf on the Slave

[mysqld]
server-id=101130

and # service mysql restart

STEP06 : Setup the Replication on the Slave

Goto the mysql client and run the following command

mysql> CHANGE MASTER TO
    MASTER_HOST='10.1.1.20',
    MASTER_PORT=3306,
    MASTER_USER='replicator',
    MASTER_PASSWORD='replpassword',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=1;

STEP07 : Setup the point-in-time Master Log and Position

Back in STEP02, I mentioned viewing the point-in-time position using

# head -22 MySQLData.sql | tail -1

You should see something like this:

#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;

Run it as command in the mysql client on the Slave

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;

Afterwards, run this command

mysql> SHOW SLAVE STATUS\G

You should see something like this:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.113.232
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 122957100
               Relay_Log_File: relay-bin.003666
                Relay_Log_Pos: 122957100
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

STEP08 : Launch Replication

Start up replication with this:

mysql> START SLAVE;

Afterwards, run this command again

mysql> SHOW SLAVE STATUS\G

If you see this

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

CONGRATULATIONS, MySQL Replication is Working !!!


Why is changing data types not effecting the database size? I have a table with four columns and ~660 million rows. Two of the columns are bigint although they are not really required to hold any content that could not also fit into int. So as a test I changed their datatype from bigint to int. There is a clustered index on that table which uses neither of those two columns for its key.
The result is puzzeling to me. The data space (in the table properties) stayed exactly the same while the index space dropped 23.5%. Can someone explain to me what happened there? Why did the data space not change?
As an aside, I did something similar to another table. There the data space dropped 30,4% which is exactly what I calculated from the change in row size. The drop in index space was similar.
[edit note: I originally overlooked restoring one nonclustered index. This has been fixed and the question changed accordingly]

How are you measuring the size of your tables and indexes?
The way SQL Server works is once the database is allocated disk space, it still retains that disk space and only marks it empty / for re-use internally when data is reduced / removed until a SHRINK or similar operation occurs that forces the release of that acquired space back to the disk.
So changing data types of a fixed-width to a type that uses less space doesn't automatically trigger the release of that previously claimed space back to the disk unless you rebuild your indexes, run a SHRINK, etc.
As Martin points out in the comments, if you are using compression, then likely the used space of the BIGINT column that only held INT values was already being compressed to an equivalent space use of an INT column, which would also explain why you wouldn't see it change in size by changing data types, even if you did rebuild your indexes or ran a SHRINK operation.


PGPool Frequent Queries We recently setup a PostgreSQL Cluster with PGPool in front of it. This is our first go at such a thing. We setup our QA environment to use the new PGPool, and we cranked up the logging on the Postgresql servers so we could point pgBadger at it and see how things are going.

After minimal use, and not quite 24 hours of logging, pgBadger showed me this report this morning:

That seems absurd to me. The first three top queries don't even appear to have anything to do with our application nor application user, yet they all accuse the application of instigating them.

We assume that PGPool is doing this in the background when the application instigates it, but even then ... there is no way our QA application instigated 369K times. Admittedly, the time spent on these are negligible, all of 24 seconds over the course of a day.

However, we would like to grasp:

Why are we seeing this amount of traffic from pgPool?
Can we expect this to scale? Currently we have a single QA DB here with very light usage. What do we expect when we get 200 production databases running in this cluster?
Is there an easy way to prevent posgtresql from logging these types of queries from pgpool in order for us to more easily parse queries that are truly instigated by our application?
Is there another question we should ask that we haven't?

I don't think these are issued by PGPool.  I think they are instigated by your application (which I am guessing is written in PHP and running over some sort of ORM).

The first entry is DEALLOCATE which means effectively to tell the server to free up memory from a prepared statement.  These look like they are issued through some PDO module.

The second and third queries look like they are ORM-related mapping queries.  Your application is probably unware that these queries are being issued by the framework it is running on, but these queries only make sense really in an ORM or similar environment.


search path is getting updated automatically In postgres 11.5 for a running session suddenly lot of errors reported in pg_logs "relation does not exist". After enabling extra logging we have found out that just before the errors the following statement is getting executed and feels same is the culprit.
SELECT pg_catalog.set_config('search_path', '', false)

We are not running it. Could anyone please help me to understand by whom and under what circumstances it is getting executed.

Let me pull out my crystal ball and see what you did...
Ah! I see you restoring a plain format pg_dump from an interactive session using \i dumpfile, so that the settings from the dump remain active in your session after the restore is completed.


Optimize where clause using dates I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses:

AND (DATEADD(DAY
            , ISNULL(a.[due_days], 30) + 30
            , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL))
            ) < GETDATE()

The CalcDate udf based on the type field value makes some comparisons and returns a date. Then adds an amount of days to that date and compares to current date. In order to be able to use an existing index on due_days I want to transform the operation to apply all the transformations to GETDATE(), let's say I want to make it sargable, if possible. Also, if there is some recommendation on what can be done to improve the use of the udf even better.

It's not so hard to do this transformation. Step by step:

DATEADD(DAY
       , ISNULL(a.[due_days], 30) + 30
       , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
       ) < GETDATE()

means:

[dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
  + (ISNULL(a.[due_days], 30) + 30) DAYS
< GETDATE()

then we have to break the ISNULL() into 2 cases:

    [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
      + (a.[due_days] + 30) DAYS
    < GETDATE()
OR
    a.[due_days] IS NULL
  AND
    [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
      + (30 + 30) DAYS
    < GETDATE()

which can be written as:

    (a.[due_days] + 30) DAYS
    < GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
OR
    a.[due_days] IS NULL
  AND
    (30 + 30) DAYS
    < GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)

so we can use DATEDIFF():

    (a.[due_days] + 30)
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              )
OR
    a.[due_days] IS NULL
  AND
    (30 + 30)
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              )

and finally:

    a.[due_days]
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
OR
    a.[due_days] IS NULL
  AND
    30
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30

Corrected, taking care of the time parts:

    a.[due_days]
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
      - CASE WHEN DATEADD( day
                         , DATEDIFF( day
                                   , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                                   , GETDATE()
                                   )
                         , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                         ) > GETDATE()
             THEN 1 ELSE 0
        END
OR
    a.[due_days] IS NULL
  AND
    30
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
      - CASE WHEN DATEADD( day
                         , DATEDIFF( day
                                   , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                                   , GETDATE()
                                   )
                         , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                         ) > GETDATE()
             THEN 1 ELSE 0
        END

You could simplify it a bit, with the use of CROSS APPLY:

CROSS APPLY
    ( SELECT gdt = GETDATE(),
             calc = [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
    ) AS c
CROSS APPLY
    ( SELECT diff = x.diff - CASE WHEN DATEADD( day, x.diff, c.calc ) > c.gdt
                                 THEN 1 ELSE 0
                             END
      FROM
          ( SELECT diff = DATEDIFF( day, c.calc, c.gdt) - 30
          ) AS x
    ) AS y
----
    WHERE (  a.[due_days] < y.diff
         OR  a.[due_days] IS NULL  AND  30 < y.diff
          )


Handling or preventing conflicts in a multi-user system I have a web application that is accessed by multiple users from different locations worldwide. Let's say I have an "edit" form for a specific document and two (or more) users are editing the document at the same time. I know that both of the users can edit the document and submit it, but user1 will not see that user2 made changes to the document and might submit an older version of it.

I was thinking about adding some kind of a lock, but then, if user1 only opened the document for edit, but never changed anything (and kept it open) user2 will never be able to edit it. Therefore, I was thinking to add a Timeout for the editor, but then user1 might time out before he finished doing his changes to the document (let's say, went out for launch).

The question is, how would one prevent the document from being edited from one user while the other changes it?

What you are looking for is called Optimistic Concurrency Control.

Add an int column to your table called version. Start at 0.

create table mytable (
  id bigserial primary key,
  whatever text,
  version int not null default 0
);

Add a row:

insert into mytable (whatever) values ('something');

Read a row:

select id, whatever, version from mytable where id=1

Results:

1, 'something', 0

Update a row:

update mytable set whatever = 'something new', version = version + 1 where id = 1 and version = 0

If that row was not found, throw an exception. If you have a REST API on top of your database, you should use HTTP 409 Conflict.

If you use an ORM like Hibernate, you can just add a field with a @Version annotation and it will handle this for you.


How to create user for existing I have a database which get restored everyday through a agent job, but after restoration the user gets killed so i want to create a step so that after restoration user should be created with readonly permission.

CREATE USER [username] FOR LOGIN [loginname]
 ALTER ROLE [db_datareader] ADD MEMBER []

Is this ok ?

Is this ok ?

The db_datareader role gives implicit access to SELECT against all tables and views in a database.

If that's ok, then it's ok

You might be interested in the information in this post about db_datareader

You can conditionally check to see if the user already exists

use myDB
GO

if not exists(select * from sys.database_principals where name = 'foo')
-- create your database user

When restoring or attaching databases where the user already exists, you might have to 'remap' any Sql logins to the restored database.  This is what I use as part of normal 'refresh' scripts

use <YourDatabase>
go
set nocount on
declare @cmd varchar(max)=''
select
    @Cmd=@cmd + 'ALTER USER ' + dp.name + ' WITH LOGIN = ' + dp.name + ';' + char(10)
from
    sys.database_principals dp left join
    master.sys.server_principals sp on
        sp.name=dp.name
where
    dp.type='s' and
    dp.default_schema_name<>dp.name and
    sp.name is not null
exec (@cmd)


Combination Identifier I want to identify in one column those who are present in the data but there is limit to only 10 nests. Is there easier or some other way for me to get the result that I want?

I hope you could help me.

You can do that using a CASE expression:

select a,b,c,d,
       case (a + b * 2 + c * 4 + d * 8)
          when 0 then 'none'
          when 1 then 'a'
          when 2 then 'b'
          when 4 then 'c'
          when 8 then 'd'
          when 3 then 'a-b'
          when 7 then 'a-b-c'
          when 5 then 'a-c'
          when 9 then 'a-d'
          when 6 then 'b-c'
          when 10 then 'b-d'
          when 14 then 'b-c-d'
          when 12 then 'c-d'
          when 15 then 'a-b-c-d'
          else 'error'
        end as present
from the_table;

This creates a "bitset" with one bit for each column, then tests for the values that uniquely identify each combination.

This assumes that there are no NULL values in those columns.

Online example on db<>fiddle

Another option might be to concat expressions:

select concat(case when a = 1 then 'a-' end,
              case when b = 1 then 'b-' end,
              case when c = 1 then 'c-' end,
              case when d = 1 then 'd-' end)
from the_table;

That leaves an ugly dangling - at the end, but you could probably remove that with a combination of substring() and/or replace() calls.


SQL Server 2014 compile Stored Procedure response I have several SQL Servers and databases and when I compile a Stored Procedure I receive one of two responses...

ALTER_PROCEDURE - dbo.usp_MyProcedure

or

Command(s) completed successfully.

My question is why did I receive different responses. The setting has to be at the server level somewhere, because every database on that server where I compile the SP I get the same message.  I received the alter message on 17 databases on 3 servers, and the command completed message on 19 databases on 7 servers.

I use Redgate's Multi Script to push the stored procedure at the same time, I know it is not in Multi Script due to compiling the SP using SSMS yields the sames responses.
Can anybody point me to the setting that controls this response?

It looks like a DDL trigger exists in some, but not all, of the databases. The trigger apparently uses a PRINT or RAISERROR statement to generate a message containing the DDL action and object name.


In star schemas, what should I do when a dimension has a date attribute? I'm trying to learn about dimensional models and star schemas.  Say I have a Sales fact table recording the total sales at retail stores, with, say, four dimensions, Date, Customers, Stores, and Promotions (as in sales promotions like coupons).  (Pseudo-schema below).

I really like the idea of prepopulating the Date dimension with attributes like day of the week, so that you can easily filter the facts by seemingly complex conditions like "saturdays in the second quarter for the past 10 years".

Now, say I want the Promotion dimension to have Start_Date and End_Date attributes which denote the start and end of the promotion.  How do I do this?  I thought about it and can come up with three less than ideal solutions:

1) Make Start_Date and End_Date regular, atomic attributes (either ISO8601 strings or the DB's datetime object) and only allow limitted filtering.

2) Make Start_Date and End_Date foreign keys to the Date table, breaking the star schema, but allowing the same filtering capabilities as the full Date dimension.

3) Include attributes like Start_Date_Day_of_Week, Start_Date_Quarter, maintaining the star schema but increasing the dimension size and essentially duplicating the structure of the Date dimension in the Promotions dimension.

Sales Table
-----------
Date key      (FK to Dates table)
Promotion key (FK to Promotions)
Customer key  (FK to Customers table)
Store key     (FK to Stores)
Sales Amount

Date Table
----------
Date Key (PK)
Month
Day of week
Day of month
Day of Quarter
Day of year
Holiday?
Quarter
Day since Epoch
Month since Epoch
etc...

Promotion Table
---------------
Promotion Key (PK)
Type (Coupon code, 2-for-1 sale, 50% off, etc)
Start_Date
End_Date

Customer Table
--------------
Customer Key (PK)
Name
Age
Sex
etc...

Stores Table
------------
Store Key (PK)
Address
City State
Zip
etc...

Old question & I know the original posters are inactive, but since it came up in a Google search, I thought I'd add my thoughts.

  1) Make Start_Date and End_Date regular, atomic attributes (either
  ISO8601 strings or the DB's datetime object) and only allow limitted
  filtering.

Putting a date attribute into an existing dimension makes sense if the main goal is informational (i.e. people want to include the start/end date of the promotion in reports). However, if the intention is to filter, then filtering on an atomic date attribute will be relatively painful.

  2) Make Start_Date and End_Date foreign keys to the Date table,
  breaking the star schema, but allowing the same filtering capabilities
  as the full Date dimension.

This type of snowflaking is called an Outrigger Dimension and can be acceptable in a star schema. (http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/outrigger-dimension/). I imagine this is what you did.

  3) Include attributes like Start_Date_Day_of_Week, Start_Date_Quarter,
  maintaining the star schema but increasing the dimension size and
  essentially duplicating the structure of the Date dimension in the
  Promotions dimension.

The duplication would get messy (maintaining multiple date fields, especially since you have multiple dates in the same dimension). It also makes future maintenance more complex: if you want to alter your date dimension, you have to repeat that alteration in dozens of places (or have an inconsistent user experience).

On the flip side, this might be a valid option if, for example, there are just one or two ways that you know people want to filter promotions on. For example, if you know end-users want to quickly filter to active promotions or "promotions closed in the last 30 days", you could calculate and add that as a specific attribute in the promotion dimension, rather than linking to a full date dimension for promotion start & end date. (Though what starts out as one or two attributes can expand quickly, as end-users find more and more time periods they want to filter to.)

There is a 4th and 5th option, depending on the specifics of what you intend to filter by promotion start/end dates:

4) Add the promotion start/end date fields as keys in the Sales table.

You'll be storing the same piece of information each time a promotion is used, so at first glance, it feels like a lot of duplication. But denormalization is the name of the game in a star schema. If your goal is to filter to sales based on the promotion start or end date (i.e. sales where the promotion start date was last month), I would recommend this over an outrigger dimension.

5) Create a new, factless fact table, for promotions

If your goal isn't to filter sales at all, then a factless fact table might be a better solution. This could tell you how many promotions were in effect on a given date (regardless of whether there were associated sales), for example. More information: http://www.kimballgroup.com/2011/04/design-tip-133-factless-fact-tables-for-simplification/

My experience with dimensional modeling is that the best solution often depends on what the end-user is trying to accomplish. If you (or they) are not yet sure what they're trying to accomplish (i.e. you know promotions have start & end dates and that's it), I'd start with option #1. This puts the information in your model without too much work.

If it later transpires end-users are trying to filter Sales by start & end dates, that's when I'd consider #2 or #4. If it transpires end-users are trying to analyze promotions independently of sales, I'd look at #5.


Identify performance bottleneck in SQL Server, checked the usual suspects I'm trying to identify what users are describing as slow application performance. I have a hunch it may be related to the application itself and not an issue with the SQL server. I've went over many of the usual suspects to identify common bottlenecks.

Is there anything i interpreted incorrectly?
Is there anything i neglected to review?

The MS SQL server (11.0.5058) is running on Hyper-V, 2012r2 with the following assigned to the guest: 72GB ram, 16 processors, 4 disk controllers with tempDB, OS, DB, Program Files all on separate VHDs.

Ran perfmon over the course of a day

% Processor Time < 40% Max

Avg Disk Q Length 0.003 Avg - 0.41 Max

% Disk Time Avg 0.0038

Avg Avail MBytes 5010

Buffer Cache Hit Ratio 99.996 Avg 98.614 Min

I don't see any issues with disk, mem, or processor. Where should i look next?

I'm going to be brutally honest here: those are not the right metrics to look at, and wherever you're looking that gives you those metrics is wildly outdated. Those are not the usual suspects.

In the year 2018 (and indeed, for the last several years), the way you diagnose a slow SQL Server is by using wait stats. SQL Server is constantly tracking what queries are waiting on, and you can see it in the DMV sys.dm_os_wait_stats.

My favorite way to visualize that is with sp_BlitzFirst @SinceStartup = 1. (Disclaimer: it's my favorite because I wrote it, and it's free & open source.)

Try editing your answer to include a screenshot of your wait stats from sp_BlitzFirst, and we may be able to get you a lot closer.


What does "+date+" mean in this context? select LTRIM(RTRIM(convert(char(10), max(date), 101))) as str
from   inventory
where  date < '" + date + "'"

I happen to see this a lot in my Java code. Can someone please re-phrase the query above in plain English? Thank you.

SELECT LTRIM(RTRIM(CONVERT(char(10), MAX(date), 101))) as str
FROM   inventory
WHERE  date < '" + date + "'"

  Table: inventory

  Field: date (Your inventory table has a field named date)

MAX(date): Returns the highest date according WHERE conditions.

CONVERT(char(10), MAX(date), 101): Convert one data type into another applying some style. On this case 101 means U.S. format: mm/dd/yyyy. This is a MS SQL-Server specific syntax.

RTRIM(): Returns a character string after truncating all trailing spaces.

LTRIM(): Returns a character expression after it removes leading blanks.

Give me the highest date of inventory table, using mm/dd/yyyy format,
without leading and trailing spaces and considering only records
where this date is lower than an specific value.


DB Engine Stolen Server Memory is too high I get error from System Center Operations Manager (SCOM).

How to solve this error?

  SQL DB 2012 Engine Stolen Server Memory is too high.

I run query and get this counters:

╔═══════════════════════════════╦═════════════╗
║ Stolen Server Memory (MB)     ║ 7354.773437 ║
║ Lock Memory (MB)              ║ 106.195312  ║
║ Free Memory (MB)              ║ 64.632812   ║
║ Connection Memory (MB)        ║ 24.203125   ║
║ Log Pool Memory (MB)          ║ 14.085937   ║
║ Optimizer Memory (MB)         ║ 2.351562    ║
║ Granted Workspace Memory (MB) ║ 1.296875    ║
║ Cursor memory usage           ║ 0.000000    ║
║ Cursor memory usage           ║ 0.000000    ║
║ Cursor memory usage           ║ 0.000000    ║
╚═══════════════════════════════╩═════════════╝

How many times do you see such messages? If it is once or twice it can be ignored.

'Stolen memory' as described in this older support article and How to use the DBCC MEMORYSTATUS is:

  Stolen memory describes buffers that are in use for sorting or for hashing operations (query workspace memory), or for those buffers that are being used as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.

The memory is usually taken from Buffer Pool. If you run DBCC MEMORYSTATUS and the output shows you high Stolen Pages, this means that some process is stealing memory from buffer pool more that what is necessary and you need to find that process.

It's quite likely that there was some operation running which performed a huge sort operation and at same time SQL Server was heavily consuming memory thus leading to this message. Unless you are facing some OOM issue this message would be  more considered as a warning.


MySQL Syntax Error When Creating a Trigger I am trying to create a trigger but I am getting a syntax error and I am not really sure why I am.

CREATE TRIGGER Section_Insert AFTER INSERT ON Section
    -> FOR EACH ROW BEGIN
    -> INSERT INTO Audit(changeTime, tableName, Action) VALUES (NOW(), 'Section', 'INSERT');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

You need to use the Delimiter /// first if you are creating a trigger.

Delimiter ///
CREATE TRIGGER Section_Insert AFTER INSERT ON Section
FOR EACH ROW BEGIN
INSERT INTO Audit(changeTime, tableName, Action) VALUES (NOW(), 'Section', 'INSERT');
End;
///