Get a user's rank from an equation, and then store it in the table I'm working on a project for my database class, and I have a table called "accounts". It looks like
What I want to do is have a stored procedure where it calculates the new ranks for each account. The ranks will be based off a value (higher the better) that will be computed with an equation I am working on. How would I be able to get the rank, and store the rank in my table?
Ex: Account_id||Games_won||Games_lost||(calculated value) not in table||Account_rank
         1        200           157              1856                    1

EDIT: Here is my CREATE TABLE statement:
CREATE TABLE IF NOT EXISTS league_of_legends.accounts (
account_id INT NOT NULL AUTO_INCREMENT,
account_name VARCHAR(45) NOT NULL,
account_password VARCHAR(45) NOT NULL,
subscriber_first_name VARCHAR(45) NOT NULL,
subscriber_last_name VARCHAR(45) NOT NULL,
subscriber_email_address VARCHAR(45) NOT NULL,
subscriber_dob DATE NOT NULL,
server VARCHAR(45) NOT NULL,
account_balance INT NULL,
games_won INT NULL,
games_lost INT NULL,
account_rank INT NULL,
PRIMARY KEY (account_id, account_name, subscriber_email_address));
My INSERT sample statements:
INSERT INTO accounts (account_id, account_name, account_password, subscriber_first_name, subscriber_last_name, subscriber_email_address, subscriber_dob, server, account_balance, games_won, games_lost, account_rank)
VALUES (DEFAULT, 'Bwomp', 'asdfqwer', 'Nile', 'Williams', 'getrekt@gmail.com', '2003-10-09', 'NA', 1250, 100, 100, NULL),
(DEFAULT, 'Dirty Dan', '1234567890', 'Daniel', 'Brown', 'awovuca-1084@yopmail.com', '1990-07-16', 'EU', 0, 3, 20, NULL),
(DEFAULT, 'Alfy', 'doodcool', 'Alfonso', 'Riggleman', 'AlfonsoRRiggleman@armyspy.com', '1992-12-27', 'KR', 50, 400, 410, NULL),
(DEFAULT, 'Justaguy1', 'retrodonkey', 'Nick', 'Chetta', 'NicholasSMaine@jourrapide.com', '1996-06-28', 'NA', 3, 0, 5, NULL);
Sample equation:
(Games_won / total_games) * games_won = value

OK - this should be easy for the first part, if you're willing to be a bit adventurous :-).

MariaDB has had computed columns (otherwise known as virtual or generated columns) for quite a while now. Otherwise, the latest milestone release of MySQL (5.7.7) also has them.

If you want to do this with a current GA version, you'll have to use TRIGGERs.

However, if you want to update the RANK (see a lovely post here) on each update of games_won/games_lost, then you'll have to call a PROCEDURE which will update RANK. MySQL's Stored Procedure programming language isn't sophisticated enough for this on its own, but see here for some ideas. It says that there can be rollback issues, but since you're updating only one table, it should be easier than most - good luck :-). BTW, I'd use float/double for games_won/_lost - otherwise, you might have problems with integer division.

My $0.02; perform the calculation app side. It will scale better. Its possible to perform the calculation in an update or an insert on duplicate key update statement.


How to write a query to find all tables in a db that have a specific column name I've got a database with about 100 tables and I need to build a join query to get specific data from two of them.  I know one but not the other.  Basically I need something like:

select <tables> from <database> where exists table.column name;

How can I do this?

Using information_schema

This is the standards-compliant cross-RDBMS way to do it.

SELECT table_catalog, table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns
WHERE column_name = '<your column name>';

You can see this documented

PostgreSQL
SQL Server
MySQL 8.0

//Select the Particular table:

SYNTAX:
       SELECT COLUMN_NAME FROM TABLE_NAME WHERE COLUMN_NAME='VALUE';
EXAMPLE:
       SELECT PERSON_NAME FROM PERSON WHERE PERSON_ID=1;


Get distinct category columns with multiple subcategory columns mapped in table I have 2 tables, category and subcategory, this query

select C.Description,s.Description
from category C
inner join SubCategory S on C.CategoryID =S.CategoryID
where  C.IsActive=1 and S.IsActive=1

gives the following output:

But my output has to be as follows:

You can accomplish that using ROW_NUMBER().

Take a look at the example below:

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp ( Description VARCHAR(100), SubDescription VARCHAR(100));

INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds2' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds3' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds4' );

INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds2' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds3' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds4' );

WITH Temp
AS (
    SELECT
        Description,
        SubDescription,
        ROW_NUMBER() OVER ( PARTITION BY Description
ORDER BY Description ) rownumber
    FROM #Temp
)
SELECT
    CASE WHEN Temp.rownumber = 1 THEN Description ELSE '' END Description,
    SubDescription
FROM Temp;

But I'd prefer doing that in the application layer.


SQL Server 2019 performance worse than 2012... am I missing something? We have a SQL Server 2012 server which far outperforms a SQL Server 2019 database on (as far as I can see) the same infrastructure. We are hosting both databases on a cloud platform with the same SLAs. Both have 180GB RAM and 16 processors.

However there are a few key differences.

The 2012 database server is Enterprise, the 2019 is Standard. As far as I know, this shouldn't make a difference
The 2012 database was restored to the 2019 server and it's version changed to 150 (2019)
MAXDOP on the 2012 server was 0, 2019 server it is set to 8 as recommended by Microsoft and others
Cost threshold for parallelism = 5 on 2012 server, 20 on 2019 server

EDIT: Another major difference I just realised - one is Windows Server 2008,the other is Windows Server 2019. It could possibly be server side settings as well...

Allocation unit size is set to 64kb for all SQL disks, SQL has the right permissions to be able to control file sizes itself. Server is set to high performance mode. Anything else I should be changing server side?

Other database settings were not changed, so the following settings are default on 2019, I believe:

Legacy Cardinality Estimation = OFF
Parameter Sniffing = ON
Query Optimiser Fixes = OFF

Mainly the type of queries we do are large complex multi join queries performing updates and inserts, with the occasional small selects from users. We load large files to the database and then process the data in large queries, usually one at a time. In between these large "loads" we have users doing selects on other database tables not being loaded/processed in preparation for future load/process steps. Generally we are getting between 30%-50% performance reductions in processing.  I figured this was because of the MAXDOP setting, but altering it to 0 made no difference over a series of runs.

Our major symptom is we are getting lock timeouts when we try to connect to the 2019 server while it is busy processing, whereas the 2012 server still services connections, just very slowly. I was thinking of setting the connection timeout setting on the server to a high amount, however I suspect we still won't get responses from the server. It's like it's blocking all new connections if its even slightly busy.

Are there other things I should try? Are those database settings worth messing around with?

I could dive in further and start looking at DMVs, however this seems to be close to a "like for like" environment upgrade with considerable drops in performance. Just checking there isn't something else I should check before doing a bigger investigation.

I believe you have just discovered why the recommended upgrade process is to to upgrade your database, enable the Query Store, and test before increasing the database compatibility level.

Change the Database Compatibility Level and use the Query Store
If you have a lot of plan regressions you can keep using the older cardinality estimator at the higher database compatibility level with:
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;

Another possibility is enabling global trace flag 9481 and all the workload in your instance will use legacy CE


How can I profile SQL Azure? I am writing a web site that uses SQL Azure heavily. However, it is painfully slow.

Is there an easy way to profile the live SQL Azure instance?

I have used the MVC Mini Profiler as part of an application to profile SQL Azure but depending on your application it may or may not work. What kind of tasks are you doing that need profiling?

You can't run Profiler against SQL Azure.


Difference between a schema and a database in db2 I can not figure out what is the difference between schema and database in db2 I find this SQL so confusing for example :

CREATE TABLE schema1.table1
 ....
 ....
 ....
IN database1.tabspace1

Can we say that the database in this case contains the tabspace whereas the schema contains the tables?

This simple question has caused an unusual amount of debate, and I feel compelled to quote an authoritative source.

The database stores all data in tables that are related to one another. Relationships are established between tables such that data is shared and duplication is minimized.
A table space is a storage structure containing tables, indexes, large objects, and long data. They are used to organize data in a database into logical storage groupings that relate to where data is stored on a system.
A schema is a collection of named objects; it provides a way to group those objects logically. A schema is also a name qualifier; it provides a way to use the same natural name for several objects, and to prevent ambiguous references to those objects.

schema = namespace within database, identical to user account
database = single database


Installing SQL Server 2000 32-bit on Windows Server 2008 x64? Am I correct in assuming there won't be any issues installing SQL Server 2000 32-bit on a Windows Server 2008 x64 system?

I understand the memory limitations, etc, but that doesn't matter at this point.

I'm not sure why you're installing SQL Server 2000 anywhere in the year 2012, but no, there shouldn't be any technical issue other than the ones you've identified and the fact that it is nowhere near supported.

In any case, there is no 64-bit version of SQL Server 2000 - there is only 32-bit for x86 and 64-bit for IA. I assume your Windows Server 2008 box isn't Itanium.

Install MS SQL server 2000 via setup instead of AutoRun. Setup can bypass the compatibility check very easily.


Retrieve parent and child data using child id I have a parent and child table, I am able to retrieve all data using UNION SELECT.

Parent Table:

id | parent_name
 1 | Parent A
 2 | Parent B

Child Table:

id | parent_id| child_name
 1 |        1 | Child A
 2 |        2 | Child B
 3 |     NULL | Child C

