Sharing Databases and Tables between users in MySQL I want to know how it is possible to share a database tables between multiple users/user groups, I mean, SQL Server has a security feature that we can create user groups and assign users to them, then we can give each user group permissions for tables in a database/schema. So, a user from visitors usergroup can't read sensitive admin tables, or a visitor can read categories but can't insert/update categories table but users from admin user group can.

[https://mariadb.com/kb/en/mariadb/roles/](MariaDB 10.0.5 has Roles); see if they provide what you desire.

(Yes, MariaDB is essentially compatible with MySQL; Roles is an extra feature.)


Inserts through Stored Procedure 6 time slower than direct insert We have recently created a stored procedure in order to test the insert speed between a direct query into SQL Server without parametrization (new plan for each different insert) and insert through parametrized stored procedure.

The users are complaining that insert through stored procedure is 6 times slower... How is this possible?

The stored procedure looks like this:

CREATE PROCEDURE [dbo].[spInsertAnalysis]
    @Class varchar(255),
    @Method varchar(100),
    @Name varchar(100),
    @Origin varchar(20),
    @Ultimate varchar(255),
    @Value varchar(100)
AS
BEGIN
    DECLARE @SQLString NVARCHAR(MAX)

    SET @SQLString = 'INSERT INTO [Analysis] ([Class], [Method], [Name], [Origin], [Ultimate], [Value] ) VALUES (''' +
        @Class + ''', ''' +
        @Method + ''', ''' +
        @Name + ''', ''' +
        @Origin + ''', ''' +
        @Ultimate + ''', ' +
        @Value + ') '

    EXEC (@SQLString)
END

I see that SQL has generated 17 different query plans for this insert and I found that the cause is the numeric value is different for each query plan (The rest is the same):

(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000),@5 numeric(15,9))
(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000),@5 numeric(14,7))
(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000),@5 numeric(14,5))

But anyway I don't know why it is getting 6 times slower than making direct inserts...

Any help will be appreciated

Make your code simple. Do not create dynamic query for simple one table insertion. for example:

create proc [dbo].usp_Add<Your table> List of Parameters--, datatype similar to table columns
AS
BEGIN
SET NOCOUNT ON
INSERT INTO <MY TABLE> (LIST OF COLUMNS) SELECT @Parameter1, @Parameter2
--OR
INSERT INTO <MY TABLE> (LIST OF COLUMNS) values(@Parameter1, @Parameter2)

END


Adding indexes to very large tables in MySQL I have a very large table (600M+ records, 260G of data on disk) within MySQL that I need to add indexes to.  I'd expected to add them directly, but doing some searching and some recommend creating a placeholder table, creating index(es) on it, dumping from first table and then loading to second table.

Can anyone advise on a preferred approach and guesstimate potential processing time improvement.

MySQL-8.0 has a number of ALTER TABLE variations in innodb than can be done online.

Recommend testing the table structure in a test environment using ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE; Do this one index at a time.

Other reference documentation has examples.

Alternatives include gh-ost which require more spac.e


NoSQL or MySQL for storing API usage I need to be able to track API usage in order to generate reports or charts later. I've been struggling to decide whether to use regular MySQL or something like MongoDB or Elastic Search which I don't have any experience with at all.

The records I will insert will be

user_id (int)
request_time (datetime)
ip_address(varchar)

I will need to count total records per user_id per given time
I will need to grab all ips from user_id per given time
I will be inserting around 400k records per day
I need to be sure this will be able to handle at least 1 million records per day for inserts

Inserts are sent to a queue server (Redis) that will do single inserts to whatever system we decide to use.

What do you think will be the best solution for this scenario? It's worth mentioning that we are trying to find the least expensive way to do this.

Update: A million records using bigint for Ips will take approx 45mb making a total of 1350mb monthly

Two things.

Use the tool you already know.   This simplifies development, and reduces the desire to prematurely optimize.
Fit your environment to the requirements.  The hardware required to store 400,000 x 10 byte rows per day is vastly different from 400,000 x 10,000 byte rows.

Define your requirements by mocking up some sample data in MySQL, along with a sample of the queries you'll run.  See how much space this sample takes up, and do the math to determine the requirements for your data over a 1 month, a year, and so-forth.  This will give you a much better idea of the kind of hardware you'll need.

My answer talks about the requirements for storing billions of rows in SQL Server and shows some of the things to consider.  The other answers on that question are also quite instructive.  As I note in my answer, don't store an IP address as a varchar.  Use an integer.


Is an Oracle SID UPPERCASE or lowercase? This may sound silly, but is an Oracle Service Identifier (SID) aka database name normally in upper or lowercase?

According to the documentation, a SID "can contain only the characters a-z, A-Z, and 0-9", so both upper and lowercase is allowed. In my experience, it usually doesn't really matter. However, I am installing Oracle 11.2 on a file system that is case sensitive, and a lot of directories and files have the SID in them (e.g. initMYDB.ora).

EDIT:

I found a quote saying that the SID in the password file name (orapwMYDB) is case sensitive and must match the instance name.

Case sensitive on Linux/UNIX and not case sensitive on Windows.


Testing on Logical Standby Database Primary DB: 11.2.0.3 Enterprise
  Standby DB: 12.1.0.2 Enterprise

I've found several articles on making changes on a Physical Standby DB, but I've been having difficulty locating how to enable user testing on a Transient Logical Standby DB (then flashing back their changes and re-enabling log transport and SQL Apply after the testing has been completed).

Based on what I know of Logical Standby Databases, I wrote up the following procedures.  However, this is untested since if I botch up the Standby DB, I can't simply re-duplicate the DB through RMAN since the database versions are different.  I'll essentially have to re-clone the Primary DB, reconfigure the Standby for dataguard, upgrade the Standby DB version, etc, etc... basically, a lot of work that I would prefer to avoid, if possible...

If someone who has had experience with this can look over my below procedures and let me know if i'm forgetting anything, or what potential issues/caveats I should watch out for or be aware of, I would greatly appreciate it:

*note: the primary => standby log transport is specified in log_archive_dest_3

PRIMARY
alter system set log_archive_dest_state_3=defer scope=both;

STANDBY
alter database stop logical standby apply;
alter database guard none;
alter database flashback on;
create restore point before_testing guarantee flashback database;

*AFTER TESTING IS DONE*

STANDBY
flashback database to restore point 'before_testing';
drop restore point before_testing;
alter database flashback off;
alter database guard all;
alter database start logical standby apply immediate;

PRIMARY
alter system set log_archive_dest_state_3=enable scope=both;

My procedures seem valid... I was able to test the logical DB, then bring it back into an exact copy Logical Standby and re-enable SQL Apply without issues.

However, there was a minor change to my procedures: flashback database requires that the DB is in mount state and that alter database open requires resetlogs or noresetlogs.

Therefore, the amended procedures are as follows:

PRIMARY
alter system set log_archive_dest_state_3=defer scope=both;

STANDBY
alter database stop logical standby apply;
alter database guard none;
alter database flashback on;
create restore point before_testing guarantee flashback database;

*AFTER TESTING IS DONE*

STANDBY
shutdown immediate;
startup mount;
flashback database to restore point 'before_testing';
alter database open resetlogs;
alter database guard all;
alter database start logical standby apply immediate;
drop restore point before_testing;
alter database flashback off;

PRIMARY
alter system set log_archive_dest_state_3=enable scope=both;


Detached database with renamed drive I have detached a database and had the systems team rename the drive letter (from E: to P:). When I try to attach the database again, it's looking for the original drive.

I don't have a backup of the database. Is there any way I can change the system tables where the actual physical locations of the files (.mdf) are stored?

Are there any alternative solutions now?

Just to be clear: It's my mistake that I approved the systems team to change the drive letter.

Specify the path for every database file (including ldf file) and SQL Server will not look for the original path. If the GUI does something strange, then use the CREATE DATABASE ... FOR ATTACH directly (examples in the product documentation, for instance).


Chained two Many-to-Many relations I have the following case, which I can only describe as a chain of two many-to-many relationships:

There are three business entities (tables) and two junction tables:

Orders
Products
Features
Orders has a many-to-many relation with Products through a order_products table
Products has a many-to-many relation with Features through a feature_products table

Ideally, it means that the same Product may has different Features through one or more Orders.

For example, suppose we have Products of "Chocolate Cake" and "Cheese Cake", and within one Order we have:

2 Chocolate Cake: plain, small size and diet (so using 3 Features)
1 Chocolate Cake: decorated, medium size, Coconut added and candle (using 4 Features)
1 Chocolate Cake: plain, big size (2 Features)
3 Cheese Cake: cinnamon, banana (2 Features)

How could I store and maintain this relation, when the same Product may have one or more of its related Features within one Order?

Looks like you need a one-to-many between orders_products and Features.

Your orders_products will hold the first part of the order:

OrderItemID, OrderID, quantity, ProductID (e.g. "Item 1: 2 Chocolate Cakes")

and a new table (e.g. order_products_features) will hold the one-to-many relation needed for Features:

OrderItemID, FeatureID (e.g. "for Item 1, add "Plain")
OrderItemID, FeatureID (e.g. "for Item 1, add "Small Size")
OrderItemID, FeatureID (e.g. "for Item 1, add "Diet")

You will need some extra logic to check that users only enter valid Product-Feature combinations into this new table.

Designing it this way means that in the future, if you change the Product-Feature links, you will still have a historical record of what people ordered in the past, even though those Product-Feature links might not be valid today.


MongoDB Replica Set: Can't Add Member/Connect I'm building a MongoDB replicaset w/ three Ubuntu 14 servers.

I'm connected to the primary server and authenticated w/ userAdminAnyDatabase and clusterAdmin privileges.

When I run rs.add(xx.xx.xx.xx:27017), I get this error:

exception: set name does not match the set name host xx.xx.xx.xx:27017 expects

I've tried using the IP address, external hostname, and internal hostname.

I have xx.xx.xx.xx rs2 in my /etc/hosts file and use rs2 in the config file of the server I want to add (replSet=rs2). No matter what I try, it says the name doesn't match.

What am I doing wrong?

The issue was a result of having different values for replSet in /etc/mongod.conf. I thought the servers should be named uniquely -- this is incorrect. Each server in the replicaset must have the same value for replSet.


Mysql cluster configuration help I am new to mysql cluster I was reading around the web how to set it up. I came up with 2 different set up not sure which one is better fail safe vs cost efficient. Our new site have clients in USA and Japan. We are not big site, and saving money is high priority for us.

V1)

US Servers, Japan Servers

Front End 3 servers
  Apache
  local memcached + connects to mysql memcached

Mysql cluster
  One management node (This will be aware of japanese/us management node)
  Two data Nodes
  Two Sql Nodes

V2)

US Servers, Japan Servers

Front End 3 servers
  Apache
  local memcached + connects to mysql memcached

Mysql cluster
  One management node (This will be aware of japanese/us management node)
  Two data Nodes/ Sql Nodes combined

Thanks

Assuming that there is not a performance issue with having data and sql living on the same node, V2 will be viable and cheaper.

I generally do not like having caches in front of caches, so I question the need for memcached.


Query with Indexes slower than without Indexes I'm using MySQL 5.6 and my storage engine is InnoDB.

I have a table with 1 million rows containing with the columns:

ID (primary key)
FirstName
LastName
foreign_key_id (foreign key, NOT NULL)
foreign_key_id2 (another foreign key, default NULL)

The rows are seperated under:

25% with foreign_key_id value 1 and foreign_key_id2 NULL
25% with foreign_key_id value 1 and foreign_key_id2 NOT NULL
25% with foreign_key_id value 2 and foreign_key_id2 NULL
25% with foreign_key_id value 2 and foreign_key_id2 NOT NULL

With the following indexes:

index foreign_key_idx on foreign_key_id
index foreign_key_2_idx on and foreign_key_id2
composite index foreign_key_comp_idx on (foreign_key_idx, foreign_key_2_idx)

I perform the following queries:

  Query 1 - without indexes:

SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_idx, foreign_key_2_idx, foreign_key_comp_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL

  Query 2 - with indexes (no composite index):

SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_comp_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL

  Query 3 - with composite index (no other indexes):

SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_idx, foreign_key_2_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL

The results:

  Query 1 (no indexes) performs a full table scan and uses 1 million records with a
  total duration of 0.37 seconds.

  Query 2 (indexes, no composite index) performs a non-unique key lookup on foreign_key_idx index and
  uses 500K records with a total duration of 0.6 seconds.

  Query 3 (composite index only) performs an index range scan on composite index and uses 480K
  records with a total duration of 0.13 seconds.

What I really don't understand is: why is query 2 (with indexes) always performing slower than query 1 (without indexes)? I'm really really stuck and need some help...

I've tested the queries above with different amount of rows, like 1k, 10k, 20k, 50k, 100k, 200k, 250k, 500k, 1M etc, always with the same ratio (25%), and the results where the same (query 2 always performing slow)

Thank you in advance, really appreciate any kind of input!

Edit (2 May 2016)

SHOW CREATE TABLE COMMAND:

CREATE TABLE `table` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `FirstName` varchar(255) NOT NULL,
   `LastName` varchar(255) NOT NULL,
   `foreign_key_id` int(11) NOT NULL,
   `foreign_key_id2` int(11) DEFAULT NULL,

   PRIMARY KEY (`ID`),
   KEY `foreign_key_idx` (`foreign_key_id`),
   KEY `foreign_key_2_idx` (`foreign_key_id2`),
   KEY `foreign_key_comp_idx ` (`foreign_key_id`,`foreign_key_id2`),

   CONSTRAINT `foreign_key_idx` FOREIGN KEY (`foreign_key_id`) REFERENCES `table2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `foreign_key_2_idx` FOREIGN KEY (`foreign_key_id2`) REFERENCES `table3` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 ) ENGINE=InnoDB AUTO_INCREMENT=1515998 DEFAULT CHARSET=latin1

EXPLAIN PLANS:

Not sure if important, but table2 has 20 records and table3 also 1 million.

My understanding:
The table contains 1M rows of which 250k are returned by the query.
There are 500k rows with foreign_key_id = 1 and 500k rows with af.foreign_key_id2 IS NOT NULL.

The query using full table scan (actually doing full index scan on the PRIMARY key in InnoDB) will read all 1M rows sequentially and check each of them for the conditions.

The query using foreign_key_idx (and it should be the same if it used 'foreign_key_2_idx') has to read 500k rows by "random" order (supposing the rows were inserted or assigned the ID randomly) and check the other condition on them. That means the query reads 500k rows by their primary key from the table - but that means it will probably access 100% of data pages of the table. So in the end the query reads half of the index and all of the table - more data to go through in total and the table is accessed by random seeks, not sequentialy. It should be no surprise that such query is slower than full table scan.

The query going by foreign_key_comp_idx will find the part of the index for foreign_key_id = 1 by ref access method and then fetch its part satisfying af.foreign_key_id2 IS NOT NULL by range access - finding 250k rows which it will then read from the table. Again there is a high probability that it will have to read more than 50% of the table pages but it may just be lucky with the data distribution that it is less than 100% and as a plus it does not have to check the conditions again as they are assured by the index.

What surprises me: usual understanding is that the optimizer will not even want to use an index if it will lead to fetching ~20% of the table rows, prefering full table scan instead as it is usually faster. Your IGNORE INDEX() hints should not change that, thats what FORCE INDEX() hint is for (telling optimizer that the table scan is extremely costly compared to the suggested index).

But maybe if the insert order was not totally random, the statistics and index dives show that (even when the expected rows numbers do not).


Is twitter's character limit a way they found to avoid drastic database fragmentation? so I just started reading a book about "Mongo schema design" and am currently reading the portion about MMAP and fragmentation.
From what I understood there are different ways to avoid database fragmentation. What I wanted to know, however is if the character limit in platforms like twitter a way they found to counter or solve the issue of database fragmentation and if so what would be another solution that does not involve tweaking the front end?

The original character limit in twitter was because it started out as a micro-blogging platform with the option to update via SMS. SMS messages had a limit of 160 bytes. The 140 limit on the message length is because the extra metadata (to identify the user) was up to 20 characters long.
It had nothing to do with things that could affect internal or external fragmentation (rows/page, dynamic/static row sizes, excess page splitting due to key values effectively in random order, ...) or other concerns of the backend database design.


SQL server 2012 Enterprise Will CDC work with SQL Server Always on Availability groups? Do you need to set up CDC on both/multiples nodes if using Availability Clusters?

Yes, CDC will work with SQL Server Always ON availability groups. Check Change Data Capture.

There is already an answer here - Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup?


SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me Recently upgraded from SQL Server 2008 to 2016, been running in compatibility mode 100 for 4 months everything was ticking along nicely (fast). After a fair amount of testing and running the migration adviser i decided to flick the "switch" and changed the compatibility level to 130...

This has had a detrimental effect on a quite a few sprocs/queries , some which where running in split seconds are now taking minutes. Also CPU has bumped up due to this.

These queries are written well, also I rebuild indexes and stats nightly..

Compared plans which look the same, with a couple of percentages slightly off, still the same plan though! i think CE isn't getting good row counts.

Also what is weird some times the queries run fine in 130 so i think all is good, but suddenly get an alert and the query is taken ages just by 2 rows being different then I have to add back OPTION (QUERYTRACEON 9481) to get it speedy again.

Are there any other things I can check / do to help get these queries get back to their former glory?

Should i remove all stored procedures plans from cache???

Microsoft has an upgrade strategy for changing the compatibility mode on SQL Server 2016. Quoting the linked article:

The recommended workflow for upgrading the query processor to the latest version of the code is:

Upgrade a database to SQL Server 2016 without changing the database compatibility level (keep it at prior level)

Enable the query store on the database. For more information about enabling and using the query store, see Monitoring Performance By Using the Query Store.

Wait sufficient time to collect representative data of the workload.

Change the compatibility level of the database to 130

Using SQL Server Management Studio, evaluate if there are performance regressions on specific queries after the compatibility level change

For cases where there are regressions, force the prior plan in the query store.

If there are query plans that fail to force or if performance is still insufficient, consider reverting the compatibility level to the prior setting and then engaging Microsoft Customer Support.

You could try a version of that for your situation. Change the compatibility mode back to 100, enable the query store, go through a full business cycle and get a good baseline, then change compatibility mode and use the query store to analyze poorly running queries and take further action on them.


Query three tables with join I have five tables that are connected with each other using joins:

department vs course -- one to many
course vs classes -- one to many
classes vs section -- many to many

I want to retrieve the sections the belong to a particular course. I have tried inner join to achieve this but I can not get good results, I get all the sections instead.

select sectionname from Section
inner join Classes on classid=sectionid
inner join Course on courseid=Classes.fk_course_courseid;

Data contained in the table as shown below. I want to retrieve all the sections the belong to courseid = 1 or department id = 1.

SELECT DISTINCT sectionname ,
               sectionid
FROM   section
       LEFT JOIN classsection
              ON classsection.fk_section_sectionid = section.sectionid
       LEFT JOIN classes
              ON classid = classsection.fk_class_classid
       LEFT JOIN course
              ON courseid = fk_course_courseid
WHERE  coursename = 'School'

i just joined section and classses first and then joined courses to see the department name which sections are belong to which classes and it worked kudos


Foreign keys not working I have created a db, and my problem is that I can insert ANY value in FK.

Sorry for having the values in Spanish.

This is my Database:

CREATE TABLE "Articulo" (
`ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
`nombre`    TEXT NOT NULL,
`precio`    REAL NOT NULL,
`cantidad`  INTEGER NOT NULL,
`nombre_usuario`    TEXT,
FOREIGN KEY(`nombre_usuario`) REFERENCES Usuario)

CREATE TABLE `Categoria` (
    `nombre`    TEXT,
    PRIMARY KEY(nombre)
)

CREATE TABLE "Categoria_Articulo" (
    `ID_articulo`   INTEGER,
    `nombre`    TEXT,
    PRIMARY KEY(ID_articulo,nombre),
    FOREIGN KEY(`ID_articulo`) REFERENCES Articulo,
    FOREIGN KEY(`nombre`) REFERENCES Categoria
)

CREATE TABLE "Pedido" (
        `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
        `precio_total`  REAL NOT NULL DEFAULT 0,
        `nombre_usuario`    INTEGER NOT NULL,
        `fecha` TEXT NOT NULL,
        FOREIGN KEY(`nombre_usuario`) REFERENCES Usuario
    )

CREATE TABLE "Linea_Pedido" (
    `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `precio_total`  REAL NOT NULL,
    `cantidad`  INTEGER NOT NULL,
    `ID_pedido` INTEGER NOT NULL,
    FOREIGN KEY(`ID_pedido`) REFERENCES Pedido
)

CREATE TABLE "Linea_Pedido_Articulo" (
    `ID_linea_pedido`   INTEGER,
    `ID_articulo`   INTEGER,
    PRIMARY KEY(ID_linea_pedido,ID_articulo),
    FOREIGN KEY(`ID_linea_pedido`) REFERENCES Linea_Pedido,
    FOREIGN KEY(`ID_articulo`) REFERENCES Articulo
)

CREATE TABLE "Linea_Pedido_Articulo" (
    `ID_linea_pedido`   INTEGER,
    `ID_articulo`   INTEGER,
    PRIMARY KEY(ID_linea_pedido,ID_articulo),
    FOREIGN KEY(`ID_linea_pedido`) REFERENCES Linea_Pedido,
    FOREIGN KEY(`ID_articulo`) REFERENCES Articulo
)

CREATE TABLE `Usuario` (
    `nombre_usuario`    TEXT,
    `nombre`    TEXT NOT NULL,
    `apellidos` TEXT NOT NULL,
    `contraseña`    TEXT NOT NULL,
    PRIMARY KEY(nombre_usuario)
)

CREATE TABLE `favorito` (
    `usuario_tiene_favorito`    TEXT,
    `usuario_es_favorito`   TEXT,
    PRIMARY KEY(usuario_tiene_favorito,usuario_es_favorito),
    FOREIGN KEY(`usuario_tiene_favorito`) REFERENCES Usuario,
    FOREIGN KEY(`usuario_es_favorito`) REFERENCES Usuario
)

For example, I am able to insert in Linea_Pedido_Articulo in my all empty database which is an N-N relationship, the values I wanted, when it should show an error.

I have found this similar question, but it is not well answered I think.

Thanks for helping.

The problem is in the FK constraint, because they are not enabled. I am using DB Browser for SQLite, so you only have to go here in order to enable it:

Then just check that Foreign Keys is enabled:


SQL Server: how to select rows when changes happen? I have a question how to select row when changes happen.
Here is the table:

CreationDate | Value
2019-06-04   | apple
2019-06-01   | apple
2019-05-30   | apple
2019-04-01   | orange
2019-03-01   | apple
2019-02-01   | apple
2019-01-01   | orange

If value doesn't change, then remove from the table.
I sort by date from new to old.
I want select value changes rows.

I expect a table like this:

CreationDate | Value
2019-05-30   | apple
2019-04-01   | orange
2019-02-01   | apple
2019-01-01   | orange

I want a table of which changes happened.

This should get you what you want.  You want to look into using the LEAD function.

DECLARE @T TABLE
    (
    CreationDate DATE NOT NULL
    , [Value] VARCHAR(10) NOT NULL
    )

INSERT INTO @T
    (CreationDate, [Value])
VALUES ('6/4/2019', 'apple')
    , ('6/1/2019', 'apple')
    , ('5/30/2019', 'apple')
    , ('4/30/2019', 'orange')
    , ('4/1/2019', 'orange')
    , ('3/1/2019', 'apple')
    , ('2/1/2019', 'orange')

;WITH CTE_Source AS
    (
    SELECT TOP 100 PERCENT CreationDate, [Value], LEAD([Value]) OVER (ORDER BY (SELECT NULL)) AS PriorValue
    FROM @T AS T
    ORDER BY T.CreationDate, T.[Value]
    )
SELECT CreationDate, [Value]
FROM CTE_Source
WHERE PriorValue IS NULL OR PriorValue <> [Value]


Error while creating CMK(column master key) in always encrypted I am trying to explore SQL 2016 always encrypted feature but i am getting an error while generating new column master key, after entering name and selecting 'Windows Certificate Store-Current User' as key store through SSMS when i click on ok its throwing an error as  object reference not set to an instance sql(sqlmanagerui)
 I googled it and checked the permission on registry but unable to solve this.

These kind of errors occur when there is something corrupt in windows, so please try repair sql server by running setup and also please do check if there is any pending reboot or an updates pending.
I have face same kind of problem and it was due to pending update so its something to deal with the registry


ORA-12154: TNS: Could not resolve the connect identifier specified I am running a script that "creates/replaces" a procedure in a database and then runs the procedure. The code is as below:

WHENEVER SQLERROR EXIT

UNDEFINE g_cust_pwd
UNDEFINE g_db_name

SPOOL 'output'

PROMPT setup_and_run.sql begin

ACCEPT g_db_name CHAR PROMPT 'Please enter the DB instance: '
ACCEPT g_cust_pwd PROMPT 'Please enter the TEST_SCHEMA schema password: ' HIDE

PROMPT Testing connection

CONNECT TEST_SCHEMA/&&g_cust_pwd@&&g_db_name

WHENEVER SQLERROR CONTINUE

SET SERVEROUTPUT ON

SET DEFINE OFF

PROMPT
PROMPT @procedure_test.prc
PROMPT Creating or Replacing Procedure
@procedure_test.prc

PROMPT
PROMPT procedure_test
PROMPT Running Procedure...
EXEC procedure_test

PROMPT setup_and_run.sql complete

SPOOL OFF

UNDEFINE g_cust_pwd
UNDEFINE g_db_name
/

Whenever I run the code above with the correct credentials (double, triple checked these to make sure that this isn't the issue). The code works fine, as expected, the problem occurs whenever I run the code for a second time in the same SQLPlus session it gives a TNS error.

Normally, I would assume that this is merely a TNS names error and the database needs to be added to the list, but it works only once per session (even if I add a disconnect at the end)

In line 21:

SET DEFINE OFF

But you never re-enable it. When you run the script the 2nd time, substitution variables are not replaced with values provided by the user, because DEFINE is still OFF. Just add the below to the start of your script, so variables will be used in consecutive runs:

SET DEFINE ON


How to design a parent-child relationship that includes child priority/order I am trying to design a table with a string column in SQLite3, I believe this is necessary to connect the tables parent to child, which represent entity types that are involved in a connection with a one-to-many (1:M) cardinality ratio. Te relevant data should be ordered by priority, something like this:

"1|3|5|6"

...this example contains these order values:

1, 3, 5, 6

Is it a good design strategy to create other two tables, one containing the order of the child and the other containing the child connection with the parent.id?
Current considerations
Here's a picture to clarify the situation, illustrating the design options I have evaluated so far:

Is there a better way to represent this kind of scenario?

If I understand your question correctly, your existing "parent" table has a column that contains a string that represents the ID values from a "child" table, and those ID values are in a meaningful order. The question, then, is how to move those parent-child relationship representations out of a string with meaningful order, and into a more normalised database design.

If this is the goal, then I suggest your second design (DB#2), with a minor change, might work best for you.

In summary, a single new table can represent the relationships between child and parent records, with one column for each of those foreign keys. (You already have this in the design). The order of those relationships can be represented in an additional column. (You already have this in the design). The only change I would make is to remove the ID (first) column - there is no need for it.

If a child may only be represented once in relationship with its parent, then make your primary key across (idParent, idChild). If a child can be represented many times against its parent (ie. because it can occupy multiple places in the ordering of the relationships), then make the primary key across all three columns (idParent, idChild, priority).

Per your design, the parent table can drop its obj#3 column (ie. the one that currently contains all the strings).

I see no reason to introduce an extra table to represent the ordering / priorities.

This design allows for two levels in your hierarchy: parent and child. If you have a requirement for multiple levels (such as a person belongs to a department which belongs to an organisation), the question becomes more complex and there are some interesting articles out there on how to model such open-ended hierarchies. Otherwise, with the information presented, I recommend database #2 and, as mentioned, the first column is not required.


How does RANK() work in this query? I want to delete duplicate rows from a table. I have searched for the query online, and found the following sample:

DELETE
FROM dbo.LeadsContacts
WHERE Sr_No IN
(
    SELECT Sr_No
    FROM (
        SELECT *,
        [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC)
        FROM dbo.LeadsContacts
    ) T
    WHERE T.Rank>1
);

I don't understand the flow and working of the query.  Can somebody please explain it works?

RANK() OVER (PARTITION BY xxx, yyy ORDER BY zzz) provides a number ranking for each row where xxx and yyy are identical, and orders that list by zzz

The sub-query contained in your question:

SELECT Sr_No
FROM (
    SELECT *,
    [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC)
    FROM dbo.LeadsContacts
) T
WHERE T.Rank>1

returns a list of Sr_No values where the corresponding rows have duplicate Email and Campaign_Name column values, excluding the most-recent row.  The most recent row is excluded by the WHERE T.Rank>1 statement, in combination with the RANK() ... ORDER BY clause return rows sorted in Sr_No reverse order (the DESC) keyword.

I used the following code to test your query against a small sample of rows:

USE tempdb;

CREATE TABLE dbo.LeadsContacts
(
    Sr_No INT NOT NULL
    , Email VARCHAR(255) NOT NULL
    , Campaign_Name VARCHAR(255) NOT NULL
);

INSERT INTO dbo.LeadsContacts (Sr_No, Email, Campaign_Name)
VALUES (1, 'test@test.com', 'campaign1')
    , (2, 'test@test.com', 'campaign1')
    , (3, 'test@test.com', 'campaign1')
    , (4, 'test@test.co.uk', 'campaign1')
    , (5, 'test@test.co.uk', 'campaign1')
    , (6, 'test@test.com', 'campaign1')
    , (7, 'test@test.co.uk', 'campaign2');

SELECT *
FROM dbo.LeadsContacts;

DELETE
FROM dbo.LeadsContacts
WHERE Sr_No IN
(
    SELECT Sr_No
    FROM (
        SELECT *,
        [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC)
        FROM dbo.LeadsContacts
    ) T
    WHERE T.Rank>1
);

SELECT *
FROM dbo.LeadsContacts;

--DROP TABLE dbo.LeadsContacts;

The only rows left in the table are:

    5, 'test@test.co.uk', 'campaign1'
    6, 'test@test.com', 'campaign1'
    7, 'test@test.co.uk', 'campaign2'


Performance issues when extracting scatter plot data First off: Sorry for the title, couldn't think of anything better. Feel free to change it though.

Ok, assume I have the following table:

 -----------------------------------------------------------------------------------
| id | category | value | foreign_id_1 | foreign_id_2 | foreign_id_3 | foreign_id_4 |
 -----------------------------------------------------------------------------------
|  1 | A        |     4 | 1            | 1            | 1            | 1            |
|  2 | B        |     9 | 1            | 1            | 1            | 1            |
|  3 | A        |     5 | 2            | 2            | 2            | 2            |
|  4 | A        |     6 | 3            | 3            | 3            | 3            |
|  5 | B        |     8 | 3            | 3            | 3            | 3            |
|    |          |       |              |      ...     |              |              |
 -----------------------------------------------------------------------------------

Now I want to create a scatter plot for the categories (A as x, B as y based on the value column).
The desired output would be:

 ----------------------
| foreign_id_1 | x | y |
 ----------------------
| 1            | 4 | 9 |
| 3            | 6 | 8 |
 ----------------------

Row 3 from the original table isn't contained, because there only is a value for A and not for B.

What I've tried so far is the following:

SELECT A.foreign_id_1, A.value as x, B.value AS y
FROM
  (
    SELECT value, foreign_id_1, foreign_id_2, foreign_id_3, foreign_id_4
    FROM table
    WHERE category = 'A'
  ) AS A
JOIN
  (
    SELECT value, foreign_id_1, foreign_id_2, foreign_id_3, foreign_id_4
    FROM table
    WHERE category = 'B'
  ) AS B
ON
  (
    A.foreign_id_1 = B.foreign_id_1
    AND A.foreign_id_2 = B.foreign_id_2
    AND A.foreign_id_3 = B.foreign_id_3
    AND A.foreign_id_4 = B.foreign_id_4
  )

That all works fine, but it's really slow (30s with my current data set and that might expand in the future). The problem seems to be that the only way to identify the "matching rows" (the ones that form x and y of a data point) is expensive, because all foreign keys have to match.

Now to the question:

Assuming that I cannot change the table structure, is there a way to make this query faster? Or maybe there is a different way to get the desired result that I couldn't think of?

There is no reason to use derived tables for this query. It can affect performance badly with MySQL's primitive optimizer. Try this query:

SELECT
    a.foreign_id_1, a.value AS x, b.value AS y
FROM
    table AS a
  JOIN
    table AS b
      ON  a.foreign_id_1 = b.foreign_id_1
      AND a.foreign_id_2 = b.foreign_id_2
      AND a.foreign_id_3 = b.foreign_id_3
      AND a.foreign_id_4 = b.foreign_id_4
WHERE
      a.category = 'A'
  AND b.category = 'B' ;

If the efficiency of the query is crucial, you can add a covering index, with the columns in tailor made order, especially for the query:

ALTER TABLE tablename
  ADD INDEX category_foreign_ids_value          -- pick a name for the index
                                                              -- columns from:
    (category,                                                -- the WHERE
     foreign_id_1, foreign_id_2, foreign_id_3, foreign_id_4,  -- the JOIN ... ON
     value) ;                                                 -- the SELECT


How to track overhead of Change Tracking in SQL Server We're going to implement a solution based on Change Tracking feature
from SQL Server.
Before going in production (even after), we'd like to keep track of the overhead
the CT will introduce.

Once in production, we'd like to have a counter / event / DMV telling us how much time
SQL Server is spending to "keep track" of changes.

Our current (production) installation have tens of (multi-gigabyte) databases (on every instance) and, even if the overhead is low on a single DB, we should measure how much it
will be on several Databases.

Thank you
Igor.

http://technet.microsoft.com/en-us/library/hh710064.aspx talks about monitoring Change Tracking, however it neglects to mention anything about performance other than "Change Tracking is lightweight".

It seems you're out of luck...


Differential backups in SQL Server 2008 R2 I created a maintenance plan to backup all user databases (full) and set them to expire after three days.

This has proved to be cost inefficient in regards to disk space so I want to switch to differential backups.

I then created a maintenance plan to backup all user databases (differential) and set them to expire after seven days.

I had thought the first time this job ran that the first backup would have to be full but it's not, it's significantly less than what a full backup should be.

I thought it would work like this:

Day 1: Full

Day 2-7: Differential

Then on day one it would do another full backup and start all over.

Am I just misunderstanding how differential backups work?

I mean what did the plan back up differentially if it didn't do a full backup first?

Thanks!

Differential backups include data that has changed since the last full backup (ignoring any full backups taken with the COPY_ONLY option).

If you take a differential backup immediately after a full backup, it will be very small, as little (or no) data will have changed. As time goes on, the differential backups will become larger and larger, until you do another full backup, which resets the differential change map.

Note that you must still have the last full backup taken prior to the differential backup, or the differential backup will be useless.


Join 3 tables with where and sorting clauses I have 3 tables, vendors, vendor_modelsand cameras. I am trying to get some values from db all of vendor_models but name from vendor too.

Each camera has a reference of vendor_model as model_id.

10 cameras can have one same vendor_model.

I am doing this query

SELECT v0."id", v0."vendor_id", v0."exid", v0."name", v0."username",
   v0."password", v0."jpg_url", v0."h264_url",  v0."mjpg_url", v0."mpeg4_url",
   v0."mobile_url", v0."lowres_url", v0."shape", v0."resolution",
   v0."official_url", v0."more_info", v0."audio_url", v0."poe",
   v0."wifi", v0."upnp", v0."ptz", v0."infrared", v0."varifocal",
   v0."sd_card", v0."audio_io", v0."discontinued", v0."onvif",
   v0."psia", v0."channel", v0."created_at", v0."updated_at"
FROM "vendor_models" AS v0
INNER JOIN "vendors" AS v1 ON v0."vendor_id" = v1."id"
WHERE ((lower(v0."name") LIKE $1))
  OR (lower(v1."name") LIKE $2)
ORDER BY v1."name" ["%%", "%%"]

after getting all those values. I am doing another query. such as

SELECT c0."id", c0."owner_id", c0."model_id", c0."exid",
   c0."name", c0."timezone", c0."thumbnail_url",
   c0."is_online", c0."offline_reason", c0."is_public",
   c0."is_online_email_owner_notification", c0."alert_emails",
   c0."discoverable", c0."config", c0."mac_address",
   c0."location", c0."last_polled_at", c0."last_online_at",
   c0."created_at", c0."updated_at"
FROM "cameras" AS c0
WHERE (c0."model_id" = $1) [4]

which then give me the total number of cameras which are using this model.

my problem is I want to merge this query to in single one.

where I can get all the values of VendorModel as well as vendor. and also the count of cameras which are using each vendor model. which I am unable to do right now any help would be appreciable.

Update:

I have got here

SELECT vendor_models.id, vendor_models.name, count(cameras.id) as count
  FROM "vendor_models"
  INNER JOIN "vendors" ON "vendor_models"."vendor_id" = "vendors"."id"
  INNER JOIN "cameras" ON "vendor_models"."id" = "cameras"."model_id"
  GROUP BY vendor_models.id, vendor_models.name
  ORDER BY count desc

But I cannot get vendor.name.. can you help?

Update:

SELECT vendor_models.id, vendor_models.name, v.name, count(cameras.id) as count
  FROM vendor_models
  INNER JOIN vendors as v ON vendor_models.vendor_id = v.id
  INNER JOIN cameras ON vendor_models.id = cameras.model_id
  GROUP BY vendor_models.id, vendor_models.name, v.name
  ORDER BY v.name asc

I got the final one. can you help to make it better?

Still room for improvement. This is your last version, untangled, formatted and shortened with table aliases:

SELECT vm.id, v.name, vm.name, count(c.id) AS count
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id
JOIN   cameras       c  ON c.model_id = vm.id
GROUP  BY vm.id, v.name, vm.name
ORDER  BY v.name;

Remaining problems:

You have the name name twice in the result, which is unhelpful to say the least.
(A shining example why non-descriptive names like "name" are bad names.)
Sort order not fully deterministic.
Not as fast as it could be.

I suggest:

SELECT vm.id
     , v.name AS vendor, vm.name AS model  -- column aliases to disambiguate
     , c.camera_count
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id
JOIN  (
   SELECT model_id, count(*) AS camera_count  -- count(*) slightly faster
   FROM   cameras
   GROUP  BY 1           -- aggregate first, join later
   ) c ON c.model_id = vm.id
ORDER  BY v.name, vm.name, vm.id; -- add "id" if the names are not unique

If you count cameras per model first, you have far fewer join operations and don't have to aggregate outer columns at all. Should be faster.

count(*) is a bit faster than count(expression) (separate implementation) while doing the same in the absence of NULL values.

Since columns from the outer tables (vendors, vendor_models) are not aggregated at all now, you can put any of these columns in the SELECT list at will - including vm.*.

Related:

Understanding multiple table join with aggregation
Slow queries related to subqueries using aggregation


Ways to invoke methods on CLR datatypes I have difficulty understanding when to use .method() and when to use ::method() in SQL Server.

For example

For HierarchyID data type
I have seen HierarchyID::GetRoot(), but also @sample_node.GetAncestor(1);
For Geometry data type,
There is GEOMETRY::parse() that is used to insert rows. But there is also methods such as @sample_geometry.ToString() to output data with better readability.

What are the differences between them?

The difference is between CLR static methods (type_name::method()) and instance methods (instance.method())

Static methods are defined on the type itself and are generally utility methods that get everything they need to operate passed in as method parameters.

Instance methods operate on an object of a particular datatype and are able to access the private state of that object and mutate the object or return a result that uses that state.

HierarchyID::GetRoot() always returns the same result.

@sample_node.GetAncestor(1); will return a different result dependent On what @sample_node is.


Can I decrypt TDE transaction log files for use with 3rd party software? Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to recover some lost data.

Is there a way to decrypt TDE log files on disk for use with 3rd party software?
The short answer is -- this cannot be done.

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.
[...] When the data is written from buffer pool to disk (MDF, LDF & NDF) the data is first encrypted and then then encrypted data will be sent to disk.
When the data is read from  the disk, first the data will be decrypted and then it will be sent to buffer pool.

Taken from How to enable/remove Transparent Data Encryption (TDE).
See also SQL Server 2008 Transparent Data Encryption.


Separation of users columns in another table I've got a quite a lot of columns on the users table and I have been cleaning it up recently. Now since I'm searching over the users I figured it would improve performance if I separated verification columns from user. I'm talking about these kind of columns :

verified_telephone_number
verified_telephone_by_id
verified_photo_by_id
verified_photo_source
verified_photo
verified_photo_at
video_call_requested
skip_video

So I was thinking to create verifications table with each user having one verification and verification having these above attributes and some more.

Is there a downside to this approach? I mean for one, in terms of count, there will be verifications rows = users rows(count). Instead of having these extra columns.

Such a 1:1 mapping is sensible when part of the table is heavily accessed and part of it is lightly used.

It's a good choice.


SQL Server Chaining Many-To-Many tables I have a scenario where we want to build an inventory description system and I am trying to design the schema but need a little guidance to make sure i'm not veering off the track.
The basic premise is: A combination of three locations (Selling location, Shipping Location and Destination Location) along with the Item being sent, is used to determine how something should be packaged and sent. It gets a little more complex with breaking this down into levels (Level1: Put item in box. Level2: Tape box up etc...) but still manageable.

By the time we get to the Packing level, we're dragging all the fields in to make a PK using five fields to make a unique record.

My question is, is there a reasonable limit on key field length. Does it make sense to provide a surrogate key for each group and then just just that going forwards?

So the UniqueLocationLookup would have a single field used as an identifier in the ItemLookup Table.

The Combination of UniqueLocationID and Item would then become another unique key used by LevelLookup and so on...

The stacked keys approach (my name for it), where you carry the combination of keys through your tables, tends to pile up very quickly and become unwieldy. I think you are beginning to see this with the example in your question.

So, if you don't want to have to drag every key around for all your relations, then then the answer to the surrogate key question is "yes".

There is a caveat, however. You want to make sure that within each of your lookup tables that you enforce uniqueness on the combinations of lookup table items (in SQL Server, use a UNIQUE KEY). For instance, from the example diagram below:

LocationLookup table:

PK: LocationLookupID
UK: LocationIDSales, LocationIDShip, LocationIDSend

ItemLookup table:

PK: ItemLookupID
UK: LocationLookupID, ItemID

LevelLookup table:

PK: LevelLookupID
UK: ItemLookupID, LevelID

Look how much simpler life becomes when you do this!

Note: When using surrogate keys, I think it is always a good idea for every table to also have at least one unique key constraint.


Difference between High Availability data synchronization across local replicas and DAG I see a significant difference when synchronizing data between Primary - Secondary and between Primary HA and Secondary HA as part of Distributed Availability Groups.

The difference in the amount of data sent across the wire. When making a significant amount of changes on Primary including change tracking enabled tables, we see a huge jump in the amount of data in Redo Queue on all local replicas (synchronous - compression disabled and asynchronous-compression enabled). But across DAG, redo queue is much lower and processing much faster without any significant backlog.

I want to understand on a deeper technical level the difference between local synchronization and DAG.

The specific questions:
- What is the difference between synchronizing from Primary to Secondary and across DAG (amount of data)
- Can we enable local synchronization to work similar to the one across DAG
- What factors that affect the amount of data  sent across the wire beside the obvious ones - activity on the DB and HA compression enabled/disabled

What is the difference between synchronizing from Primary to Secondary and across DAG (amount of data)

The main difference is that in a Distributed AGs, there is a "forwarder" which is the primary replica on the secondary side. The data is transferred once across the wire to the forwarder and then sends it to the replicas in its' cluster. My guess is that you've also set the Distributed AG up as asynchronous, which would also show differences over synchronous.

  Can we enable local synchronization to work similar to the one across DAG

I'm not sure what you mean, here.

  What factors that affect the amount of data sent across the wire beside the obvious ones

Generally it can be traced back to insufficient disks, high latency networks, overloaded VMs, etc. The way that applications are written, to an extent such as the size and scope of transactions can play a large part too. There are too many variables so I listed the main ones.


GROUP BY with subqueries for a Group? Here's a simplified recreation of what I have:

CREATE TABLE "DocumentStore" (
  "ID" int NOT NULL,
  "Content" jsonb NOT NULL,
  CONSTRAINT documentstore_pk PRIMARY KEY ("ID")
);

INSERT INTO "DocumentStore"
VALUES (1, '{ "ID": 1, "Group": 0, "RecordNumber": 0, "When": 1490280300 }');

INSERT INTO "DocumentStore"
VALUES (2, '{ "ID": 2, "Group": 0, "RecordNumber": 1, "When": 1490280400 }');

INSERT INTO "DocumentStore"
VALUES (3, '{ "ID": 3, "Group": 0, "RecordNumber": 2147483647, "When": 1490280500 }');

INSERT INTO "DocumentStore"
VALUES (4, '{ "ID": 4, "Group": 1, "RecordNumber": 0, "When": 1490280600 }');

INSERT INTO "DocumentStore"
VALUES (5, '{ "ID": 5, "Group": 1, "RecordNumber": 2147483647, "When": 1490280700 }');

INSERT INTO "DocumentStore"
VALUES (6, '{ "ID": 6, "Group": 2, "RecordNumber": 0, "When": 1490280900 }');

INSERT INTO "DocumentStore"
VALUES (7, '{ "ID": 7, "Group": 2, "RecordNumber": 1, "When": 1490281000 }');

I'm looking to break things down by Group and get a result set that looks something like this:

+-------+------------+------------+-------+
| Group |    Start   |    Stop    | Count |
+-------+------------+------------+-------+
|   0   | 1490280300 | 1490280500 |   3   |
+-------+------------+------------+-------+
|   1   | 1490280600 | 1490280800 |   2   |
+-------+------------+------------+-------+
|   2   | 1490280900 |    NULL    |   2   |
+-------+------------+------------+-------+

Start and Stop are Unix timestamps. I just grabbed ones from this morning and rounded out the numbers for simplicity.

A group will always have a "RecordNumber" = 0 entry so if there's a group, there's a start time. A group that doesn't have an entry with "RecordNumber" = 2147483647 should not have an end time.

I'm trying to summarize every Group by looking up it's Start time by finding the jsonb document for that Group that has a RecordNumber of 0 and using it's When value, and look up it's Stop time by finding the jsonb document for that Group that has a RecordNumber of 2147483647 and use it's When value, and finally a count of how many documents are in that Group.

I started with this:

SELECT
    "Content"->>'Group' AS "Group",
    (SELECT "Content"->>'When' FROM "DocumentStore" WHERE ("Content"->>'RecordNumber')::int = 0)::int AS "Start",
    (SELECT "Content"->>'When' FROM "DocumentStore" WHERE ("Content"->>'RecordNumber')::int = 2147483647)::int AS "End",
    COUNT("Content") AS "DetailCount"
        FROM "DocumentStore"
        GROUP BY "Group";

It works if all the documents in the table belong to the same Group. As soon as there are 2 groups my subqueries become a problem: more than one row returned by a subquery used as an expression. I tried adding WHERE "Content"->>'Group' = "Group" to the subqueries but it seems I can't do that (column "Group" does not exist).

I'm thinking this might be a chance to join a table with itself but I'm not sure where to begin with that.

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group",
         min(case when Content->>'RecordNumber' = '0'
                  then Content->>'When'
                  else null
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647'
                  then Content->>'When'
                  else null
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

As Evan Carroll pointed out, you can also take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions in the Postgres docs.

select   Content->>'Group' as "Group",
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620


How to handle the My SQL DB Master to Master replication when there was a disk space outage We had master to master replication with two node nodes A & B, both are in virtual environment. Initially there was an outage (diskspace issue) in node A and stopped replication. Application level traffic has been diverted to node B and relocated the node A data store and made available of space.

Node A has started successfully & up. Coming to the replication has started and got an error in node B while syncing with node A and bin log in node B got corrupted. The root cause is still a mystery. But analyzing the logs could found some duplicate entries and below is the error log info :

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236).
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
111014 20:25:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.

How do we handle the replication in this situation? Can we skip the current bin-log and start the replication with the next available bin-log and its position. Is it a good idea to sync node B with node A:

CHANGE MASTER TO
  MASTER_HOST='XX.XX.XXX.XXX',
  MASTER_USER='replicate',
  MASTER_PASSWORD='slave',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.001025',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

The main reason I am posing this scenario is, that I don't want to restore the DB and build it from scratch, where DB backup is around 80 GB in node B. How can I repair the replication?

There are two things that need to be mentioned here:

1) The word impossible is a dead giveaway. Client requested master to start replication from impossible position. Here is what this essentially means:

It just so happens that the file size of a binary log and the position of the binary logs are one and the same. The slave wants to read from mysql-bin.001067 position 183468345. Since the word 'impossible' comes up in the message, this indicates that the master binary log mysql-bin.001067 is less than 183468345 bytes. To get replication going again, skip to the next binary log:

CHANGE MASTER TO
  MASTER_HOST='XX.XX.XXX.XXX',
  MASTER_USER='replicate',
  MASTER_PASSWORD='slave',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.001068',
  MASTER_LOG_POS=NewPos,
  MASTER_CONNECT_RETRY=10;
START SLAVE;

NewPos is dependent on the version of MySQL.

MySQL 5.6 : NewPos = 120
MySQL 5.5 : NewPos = 107
MySQL 5.1 : NewPos = 106
MySQL 5.0 : NewPos = 98
MySQL 4.x : NewPos = 98

2) You could just look into using data synchronization tools from Percona.

mk-table-checksum
mk-table-sync

I have used these tools for about 2 years and they help you hunt down differences in tables between master and slave, even if the table on the master is InnoDB and the same table on the slave is MyISAM (provided the tables have the same table structure).

Replication must be on while running these tools.

BTW Percona has a new set of tools called the Percona Toolkit. They forked away from their own MAATKIT tools to make better ones. The tools are probably called pt-table-checksum and pt-table-sync.


Sum values at a group level rather than detail I have an invoice report I'm developing for some project billing. The main tablix has several groups, in this order:

ProjectGroup
TicketGroup
NoteGroup
Details (actual billing details)

The data can end up looking something like this:

project_id  ticket_id   note_id     hours        billing_agreement_id billed_hours
----------- ----------- ----------- ------------ -------------------- ------------
1           1           1           0.50         4                    0.75
1           1           2           0.50         5                    1.00
1           1           3           1.00         7                    1.00
1           1           8           5.00         7                    3.00
1           1           8           5.00         2                    2.00
2           2           4           4.00         1                    4.00
2           2           5           2.00         6                    3.00

In other words, a ticket is assigned to a project, notes are entered on a ticket (with a corresponding number of hours worked), and note hours are then billed to one or more agreements/contracts (if a block time agreement is exhausted, the remainder may spill over to another agreement, for example). This is all working fine, as far as the application is concerned.

So now I'm creating the invoice report, and I need to provide a grand total of hours worked at the end of the report. Take a look at the 4th and 5th rows (with note_id = 8). The report footer uses Sum(hours), which then ends up counting that note as 10 hours. Both note_id and hours come from the note row, and should be summed accordingly. Including hours in the grouping expression for NoteGroup has no effect on the behavior of Sum, naturally.

Is there a straight-forward way to make Reporting Services sum up hours, looking only at the grouped values for that group, rather than the detail rows? I can't think of anything particularly elegant as of yet. I can fall back to weird custom code stunts if necessary.

Well, after a lot of experimenting, I couldn't find a good way to do it in Reporting Services. I tried using a custom function to accumulate total hours (called in the cell displaying the hours in the NoteGroup header), then another custom function in the table footer which would retrieve the value, but the accumulated values would reset on page breaks.

In the end, I've mangled the query into assorted subqueries. It sucks, but it works. Would be sweet if you could do something like SUM(hours) OVER(DISTINCT note_id PARTITION BY client_id)...


Error 823 and no connection One of our servers tells us it has Error 823 issues:

  TITLE: Connect to Server

  Cannot connect to ServerName.
  ADDITIONAL INFORMATION: Warning: Fatal error 823 occurred at Mar  1 2016 >
  10:45AM. Note the error and time,
  and contact your system administrator. (Microsoft SQL Server, Error:
  21)

The windows log is floated with the same message.

No connection to the sql server is possible. So DBCC CHECKDB cannot be used / executed.

I ran SQLIOSIM and it raised couple of errors that requests are outstanding for more than n sec on a specific drive.

A Similar issue happened last night on another system - and it looks like it was workarounded by a failover.

I don't know where to look at next since I cant even connect to the Sql Server.

SQLIOSIM has identified at least one disk that is failing. You could look in the system event log for errors or warnings that concern the same disk subsystem's IO. The active SQL Server errorlog (C:\Program Files\Microsoft SQL Server\MSSQLx\MSSQL\Log, which is a text file), will also contain the 823 error. The 823 error being raised in the system event log could concern a driver. If SQL Server is failing to start, one of the system databases' disk(s) is likely to have failed.

Failing over SQL Server resets the driver and sets up a new IO path, but failover clustering is designed for high availability, not fault tolerance. Each time an IO failure occurs, a database can become corrupted, which database backups can backup. You should restore your backups on a DR server, run checkdb upon the restored backups. In the meanwhile, ask your system administrator to resolve the disk IO failures. The longer you wait, the higher the risk of corruption being backed up.


Optimise MySQL SELECT with LEFT JOIN subquery I have a query which combines a LEFT JOIN and subquery. The dataset is quite big and the time to execute the statement is over 70 seconds.

SELECT
    s.siblings,
    l.id
FROM
    `list` l
        INNER JOIN
    child c ON c.id = l.child_id
        INNER JOIN
    parent p ON p.id = c.parent_id
    LEFT JOIN (
      SELECT COUNT(c.id) AS siblings, c.id, c.parent_id
      FROM child c
      GROUP BY c.id
    ) AS s ON s.parent_id = c.parent_id AND s.id != c.id
WHERE
    l.country = 1
GROUP BY l.id, s.siblings
ORDER BY l.dateadded

This query should return all lists for a country. Each list is specific to a unique baby. For each list I would like to return a count of the number of children that have the same parent.

If I remove the LEFT JOIN subquery the fetch time is 0.1 seconds. Is there a way to make the query more efficient?

The main reason for the slow query is the join on a subquery. This will not use indexes. Then, you not only join with a derived table (subquery), but as well group total result based on subquery column - GROUP BY l.id, s.Siblings

In this case it could help to:

create temporary table from subquery, it also could include subquery
for return correct parent_id
create index on this table
use temporary table in join
drop temporary table

This could have variants, but it is often faster and less server-loading than a complicated set of subqueries with joins.


SQL Rebuild indexes data size percent a little smaller I see a small change int the percent of data when rebuilding. I feel that this might be a usual issue but as I am new to SQL I get little worried when I see that the percent of data goes down after running the rebuild. I used the data usage report to get the percent of data.

I think what happen here is that some data got reorder during the index rebuild. Which in turn free up a small percent of space. I was not expecting this because my research did not indicate that this would happen. That is why it got me worried that there might be data loss.

I have confirmed from a good copy of the db that the number of records is either the same or garter in the copy where I rebuilt the indexes. Let me if I'm wrong.

SQL Server Internal Fragmentation is caused by pages that has too much free space. for example at the beginning of the day we have a table with 50 pages that is 100% full, but by the end of the day we have a table with 60 pages that is only 80% full because of various delete and insert statements throughout the day. This may lead to a decrease in performance because now when we need to read from this table we have to scan 60 pages instead of 50. Index maintenance tool and additional information here.


page_verify none by default I am reviewing freshly generated scripts out of SSDT publish tools.  These projects were built from a default template, as were the publication profiles.  They set page_verify none for the database.

I was under some hazy impression that the best practice would be to have it set to checksum.

The only thing I can think of is that the SSDT target platform is Azure.  Can someone clear this up with something authoritative?

Thanks

The default options that ssdt chooses often aren't best practices, instead you should choose what you want. For page_verify I agree checksum but it is up to you:

http://www.sqlskills.com/blogs/glenn/setting-your-page-verify-database-option-to-checksum/

Incidentally if you import from a production (or similar environment) and you choose "Import database settings" the actual settings will be imported from the database - if it is a new database I would get your dba's to create an empty one on a dev instance with their standard build etc.

Ed


Return every instance of a string contains search I have this sample table and data

CREATE TABLE [dbo].[SampleTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TextColumn] [nvarchar](1000) NOT NULL,
 CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[SampleTable] ON

INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (1, N'This is the first Test string')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (2, N'This is the second one')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (3, N'This is the first really long string of text that should be included in the result set.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (4, N'This is the second long string that will not be returned in the result set')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (5, N'This is a really really long result set that should also be first included in the result set.  It has a seperate sentence in it as well.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (6, N'Now this is a really really first one.   It is so long that I have forgotten how long it really was.   Well it could be really long but first lets do this.  ')
SET IDENTITY_INSERT [dbo].[SampleTable] OFF

With The Below Query

;with cte as (SELECT
      a.Id ,
      a.TextColumn,
      CASE When LEN(TextColumn) <= 60
          Then TextColumn
          ELSE LEFT(TextColumn, 60) + '...'
          END As Target,
          charindex('first', TextColumn) as SearchPos
  FROM
       SampleTable a
       WHERE Contains((TextColumn), '%first%'))

select *, case when SearchPos > 20 then substring(TextColumn, SearchPos - 20, 20) + 'first' +
substring(TextColumn, SearchPos + len('first'), 20) else substring(TextColumn, 1, SearchPos-1) + 'first' + substring(TextColumn, SearchPos + len('first'), 20)
 end as NewString
from cte

I am trying to return every instance of a word with a Contains search.  But the string could be in the middle of a large sting.  So I am returning the string and a given length to the left and right of the searched string.  Sort of like a summary.  If the word occurs multiple times it is only returning the first instance and moving on to the next instance.  How can I get it to return all instances even multiple ones in the same cell.  So the item with Id of 6 should return twice not once because I am looking for the string 'first'.  I am using Sql Server 2012. The New String Column Is What I am shooting for.

Use following query if you want to count the number of occurrence of the string 'first' in the TextColumn each row.

SELECT (LEN(TextColumn) -
LEN(REPLACE(TextColumn, 'first', '')))/LEN('first') as NumOfOccurrence,
TextColumn FROM dbo.SampleTable;

Update: To cover the trailing space case (as caught by Twinkles in the following comment), I modified the query as below.

SELECT (LEN(TextColumn) -
LEN(REPLACE(TextColumn, 'a ', '')))/(LEN('a ' + 'x') - 1) as NumOfOccurrence,
TextColumn, id FROM dbo.SampleTable;

Following code gets the multiple instances in a New String Column, with separator defined as @sep and search word defined as @word, where @sep <> @ word:

declare @sep as char(2);
declare @word as varchar(24);
set @sep = ', ';
set @word = 'first';

SELECT  ID, TextColumn, ISNULL(STUFF(REPLICATE(@sep + @word, (LEN(TextColumn) -
LEN(REPLACE(TextColumn, @word, '')))/(LEN(@word + 'x') - 1)), 1, 1, ''), '')
as [New String Column] FROM dbo.SampleTable;


Running parallel delete statements Problem: I have a massive table - occupies a whopping 9.5 TBs. I need to delete around 5 mil rows from it. The best method I have come up with deletes 1 million records in 2 hours! Since I can not run this during peak time, I get only 2 hour window everyday and deleting 5 mil rows would take me 4 working days at this rate.

Question: I was wondering if SQL Server has a feature where I could run 2 or more deletes on a table simultaneously. Possibly something like virtually partitioning the table - not sure.

I also want to add that running my script induces page locks but keeps the transaction log from growing. My script is very similar to the one suggested by vonPryz:

How to Delete From Large Tables in Batches Without Growing the Transaction Log by Greg M Lucas.

Let me try to summarise the answers as per the commenters above.

First of all, to achieve higher deletion speeds you can do three things:

Delete a higher number of rows at a time
Use partitioning
Run multiple deletes

Ad 1) Since deleting a large number of rows at a time hit the transaction log hard and causes locking - you likely want the number of deletes in a statement to be "small". I find that around 10K rows is a good number (and having table lock escalation turned off with ALTER TABLE Foo SET LOCK_ESCALATION = Disabled

Ad 2) If your table is not already partitioned, this is not an option

Ad 3) DELETE in SQL Server is single threaded, so you need to run more than one at a time to get max speed. To run multiple deletes concurrently, you need a way to partition them so each parallel delete runs on its own set of keys and doesn't block with the others. Typically, you can use the primary key of the table to partition the delete.

For example, if you have an IDENTITY column called key on the table, you can first:

SELECT MAX(key) - MIN(key), MIN(key) FROM Foo WHERE <rows that must be deleted>

Keep the Max - Min and Min somewhere (a table in tempdb) that you can quickly read from into variables @IntervalSize, @MinKey respectively.

Let us say you decide that you can allocate 4 cores for running deletion. You now run 4 queries, either from 4 x SSMS new query or four command prompt via SQLCMD, each doing this:

DECLARE @NumDone INT = 1
WHILE @NumDone > 0 BEGIN
  SET ROWCOUNT 10000

  DELETE FROM Foo WHERE Key BETWEEN @MinKey + @IntervalSize / 4 * @n
     AND @MinKey + @IntervalSize / 4 (@n + 1)
  SET @NumDone = @@ROWCOUNT
END

Pick @n = 0,1,2,3 for each of the concurrently running DELETES.


There's a long delay in this query that I need to resolve This is a multi-tenant/portal DNN (formally DotNetNuke) database, and a core query as part of the registration process seems to be terribly slow.

Here it is:

declare @PortalID int = 0;

SELECT R.*,
    (SELECT COUNT(*)
     FROM   dbo.[UserRoles] U
     WHERE  U.RoleID = R.RoleID
            AND U.Status = 1
            AND (U.EffectiveDate < GETDATE() OR U.EffectiveDate IS NULL)
            AND (U.ExpiryDate > GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
FROM  dbo.[Roles] R
WHERE (R.PortalId = @PortalID OR R.PortalId is null)
      AND (R.RoleId >= 0)
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId UNKNOWN))

I'll be honest, I haven't used optimization options before. What's critical here is it's a multi-portal site so what I change I have to be absolutely sure it doesn't spring up a nasty gotcha down the road.

I'm assuming the execution plan has to accommodate potential nulls in the @PortalID that of which is a parameter within the stored procedure. My example code above is extracted and I'm setting the @PortalID to 0.

The core developers created this procedure, and response times to get assistance vary so I'm asking here.

Is there a risk to changing this optimization to:

OPTION (OPTIMIZE FOR (@PortalId = 0))

This is the main portal used and is by far the most important. The core developers have to account for sites that have hundreds or even thousands of portals.

Even so, changing this leaves no guarantee of future upgrades as they may overwrite it. Just trying to track down a problem and want to know of a fail-safe way to optimize this query.

So if I set:

OPTION (OPTIMIZE FOR (@PortalID = 0))

It runs fast... 10x faster.

But I'm uncertain of caching or other problems that may arise from changing this.

Testing the Impact that OPTION(OPTIMIZE FOR @PORTALID = 0) could have

Create the tables

CREATE TABLE dbo.[Roles]
(
RoleID INT PRIMARY KEY,
PortalId INT,
RoleName
varchar(255)
)

CREATE TABLE DBO.[UserRoles]
(
UserRoleID INT IDENTITY(1,1),
RoleID INT FOREIGN KEY(RoleID) REFERENCES dbo.roles,
Status int default (1),
EffectiveDate datetime2,
ExpiryDate datetime2
)

Insert some data into roles

INSERT INTO DBO.Roles(RoleID,PortalId,RoleName)
VALUES(1,0,'Role1') -- 1 value with portal id = 0

DECLARE @ID INT = 2
WHILE @ID < 1000
BEGIN
INSERT INTO DBO.Roles(RoleID,PortalId,RoleName)
VALUES(@ID,1,'Role2') -- 998 rows with @PortalID = 1

SET @ID += 1
END

Insert some data into UserRoles

DECLARE @ID INT = 1
WHILE @ID < 1000
BEGIN

INSERT INTO DBO.[UserRoles](RoleID,EffectiveDate,ExpiryDate)
VALUES(@ID,getdate(),NULL) -- one user role for each role id.

SET @ID += 1
END

Testing the query with @PortalID = 0 and @PortalID = 1

@PortalID = 0

SET STATISTICS IO, TIME ON;
declare @PortalID int = 0
SELECT R.*,
    (SELECT COUNT(*) FROM dbo.[UserRoles] U
     WHERE U.RoleID = R.RoleID
     AND U.Status = 1
     AND (U.EffectiveDate < GETDATE() OR U.EffectiveDate IS NULL)
     AND (U.ExpiryDate > GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
 FROM dbo.[Roles] R
 WHERE (R.PortalId = @PortalID OR R.PortalId is null)
AND (R.RoleId >= 0)
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId = 0))

The ususal scan's since we don't have indexes

The expectations match 100%

@PortalID = 1

declare @PortalID int = 1
SELECT R.*,
    (SELECT COUNT(*) FROM dbo.[UserRoles] U
     WHERE U.RoleID = R.RoleID
     AND U.Status = 1
     AND (U.EffectiveDate < GETDATE() OR U.EffectiveDate IS NULL)
     AND (U.ExpiryDate > GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
 FROM dbo.[Roles] R
 WHERE (R.PortalId = @PortalID OR R.PortalId is null)
AND (R.RoleId >= 0)
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId = 0))

That's gonna hurt on bigger tables

Low expectations

What if we run it without the optimize for? Spoiler: (This creates a new execution plan)

declare @PortalID int = 1
SELECT R.*,
    (SELECT COUNT(*) FROM dbo.[UserRoles] U
     WHERE U.RoleID = R.RoleID
     AND U.Status = 1
     AND (U.EffectiveDate < GETDATE() OR U.EffectiveDate IS NULL)
     AND (U.ExpiryDate > GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
 FROM dbo.[Roles] R
 WHERE (R.PortalId = @PortalID OR R.PortalId is null)
AND (R.RoleId >= 0)
ORDER BY R.RoleName

That's better

TL;DR

As is usual, YMMV.

What will happen when implementing something like this depends on the indexes, the data, and all the other things SQL Server takes into account when creating an execution plan. How much the query is executed with certain parameters, ....

This is just to show what might happen if you implement OPTION (OPTIMIZE FOR (@PortalId = 0)) on a production system.
In the end it is up to you to test the possible outcomes, and what is best for your system.
You could opt to use the query as it is and deal with parameter sniffing, to use optimize for unkown so the SQL Server engine will use stats and other heuristics to optimize its execution plan, to use OPTION(RECOMPILE) as to not cache the plan but create it on each execution, to use OPTION( OPTIMIZE FOR @PortalId = 0), but deal with queries that using a different value, as they might go slower, ... .


Query to get reposts from people the user is following I have a table posts where all the posts by users are stored, the structure of this table is as follows.

| post_id | post_user | post_content | post_date |

The users table is as follows.

| user_id | username | user_joined |

User relationship table is as follows.

| follower | following | rel_date |

This is the query I am using to get the posts from people that user is following to show them.

SELECT * FROM posts
WHERE post_user in (
    SELECT follower
    from user_follow
    where following=$loggedin_user)

Now I want users to share posts, for which I created a table repost_user as follows.

| repost_user | original_post_user | post_id | repost_date |

SELECT p.*,r.repost_userr.repost_date,IF(r.post_id>1,'true','false') FROM posts p
left join repost_user r on p.post_id = r.post_id
left join user_follow u on p.post_user = u.follower
where following=$loggedin_user;

Regarding the performance implications of using union all in place of joins, please see this answer on Stack Overflow:

Inner Join versus Union All


UUID or BIGSERIAL Foreign Keys Postgres I have 8 tables which use UUID Primary Keys and 7 of them have UUID Foreign Keys. Would it be better for performance to use BIGSERIAL instead of UUID?

Use uuid if you need it.
Don't use it if you don't.

bigint is smaller and a bit faster in multiple ways. 8 vs. 16 bytes do not sound like much, but the difference stacks up for big tables and multiple instances. (And rarely would either uuid or bigint make sense for small tables to begin with.)

bigint is typically also much easier on the human eye and for human handling and for representation when numbers don't get that big after all. UUID always requires 32-character representation (optionally plus dashes).

If you have two of those (PK & FK) in the same table, rows grow by 16 bytes. That may make a difference for narrow rows, or be negligible for wide rows. More importantly, associated indexes also grow in size. While the PK index for a bigint occupies 16 bytes per row (incl. 8 byte index tuple overhead), it's 24 bytes for uuid. If RAM is not available in abundance, that can mean the difference of several indexes residing in RAM vs. being evicted and read back from storage repeatedly. And that would be a substantial difference.

Do you even need bigint? Often, a 4-byte integer is good enough (if it's really good enough) ...

Related:

What is the optimal data type for an MD5 field?


Check for duplicate data from called functions I'm new to SQL and am not sure how to go about getting the result set I am after. Below is the closest I have gotten to my desired result set although I am seeing duplicate data.

I have a stored procedure

Select
o.cFirstName+' '+o.cLastName [owner], o.nContactUID [oUID],
p.cFirstName+' '+p.cLastName [Process], p.nContactUID [pUID]
from (
     SELECT

         cFirstName,
         cLastName,
         nContactUID
     FROM  dbo.ip_contact_owner(@facilityID)
        union
     SELECT

         cFirstName,
         cLastName,
         nContactUID
     FROM
     dbo.ip_contact_owner_alternates(@facilityID)
 ) as o,
 (
     SELECT
        cFirstName,
        cLastName,
        nContactUID
     FROM  dbo.ip_contact_process(@facilityID)
        union
     SELECT
        cFirstName,
        cLastName,
        nContactUID
     FROM
     dbo.ip_contact_process_alternates(@facilityID)
 ) as p

is calling the below table-valued functions for each owner, owner alternate, process and process alternate:

ALTER FUNCTION [dbo].[ip_contact_owner](@facility varchar(100))
RETURNS TABLE

AS
  RETURN  (

  SELECT
 f.nFacilityUID,
 c.cFirstName,
 c.cLastName,
 c.nContactUID
 from dbo.ip_ContactAssignments as a
 join dbo.Contacts as c on a.nContactUID = c.nContactUID
 join dbo.Facilities as f on a.nFacilityUID = f.nFacilityUID
 join dbo.ip_ContactPermissions as p on a.nPermissionUID = p.nPermissionUID
 where
      a.lActive = -1 and
      c.lActive = -1 and
      f.lActive = -1 and
      a.lPrimary=-1 and
      p.cDescription = 'owner' and
      f.nFacilityUID = @facility
 )

ALTER FUNCTION [dbo].[ip_contact_owner_alternates](@facility varchar(100))
RETURNS TABLE

AS
  RETURN  (

  SELECT
 f.nFacilityUID,
 c.cFirstName,
 c.cLastName,
 c.nContactUID
 from dbo.ip_ContactAssignments as a
 join dbo.Contacts as c on a.nContactUID = c.nContactUID
 join dbo.Facilities as f on a.nFacilityUID = f.nFacilityUID
 join dbo.ip_ContactPermissions as p on a.nPermissionUID = p.nPermissionUID
 where
      a.lActive = -1 and
      c.lActive = -1 and
      f.lActive = -1 and
      a.lPrimary = 0 and
      p.cDescription = 'owner' and
      f.nFacilityUID = @facility
 )

My result set currently looks like this (owner, ownerID, process, processID):

Lisa Simpson |348392000000267 | Bart Simpson   | 348392000000347
Lisa Simpson |348392000000267 | Homer Simpson  |  348392000000350
Lisa Simpson |348392000000267 | Maggie Simpson |  348392000000306

I would like for it to return like this

Lisa Simpson |348392000000267 | Bart Simpson   | 348392000000347
NULL         |NULL            | Homer Simpson  |  348392000000350
NULL         |NULL            | Maggie Simpson |  348392000000306

Basically, there will always be 1 owner and 1 process name returned but 0 to many owner and process alternates. How do I eliminate the duplicate data?

I'm on SQL Server 2008

Right now your code is doing a cartesian join between derived tables o and p. If derived table o contains M rows and derived table p contains N rows then your result set will M x N rows with none of the column values being NULL unless your TVFs return NULL rows. You need to add a join clause between o and p to get the result set that you want.


Oracle multiple databases on same server I want to be able to import multiple database dumps locally and be able to switch between them.

What I would like to achieve:

Import dumps (objects+data) from multiple databases to local database
Switch between imported databases (db1 and db2) locally

db1 and db2 have users/schemas/objects/etc with same name and this shouldn't cause a conflict.

Expected result would be following:

connect with one USERNAME/PASSWORD and query tables from schema named MYSCHEMA and then db1 data would be used.
"switch" active database (by passing some additional parameter on connecting) or executing some command.
now same USERNAME/PASSWORD combination and querying tables from MYSCHEMA should return data from db2

You can use the Multitenant option, create a container database, create 2 pluggable databases called db1 and db2, then import the first dump file to db1, the other dump file to db2.

You can switch between pluggable databases with as:

alter session set container=db1;
alter session set container=db2

But this requires at least version 12c, and it is an extra cost option.

For lower versions, or without the Multitenant option:

Simply create 2 traditional databases on the server: db1 and db2. Import one dump to db1, the other to db2. You can specify in your connection string the service (or SID) for the database you want to connect to. For example:

sqlplus user/password@hostname/db1
sqlplus user/password@hostname/db2


I couldn't compile this little query (needs 2 tables)(mysql) i have 2 tables as like seen :

user table  :   id, userid , country

friends table :  id, userid , friendid

so i want to get friendid rows from friends table where friends.userid == user.userid  and user.country='USA' ?

thanks for your helps

select f.friendid
from friends f
  inner join user u
    on u.userid = f.userid
where u.country='usa'


Creating a table from the existing table along with the constraints in oracle I have table emp and I want to create a duplicate table from the emp table. I tried the below query, which is copying all the table columns, data and the not null constraints.

create table emp_backup as select * from emp.

Is there any way to duplicate a table along with all constraints?

Here is the base table:

create table emp
(
empno number Primary key,
ename varchar2(50),
deptno number,
constraint emp_deptno_fk foreign key(deptno) references dept(deptno)
)

I need to create a table emp_backup from the emp along with emp_deptno_fk constraint.

You may try to obtain the source table's DDL using:

select dbms_metadata.get_ddl( 'TABLE', 'SCHEMA_NAME', 'DATABASE_NAME' ) from dual;

Select it into a variable, replace table name to one you need (maybe edit or remove something else) and execute.


Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row" I'm currently making SQL that'll update data from 1 table, based on data from another table for my company

update table1
set column1 = (SELECT color
                FROM table2
                Where table1.id = table2.id)

My goal is to make table1's column 1 to consist of the colors from table 2, however the id is not unique, instead it's cvr of the companies, therefore they appear multiple times giving me the error "single-row subquery returns more than one row". The duplicates can have different colors however, making me unsure on how to tackle this, any suggestions?

Also it would seem that while duplicates can be differnet colors, in 99% of all instances they don't, so if the first isn't possible, would there be a way for me to update, so all duplicates have same color?

The set column1 = clause requires a single value as the error message indicates.  Sounds like you have a data model problem, but if you need a quick fix, your select query can be modified to return a single value; the following a unique color from table2 if more than one color matches your where clause:

update table1
set column1 = (SELECT distinct color
                FROM table2
                Where table1.id = table2.id
                and rownum = 1
              )

If I had more time, I'd add another level to find the most common color that matched, and returned the first row.

Updated subquery to return first distinct color - thanks to RDFozz for pointing this out.


remove Analysis service from a SQL 2014 cluster Is it possible to uninstall Analysis service from a SQL 2014 cluster. I’ve seen that it cannot be done in 2008/12. I’m hoping that this maybe fixed now

I would suggest not removing it. Why do you need to remove it? If you aren't going to use it anymore, I would just remove it as a cluster resource (and any dependencies), and then disable the service on each node. No need to uninstall anything except to save a tiny amount of disk space. IMHO, anyway.


merge multiple fields of csv file while running mysqlimport command I have a mysql table like this

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| roll_no        | varchar(50)  | NO   | MUL | NULL    |                |
| marks          | varchar(100) | YES  |     | NULL    |                |
| academy        | varchar(100) | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

the marks column can have multiple numbers separated by comma like this

45,67,78,80, # First Example
34,56,  # Second Example

I have to run mysqlimport command such that the fields after the first fields till second-last fields should merge into marks column

I tried something like this

mysqlimport --fields-terminated-by=, --columns='roll_no, marks, academy' --ignore --local -u root -pxxxx result results.csv

But extra fields (after 3rd one) gets truncated. How to handle such scenarios where the csv fields are dynamic

Because it is a CSV file (Comma Separated Values), it thinks that the each of the marks is a different field. Your initial export should either separate values by a different char, like "#", or quot the values bu ".


In SQL Server, how do read locks work? Suppose I have the following long-running query

UPDATE [Table1]
SET [Col1] = 'some value'
WHERE [Col2] -- some clause which selects thousands of rows

and suppose the following query is executed while the above query is running

SELECT *
FROM [Table1]

Does the first query prevent the second query from running until the first query is done? If so, does the first query prevent the second query from running on all of the rows or just the rows involved in the WHERE clause?

EDIT:

Suppose the second query is

SELECT [Col1], [Col2]
FROM [Table1]
WHERE [Col2] -- some clause whose matching elements overlap those from
             -- the clause in the first query and which has additional matching elements

I recommend you read Understanding how SQL Server executes a query, it has an explanation of how reads and writes work and how locking works.

The 10000ft view goes as follows:

read operators acquire shared locks on the data they read, before reading the data
write operators acquire exclusive locks on the data they modify before modifying the data
data locks are just strings, eg. a hash of the key being read scoped by database and object.
the lock manager maintains a list of all locks granted and detects incompatibilities, according to the Lock Compatibility matrix
incompatible requests are suspended until the incompatible grant blocking them is released
operators use a lock hierarchy to declare intent to read or update data at higher level (page or table level, ignoring partition level options). This allow operators to lock entire tables w/o locking every individual row
lock lifetime and range locks are used to enforce higher isolation levels

This is really just the tip of the ice berg. The subject is vast. In your example, nobody can answer your question about what is being actually locked because it will depend on many factors. Of course, no application should issue a SELECT * FROM Table1  because it's missing a WHERE clause and is using *. These are bad practices because, among other things, they will lead exactly to lock contention.

If you encounter read vs. write locks you need to look into row versioning and snapshot isolation. Read Understanding Row Versioning-Based Isolation Levels.


Two aggregated sub queries wont run I am having trouble with the below, I need to average two columns in a correlated subquery and I cannot get it to work, how do i get both aggregate sub queries to run then go through the outer query? I am not even sure I am on the right path so any guidance appreciated.

Question: Display the ID, Name and average Exam and Coursework weights for all lecturers and make sure all the columns have suitable headings, using a correlated query. Investigate use of the ISNULL function to produce suitable output in the case of a lecturer not teaching any modules.

SELECT l.StaffID, l.FirstName, l.Surname, m.ModuleDescription
FROM dbo.Lecturer as l
RIGHT OUTER JOIN dbo.Module as m
    ON l.StaffID = m.ModuleConvenor
WHERE m.ExamWeight = SELECT AVG(m.ExamWeight) AS 'Avg ExWeight'
                     FROM dbo.Module as m2
                     WHERE m2.ModuleConcenor = l.StaffID OR m2.ModuleConvenor IS NULL)
WHERE (SELECT AVG(m.CWWeight) AS 'Avg CWWeight'
    FROM dbo.Module as m3
    WHERE m3.ModuleConcenor = l.StaffID OR m3.ModuleConvenor IS NULL)

Try this, it should help put you in the right direction.

SELECT l.StaffID,
       l.FirstName,
       l.Surname,
       m.ModuleDescription
 FROM dbo.Lecturer as l
 RIGHT OUTER JOIN dbo.Module as m
   ON l.StaffID = m.ModuleConvenor
WHERE m.ExamWeight = (
            SELECT AVG(m2.ExamWeight) AS 'Avg ExWeight'
              FROM dbo.Module as m2
             WHERE m2.ModuleConcenor = l.StaffID OR m2.ModuleConvenor IS NULL)
 AND m.CWWeight =  (
    SELECT AVG(m3.CWWeight) AS 'Avg CWWeight'
     FROM dbo.Module as m3
    WHERE m3.ModuleConcenor = l.StaffID OR m3.ModuleConvenor IS NULL)

I did the following

Updated your where clause syntax
Update your aggregate function typo from AVE to AVG
Updated your alias being used in the aggregate function in both subqueries

You may need to post some sample data and table ddl information. It is not clear to me how the module and lecturer tables should be joined based on your query. This answer helps correct your syntax, it may not be full solution to what you are trying to achieve.


Are utf8_bin and utf8_general_cs the same? Here's the situation:

I have come across several posts for "utf8_bin vs utf8_general_ci".
Similarly, "utf8_general_cs vs utf8_general_ci".

But I can't find a post for "utf8_bin vs utf8_general_cs". So, are they the same? If not, what's the difference between them?

Note: I have checked that utf8_general_cs is not available in default MySQL. May I know why?

The notion that binary collations are the same as case-sensitive collations is, unfortunately, an extremely common one.

However, they are very much not functionally equivalent. There are four areas where behavioral differences can be seen (at least four that I am aware of):

Combining Characters

Consider having a lower-case ü ("u" with diaeresis) and an upper-case Ü ("U" with diaeresis). Both types of collations will be able to distinguish them.

Now, consider having an upper-case U and a ̈ (combining diaeresis). When the combining character is placed after the non-accented U, you get Ü. Visually it is the same as the single Ü ("U" with diaeresis). And a case-sensitive (and even accent-sensitive) collation will seem them as being the same, even though one is a single code point and the other is a combination of two code points. But a binary collation cannot compare them as being equal since they are not the same code points (or even the same number of code points).
Fullwidth Characters

A collation that is case-sensitive but width-insensitive would be able to equate =ｏ= and =o=. However, a binary collation cannot seem them as equal since they are different code points.
Accent Insensitivity

A collation that is case-sensitive but accent-insensitive would be able to equate o and ô. However, a binary collation cannot seem them as equal since they are different code points.
Sorting

A case-sensitive collation will sort ~ before a and A after it. But, a binary collation will sort those same characters as: A then a then ~.

All of this is documented in the following post of mine:

No, Binary Collations are not Case-Sensitive

It is presented in terms of Microsoft SQL Server, but the behavior is based on rules defined by the Unicode standard, which should be the same across RDBMSs, languages, OSs, etc (with minor variation due to which version of the Unicode Standard is being used, and who did the implementation since Unicode is only a standard and has been implemented with slight variation across vendors).

  I have checked that utf8_general_cs is not available in default MySQL. May I know why?

My guess is that the "general" collations are obsolete and have been superseded by newer "unicode" and culture-specific collations. The documentation (half-way down the page, paragraph starting with "For any Unicode character set,") even states:

  utf8_general_ci is a legacy collation that does not support expansions, contractions, or ignorable characters. It can make only one-to-one comparisons between characters.

The "unicode" collations are probably the default sort weights and collation rules. The culture-specific collations tailor the weights and rules to be correct for that culture (when the default is incorrect). For more info on why there are different collations, please see my answer to the following S.O. question:

What is the point of COLLATIONS for nvarchar (Unicode) columns?