Using this query (http://sqlfiddle.com/#!9/f9f1c6/1)

Select parent.child_id, parent.parent_id, parent.child_name from Child c
RIGHT JOIN (
  Select NULL as child_id, parent.id as parent_id, parent_name as child_name
  from parent
  Union
  Select child.id as child_id, NULL as parent_id, child_name
  from Child
) parent on parent.parent_id = c.parent_id;

I get this result:

child_id| parent_id|         name|
   NULL |        1 |    Parent A |
      1 |        1 |     Child A |
   NULL |        2 |    Parent B |
      2 |        2 |     Child B |
      3 |     NULL |     Child C |

This is where I'm kinda lost.
I want to retrieve a child that will still include the parent data.

Using WHERE ChildId = '1'; outside of join.

Expected result:

child_id| parent_id|        name |
   NULL |        1 |    Parent A |
      1 |        1 |     Child A |

I was able to figure out what I wanted to achieve.

I just added an OR on right join. (http://sqlfiddle.com/#!9/f9f1c6/12)

Select parent.child_id, parent.parent_id, parent.child_name from Child c
RIGHT JOIN (
  Select NULL as child_id, parent.id as parent_id, parent_name as child_name
  from parent
  Union
  Select child.id as child_id, NULL as parent_id, child_name
  from Child
) parent on parent.parent_id = c.parent_id OR parent.child_id = c.id
WHERE c.id = '2';

Debugged using source data from author's fiddle.

  UNION SELECT Result

    SELECT NULL Child_Id, Parent.Id Parent_Id, Parent_Name, NULL Child_Name
    FROM Parent
UNION ALL
    SELECT Id, Parent_Id, NULL, Child_Name
    FROM Child
;

  Using WHERE ChildId = '1';

SELECT NULL Child_Id, Parent.Id Parent_Id, Parent_Name, NULL Child_Name
    FROM Parent, Child
    WHERE Parent.Id = Child.Parent_Id
      AND Child.Id = 1
UNION ALL
    SELECT Id, Parent_Id, NULL, Child_Name
    FROM Child
    WHERE Id = 1
;

  Is there anway to have the Where ChildId =1; out side the union?

SELECT Child_Id, Parent_Id, Parent_Name, Child_Name
FROM (
    SELECT NULL Child_Id, Parent.Id Parent_Id, Parent.Parent_Name, NULL Child_Name, Child.Id Id
    FROM Parent
    RIGHT JOIN Child ON Parent.Id = Child.Parent_Id
UNION ALL
    SELECT Id, Parent_Id, NULL, Child_Name, Id
    FROM Child
    ) subquery
WHERE Id = 1
;

fiddle


Turn off listener logging in oracle 12c release 2 I am trying to turn off listener logging in oracle 12c R2 (SAP on oracle), but I am getting a TNS-12508 error. I have verified that the listener.ora is owned by the oracle user and I am performing the task as same oracle user, but I am still getting an error.

File Permissions

-rw-r--r--    1 oracle   oinstall       1151 Oct 23 13:48 listener.ora

Listener Error

LSNRCTL> set current_listener LISTENER_AAA.
Current Listener is LISTENER_AAA

LSNRCTL> set LOG_STATUS OFF
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=AAA.WORLD))
TNS-12508: TNS:listener could not resolve the COMMAND given

LSNRCTL> set log_status off
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=AAA.WORLD))
TNS-12508: TNS:listener could not resolve the COMMAND given

Am I missing something here?

This can happen if you've got ADMIN_RESTRICTIONS_LISTENER set to ON in your listener.ora file.

Turning it off should resolve the problem.


XAMPP - Error: MySQL shutdown unexpectedly Last night MySQL Service on my XAMPP local server was running fine. Today... it starts for 10 seconds and then give me following error:

10:14:28 AM  [main]     Initializing Modules
10:14:28 AM  [main]     The FileZilla module is disabled
10:14:28 AM  [main]     The Mercury module is disabled
10:14:28 AM  [main]     Starting Check-Timer
10:14:28 AM  [main]     Control Panel Ready
10:14:31 AM  [Apache]   Attempting to start Apache app...
10:14:31 AM  [Apache]   Status change detected: running
10:14:38 AM  [mysql]    Attempting to start MySQL app...
10:14:38 AM  [mysql]    Status change detected: running
10:14:49 AM  [mysql]    Status change detected: stopped
10:14:49 AM  [mysql]    Error: MySQL shutdown unexpectedly.
10:14:49 AM  [mysql]    This may be due to a blocked port, missing dependencies,
10:14:49 AM  [mysql]    improper privileges, a crash, or a shutdown by another method.
10:14:49 AM  [mysql]    Press the Logs button to view error logs and check
10:14:49 AM  [mysql]    the Windows Event Viewer for more clues
10:14:49 AM  [mysql]    If you need more help, copy and post this
10:14:49 AM  [mysql]    entire log window on the forums

The Log file follows:

2019-11-21 10:14:38 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2019-11-21 10:14:38 0 [Note] InnoDB: Uses event mutexes
2019-11-21 10:14:38 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-11-21 10:14:38 0 [Note] InnoDB: Number of pools: 1
2019-11-21 10:14:38 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-11-21 10:14:38 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2019-11-21 10:14:38 0 [Note] InnoDB: Completed initialization of buffer pool
2019-11-21 10:14:38 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-11-21 10:14:38 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-11-21 10:14:38 0 [Note] InnoDB: Setting file '\CoreApps\Mayfield\xampp-rtweek\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-11-21 10:14:38 0 [Note] InnoDB: File '\CoreApps\Mayfield\xampp-rtweek\mysql\data\ibtmp1' size is now 12 MB.
2019-11-21 10:14:38 0 [Note] InnoDB: Waiting for purge to start
2019-11-21 10:14:38 0 [Note] InnoDB: 10.3.15 started; log sequence number 1604056; transaction id 9
2019-11-21 10:14:38 0 [Note] InnoDB: Loading buffer pool(s) from E:\CoreApps\Mayfield\xampp-rtweek\mysql\data\ib_buffer_pool
2019-11-21 10:14:38 0 [Note] InnoDB: Buffer pool(s) load completed at 191121 10:14:38
2019-11-21 10:14:38 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-11-21 10:14:38 0 [Note] Server socket created on IP: '::'.

Apparently, there doesn't seem to be any error. I have an older version of XAMPP that is workin' fine, which means there is no Port 3306 issue.

I have tried couple of suggested solutions on other platforms, e.g. deleting ib_logfile0, ib_logfile1, and ibdata1 files and adding

innodb_force_recovery = 4

to configuration file, that didn't work for me.

What could be the issue here?

first suspect .. the usual - "windows happened" - Wednesday = patchday - there is potential risk that one of the updates "f**ked" things up - and in most cases a simple reboot should do it.

But if you tried with a second mysql server ... make sure that the port is free when you try to start this one the next time.


Trigger with a Variable Select and Order by Is there a way to a set trigger that will activate once a new table is created and will get the new tables name?
I was able to come up with the query below but it only gets the first query by alphabetical order, not by date created

CREATE TRIGGER CreateTable
ON DATABASE
    FOR CREATE_TABLE
AS
     DECLARE
       @table varchar(max);
     SELECT  top 1  @table =   [name] FROM sys.tables
     insert into NewTable values(@table)
     ;
     end

You don't look at sys.tables for that... even if you got the last one created, on a busy system, how would you know that someone else didn't create one more recently? With TOP comes ORDER BY so the following would likely work most of the time, but it is a completely bad idea:

SELECT TOP (1) @table = name FROM sys.tables ORDER BY create_date DESC;

No, instead, you need to extract the table name from EVENTDATA():

CREATE TRIGGER CreateTable
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    DECLARE
      @sql       nvarchar(MAX),
      @EventData xml = EVENTDATA();

    ;WITH x(s,o)
    AS
    (
      SELECT
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'nvarchar(128)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'nvarchar(128)')
    )
    INSERT dbo.NewTable(ColumnName)
      SELECT QUOTENAME(s) + N'.' + QUOTENAME(o)
      FROM x;
END
GO

WITH C
AS
(
SELECT
     [name], object_id, create_date, modify_date
FROM
     A.sys.tables ),
     D
     AS
         (
     SELECT * FROM A.INFORMATION_SCHEMA.TABLES
     )
     SELECT TABLE_SCHEMA,TABLE_NAME
     FROM C
     INNER JOIN D
     ON C.NAME =D.TABLE_NAME
     ORDER BY create_date DESC
    OFFSET 0 ROWS
     FETCH NEXT 1 ROWS ONLY

A = your database name


Why would shrinking TEMPDB log lead to slowness? I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?

I'd ask the person who told you that, to at least hear why they think it will decrease performance.

One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you have Auto Growth set. Depending on how you configured it, it will grow a set amount each time, or a percentage of it's size each time. Thus, each time your transaction log needs to grow, SQL Server has to do work, which you could notice regarding performance.

A primary reason is detailed in the first link below:

  When the Auto Growth mechanism of the Transaction Log kicks in, SQL
  Server always has to zero-initialize the new chunk that is added to
  the end of the file. It doesn’t matter if your SQL Server instance is
  running with the privilege Instant File Initialization, or not – the
  Transaction Log is always zero initialized.

What makes more sense is setting a dedicated drive for this file, and setting the LOG file to the size of the drive. Why shrink to 0 when you know it's only going to grow again, leaving you in the same spot with it’s size, cause fragmentation, etc?

Why Transaction Log Auto Growths are Degrading your Performance
Percent or 1MB Growth in Use, Slow File Growths
Microsoft's Considerations for AutoGrow

tempdb uses the simple recovery model and clears the log when ever a checkpoint is issued. This automatically happens when the log usages reaches 70% and in the case of tempdb, it doesn't force dirty tempdb pages to disk. See Paul Randall's post on this. VLF's are made inactive if possible during the checkpoint as well. When you shrink the log, the VLFs are removed.

Paul Randal 8 Steps to better Log Throughput (see 7-8)
Pinal Dave Reducing High VLF Counts

Not only for tempdb but also for any other db, shrinking to a size less than needed would cause slowness. You are not only making db engine more grow work, also increasing vlf amount. Look for VLF on google please.


SQL Server - failed to update records when RAM is fully occupied We are using SQL server 2008 in Windows Server 2008 R2 and Ram is 32 GB.
When we check in the Windows Task manager 25GB is always occupied even though no one is accessing the server.

Around 10 clients connected to the server. When we run a report from application,  (2 or more clients runs it simultaneously) the memory occupied fully ie 32 Gb and saving records from other application got failed.

I checked in RAMmap tool AWE occupies 25 GB.

How to handle this situation?  ie., how  to limit the usage of memory for SQL server  or adding records when  the report query takes long time to run?
Thanks

As suggested set the “Maximum Server Memory”.

https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

Additionally, please note that your error is more likely related to locking or timeouts rather than “running out memory”. An extensive exclusive lock on the target table combined with missing indexes for the update statement can result in what you are experiencing.

1)  Tune your report query and ensure that its not having redundant excessive reads. Verify that hash and sort operators in your execution plan are not spilling to disk.

2)  Make sure that your update statement has logical supporting indexes.

Tuning both process might enhance concurrency of your application.

Good luck :)

It seems you have not set the "Maximum Server Memory" at your instance level. In case you don't set them up and there are tables which are voluminous and connections which retrieves them or puts joins on heavy tables on periodic basis will cause an issue. Other issue here is - consumed memory won't be released until there is a request from Windows.

you should set them up based on various guidelines and also keeping in mind the load which is present on your database server. Also keep in mind that these values are in MB(Mega Bytes) and not in Giga bytes, do calculations in advance before setting that up. By default this value will be set as "2147483647".

Also note that this change doesn't require reboot of SQL service and hence can be changed on the fly. Please consider reading below links for more detailed understanding:

https://www.brentozar.com/blitz/max-memory/

https://www.mssqltips.com/sqlservertip/4182/setting-a-fixed-amount-of-memory-for-sql-server/

I hope above helps.


How can I get this code to work? It's in mysql, I keep getting an error 1822' failed to add foreign key constraint. Missing index for constraint' CREATE TABLE EMPLOYEE (
 employeeNumber VARCHAR (15) NOT NULL,
employeeName VARCHAR (20) NOT NULL,
department VARCHAR (10) NOT NULL,
 phone INT NOT NULL,
PRIMARY KEY (employeeName, department)

CREATE TABLE DEPARTMENTS(
department VARCHAR (10) NOT NULL,
 department Code VARCHAR(10) NOT NULL,
department Name VARCHAR(30) NOT NULL,
FOREIGN KEY (department)
REFERENCES EMPLOYEE (department)

CREATE TABLE EMAIL_INFO(
 employee Name VARCHAR (20) NOT NULL,
 email VARCHAR (30) NOT NULL,
FOREIGN KEY (employeeName)
 REFERENCES EMPLOYEE (employeeName)
);

From MySQL documentation:

  MySQL requires indexes on foreign keys and referenced keys so that
  foreign key checks can be fast and not require a table scan. In the
  referencing table, there must be an index where the foreign key
  columns are listed as the first columns in the same order. Such an
  index is created on the referencing table automatically if it does not
  exist. This index might be silently dropped later if you create
  another index that can be used to enforce the foreign key constraint.
  index_name, if given, is used as described previously.

You need to create corresponding indexes on EMPLOYEE table.

You have some problems in your code in mysql.

You can't use column manes with spaces,  at least not without backticks like you see in the table departments.

The referenced columns need an index, your table  employee has a primary key, but because it is a double column you can not use it.
 in emploee and department
your code also fell short in a parenthesis and semicolon

CREATE TABLE EMPLOYEE (
 employeeNumber VARCHAR (15) NOT NULL,
 employeeName VARCHAR (20) NOT NULL,
 department VARCHAR (10) NOT NULL,
 phone INT NOT NULL,
 INDEX (employeeName),
 INDEX (department),
PRIMARY KEY (employeeName, department)
);

CREATE TABLE DEPARTMENTS(
 department VARCHAR (10) NOT NULL,
 `department Code` VARCHAR(10) NOT NULL,
 `department Name` VARCHAR(30) NOT NULL,
 FOREIGN KEY (department)
   REFERENCES EMPLOYEE (department));

CREATE TABLE EMAIL_INFO(
 `employee Name` VARCHAR (20) NOT NULL,
  email VARCHAR (30) NOT NULL,
 FOREIGN KEY (`employee Name`)
   REFERENCES EMPLOYEE (employeeName)
);


Remote execution of SQL Agent job Working on setting an automated restore of dbs from Production to the test/dev environments using Net App SQL Snap Manager. The prod job handles the kick off of the restore, nothing being ran on the test/dev sql agent side. Not really familiar with the Net App process either, being handled by storage team. My question is, without using linked servers, is it possible to kick off a SQL agent job on the test/dev box remotely from the prod box? Would powershell give me the functionality I'm looking for? Or would linked servers be the better way to go?

Thanks

You should be able to achieve this using plain SQLCMD from the production server, but have it pointing to your other server

sqlcmd -S "servername" -Q "execute msd.dbo.sp_start_job @job_name='MyJobOnRemoteServer'"

you can use the remote machine to run sp_executesql command within the dynamic SQL.

exec server.master.dbo.sp_executesql 'BACKUP DATABASE DBNAME1 to disk='\\Server\Share\backupfilename.bak''

Or you can do this

exec server.master.dbo.sp_executesql 'BACKUP DATABASE [DBNAME1] to disk='\\Server\Share\backupfilename.bak''


Why aren't primary key / foreign key matches used for joins? As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to join (like natural join does with names). Why is that? If you've already defined a relationship between 2 tables with a pk/fk, why can't the database figure out that if I join those tables I want to join them on the pk/fk columns?

EDIT: to clarify this a bit:

suppose I have a table1 and a table2. table1 one has a foreign key on column a, which references to the primary key on table2, the column b. Now if I join these tables, I'll have to do something like this:

SELECT * FROM table1
JOIN table2 ON table1.a = table2.b

However, I already defined using my keys that table1.a references to table2.b, so it seems to me that it shouldn't be to hard to make a DBMS system automatically use table1.a and table2.b as the join columns, such that one can simply use:

SELECT * FROM table1
AUTO JOIN table2

However, many DBMS don't seem to implement something like this.

In many cases, there are more than one way to join two tables; See the other answers for lots of examples. Of course, one could say that it would be an error to use the 'automatic join' in those cases. Then only a handfull of simple cases where it can be used would be left.

However, there is a severe drawback! Queries that are correct today, might become an error tomorrow just by adding a second FK to the same table!

Let me say that again: by adding columns, queries that do not use those columns could turn from 'correct' into 'error'!

That is such a maintenance nightmare, that any sane style guide would prohibit to use this feature. Most already prohibit select * for the same reason!

All this would be acceptable, if performance would be enhanced. However, that's not the case.

Summarizing, this feature could be used in only a limited set of simple cases, does not increase performance, and most style guides would prohibit its usage anyway.

Therefor it is not supprising that most database vendors choose to spend their time on more important things.

why can't the database figure out that if I join those tables I want to join them on the pk/fk columns?

Parts of the reason are:

1 - in theory you can join tables on arbitrary columns from the two tables. While this is not common practice it is valid. Remember that SQL is like a programming language it does not understand what information is inside the columns of course and names, to SQL, don't mean much in this regards.

2 - There are different types of joins (left, righ, inner) - Inner Joins is only 1 of them.

3 - SQL standard may be guided by the principle of being a lower-level language that allows higher level dialects to form intelligence using it. The comparison is somewhat clearer if you think of a 4th generation language vs. a 3rd generation language. In fact, one tool I have used, IEF, allowed you to write something like this:

ReadEach Customer
Where Customer Places Orders and That Customer LivesIn "California"
and OrderValue > 100.00

In summary your suggestion is interesting and could be implemented either as part of the standard or as a stored procedure (it would default to an Inner Join).


Single query approach to split and copy string values in a column This is a part of my previous question.

I have the below logic to split a string value from a column in table tblProjects as below:

SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID
SET @Docs = @Docs + '$'
SET @pos = 0
SET @len = 0

WHILE CHARINDEX('$', @Docs, @pos + 1)>0
BEGIN
  SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos
  SET @value = SUBSTRING(@Docs, @pos, @len)

  IF CHARINDEX('|', @value) >0
  BEGIN
    SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),
            @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)
    INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)
  END

  SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1
END

A sample input would be

insert into tblProjects(ID, Documents)
values('P_1', 'gdgfg|jkkgk$bkgkkj|kjgkgk$vjffj|Khkgjhg$jkgkvghv|kbkghhkgk');

I need to split the Documents string and insert the values into a separate table. Values delimited by | should go into separate columns of the same row, whereas values for a particular row are delimited by $.

Is there a single query method to complete this operation, for all rows in the table, in the minimum possible time?

You should make use of a split string function.

select left(S.Item, charindex('|', S.Item) - 1),
       stuff(S.Item, 1, charindex('|', S.Item), '')
from dbo.tblProjects as P
  cross apply dbo.SplitString(P.Documents, '$') as S;

Aaron Bertrand blogged about some of the alternatives you have in Split strings the right way – or the next best way

The following example works perfectly fine with this single query -

Here is the data example

  Table = CityItem

ID     Name     City       Items
===    ===      =====      ======
1      Michael  Miami      item|item2|item3|item4|item5
2      Jorge    Hallandale item|item2|item3|item4|item5

SQL Query for the result given below -

SELECT ID, Name, City, s.value AS Item
FROM CityItem as mb
  CROSS APPLY dbo.ufn_N_Split('|', mb.Items) as s;

NOTE:- "ufn_N_Split" is a custom function that splits string values (like items) into a column named "value".

Output / Result -

ID     Name     City       Item
===    =====    =====      =======
1      Michael  Miami      item
1      Michael  Miami      item2
1      Michael  Miami      item3
1      Michael  Miami      item4
1      Michael  Miami      item5
2      Jorge    Hallandale item
2      Jorge    Hallandale item2
2      Jorge    Hallandale item3
2      Jorge    Hallandale item4
2      Jorge    Hallandale item5
...
...
...


SSMS Registered Servers with ReadOnly Application Intent We are working on SQL Server 2014 POC testing with AlwaysOn and one of the user asked about saving the configuration of SSMS with ReadOnly Intent using the registered servers in Local server group. This way they don't have to type in the alias name every time they need to access the ReadOnly replica.

Unfortunately there is no option within registered servers to add the ApplicationIntent Option unlike the regular object explorer.

I came across this article from Microsoft about changing the connection string in RegSrvr.xml.

https://connect.microsoft.com/SQLServer/feedback/details/786323/ssms-sql-server-management-studio-2012-missing-connection-properties-for-availability-groups

I tried their suggestion and it did not connect to the right replica node when connecting via the local server in the registered servers.

The ReadOnly option works fine from object explorer when using the options in the connect window > Additional Connection Parameters. But it does not save the changes made to the connection.

Does anyone know of any alternate solutions on saving the configurations with ReadOnly Intent property with SSMS? Thanks in advance for your help.

SQL Management Studio (versions prior to 2016)

Unfortunately, there are some caveats that make using Application Intent in SQL Management Studio somewhat painful:

To connect manually with ReadOnly intent, after bringing up the Connect
       to Server dialog from the Object Explorer, users must remember to:

Click Options >>.
Go to the Additional Connection Parameters tab.
Enter the additional parameter as ApplicationIntent=ReadOnly;
(Note: Users must not click the Options << button after entering
        the Additional Connection Parameters or the parameters will be
        lost.)
Click Connect.
Always launch query windows by right-clicking on the desired database in the Object Explorer view and choosing New Query to avoid running into the #3 caveat below.

The caveats that apply are as follows:

Although you can get SQL Management Studio to connect with Read Only Intent, it does not store the Additional Connection Parameters when a connection is added to Registered Servers.
Behavior when hand editing the locally registered servers in the RegSrvr.xml file to add the Application Intent is extremely inconsistent and will be overwritten any time a change is made through the GUI making this workaround unreliable.
The Always On database must be selected before the query window is opened; otherwise, the connection gets routed to the primary server. If you attempt to select the database using the query window's drop down after the query window has already been opened to a non-Always On database, you will get an error dialog. If you try to change the database to an Always On database with a USE statement after the query window has already been opened to a non-Always On database, the results look like this when you attempt to execute the SQL query:

      Msg 979, Level 14, State 1, Line 1
      The target database ('AlwaysOnDatabase') is in an availability group
      and currently does not allow read only connections. For more
      information about application intent, see SQL Server Books Online.

SQL Management Studio (versions 2016 or later)

SQL Server Management Studio 2016 or higher can connect with Read Only Application Intent (using the same 6 steps as prior versions) and it does store the Additional Connection Parameters. There are still some caveats:

The Object Explorer view will not enumerate any of the tables or
other objects in the AlwaysOn databases. Attempting to expand them
results in an access denied message.
You cannot have a Read Only and a non-Read Only connection to the
    same listener open at the same time.
Intellisense for object names in the database does not work. (Oddly
    enough, Objects get enumerated just fine in the Query Designer that
    you can launch using Design Query in Editor... from the
    right-click menu.)
Caveat 3 from the prior versions' caveats still applies.

Third-Party Products

LinqPad stores the whole connection string including Application Intent and the database when you save a connection and therefore might be a viable option for performing Read Only queries against Always On databases.

You may be missing an important part of the puzzle or at least I didn't see it anywhere in your question:

ApplicationIntent=readonly is one half of the equation. You also need to pass the parameter MultiSubnetFailover=True.

For instance, in the Additional Connection Parameters tab, you want to enter the following:

MultiSubnetFailover=True;ApplicationIntent=readonly;


Database log unavailable I am using SQL Server 2005 and I see the following message in event log (event id 9001)

"The log for database xxxx is not available."

After doing some research I found that it could be a corrupted database or possibly I/O related problem.
I tried running DBCC CHECKDB and it did not report any problem. At the end it shows following message:

"CHECKDB found 0 allocation errors and 0 consistency errors"

I just want to know if there could be any damage to the database or log and what other steps I have to perform to verify it?

Things to check:

Is your Log file size set to a limit?
Have you run out of disk space on the drive where the log file resides (seems unlikely)?
Is this a physical or virtual server?
What service pack are you running?
What, if any, are the preceding errors in the error log?

Try to detach and reattach the database. If you encounter any problems, check out SQL Server log files. They might contain useful info. Note that each of the instances has it's own error logs. The easiest way to access SQL Server logs is in SSMS:

In Object Explorer, expand a SQL Server instance
Expand Management
Expand SQL Server Logs
Right-click a log and click View SQL Server Log


SELECT all substrings (n-grams) of length n? How could I select all n-grams, ie. substrings of length n from a string using SQL? For example, the 3-grams of string example are exa, xam, amp, mpl, ple.

I'm using PostgreSQL to be more precise.

This does the trick:

SELECT SUBSTRING('example' FROM n FOR 3)
FROM GENERATE_SERIES(1, LENGTH( 'example' )-2, 1) n;
exa
xam
amp
mpl
ple

Here it is in a function:

CREATE OR REPLACE FUNCTION ngrams(varchar, integer) RETURNS SETOF TEXT AS $$
SELECT SUBSTRING($1 FROM n FOR $2)::TEXT
FROM GENERATE_SERIES(1, LENGTH($1)-($2-1), 1) n;
$$ LANGUAGE SQL;

Use it like:

SELECT ngrams('example', 3)

DECLARE @str varchar(MAX)
DECLARE @ngram int
DECLARE @i int

SET @i = 1
SET @ngram = 5
SET @str = 'Helloworldsed'

WHILE (@i <= (len(@str)-@ngram)+1)
BEGIN
SELECT SUBSTRING(@str,@i,@ngram)
SET @i = @i + 1
END


ALTER TABLE SWITCH statement failed ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables...

Does anyone knows how to fix that on already existing table full of data?

Well, I've found that it is simply possible to recreate same table with same ANSI_PADDING parameter, but the problem is that the table I'm trying to switch into a larger main table is already filled with data. I don't want to spend again 37 hours to reload that partition =(

I hate to say this, but there's a bug in Books Online. The Books Online page on partition switching says:

  Source and target tables must have the same column structure and
  order. The tables must have the same columns with the same names and
  the same data type, length, collation, precision, scale, nullability,
  and PRIMARY KEY constraints (if any), and also have the same settings
  for ANSI_NULLS and QUOTED IDENTIFIER.

But as you've discovered ANSI_PADDING is also involved - both the incoming & outgoing tables have to have the same setting. There is no way to fix this after the data is loaded.

create the create table script of source table , modify the create table script as to create a new target table then run the switch command again


Optimize rows to columns conversion I have a big Oracle table (around 40 million rows) that looks like this:

ID  Name    Question Answer Reason
3   Name1   1       Yes     blah blah
3   Name1   2       No      NA
3   Name1   3       No      NA
3   Name1   4       Yes     blah2
3   Name1   5       Yes     null
3   Name1   6       Yes     blah3
3   Name1   7       No      null
6   Name2   1       Yes     blah4444
6   Name2   2       No      null
6   Name2   3       Yes     blah3
6   Name2   4       NA      blah5
6   Name2   5       Yes     null
6   Name2   6       Yes     blah6
6   Name2   7       NA      null

I need one row per ID i.e. I will need to add columns for each question's answer (there are 7 questions per ID) and each question's reason. I need to make it look like this:

ID  Name    Q1     Q1-Reason    Q2    Q2-Reason   Q3    Q3-Reason    etc.
3   Name1   Yes    blah blah    No    null
6   Name2   Yes    blah4444     No    null

My query currently looks like this:

select
   A.ID,A.NAME,B1.Q1,B1.Q1-REASON,B2.Q2,B2.Q2-REASON
from
   TABLENAME A
inner join
    (
    select distinct C1.ID,C1.ANSWER as Q1,C1.REASON as Q1-REASON
    from TABLENAME C1
    where C1.QUESTION=1
    ) B1 on B1.ID=A.ID
inner join
    (
    select distinct C2.ID,C2.ANSWER as Q2,C2.REASON as Q2-REASON
    from TABLENAME C2
    where C2.QUESTION=2
    ) B2 on B2.ID=A.ID
...
...

However, as the table is huge, this is taking a VERY long time to retrieve the data. Could someone suggest ways to optimize this query? Any help would be appreciated!

I'm on Oracle 10g and SQLDeveloper 4.0.2.15

Instead of using so many JOINs to get the result, you might get better performance pivoting the data using an aggregate function with a CASE expression.  Oracle 10g doesn't have a PIVOT function so you'd have to use this type of query (aggregate/CASE) if you aren't going to use multiple JOINs on your table.

Since you have a limited number of questions (7), you can easily hard-code the query:

select
  id,
  name,
  max(case when question = 1 then Answer end) Q1_Answer,
  max(case when question = 1 then Reason end) Q1_Reason,
  max(case when question = 2 then Answer end) Q2_Answer,
  max(case when question = 2 then Reason end) Q2_Reason,
  max(case when question = 3 then Answer end) Q3_Answer,
  max(case when question = 3 then Reason end) Q3_Reason,
  max(case when question = 4 then Answer end) Q4_Answer,
  max(case when question = 4 then Reason end) Q4_Reason,
  max(case when question = 5 then Answer end) Q5_Answer,
  max(case when question = 5 then Reason end) Q5_Reason,
  max(case when question = 6 then Answer end) Q6_Answer,
  max(case when question = 6 then Reason end) Q6_Reason,
  max(case when question = 7 then Answer end) Q7_Answer,
  max(case when question = 7 then Reason end) Q7_Reason
from tablename
group by id, name;

See SQL Fiddle with Demo

This question is a hardy perennial on the Oracle lists. The PIVOT operator only came in in 11g, so that's no good to you. You might start by looking here (also gives non-PIVOT solutions - oracle-base is an excellent tips and snippets site).

The Oracle forums FAQ has a "How do I convert rows to columns" section here. An interesting application of windowing functions is covered here (Sys_connect_by_path). You can also use the MODEL clause - covered here. Here is also of interest - the Orafaq site is also an excellent site for all things Oracle - they have forums similar to this one, but focussed exclusively on Oracle - I've found them very good in the past.

As for performance, I suggest that you first asses which methods provide you with the correct answer and then go on to test them for performance. Just as an aside, this might also be of interest.


Increased server load after InnoDB deadlock I am using InnoDB instead of MyISAM for a couple of months right now, and it has really optimized my game database-wise. I was aware of the danger of deadlocks, but hadn't had any problems with it, until about a week ago.

I've had a deadlock because two cronjobs were clashing with each other, I believe. Anyway, we've got that fixed by killing both queries and running the cronjobs after each other manually.

All seemed good, except for the fact that the server load is peeking a lot since then (about every 5 minutes) which I don't really know why. I've checked the running queries at those moments and they seem fine.

Do you have any tips where to look on how to fix this? Couldn't find too many information about this on the internet. Thanks in advance.

may be you need to clear or reload various internal caches
use these commands
flush tables ;
flush logs ;
you can find more details about flush command in this link
http://dev.mysql.com/doc/refman/5.0/en/flush.html


SQL Service failed to start master.mdf is in use I've got this issue which has me baffled at the moment.

When I came in this morning there were 2 instances on a server with 4 SQL Server instances who had a backupthread process which got stuck when I killed this SQL Server process this stayed in the killed/rollback state. When trying to kill the process which was the culprit this showed to be a process which was non-existing. So after some googling and consulting with co workers we thought it best to restart the SQL Server instance.

So far so "good".

When stopping and then starting the SQL Server service again, the service refused to start. with the following error: The process cannot access a file because it used by another process.

And it was using the master.mdf file.

When checking which process was using the file it was the SQLservr.exe process which was using the file. (Using process explorer https://technet.microsoft.com/en-us/sysinternals/bb896653.aspx)

When I looked over to services, the service of the instance was successfully stopped. The Process however was still running according to task manager and process explorer although it was not using any resources.

When trying to kill this process ID it gave me an access denied error, when trying to kill it through command line with using taskkill /pid XXXX /F it still gives me access denied error, even when running as administrator with local admin and domain admin account.

The only shortterm solution was rebooting the Server, this solved the issue. Until I tried to restart the service again. When we got the same problem again.

EDIT: They rebooted again without my knowledge, now the issue cannot be recreated

SQL Server is patched until the latest version, however the Windows server has not been patched since 2013.

We are running on SQL Server 2012 and Windows server 2012

Is this a known issue with windows server? Is this a SQL Server issue? Any guidance or information would be helpful.

I have found the issue, it was something called a ghost process.

If you want then use the extra tool to kill the process SQL Server will start.

more information on http://amorvita.github.io/2013/08/finding-a-zombie-process/

Check the backup target location isn't on a windows compressed volume !  I've seen that causing backups to get stuck.


Is mysqldump a "hot" backup kind? I see in the documentation that the database backup tools are divided into four categories: hot backups, cold backups, physical backups and logical backups.

I understand the the most important difference between cold and hot backup is that the latter can be done while the database is operating and receiving read&write queries (and the result will be consistent and atomic).

But then, how do I know if mysqldump has this "hot" aspect or "cold" aspect? The documentation just places it in the logical backup category and is not 100% clear about this (yes, it seems to mention that it uses the same snapshot of the DB at the beginning of the mysqldump procedure, which suggests it's definitely a HOT backup tool, but just wanted to double check here).

The tables use InnoDB engine.

First of all, categorizing the backup into the four mentioned categories is not accurate. You can categorize into (hot, warm, cold), and into (physical, logical). In other words, the backup can be cold and physical, or, hot and physical, etc. It cannot be (physical and logical).

Notice please that physical backup is better called "Raw backup", which is a copy of the data files, and other related file, at filesystem level.

mysqldump is logical, i.e. it produces the statements that re-create the DB and/or the tables and/or the data, etc. Therefore it is not "physical"

"Hot Backup" means that the backup can happen without affecting the users, and without taking the application or DB server down, AND, the resulting backup is consistent. There are different strategies and tools to make hot backups. Sometimes, it is conditional to the storage engines you use.

To answer your question: mysqldump is not hot backup tool by default. This is especially true if you are using MyISAM among other engines, and/or tables are being locked during the backup.

But if your tables are pure InnoDB, and you use the --single-transaction option with mysqldump, then, yes, it would be considered hot.

HTH

Jehad Keriaki nailed it.
For InnoDB Engine Hot Backup, Percona is having a backup solution called "XtraBackup" which I believe is based on


ADD PERIOD FOR SYSTEM_TIME on table failed I have:

table with existing data
SQL Server 2016 SP1
SQL Server Management Studio 17.5

I am using the following statement to make a make my table a temporal one:

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT GETUTCDATE()
   ,[SysEndTime] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2(0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]);

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));

The issue:

On my local SQL instance I have many databases; it's very strange that the query runs successfully on some of them, and on some of them it gives me the following error:

  Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on
  table 'dbo.AnalysisCustomRollupsV2JoinGroups’ failed because there are
  open records with start of period set to a value in the future.

Sometimes, when I am debugging/executing the query, the initial query runs successfully.

I read, that this could be because I have existing data in the table. So, I have change the logic like this:

create buffer table and populated it with all records
delete the records from the original table
create the temporal table
move the records back and drop the buffer table

and again, on some databases it is OK, and on other is not. Trying to resolve the issue I have found the following:

  For the StartDate I’ve specified the current UTC date – this could be
  any date and time that’s not in the future, though note it should be a
  UTC one. If I’d tried to use GETDATE, as I’m currently on British
  Summer Time, I would get the following error: Msg 13542, Level 16,
  State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table
  ‘TestAudit.dbo.SomeData’ failed because there are open records with
  start of period set to a value in the future.

What does the above means? I need to change the machine time? Or because my local machine is not on UTC time I am getting this error sometimes?

I think I have found how to fix my issue, but I am not going to accept this as answer as I am not able to explain what is causing the problem and guarantee this will work anytime. It's fix found after a lot of testing and I will be glad if someone can bring more light here.

I have never used datetime2 with precision. So, I went back to the source of this format datetime2(0) - Alter Non-Temporal Table to be System-Versioned Temporal Table. The only difference with the script I have been using was the date time function. I used GETUTCDATE() as I do not need to be so precised with datetime(0)(2018-03-15 07:21:02 for example) and in the example it is  SYSUTCDATETIME(). So, I have changed it.

I have create a script which is dropping a database if exists, restoring a database from backup and then executing my code in a loop (as I said I get the error sometimes and it was very hard to reproduce it).

I have run a the script a lot of times and I was getting different number of fails (sometimes 70%, sometimes 50%, sometimes below):

I have found this Why is GETUTCDATE earlier than SYSDATETIMEOFFSET? discussion about differences between old and new date time functions. Then build the following query:

DECLARE @UTC DATETIME2(0) = GETUTCDATE();
DECLARE @SYSUTC DATETIME2(0) = SYSUTCDATETIME();

WHILE DATEPART(SECOND, @UTC) = DATEPART(SECOND, @SYSUTC)
BEGIN;
    SET @UTC  = GETUTCDATE();
    SET @SYSUTC  = SYSUTCDATETIME();
END;

SELECT @UTC AS [UTC]
      ,@SYSUTC AS [SYS UTC]
      ,DATEPART(SECOND, @UTC) AS [UTC sec]
      ,DATEPART(SECOND, @SYSUTC) AS [SYS UTC sec]
      ,CASE WHEN @UTC < @SYSUTC THEN 1 ELSE 0 END AS [TimeTravelPossible]
      ,CONVERT(DATETIME2(0), @UTC) AS [UTC date]
      ,CONVERT(DATETIME2(0), @SYSUTC) AS [SYS UTC date]
      ,IIF(CONVERT(DATETIME2(0), @UTC) = CONVERT(DATETIME2(0), @SYSUTC), 1, 0) AS [Are The Same];

I just wanted to check if I can get different datetime2(0) dates using sys and not sys date time function. And of course it is possible.

Maybe, the checks that the engine is making is doing something like this, comparing its current date with my newer date and this is causing the error - open records with start of period set to a value in the future.

I have change the script like this and executed 1 000 times last night - no errors were generated. So, I believe I have fixed this particular issue, but I can't be sure.

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT SYSUTCDATETIME()
   ,[SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]);

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));

This worked on Sql Server and Azure SQL (PaaS) environment.

`ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
     ADD SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN
         CONSTRAINT DF_SysStart DEFAULT DATEADD(second, -1, SYSUTCDATETIME()),
     SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN
         CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
     PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[AnalysisCustomRollupsV2JoinGroups_History]));

GO
`


Table doesn't exists issue with Oracle stored procedure I am new to Oracle and in process of converting SQL Server stored procedures into Oracle stored procedures.
I was able to transform the SQL statements and have tested them individually and they are working fine as expected.

However facing some challenges when trying to execute them in Oracle stored procedures.

For example I have a stored procedure TEST_PROC wherein I am creating a TEST_TABLE_2 using existing table TEST_TABLE_1

BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE TEST_TABLE_2 as
 SELECT
 COL1,
 COL2,
 COL3
 FROM TEST_TABLE_1';
END;

and then inserting data to some other table with the help of table TEST_TABLE_2

INSERT INTO TEST_TABLE_MAIN
(
COL1,
COL2,
COL3
)
WITH CTE_TEST_TABLE_DATE AS (
SELECT
a.COL1,
a.COL2,
a.COL3
FROM TEST_TABLE_2 a LEFT JOIN
 (SELECT GROUP_ID FROM TEST_REF_TABLE) b
 on a.COL1= b.GROUP_ID
)
SELECT * FOM CTE_TEST_TABLE_DATE;

As you can see that TEST_TABLE_2 is getting created on the fly in this procedure
and will be getting dropped in next subsequent procedure.

when I am running this TEST_PROC I am facing below issue which is probably due to that table doesn't exists.

exec TEST_PROC;

  PLS-00905 object SYSTEM.TEST_PROC is invalid

The table does not exist at compile time, so with static SQL, the procedure remains invalid.

Run the INSERT from dynamic SQL as well (execute immediate).

Or even better, do not create tables on the fly.

I think the procedure is getting dropped saying it couldnt find the procedure name in the Oracle dictionary.

Could you please elaborate on the procedure that you use or the full code.

Thanks


SQL Server 2000 Backup fails with Status = 33 I am trying to take a Full Backup of a database size about 75GB.

It is SQL Server 2000 - 8.00.2055 x86  version. I have plenty of disk space where the backup is being created. So disk space isn't an issue.

The backup process starts fine but half way through it errors out just saying with status = 33.

I have been looking for any information online but couldn't find anything....  Any suggestions any solutions any pointers in the direct direction is much appreciated.......

Further investigation and "Googling" solved the issue, The issue was with drive, It was compressed and SQL Server did not like that.

Solution

Removed the compression from Drive and this time backup completed without any errors and much faster.


Tips about MySQL installation I'm starting to prepare my server for a classic configuration Apache PhP MySQL.

I installed Apache 2.2.24 without problems. (Output "It works").
I installed php 5.2.1 - win32 - installer. (launching info.php I see I see all configuration details).

Now I have to install MySQL.

Question:

Which version do you recommend suitable for Apache 2.2.24 and PHP 5.2.1.
You could attach the link?

P.s.: only classic configuration (no wamp or others).

Load them all at once - WAMP.  Their current version has 64-bits (or 32-bits) with

Apache 2.4.9
MySQL 5.6.17
PHP 5.5.12
PHPMyAdmin 4.1.14
SqlBuddy 1.3.3
XDebug 2.2.5

That should be easier and better than installing end-of-life software.

I am running a similar configuration (but under Linux) and would recommend 5.5.45 available here:- http://dev.mysql.com/downloads/mysql/


Error in in sysft_Name while restoring database I have a .bak file which gives the following error while restoring:

  System.data.sqlclient.sqlerror:The path 'E:' has invalid attributes. It needs to be a directory. It must not be hidden, read-only, or on a removable drive.

But I verified that the directory is not read only. Anyone know how to restore the three files being restored ( .mdf,.ldf, and sysft)?

The script i had used for restoring database has been given below.

RESTORE DATABASE [new] FROM  DISK = N'E:\a\L11.bak' WITH  FILE = 1,  MOVE N'itest' TO N'E:\a\3.mdf',  MOVE N'itest_log' TO N'E:\a\4.ldf',  MOVE N'sysft_test' TO N'E:\a',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

I think you need a "\" at the end of the path.

RESTORE DATABASE [new] FROM DISK = N'E:\a\L11.bak' WITH FILE = 1, MOVE N'itest' TO N'E:\a\3.mdf', MOVE N'itest_log' TO N'E:\a\4.ldf', MOVE N'sysft_test' TO N'E:\a\', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO

Try changing the section:

MOVE N'sysft_test' TO N'E:\a' to MOVE N'sysft_test' TO N'E:\a\sysft_test.ndf'

Then rerun as see if the backup works.


Change Oracle user home directory at OS level after Oracle Databse/Client Software Installation Can we change oracle user's home directory at OS level after Oracle Database/Client Software-Only installation?

No database has been created yet.
We want to change the home directory to /Oracle/profile/oracle from /home/oracle.

As per your comment:

  Yes, I meant the home directory of oracle user at OS level!

You can easily achieve that.

We have a user called ora

[root@myserver /]# su - ora
[ora@myserver ~]$ pwd
/home/ora

And it has its home directory as /home/ora.
Let's create another home directory for the user ora.

[root@myserver ora]# mkdir -p /home/profile

Let's move the files such as .bash_profile and directories like Desktop and Documents, from the old home directory to new one.

[root@myserver ora]# cp -a /home/ora /home/profile/

Finally, modify the user to change the home directory. Make sure that there are no active sessions exist for the user.

[root@myserver ora]# usermod -d /home/profile/ora/ ora

Let's check the result.

[root@myserver ora]# su - ora
[ora@myserver ~]$ pwd
/home/profile/ora/

To change the Oracle Home variable in your current install you can use the command:

for Linux/Unix:

export ORACLE_HOME=/Oracle/profile/oracle

for Windows:

set ORACLE_HOME=c:\Oracle\profile\oracle

In Windows, remember you will have to edit registry keys as well:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services.


Multiple files per partition/filegroup? I am designing a partitioning based solution for a high performance large SQL Server 2016 database. Some of the data will be coming in with hundreds of millions of records per day. During the day we will be also running reporting queries and queries that looks for trends over multiple day and weeks.

My current solution will use 70 days in daily partitions using a dedicated filegroup per partition. After the data moves beyond the 70 day mark, it will go into a weekly partition for 42 weeks which are also using dedicated filegroups per partition, then 12 month and then 6 years, all set up in the same fashion.

We need really high performance and massive scale up capabilities (PB+ range). In order to minimize rework down the line, I am thinking about using multiple files per filegroup/partition for the daily and weekly filegroups/partitions. 4 per daily and 2 per weekly to be exact.

This way we could potentially increase read/load throughput per partition, as well as increase maximal capacity of the partitions (don't ask why, but we are worried to actually need that level of capacity on certain days).

Has anyone ever done it this way and what were your results? Is there any reason not to do it this way other than management overhead?

All the weekly, monthly, and yearly partitions would be in the same database on same server (application design issues, but multi-DB could be an option if properly motivated. Multiple servers or instances are not desirable).

The partition breaks are currently being discussed and evaluated. I chose the values above based on information I received on query patterns. Different number of days is of course possible but I kinda like the 10 weeks' worth of daily partitions.

We do have a seriously high-end datacenter, well 2 actually. We are discussing the purchase of a converged solution specific for this platform and one other. I personally would like to see dedicated AFA's (All Flash Arrays) but there are a some bridges to cross before I can get my hands on those.

I am aware of Data Warehouse Fast Track solutions, but they will not work for us. For one, we will be doing OLTP mostly so the benchmark figures will not be representative for what we will get. Second, they don't scale-up enough (at the moment). Some of the elements that come from the reference architectures, will be used of course but the 'turn-key' SKU's won't be an option. I am an ex-MS PFE so those resources are the first I look at.

You can't work on the partition <=> files relationship. Partitions are just affected to a Filegroup. However, splitting your Filegroup into different files will help with performance.
The numbers I always use is to have the number of partitions per Filegroup set to the MAXDOP value. I got this looking at how SQL Server split the tempdb into different files and I did notice some improvement copying this behavior.

Regarding partitions, what you should do is split them on filegroups so that the ones that are no longer updated never move. We typically have a Hot filegorup in our DWH, and a Cold one. The cold one is Read Only and we only change that status on the day we move data from Hot to cold. You could do this without having to create one filegroup per partition. That just makes it harder to manage.

This is an interesting question, and I have a similar one in myself. It is my belief that in certain situations, multiple files per file group can help with performance depending on your hardware configuration (number of cores) and physical I/O design (the files can be distributed across multiple drives)

Although of course you will be using SSDs in Raid 10 configuration for the production data drive(s), and have a ton of server memory, so that the majority of pages are being read from disk. With that hardware I'm not sure how much the physical design will matter.


How to use LIKE with subquery? In a SQLITE3 DB, I have a table A with a column 'word' containing search words that partially match a column in another table B.

I'd like to SELECT all rows FROM table B WHERE B.name LIKE %any_of_the_words_in_a.word%';

My first idea was to use a REGEX, but I can't get it to work with REGEX and a subquery.

I think this is what you are after, no need for Regex. It's just a correlated subquery, with the only difference being the LIKE condition instead of the common equality condition:

SELECT b.*
FROM tablename AS b
WHERE EXISTS
      ( SELECT *
        FROM words AS w
        WHERE b.name LIKE '%' || w.word || '%'
      ) ;

SELECT e.id, count(a.salary)
FROM (subquery here that returns "id"
     ) e left outer join
     TableA a
     on a.comment like '%'||e.id||'%'
group by e.id;


Should running number be separated column from primary key in database? I have questions that blogged my mind on separating member number and member id.

Suppose that we have a database with member id primary key of Guid type. Then, normally, when we view the profile (on web page), we will display a Profile/100001 instead of Profile/{Guid-id}. So, in this case, we would have another column for member number which is separated from member id.

Now, assume that we have a case where Member ID is int datatype, and the running number of Member No is incremented 1 each time start from 100001, should we separate the member ID (primary key) from member number in this case?

This question is focus on web application database design. Because I got to "know" that exposing primary key to user is not a good practice. So, does that means that we should have an internal ID(primary key) that is used for database and another column with running number that is display to user?

Your Primary Key is a unique key and should be sufficient to find the records that you need.  However, if you need a sequential number for other identifying reasons, then YES you should make it a separate unique value.

I personally would skip the GUID key and just use the Integer, but that is a choice you will need to make.

Getting a sequential set of keys without gaps will require some code.  Are you using Microsoft SQL Server or some other version.  If MS SQL Server, look at:

http://msdn.microsoft.com/en-us/library/ff878058.aspx

This is a discussion of using Sequence Numbers, with some warnings about the limitations.

Usage of primary key for database alone is a best practice. Member number will refer only the particular table entry but primary key is used to interlink tables using joint queries by having same primary key in different tables.


How can I time SQL-queries using psql? I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?

Just turn on timing by entering:

\timing

The time that \timing returns also includes the network latency, if you're connecting to a remote server.

When you don't want that and don't need the query output too, better use EXPLAIN ANALYZE,  which outputs the query plan with the planner estimates plus the actual execution times.

for example, EXPLAIN ANALYZE SELECT foo from bar ;


Mysql restore all data from Master MySQl Slave replication server got crashed and I am missing around 60 tables, some table structure and data which present in master and by DB size is more than 25Gb, How I Can restore to resolve issue.
Please help to solve it ASAP.
Thanking you in advance...
Nikhil

You need to restore the data from master.

Take a dump of missing tables from master using
mysqldump  --singl-transaction>missing_tables.sql

Stop the slave.

Restore the dump file in slave using
mysql -uroot -p<missing_tables.sql

Start the replication slave will catchup.


Spread SQL Server data files across devices to improve performance? Would it improve performance of a database in SQL Server if I place the .mdf (rows data) and .ldf (log) files on separate sets of raided disks, as opposed to having both files on the same set of raided disks?

All performance solutions are based on measurements. As the theory goes, SQL Server requires the transactions to flush to durable storage (disk) before acknowledging commit (WAL). This makes the log write throughput critical for high write loads. Separating the LDF file on a separate physical disk allows the log writes to achieve serial streaming access to disk, which is the fastest way a spinning disk can write (~100-150Mb/s). Having the MDFs on the same physical disk introduces random IO patterns that dramatically reduce the throughput (~10-40Mb/s). This is the background of the separate LDF /MDF disk recommendations. The point is, obviously, mute on SSD storage.

However, to improve the server performance you need to identify the bottleneck and address it appropriately. If your bottleneck is poor indexing, lock contention, CPU, small buffer pool or any other of many more possible causes then separating the LDF and MDF will help nothing. So you should first find the bottlenecks, and I recommend following the Waits and Queues methodology. Once you have measurements that prove a bottleneck that causes performance degradation then and only then you can act appropriately.

Well, it helps with I/O performance, although you won’t see a huge performance increase.
That is the real problem with Relational Databases. That's why there are No-SQL databases (MongoDb) that address the performance issues


One or several tables for attributes I just spent some time discussing two alternative DB designs with a coworker and I'm not convinced. Neither of us is strictly DBA so we may be missing something.

The overall goal is to create the ability to attach open-ended free-text attributes for each of three (possibly 4) entities.

Let's call the business entities Device, Location and Part; they have relationships between themselves.

Design A: Create DeviceAttribute, LocationAttribute and PartAttribute tables, each of which has an ID, Reference ID (FK to respective table), Name, Value and Type.

Design B: Create Attribute table with (ID, Name,Value and Type columns) and three reference tables - each holding a reference from one of the entity tables to one of the Attribute tables' IDs.

The concern is mostly about performance:
 - will 3 separate xxxAttribute tables perform better when querying only for an
   Entity's data, such as "give me Device X and all its attributes", or
   do both designs perform the same?
 - will 1 Attribute table (Design B) perform better when querying for entities with given attribute name/value, such as "give me all entities that have Attribute.Name='GPS'", or is it equivalent to querying a view that combines the 3 tables of Design A?
 - in case of Design B: does update on an entity table (Location, Device, Part) cause a lock on querying in other entity tables?

The system may have tens of thousands of Parts, thousands of Devices and hundreds of Locations and may have to process in the order of tens to few hundreds queries per second.

A couple of reasons I prefer a single table:

(1) with multiple tables you'll almost always be performing unions and you pay for the core data multiple times

(2) you can easily optimize for a subset of attributes or a combination of devices and attributes with indexes and use sparse columns to minimize wasted space

I wrote a bit about EAV here - it is by no means perfect, but we used it to solve many performance problems (at the cost of others) at a previous job.

EAV has been at the heart of many discussions but how about this design.

Table A holds the all entries relevant to the entities but the attributes.
Table B holds the attributes with a FK and a TEXT field. In the TEXT field you can write any 'json' type data.

Obviously you can further optimize the tables.


Occasional error: Could not obtain information about Windows NT group/user 'domain\user', error code 0x54b I have a query that will impersonate as a specific domain user to send email with attachment. This impersonation is required as it needs to read a file at some other server to be able to attach the file to the email.

The query is as follows

EXECUTE AS login='domain\user' -- To impersonate
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile_name',
    @recipients = @recipient,
    @copy_recipients = @cc,
    @blind_copy_recipients = @bcc,
    @subject = @subj,
    @body = @mail_body,
    @body_format = 'HTML',
    @from_address = 'email@address.com',
    @file_attachments = @files;
REVERT

Sometimes I get error when running the query with error message as:

Could not obtain information about Windows NT group/user 'domain\user', error code 0x54b.

The weird thing is that, if I execute this query multiple times, it will work and it will send out the email.

What could possibly be wrong?

SQL Server version: 11.0.2100

I would strongly advise against using sysadmin privileges to simply send an email... have you ever hear of the principle of least privilege!? This wouldn't solve your problem, just provide a work around! Similarly, you would be enabling the login to drop databases, change passwords, and create all sorts of chaos!

Have you checked the SQL error logs, or the event logs. It may also be a good idea to check the logs on your DC, to see if there is any information there.

Are you having any problems with the account that the agent job is being execute as? Presumably this has the appropriate permissions for what you are asking? when you are executing the script you are executing it under your context, not the context of the agent. This is likely why it works for you all the time.

Without any additional information, preferably from your logs, it's difficult to help any better than this


Database Design challenge: One (Table)-to-Many (Tables) Relationship? I am rather new to DBs, and after days of being stuck, researching and trying different solutions, I turn to this community's expertise for help.

I am trying to have one Person table in my MSSQL DB, that would contain all our contacts. Keeping all in one table facilitates a lot of things for us downstream.
Challenge is we have to split the Company table into different tables, by categories, as we need different attributes for each - e.g. Fuel Supplier, Engine Manufacturer and Maintenance Company would each have their own table, listing companies in said category, with their own specific attributes.

Therefore, I would need a "one table to many tables" relationship where the various Company tables feed into only one attribute ("Company") in the Person table. Goal being that within a Person record, one could choose the Company that person works for - only one company, from across multiple tables.

So ideally something like that:

Is there a way to achieve this or any workaround?

Thanks for your help!

This seems like a case where you'd want to use inheritance (subtype/supertype), but it isn't.

A company that manufactures engines won't necessarily always be an engine manufacturer, and might not only be an engine manufacturer. It could change what it makes, or it could make multiple things

"Fuel Supplier" or "Engine Manufacturer" are roles played by companies. A company has many roles. So you have a company table, and you have a company_role table with a foreign key to company. If roles have different columns, then this would be a case where you can use inheritance

While you're at it though, you should know that it's useful to use inheritance to create a super type for individuals (people) and companies (or organizations). This would let you have say a sales order linking to the "customer" which could be a person or company

This sounds similar to an Entity-Value-Attribute EVA problem. Given your Person table, and a 1NF form of your company tables, you could create an Attribute/Category table and create relationships through an EVA table, wherein it tracks Person-Company-Attribute and the Value.

If categories are shared across companies, you create a Category (or AttributeType table.

AttributeTypeID |  AttributeTypeDesc
200               |  Gallons

Then your EVA table may look like:

PersonID | CompanyID | AttributeTypeID | AttributeValue
1        | 100       | 200             | 300

I've known some instances where people normalize even AttributeValues though it's dependent on your use case. For example, if AttributeValues are a mix of datatypes, you'd possibly need to consider normalizing. Other perspectives I've seen are that they set everything in VARCHAR and use the AttributeTypeID to determine what the datatype should be adding a "AttributeTypeDataType" field, for example.

Your joins to reference this information might look like:

SELECT *
FROM EVA E
INNER JOIN Company C
    ON C.CompanyID = E.CompanyID
INNER JOIN Person P
    ON P.PersonID = E.PersonID
INNER JOIN AttributeTypeID A
    ON A.AttributeTypeID = E.AttributeTypeID


SQL server 2012 service pack installation which service pack I should apply first after installing sql server 2012 enterprise edition either sp1 or sp2 ?

Thanks in advance.

The service packs are cumulative, so sp2 includes any pieces from sp1 that were not subsequently updated in sp2.

The Best Practices, that I've been following would be to start initially with SP1 and will check how it behaves and then will upgrade it to SP2.

Depending upon the application and other various parameters its better to first implement such upgrades on DEV/TEST server first and monitor the behavior accordingly. Once everything goes well you can decide how to apply SP's on the production box.

Also, note that Cumulative updates are the one which are truly cumulative. i.e if i need to install SQL server 2012 CU9, i can go like installing SQL server 2012 RTM , then SP2 and then directly CU9, instead of installing CU's from 1-9..

You can refer here for the SP releases.


Tool to monitor database data I would like to tool that when record data changed inside all tables. so later i would get a report like

table user

added record - 3,user3..

deleted record - 2,user2..

This solution does not require triggers but requires setup and causes a performance hit if you enable it for all tables.

Auditing has been built into Oracle for many releases.  There is an article here which goes into some detail.
Basically, you turn it on, tune it for what level of detail you want and the output is available as a dba view or as XML.

Check the system parameter AUDIT TRAIL, it sounds like you want the fine grained auditing available from 9i onwards so you can access the SQL_BIND and SQL_TEXT.

There is an SCN number so you can flashback to the state of the data when the change was made.

Keep in mind that there is a performance hit for all this. You must have the hardware and resources available if this is a production system.

I am using MindArray IPM's database monitoring tools to monitor database data. It keeps utilization of database resources and monitors well for database stored procedure. We can design and customize it according to our preferences.


Cannot execute inserts querys after upgrade to mysql 5.7 I have Debian 8 (Jessie) server and i followed this procedure to upgrade from mysql 5.5 to mysql 5.7 https://dev.mysql.com/doc/refman/5.7/en/linux-installation-debian.html

Now, any script in the server that have an INSERT query is not working... The script is being executed but the INSERT query is returning always false with no error description...

Any ideas how to solve?

This is what i have installed

Also, when i click on Database button in phpmyadmin

i'm getting this error

If your question is about #1055...

1055: sql_mode includes ONLY_FULL_GROUP_BY.  When doing a GROUP BY, you should include all the columns in the SELECT other than aggregates.

If your question is about INSERT, I don't see any supporting info.

I had the same problem when i first upgraded to 5.7, i think strict sql mode is enabled by default and if you have tables with not null columns and no default value will behave like this.


Obtain the sum of count of multiple schema and tables in one query in mysql The query belows return 4 rows and it's good. But i need also to sum the results of all the counts in the same query. How to achieve it? I've tried different things but i get sintax errors.
SELECT COUNT(*) FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1

Here's an ugly solution:
SELECT MAX(tmp.col1) as col1,
       MAX(tmp.col2) as col2,
       MAX(tmp.col3) as col3,
       MAX(tmp.col4) as col4,
       MAX(tmp.col1) + MAX(tmp.col2) + MAX(tmp.col3) + MAX(tmp.col4) as total
  FROM (SELECT COUNT(*) AS col1, NULL as col2, NULL as col3, NULL as col4
          FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, COUNT(*) AS col2, NULL as col3, NULL as col4
          FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, NULL as col2, COUNT(*) AS col3, NULL as col4
          FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, NULL as col2, NULL as col3, COUNT(*) AS col4
          FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1) tmp

This will give you something like:

col1
col2
col3
col4
total

10
20
30
40
100

It's not very efficient, but it gets the job done.

Combine your sub-queries and put a SELECT clause on it.
SELECT
(SELECT COUNT(*) AS cnt FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1) s1
,
(SELECT COUNT(*) AS cnt FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1) s2
,
(SELECT COUNT(*) AS cnt FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1) s3
,
(SELECT COUNT(*) AS cnt FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1) s4

That should give you single row with 4 columns in it.

If you need sum of them in single query then use below query.
You can put them into a select clause and it should give you sum of it. here is an example.
SELECT SUM(cnt) FROM
(
SELECT COUNT(*) AS cnt FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) AS cnt FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) AS cnt FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) AS cnt FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1
) tmp


Daily snapshot fact table I have a list of products per costumer. Costumers can delete or add products to their list. I want to know in each day what is the list of products (and count) by costumer.

My first approach is to have a fact table with all products by client each day.

Is this the best approach?
This fact table will grow at the same rate that the products are added to costumers list.

btw i'm using Pentaho.

You could do that. I'm thinking something along the lines of having an active and inactive date on your fact table for each product for each customer. Then you could mark a previous product with an inactive date.

This should allow you to query it for date ranges that are include a specific date to get what their product list was for a specific point in time.

If you have multiple product table for each customer then your you should write an Indexed View to get these figures instead of going into Fact Table. Even if you have a single table for products for customers, then you should go for an indexed view.


Does LIMIT 1 still have effect on a unique column I know that adding LIMIT 1 when searching for only one record makes a lot of difference when it comes to performance. But does it still have any effect when we search a column with a unique constraint?

Let us say I store email records with a id (int) primary key column and the email field is marked as unique. Do I still need to explicitly call LIMIT 1 in my queries to speed up the returning of a result set? Or does the MySQL engine understand that as soon as one record is found the result can be returned since the field is marked as unique and is LIMIT 1 obsolete?

MySQL table definition:

CREATE TABLE Email
(
    `id` int NOT NULL,
    `email` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE (`email`)
)

Does it in this case make a difference whether I do:

SELECT * WHERE `email`="john.doe@example.com";

Or

SELECT * WHERE `email`="john.doe@example.com" LIMIT 1;

if column has unique index (constraint), You do not need to use LIMIT 1

LIMIT 1 - good tools for prevent potential errors when result MUST be single and You not sure about how many rows You have


Better approach for "LIKE OR LIKE, OR LIKE, OR LIKE, OR LIKE" In this question he's having the same problem as I am. I need something like:

select * from blablabla
where product
like '%rock%' or
like '%paper%' or
like '%scisor%' or
like '%car%' or
like '%pasta%'

This is ugly and it's not using indexes.. In this case, this is really the only way to do this ( to select multiple words inside a string ), or should I use FULLTEXT?

As I understand, with fulltext, I can select multiple words inside a string.

This question talks about Full Text as well

Full text indexes generally aren't a magic bullet, and require additional maintenance, disk space, and fairly intrusive changes to query patterns.

Unless you're truly in need of indexing large documents (think email bodies, PDFs, Word docs, etc.), they're overkill (and if we're being honest, I'd take that process out of SQL Server entirely and use Elasticsearch or something similar).

For smaller use-cases, computed columns are generally a better approach.

Here's a quick demo setup:

use tempdb

CREATE TABLE #fulltextindexesarestupid (Id INT PRIMARY KEY CLUSTERED, StopAbusingFeatures VARCHAR(100))

INSERT #fulltextindexesarestupid (Id)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (@@ROWCOUNT))
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2

UPDATE #fulltextindexesarestupid
SET StopAbusingFeatures = CASE WHEN Id % 15 = 0 THEN 'Bad'
                               WHEN Id % 3 = 0 THEN 'Idea'
                               WHEN Id % 5 = 0 THEN 'Jeans'
                               END

ALTER TABLE #fulltextindexesarestupid
ADD LessBad AS CONVERT(BIT, CASE WHEN StopAbusingFeatures LIKE '%Bad%' THEN 1
                    WHEN StopAbusingFeatures LIKE '%Idea%' THEN 1
                    ELSE 0 END)

CREATE UNIQUE NONCLUSTERED INDEX ix_whatever ON #fulltextindexesarestupid (LessBad, Id)

Querying based on even a non-persisted column gives us a plan that 'uses indexes' and everything :)

SELECT COUNT(*)
FROM #fulltextindexesarestupid AS f
WHERE LessBad = 1

sp_BlitzErik's answer hits on a lot of good points, but I don't think that's why you shouldn't use Full Text Search. Full text search isn't there to do what you think it does. It's not there to search multiple fields. It's there to vectorize word content and make use of dictionaries, stubbing, lexers, gazetteers, stop-word elimination, and a slew of other tricks none of which apply. Or, have not yet been shown to apply.

I don't agree with the solution either, though I'm not sure how to do this better in SQL Server. Let's recreate his data for PostgreSQL -- it's a lot cleaner to create in in PostgreSQL too.

CREATE TABLE fulltextindexesarestupid
AS
  SELECT
    id,
    CASE WHEN Id % 15 = 0 THEN 'Bad'
      WHEN Id % 3 = 0 THEN 'Idea'
      WHEN Id % 5 = 0 THEN 'Jeans'
    END AS StopAbusingFeatures
  FROM generate_series(1,1000000) AS id;

Now what you want is an enum type,

CREATE TYPE foo AS ENUM ('Bad', 'Idea', 'Jeans');

ALTER TABLE fulltextindexesarestupid
  ALTER StopAbusingFeatures
  SET DATA TYPE foo
  USING StopAbusingFeatures::foo;

Now you've collapsed the strings to integer representations. But even better you can query them like before.

SELECT *
FROM fulltextindexesarestupid
WHERE StopAbusingFeatures = 'Bad';

This has the effect.

conceals the fact that you're categories are an enumerated type. That complexity is encapsulated in the type and hidden from the user.
it also places the maintenance on those categories on the type.
it's standardized.
it doesn't grow the row size.

Without these benefits, you're essentially just trying to optimize out string comparison. But alas, I'm not even sure how sp_BlitzErik gets to the answer given the code in the suggestion,

like '%rock%' or
like '%paper%' or
like '%scisor%' or
like '%car%' or
like '%pasta%'

You can collapse the tokens down to integers using an enum, or the hand-rolling method suggested by sp_BlitzErik but if you can do the collapsing why are you doing the unanchored-like too? Ie, If you know '%pasta%' is the token 'pasta' why do you have the % on both sides of it. Without '%' this an equality check and it should pretty fast even as text.