Is it normal that a data flow task's OnError event handler fires 3x and how to avoid that? In an SSIS package there are event handlers on every data flow tasks which send an email with a brief description of the error. I fail the first task by introducing an error condition and get 3 emails.

Is there any way to avoid spamming the support staff with 3x the error messages and just send the first one (or if not possible, any one of the three)?

The first one is related to the actual SQL error condition:

SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"
Hresult: 0x80004005
Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"
Hresult: 0x80004005
Description: "Violation of PRIMARY KEY constraint 'PK_Amount'. Cannot insert duplicate key in object 'dbo. Amount'. The duplicate key value is (2013, 10, 25000.00).".

It is followed by the second one:

SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "Report.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Report.Inputs[OLE DB Destination Input]" specifies failure on error.
An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the failure.

And finally the last one:

SSIS Error Code DTS_E_PROCESSINPUTFAILED.
The ProcessInput method on component "Report" (29) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (42).
The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
There may be error messages posted before this with more information about the failure.

I already experimented with replacing each task's OnError with the single package's OnError but that one does not seem to have access to the error's pertinent information.

Worst part is that the 1st two emails are arriving in random order. The 1st one arrives 1st slightly more often, but still randomly. The 3d one seems to be always last.

In my SSIS packages, I enclose the entire control flow in a sequence container, with a "Send Mail" task of the container's "on failure" flow.

In my error handlers, I collect the error information and append it to a string variable.

Then, in the "Send Mail" task, I include the string with the accumulated error information in the body of the message.

Now, this only works as is if the failure in your data flow is fatal to the package as a whole. If you're (for example) processing several files, and want to send out one error message for each file that fails its processing, you'd need to modify this to send the mail at the correct time, and clear the error detail variable; however, the same principle should apply.


Clobbered default database in postgreSQL I created a script to drop a particular database and then reload just the structure. I created it using phppgsql.

I'm still new to pgsql, so I simply ran the script using \i myCreateScript.sql with the psql client. What happened was that it clobbered the default postgres database, and now all databases are utterly unusable. I didn't figure out until later that I was supposed to do \c myDatabase first to set the current database.

So.. What do I do now? I'm running on a linux server. Should I purge postgres and re-install? Is there another way to recover from this?

Thanks in advance.

Your question is very unclear (see comment) but I'm going to make a guess at it anyway.

I think you DROPped the postgres database. You're used to always connecting to PostgreSQL as the postgres superuser using the postgres database, probably via sudo and peer authentication with something like:

sudo -u postgres psql

where you don't specify a database name to connect to. So with no postgres database, you don't know how to connect anymore. You get an error like:

psql: FATAL:  database "postgres" does not exist

If so, you just need to connect to a different database and re-create the postgres database. It only exists as a convenient scratch area - the system doesn't actually care if the postgres database exists or not, and doesn't use it for anything.

Something like:

sudo -u postgres psql template1 -c 'CREATE DATABASE postgres;'

will do the trick.

If you dropped the template1 database as well, you could still recover by connecting to any other database on the server and re-creating template1 from the locked, protected template0 database; see the documentation on template databases.

Key things to understand:

You don't have to connect to the postgres database. It doesn't get used for anything except a default place to connect to when using the postgres user. If you DROP it the system doesn't care.
psql connects by default to the database with the same name as the current user. You can just pick another one with psql databasename.
psql connects by default with the username of the current unix user. You can just pick another one with (eg) psql -U someuser. This will work fine if pg_hba.conf is configured to permit the connection.
While the postgres user is the default superuser, you can create another one, and you don't have to rely on peer authentication to use it either; you can use md5 password auth or whatever you want. So while you're probably used to just using sudo -u postgres psql you can actually set things up in a variety of flexible ways. I frequently create a database superuser for my regular unix user account (CREATE USER myuser WITH SUPERUSER ENCRYPTED PASSWORD 'blah';) when I'm on a scratch/development box, so I'm not messing about with sudo all the time.


Does replication with SSL encryption work on MySQL 5.5? I am trying to set up replication with slave on Amazon EC2 and master in our local environment. The setup went fine without SSL encryption. But when I set up the replication to be SSL encrypted, it is failing to connect to Master.

MySQL version on Master : Server version: 5.5.19-log MySQL Community Server (GPL)
MySQL version on Slave  : Server version: 5.5.14-log MySQL Community Server (GPL)

I have SSL encrypted replication set up and going fine where the Master's MySQL version is Server version: 5.1.56-log Source distribution. But it is failing for the other Master machine which has MySQL 5.5.

So my question is, does SSL encrypted replication work for MySQL 5.5?

Anyone here faced the same problem? Please throw some light on how to get around this problem. Note that I have changed the permissions for my SSL certificates used in Slave machine to be mysql instead of root (Both failed anyway)

Thanks.

To use SSL for encrypting the transfer of the binary log required during replication, both the master and the slave must support SSL network connections. If either host does not support SSL connections (because it has not been compiled or configured for SSL), replication through an SSL connection is not possible.

Setting up replication using an SSL connection is similar to setting up a server and client using SSL. You must obtain (or create) a suitable security certificate that you can use on the master, and a similar certificate (from the same certificate authority) on each slave.

For more information on setting up a server and client for SSL connectivity refer this link

Please follow the steps mentioned here:

http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html

I was searching in bugs.mysql.com related to MySQL 5.5 but there is nothing reported w.r.t "replication with SSL" for v5.5.
You can mention exact error message here for analysis.


Backup Functions in PgAdmin 4 Version 1.6 How to backup all functions in pgadmin 4 version 1.6?
After executing the below query the backup functions stored in which place?
And how to find it?
How to know all information is backed up with what's being done?

SELECT pg_get_functiondef(f.oid) || E';\n'
FROM pg_proc f
   JOIN pg_namespace n ON f.pronamespace = n.oid
WHERE n.nspname = 'schemaname';

This is not backing up your functions, it is just selecting the text of them.  You could copy the result from pgAdmin and save it to a text file manually if you want.

If you take a full backup of your database the functions will be backed up along with the data.


Why does PostGIS create an ugly table in my database called "spatial_ref_sys"? I need to use PostGIS, and do use it, but it bothers me that it created this ugly "spatial_ref_sys" table in my database, in the main (public) schema, where I keep all my own tables. It looks ugly.

It also has added a trillion "functions", which is not a big problem to me since I don't have any "functions" of my own, or use them, but the same principle stands for this as well.

Why, given that it's an extension/"PG library" of sorts, doesn't it at least use its own schema, as to separate itself from my own stuff? I assume that there is a very good reason, but I can't figure it out.

Why, given that it's an extension/"PG library" of sorts, doesn't it at least use its own schema, as to separate itself from my own stuff?

If you want it to use a separate schema, you can put it there:

create schema postgis;
create extension postgis with schema postgis;

Unfortunately you can't move it after the fact, other than by dropping and re-creating, which could be very messy if you are already using it.


Can SET IDENTITY_INSERT be allowed with less privileges than db_ddladmin? I've inherited a system where the application is running under sysadmin account.
I limited this account to db_datareader + db_datawriter + EXECUTE on all database and set extended events session to catch the permission errors on the server.
It was very surprising to me to see some inserts failed even if the user is a member of db_datawriter and no granular restrictions on tables are applied.

Then I note that only inserts that set IDENTITY_INSERT failed.
The database in question is full of identity and there is too much SET IDENTITY_INSERT in their code.  The code means not only modules stored on server but also C# code.

To be able to set identity_insert user must own the table or have ALTER permission on the table. But the fact is that you CANNOT grant ALTER only on tables, I'm forced to grant ALTER on a whole database to this user
(I'll make this user member of db_ddladmin role that will add only 42 permissions(!!!)  vs 51 permissions that could be added to user permissions by granting ALTER on database)

I'm not interested in refactoring the database with sequence (server version is 2014 so I could do it theoretically) and I cannot just add execute as dbo to every sp that use identity_insert because there is also application code to rewrite, I just wonder WHY does Microsoft make such a strange permissions design that db_datawriter cannot set identity_insert?

Is there other way to make the user be able to set identity_insert that will add less permissions than db_ddladmin?

UPDATE

I tried the solution offered by LowlyDBA with synonyms:

create table dbo.t(id int identity);
go

alter schema sch transfer dbo.t;
go

create synonym dbo.t for sch.t;
go

set IDENTITY_INSERT dbo.t on;

This causes the error

  Msg 1088, Level 16, State 11, Line 1 Cannot find the object "dbo.t"
  because it does not exist or you do not have permissions.

The solution of Antoine Hernandez to grant the user ALTER only on schema seems to be le lesser of evils, so I accept it

I suggest you create a custom database role by right-clicking the database role folder.

That will bring up the Database Role that you can then customize. On the first screen you can give the role the name and owner of your choosing. You also can add the user you want to be a member of this role.

Once that is done, you can click on Securables so you can then choose what to grant.

Add Objects will show up and I suggest picking the "All objects of the types..." option and click OK.

In the Select Object Types, scroll down to find Schemas, check the box, and then click OK.

Now you will see a list of schemas so choose the schema(s) that has the objects you want to grant permissions to and it will show the permissions for that schema below.

For this example, I chose dbo schema. I resized the default screen to show more at once.

In this case I would most likely choose to grant Alter because according to Microsoft, "When granted on a scope, ALTER also bestows the ability to alter, create, or drop (emphasis mine) any securable that is contained within that scope." Based on the infographic about database permissions, located here and below granting Alter on schema will grant alter on Aggregate, Default, Function, Procedure, Queue, Rule, Synonym, Table, and View:Since I am at this stage, for this same role, I would probably also grant Execute as well so the role member could also execute any stored procedure so when new tables or stored procedures are added to the database, I would not have to modify any security to allow the new objects to work like the existing objects. So the selections would look like this once complete:

I could have taken it a step further and also granted Select, Insert, Update, and Delete, and it most likely would have covered everything the role would require to read, insert, delete, and update any table as well as execute any stored procedure without needing to grant all the other permissions that the db_ddladmin role grants. By doing this at the schema level it can ease the management of security in some ways, especially when a particular user needs to have a lot of if not all access at the schema level, but also satisfies the requirement of not granting alter to the whole database. If there were other permissions this configuration grants that the role does not need, you could then go add DENY permissions as needed.


It just means that the text of the statement contained the string "password" and SQL Server "helpfully" has masked it as a security feature to prevent you seeing some one else's password.

I was able to reproduce this as follows

CREATE TABLE T(X varchar(1000))

Connection 1

BEGIN TRAN

INSERT INTO T VALUES('password1')

WAITFOR DELAY '00:01:00'

SELECT * FROM T WHERE X = 'password2'

ROLLBACK

Connection 2

BEGIN TRAN

INSERT INTO T VALUES('password2')

WAITFOR DELAY '00:01:00'

SELECT * FROM T WHERE X = 'password1'

ROLLBACK

Then retrieving the graph from the extended events trace


newbie question - how to list people who has not email address related to him/her? Sorry for my newbie question: I would like to list all people even those who has no email address in case of using many-to-many relation with superclass-sublclass inheritance.
Please see SQL Fiddle: http://sqlfiddle.com/#!17/8f556/8
Postgresql lists only the people who has email...
I tried this query:
select people.c_id as "ID",
    people.c_family_name as "Family Name",
    people.c_last_name as "Last Name",
    email.c_email as "Email"
from
    t_email email,
    t_people people
    left join t_ref_email ref_email on ref_email.c_party = people.c_id
where ref_email.c_party = people.c_id
  and ref_email.c_email = email.c_id;

Result:
ID  Family Name Last Name   Email
1000    Test    Joe test_email@email.com

Test Bill was not listed because he has no email address...

Aliases should be short and meaningful
select pe.c_id as "ID",
    pe.c_family_name as "Family Name",
    pe.c_last_name as "Last Name",
    em.c_email as "Email"

from
t_people pe
LEFT JOIN t_ref_email re ON re.c_party = pe.c_id
LEFT JOIN t_email em ON em.c_id = re.c_email

With the result of
ID      Family Name     Last Name   Email
1000    Test            Joe         test_email@email.com
1001    Test             Bill        (null)


Full Text Search Term Casing Affects Results I have been experiencing a full text problem when searching using CONTAINS. I have the full text catalog accent sensitive setting set to false.

The table contains following rows:

Microsoft C#
Microsoft Visual C
Microsoft C

When I try to get "Microsoft C#" using the following query:

select name
from fulltexttable ft with (nolock)
where contains (
        ft.name
        ,N'microsoft and c#'
        )

...it yields the following results:

Microsoft Visual C
Microsoft C

I tried using different casing. It yields the different result set:

select *
from fulltexttable with (nolock)
where contains (
        name
        ,N'microsoft and C#'
        )

Result:

Microsoft C#

The second one is what I expected. I thought SQL Server performed a case insensitive search to get the data, but it seems casing affects the full text results?

This is documented although I couldn't find a reference in Books Online:

  The rules for characters followed by nonalphanumeric characters are
  somewhat convoluted (at least in English). The English word breaker
  accepts the token C# and returns C#. The lowercase token c#, however,
  is indexed as c with the # character stripped off. The uppercase token
  C++ and lowercase token c++, on the other hand, are both indexed as
  c++.

"Pro Full-Text Search in SQL Server 2008" - Michael Coles, Hilary Cotter, p106

You can check the behaviour of your search phrase using the sys.dm_fts_parser DMV, eg

SELECT * FROM sys.dm_fts_parser ( 'microsoft AND c#', 1033, 0, 0 )
SELECT * FROM sys.dm_fts_parser ( 'microsoft AND C#', 1033, 0, 0 )

As a workaround, you could force all strings through as upper-case, use some kind of mapping table for common search terms, or design a strategy to ensure non-alphanumeric characters are never stored or queried as part of the full-text index.

Note the behaviour is more consistent in SQL 2012 (where both lower and upper case will return only 'Microsoft C#').


Can a subscriber be a publisher at same time I want to setup a topology for replication.

The scenario is such that a subscriber is subscribed to transaction replication based publisher (distributor running on publisher).

I then want this subscriber to be a publisher to another subscriber. I want the distributor to be on publisher instance (push).

Can some body help me out?

Yes, you can achieve the goal and implement replication topology. What I understood from your requirements is depicted in the diagram.

To do this follow these steps:

Enable distribution on Server 1.
Create replication, add articles and choose server 1 as publisher and server 2 as subscriber.
Once articles are replicated, enable distribution on server 2.
Follow step 2 for servers 2 and 3.


How much "Padding" do I put on my indexes? How do you know how much "Padding" an index should have?

If you're referring to the fill factor of the index, the only legitimate answer that you can get is "it depends." The fill factor of an index can be varied based on how much you'll be inserting into the existing leaf level pages of the index.

It's going to be best to create your indexes using the default settings and monitor the level of fragmentation over a period of time. You should also be routinely performing index maintenance using an index maintenance script. These scripts will automate the process of keeping your indexes in good operating condition.


Correct way to write this query? I'm trying to write a query that basically is searching by price, though sometimes the items may have a discount on them and this price isn't stored in the DB, only the discount percent and dates the discount runs between are, so we have to work out the price of the item on the fly to see if it should be included.

Additionally, if the item is an auction we ignore the sale price and just look at the start_price and max_bid to work out the current price (max bid is only set if the item has at least 1 bid).

We know it is NOT an auction if start_price == buyout_price, so know we have to figure out if the item is on sale or not and if so calculate the sale price to see if to include it in their requested price range.

I have this code..

SELECT
    whatever
FROM
    auctions AS a
LEFT JOIN
    ..............
WHERE
    ..............
AND
    IF(a.start_price == a.buyout_price AND a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_end_date > UNIX_TIMESTAMP() AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) >= '500' AND IF(a.start_price == a.buyout_price AND UNIX_TIMESTAMP() > a.discount_start_date AND UNIX_TIMESTAMP() < a.discount_end_date AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) <= '700'
AND
    ..............;

As you may be able to make out I'm trying to get all results with prices between 500 - 700.

I'm also getting a error which appears to have something to do with the UNIX_TIMESTAMP(), though not sure why!?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== a.buyout_price AND  a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_e' at line 1

Write yourself a view that produces, in a single column, the value of price that you're interested in.  It might be a UNION of a few different criteria.  When you're done you'll have a view of all items with two columns: the id and the price.

Join that view to whatever else you need (perhaps including the original price), and voila.

Tip: skip IF and just use AND and OR, per the SQL standard.  And eschew LEFT JOIN unless you know -- and know why -- you can't use a proper inner join.  If the database is pervasively unreliable in that regard, it will lead to more pain than all the outer joins in the world can alleviate.


connect-mongo latest version return some error in my project I have updated my connect-mongo npm to the latest version 2.0.1 in node v8 its returning some error, if i comment the below code my projects works pretty well if not returning the below error

here is a bit of code

var session = require('express-session');
var MongoStore = require('connect-mongo')(session);
    app.use(session({
        resave: true,
        saveUninitialized: true,
        secret: '6256346324',
        store: new MongoStore({
            db: global.db
        })
    }));

Error

.then(() => this.changeState('connected'))
                     ^

TypeError: this.setCollection(...).setAutoRemoveAsync(...).then is not a function
    at MongoStore.handleNewConnectionAsync

modules used in package.json(i have listed only few)

connect-mongo": "^2.0.1",
    "express": "^4.16.2",
    "express-session": "^1.15.6",
    "mongodb": "^2.2.35"

so how can i solve this error?

As per your error it shows you have Express 4.16.2 and mongodb 2.2.35.

As per NPM Mongo Blog here you can check out the compatibility . As well as for connection integration of Express 4.x.

The sample connection code should be as mentioned below for Express 4.x, 5.0 and Connect 3.x:

const session = require('express-session');
const MongoStore = require('connect-mongo')(session);

app.use(session({
    secret: 'foo',
    store: new MongoStore(options)
}));

For further information see here and here


Upgrading to SQL Server 2017 from SQL Server 2012 time allowed We have a customer running a SQL 2012 database for their document management system but this needs to be upgraded, They want to upgrade to SQL Server 2017 and looking at the forums the best method is side by side.
Can I ask what sort of time should I allow for this type of upgrade - I need to inform the customer of the downtime.. Approximate size of the database is 800GB.

Any help or advise is greatly appreciated.

Backup restore depends on a lot of different factors, the biggest ones being

Your network
Your storage

The best way to ever give a good estimation is to test it.

Setup the second server/dev env, and do the migration as a test, this will give you a very good estimation of the time it will take.
And as a second huge benefit, you'll have all scripts ready + experience for the production migration.


Pivot three fields into one category I have data in this format

    Skill   Min  Ideal  Max
    ==========================
     C++    0      1    1

I want to display it like this:

    Criteria    C++
   =================
     Min          0
     Ideal        1
     Max          1

I am confused at the part where I want to convert Min, Ideal, and Max into criteria.

Will this work for you?  It's not dynamic as you need to know what the skills are ahead of time, but it gets the job done.  I've added another skill to show you how to do that.  You only need to change it in one place.

DECLARE @Table TABLE
    (
      Skill VARCHAR(10) NOT NULL
    , MinValue INT NOT NULL
    , IdealValue INT NOT NULL
    , MaxValue INT NOT NULL
    );

INSERT  INTO @Table
        ( Skill, MinValue, IdealValue, MaxValue )
VALUES  ( 'C++', 0, 1, 1 )
        , ( 'VB', 1, 1, 1 );

SELECT  *
FROM    @Table;

WITH    CTE_Table
          AS ( SELECT   T.Skill
                      , T.MinValue AS Value
                      , 'Min' AS ValueType
               FROM     @Table T
               UNION ALL
               SELECT   T.Skill
                      , T.IdealValue AS Value
                      , 'Ideal' AS ValueType
               FROM     @Table T
               UNION ALL
               SELECT   T.Skill
                      , T.MaxValue AS Value
                      , 'Max' AS ValueType
               FROM     @Table T
             )
    SELECT  *
    FROM    CTE_Table AS C
          PIVOT ( SUM(Value) FOR Skill IN ( [C++], [VB] ) ) AS P
    ORDER BY CASE WHEN ValueType = 'Min' THEN 1
                  WHEN ValueType = 'Ideal' THEN 2
                  WHEN ValueType = 'Max' THEN 3
                  ELSE 3
             END;

Or, using UNPIVOT:

SELECT
    Criteria = P.Name,
    P.[C++],
    P.VB
FROM @Table AS T
UNPIVOT (value FOR name IN (MinValue, IdealValue, MaxValue)) AS U
PIVOT (MAX(Value) FOR Skill IN ([C++], [VB])) AS P;

To make the query dynamic, see this Stack Overflow question and:

Dynamic pivot: sum of sales per month


calculate running balance of debit credit using union all i'm making account statement for which i need that account balance before today and with each transaction  row of debit or credit plus and minus from initial opening balance on the basis debit/credit amount rules....
for this i'm using this approach

SELECT '1'AS VoucherNo,''AS DocumentDate,'Opening' AS Description,isnull(SUM(JournalDetials.Debit),0)AS Debit,
    isnull(SUM(JournalDetials.Credit),0)AS Credit,(isnull(SUM(JournalDetials.Debit),0)-isnull(SUM(JournalDetials.Credit),0))[Balance],
    ''as JournalTransationStatus
    FROM JournalDetials INNER JOIN JournalMains ON JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId=GLMainAccount.Id
    where(JournalDetials.AccountId='001653')AND(JournalDetials.Date<'03/31/2019')

    union all

    select(JournalMains.VoucherNumberPrefix+JournalMains.VoucherNumber)AS VoucherNo,JournalDetials.Date as DocumentDate,
    isnull(JournalDetials.Description,''),isnull(JournalDetials.Debit,0),isnull(JournalDetials.Credit,0),''[Balance],
    ''JournalTransationStatus
    from JournalDetials inner join JournalMains on JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId = GLMainAccount.Id
where(JournalDetials.AccountId='001653' and JournalDetials.Date BETWEEN '03/31/2019' AND '04/09/2020')

I was trying to apply this solution but couldn't able to run this in SQL 2008 due to this. so now, how to calculate running balance total along with each debit credit value in SQL 2008?

Try this (you may need to adjust data types or play with clustered index on temporary table, may be use identity column):
CREATE TABLE #x(
VoucherNo int,
DocumentDate datetime,
Description varchar(255),
Debit numeric(18,2),
Credit numeric(18,2),
Balance numeric(18,2)
);
CREATE UNIQUE CLUSTERED INDEX cx_x ON #x(DocumentDate, VoucherNo);

INSERT INTO #x (VoucherNo, DocumentDate, Description, Debit, Credit)
SELECT '1'AS VoucherNo,''AS DocumentDate,'Opening' AS Description,isnull(SUM(JournalDetials.Debit),0)AS Debit,
    isnull(SUM(JournalDetials.Credit),0)AS Credit
    FROM JournalDetials INNER JOIN JournalMains ON JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId=GLMainAccount.Id
    where(JournalDetials.AccountId='001653')AND(JournalDetials.Date<'03/31/2019')

    union all

    select(JournalMains.VoucherNumberPrefix+JournalMains.VoucherNumber)AS VoucherNo,JournalDetials.Date as DocumentDate,
    isnull(JournalDetials.Description,''),isnull(JournalDetials.Debit,0),isnull(JournalDetials.Credit,0)
    from JournalDetials inner join JournalMains on JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId = GLMainAccount.Id
where(JournalDetials.AccountId='001653' and JournalDetials.Date BETWEEN '03/31/2019' AND '04/09/2020')

DECLARE @Balance numeric(18,2), @DocumentDate datetime, @amt numeric(18,2), @VoucherNo int;
SET @Balance = 0;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR SELECT DocumentDate, VoucherNo, Debit - Credit FROM #x ORDER BY DocumentDate, VoucherNo;

OPEN c;

FETCH c INTO @DocumentDate, @VoucherNo, @amt;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @Balance = @Balance + @amt;
  UPDATE #x SET Balance = @Balance WHERE DocumentDate = @DocumentDate AND VoucherNo = @VoucherNo;
  FETCH c INTO @DocumentDate, @VoucherNo, @amt;
END

CLOSE c; DEALLOCATE c;

SELECT VoucherNo, DocumentDate, Description, Debit, Credit, Balance, '' AS JournalTransationStatus
  FROM #x
  ORDER BY DocumentDate, VoucherNo;

DROP TABLE #x;


Not authorized on test to execute command Here is my code:
mongoose.connect(consts.database, {
    useNewUrlParser: true,
    useUnifiedTopology: true,
    sslCA: consts.databaseCert,
});
//...
const user = await db.userModel.findOne({
    username: usernameLowerCase
}).exec();

Here is my DB connection string (anonymized):
mongodb://myUser:userPW@SG-staging-111.servers.mongodirector.com:27017,SG-staging-43334.servers.mongodirector.com:27017?replicaSet=RS-staging-0&ssl=true&authSource=stagingDB

I'm getting this error:
MongoError: not authorized on test to execute command {
    find: "users",
    filter: {
        username: "bob"
    },
    projection: {},
    limit: 1,
    singleBatch: true,
    batchSize: 1,
    returnKey: false,
    showRecordId: false,
    lsid: {
        id: UUID("0a9400e3-83e3-429c-b8c9-92ade2ff210e")
    },
    $clusterTime: {
        clusterTime: Timestamp(1613200171, 1),
        signature: {
            hash: BinData(0, FED473B580D13E7E5073756DB5140981AADB2985),
            keyId: 6928615819992774977
        }
    },
    $db: "test"
}

DB user's info:
myUser  [{"role":"readWrite","db":"stagingDB"}]

I have no clue why I am getting this error not authorized on test to execute command, and in the return string $db: "test" I don't even have a database named test. What could I be doing wrong? I just recently added this new user myUser, but now I'm getting this error. Does this error mean that the user is not authorized to "test" commands? Or, does it mean that I am trying to (somehow) connect with a DB named "test"?

Turns out that I was missing a /authDBName in my connection string. It should be this:
mongodb://myUser:userPW@SG-staging-111.servers.mongodirector.com:27017,SG-staging-43334.servers.mongodirector.com:27017/stagingDB?replicaSet=RS-staging-0&ssl=true&authSource=stagingDB


SQL Triggers not preventing insert I am having trouble getting a trigger to work. What it needs to do is to prevent any customer with last name Glass from being inserted into the database. This is what I have:

CREATE TRIGGER no_last_name_glass_trg
ON customer
AFTER INSERT,UPDATE
AS
BEGIN
DECLARE @CUSTOMER_LAST VARCHAR;
SET @CUSTOMER_LAST= (SELECT INSERTED.customer_last FROM INSERTED);

  IF @CUSTOMER_LAST = 'Glass'
    BEGIN
    ROLLBACK
    RAISERROR('Applicant Glass Denied',14,1);
  END;
END;

The issue is when I add customers with last name of Glass, it inserts without throwing an error. Any ideas? I can make it work in Oracle, but the same method does not work in MS SQL.

The problem with your code is that you are declaring your variable as varchar without any length. With variable declarations, varchar defaults to a length of 1 (note that in other scenarios it will have a different default length).

As a best practice, you should always specify a length on the varchar datatype. For more details, see this article on the issue.


What permissions do I need to grant on service accounts on SQL root path when relocating? During a Win2012 with SQL Server 2012 two-node cluster installation, I accidentally set the system root to be the mount point volume hosting folders for the various attached LUNs for the system and user DBs.

I relocated them post-installation to the volume I have for the System DBs and made the corrections to the startup switches, registry for the full-text service, and SQL Server Agent error log. However, I'm still getting:

  error code 5: Access Denied errors on service startup

This only goes away when I grant the SQL Server service account local Admin privileges. I tried granting NTFS Full Control privileges on the mount point to the service account but no luck.

Is there another permission set I should apply and to which accounts?

I was able to get guidance from the following MSDN link: Configure Windows Service Accounts and Permissions. The pertinent section (bookmarked in the link) is "File System Permissions Granted to SQL Server Per-service SIDs or Local Windows Groups".

The short version for the solution is that either Read, Execute or Full Control are needed on the leaf-level subdirs under the system root (DATA, LOG, FTDATA, etc).

On my host, NTFS permissions weren't propagating from the mountpoint volume root or the folder by which the SYSDB LUN were mounted. Applying NTFS permissions on the leaf-level corrected the issue.


Error: "To run a ssis package outside of sql server data tools you must install [send successful email] of Integration Services or higher." NOTE that the title of this qusetion is a verbatim error message specific to this user. The error message illustrates a bug in the dtexec application, which substitutes a user variable into the error message string. In this case, the standard message Standard E
dition is replaced with send successful email.

The 'standard' error message would be :

Description: To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher.

Version SSIS 2012. Package was originally 2005 ver. Package on file system.

DB Engine / Agent on Host 1, SSIS on Host 2.

Execution from Host 2 runs successfully. Execution from Host 1 gives the error in the question subject. Saw this post, but engine and agent are installed on Host 1. Has anyone seen this? Google returns no results when searching on the exact error (subject line is exact, wording is odd in the error message).

Thank you!

SSIS 2012 components had to be installed on Host 1 to resolve the issue.

*If anyone knows of a way to resolve this without installing SSIS 2012 components where they aren't intended, please post and I'll accept your answer.


remove duplicate values in the same table My table looks like:

+----+---------+
| id | name    |
+----+---------+
|  1 | uska    |
|  2 | chinese |
|  3 | bakshi  |
|  4 | jj      |
|  5 | uska    |
+----+---------+

I want it to change it to

+----+---------+
| id | name    |
+----+---------+
|  1 | uska    |
|  2 | chinese |
|  3 | bakshi  |
|  4 | jj      |
+----+---------+

modify changes in the same table without switching to another table.

I tried many ways, many hrs to delete or select but doesn't work, I did use code to resolve this, but I will appreciate for any explanation, thank you.

I tried in two ways like

delete from t where id<>(select id from t group by name);

Here I am getting an error as

  Table 't' is specified twice, both as a target for 'DELETE' and as a separate source for data"

delete t1.* from t t1,t t2 where 1.id<>t2.id and t1.name=t2.name;

2 duplicate rows are getting deleted instead of one.

Join table to itself:

DELETE z.*
  FROM table AS w
  JOIN table AS z  ON z.name = w.name
                  AND z.id > w.id
;


MySQL - best practices/ how to speed up "update, else insert' queries? Insert + delete faster? I'm running an ETL process and streaming data into a MySQL table.

Now it is being written over a web connection (fairly fast one) -- so that can be a bottleneck.

Anyway, it's a basic insert/ update function. It's a list of IDs as the primary key/ index .... and then a few attributes.

If a new ID is found, insert, otherwise, update ... you get the idea.

Currently doing an "update, else insert" function based on the ID (indexed) is taking 13 rows/ second (which seems pretty abysmal, right?). This is comparing 1000 rows to a database of 250k records, for context.

When doing a "pure" insert everything approach, for comparison, already speeds up the process to 26 rows/ second.

The thing with the pure "insert" approach is that I can have 20 parallel connections "inserting" at once ... (20 is max allowed by web host) ... whereas any "update" function cannot have any parallels running.

Thus 26 x 20 = 520 r/s. Quite greater than 13 r/s, especially if I can rig something up that allows even more data pushed through in parallel.

My question is ... given the massive benefit of inserting vs. updating, is there a way to duplicate the 'update' functionality (I only want the most recent insert of a given ID to survive) .... by doing a massive insert, then running a delete function after the fact, that deletes duplicate IDs that aren't the 'newest' ?

Is this something easy to implement, or something that comes up often?

What else I can do to ensure this update process is faster? I know getting rid of the 'web connection' between the ETL tool and DB is a start, but what else? This seems like it would be a fairly common problem.

Ultimately there are 20 columns, max of probably varchar(50) ... should I be getting a lot more than 13 rows processed/ second?

Can't comment yet, so posting as answer - you state in one comment that "not sure if there are ever duplicates within a given document anyway" - in that case (and in most other cases too) you should use INSERT ... ON DUPLICATE KEY UPDATE as it does not need to delete the existing row, so update overhead is only seen when the target row actually exists.

Syntax is not as simple as REPLACE INTO, but there are not "weird" side effects (because REPLACE works as DELETE/INSERT pair, it can break on foreign keys or delete referencing rows when you didn't want it to)


How to GRANT object permission to a user defined server role? I want to create logins that are granted permission to access

all user databases
view all definitions
select all records of all tables

To me it seems to be too much overhead to assign the login to each database and add it to a custom db role or to a default db role like db_datareader in each db.

Instead I tried to use a user defined server role.

But how can I assign the required permissions to a user-defined
server role to allow view any definition and db_datareader for all
dbs?
Is this even possible?

To access all databases you should give the CONNECT ANY DATABASE permission.

To view all the definitions you should grant the VIEW ANY DEFINITION permission.

To select all data from all user tables you can grant SELECT ALL USER SECURABLES starting with SQL Server 2014, but it will permit you to select data only from all accessible databases, so without CONNECT ANY DATABASE it gives you nothing.

So, even if you have SQL Server 2014 and higher, you should give all three permissions to accomplish what you want:

CONNECT ANY DATABASE
VIEW ANY DEFINITION
SELECT ALL USER SECURABLES

There is no need to grant VIEW ANY DATABASE as it's already granted to public.


table is --- is full mysql MySQL 5.7 on windows'12 server, table which I am getting error crossed 2 GB in size, server memory is 4 Gb, MySQL used about 90% of memory..
Now any insertion into this table causing table full

Once I restarted MySQL works fine until the above stats come up it again starts giving exception. I am not a server guy, so need suggestions what would be the best solution here?

from the MySQL doc:

  If a table-full error occurs, it may be that the disk is full or that
  the table has reached its maximum size. The effective maximum table
  size for MySQL databases is usually determined by operating system
  constraints on file sizes, not by MySQL internal limits

Also check the Section C.10.3, “Limits on Table Size”.

Highlight for the OS limits.

+------------------------+------------------------------+
|    Operating System    |       File-size Limit        |
+------------------------+------------------------------+
| Win32 w/ FAT/FAT32     | 2GB/4GB                      |
| Win32 w/ NTFS          | 2TB (possibly larger)        |
| Linux 2.2-Intel 32-bit | 2GB (LFS: 4GB)               |
| Linux 2.4+             | (using ext3 file system) 4TB |
| Solaris 9/10           | 16TB                         |
| OS X w/ HFS+           | 2TB                          |
+------------------------+------------------------------+

And the list under "If you do encounter a full-table error, there are several reasons why it might have occurred:"


Empty table after pg_restore I have a backup of an unlogged table called potential_users, the relation has 58677237 records in it.

Steps:

pg_dump --format custom --verbose --file "potential_users.backup" --table "public.potential_users" productiondb

pg_restore -a -t potential_users potential_users.backup

I see the data prompted in the terminal and when it finishes shows:

PostgreSQL database dump complete

But after a SELECT count(*) FROM potential_users; 0 is returned.

Not really a solution but a workaround that worked is to restore through a csv file.

COPY potential_users FROM '/potential_users.csv' WITH (FORMAT csv);


Restrict account to only 1 view SQL2012.

I have a user and only want them to have select access to a single view and nothing else.

I tried to create my own schema containing the view and deny access to dbo but it seems it doesn't work as I would have expected it to. I can still run queries against tables even though I can't seem them in SSMS.

CREATE SCHEMA vl
CREATE VIEW vl.vw_Search AS SELECT 'This is a test' AS RESULT
GRANT select ON vl.vw_Search TO vlu
DENY SELECT On SCHEMA::sys To vlu
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To vlu
DENY INSERT, UPDATE, DELETE ON schema::dbo to vlu

DENY VIEW ANY DATABASE TO vlu
DENY VIEW ANY DEFINITION TO vlu

I can also still access system views which I also don't want the user to be able to do.

Use this code to see who has access to what:

;WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM    perms_cte p
WHERE   principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1;

It is likely the account in question is a member of some role, such as db_datareader which is giving it the ability to query other tables.  By default, accounts do not have the ability to access anything.


Should dates in dimensional tables use dimDate? Assuming my dimDate has a surrogate key. Should all the date columns in the dimensional tables (not the fact tables) store the surrogate key of date dimension? Or just plain date?

For example, in dimensional table dimCustomer, there may be birthday, join date, graduation date, .... Etc.

I would model this by storing both the date value and the SK back to the date dimension. Here is why storing that actual state value allows you to have ms level precision and can be useful when someone wants to see exactly when something happened. however for aggregates the SK can be a computed column if you are worried about the two values going out of sync

Customer
ID               1
Name             John Smith
BirthdateValue   2018-07-04 04:20:69.007
BirthdateSK      20180704

Repeat the pattern as needed for other dates.


Is it impossible to store 2^266 records on a database of some kind? Each records weighs 52 bytes, is it possible in today's technology if someone had the storage needed? What kind of db would hold the data and will make it possible to retrieve it.

An example record:

(5HpHagT65TZzG1PH3CSu63k8DbpvD8s5ip4nEB3kEsreAbuatmU
,1MsHWS1BnwMc3tLE8G35UXsS58fKipzB7a
,1Q1pE5vPGEEMqRcVRMbtBK842Y6Pzo6nK9)

The number of records is 115792089237316195423570985008687907853269984665640564039457584007913129639936

Absolutely possible, because someone with the resources to store
6021188640340442162025691220451771208370039202613309330051794368412 Terabyte
 definitely has enough money to get a custom made database system for his purposes as well.

I might be available as a contractor, for only 0.01€ per Terabyte.


Linked Server only for Select Statements? In linked server concept can we run only select queries like below ?

SELECT * FROM OPENQUERY([SERVER_NAME], 'SELECT * FROM DATABASE_NAME..TABLENAME')

I have a job which runs through Linked server.

I have added the AOAG Listener as Linked server in the source server and this connects to PROD server and runs the query.

But the query is very big...so how this works through Linked server!

If you have linked your servers, you can query any server using [servername].databasename.schema.object from any one of the two servers.


Should I use a Domain User account to manage SQL Server (or other services) As the SQL Server administrator for a server running inside a Windows Domain, I followed some rules:

SQL service runs under a Domain User account (least privilege)
"sa" has been disabled

The question is basically about "my" account as an administrator. To expand on this, I have two AD accounts, 1 to login to my workstation (call it ADU1), and 1 to login to the server itself (ADU2). Note that this somewhat following the MS best practices for their recommended security model.
The question is, do I use my Domain User (ADU1) which has the "sysadmin" role, or (ADU2) or basically a different AD account other than my regular domain account?
Hope this makes sense. Looking for some guidance... thanks!

What's the worst case scenario if e.g. malicious software, running as you, gets concomitant access to the database in question?
The answer to that should probably dictate what you need to do.  Note that if you want to use another domain user for administration, you can run SSMS as another user by shift-right-clicking the shortcut (as opposed to a normal right-click).  There's also a Windows 10 system setting, "show 'run as different user' in start" (under developer settings).


Get data per Customer and per Date How will I get the all the date even though there is no order between two dates?

Sample Table

OrderID  | Date      | CusID
33942    | 6-21-2014 | 6005
34059    | 6-20-2014 | 4003
53333    | 6-23-2014 | 6005
59234    | 6-23-2014 | 4003

How I could have this result?

CusID   | Date      | OrderID |
4003    | 6-20-2004 | 34059   |
4003    | 6-21-2004 | null    |
4003    | 6-22-2004 | null    |
4003    | 6-23-2004 | 59234   |
6005    | 6-20-2004 | null    |
6005    | 6-21-2004 | 33942   |
6005    | 6-22-2004 | null    |
6005    | 6-23-2004 | 53333   |

This what I did so far.

I create a calendar table

CREATE TABLE #Calendar
(
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @EndDate = GETDATE()
SET @StartDate = DATEADD(Year, -1, @EndDate)

WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO #Calendar
             (
                   CalendarDate
             )
             SELECT
                   @StartDate

             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END

Then here is my query but does not give me the needed result

Select t.CusID, c.CalendarDate, t.OrderID
From #Calendar c
left outer join
#temp t
ON
CONVERT(VARCHAR(10), c.CalendarDate, 112) = CONVERT(VARCHAR(10), t.Date, 112)
Where c.CalendarDate Between '6-20-2014' and '6-23-2014'
Order By t.Name

You also need a table with customers. The customers table needs to be CROSS JOINed with the calendar table to get the list of all possible dates per customer.

This SQL Fiddle demonstrates the query that gets the desired result. Please note that your database schema may have a table with customers, and you may not need a customers CTE I have created. Just use your existing customers table to CROSS JOIN it with the calendar table.

Also, as you use SQL Server 2008, it is about time you've learned CTE. It is much easier to use them than the temp tables. However, do not overuse them. Do not use them when performance is very important. Please read this question to better understand when to use CTE vs Temp tables

Another note: when truncating times from the datetime columns do not use CONVERT(VARCHAR(10), c.CalendarDate, 112). It is about the slowest possible method. Read this for more info

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1
    ([OrderID] int, [Date] DATETIME, [CusID] int)
;

INSERT INTO Table1
    ([OrderID], [Date], [CusID])
VALUES
    (33942, '6-21-2014', 6005),
    (34059, '6-20-2014', 4003),
    (53333, '6-23-2014', 6005),
    (59234, '6-23-2014', 4003)
;

Query 1:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @EndDate = GETDATE()
SET @StartDate = DATEADD(Year, -1, @EndDate)

;with calendar as (
  SELECT CAST(@StartDate AS DATE) CalendarDate
  UNION ALL
  SELECT DATEADD(day, 1, CalendarDate) FROM calendar
  WHERE CalendarDate < @EndDate
  ),
customers as (
  SELECT DISTINCT CusID FROM Table1
  )
SELECT a.CusID, c.CalendarDate, OrderID
FROM customers a CROSS JOIN Calendar c
LEFT JOIN Table1 t
ON CAST(c.CalendarDate AS DATE) = CAST(t.[Date] AS DATE) AND a.CusID = t.CusID
WHERE CAST(c.CalendarDate AS DATE) BETWEEN '6-20-2014' and '6-23-2014'
ORDER BY 1, 2
OPTION (MAXRECURSION 0)

Results:

| CUSID | CALENDARDATE | ORDERID |
|-------|--------------|---------|
|  4003 |   2014-06-20 |   34059 |
|  4003 |   2014-06-21 |  (null) |
|  4003 |   2014-06-22 |  (null) |
|  4003 |   2014-06-23 |   59234 |
|  6005 |   2014-06-20 |  (null) |
|  6005 |   2014-06-21 |   33942 |
|  6005 |   2014-06-22 |  (null) |
|  6005 |   2014-06-23 |   53333 |


Running SSIS from a different server than the primary database instance We currently have a single SQL Server Integration Services (SSIS) package that performs an extract-transfer-load (ETL) process to move some data from SQL Server to DB2.  On the current production server SSIS is installed on the same server as the database instance and the package execution is managed by the SQL Agent via a Job.

We are upgrading our SQL infrastructure to be clustered and the senior database administrator would like to have SSIS on it's own server, so the ETL server was set up by just installing integration services service onto the machine.

Does the SSIS server also need it's own database instance and/or SQL Agent to manage the execution of the packages?

I'm having some trouble understanding how all the pieces would work in this scenario.

We are using SQL Server 2008 R2 SP1.

I would suggest reading up on SSIS at MSDN:
Initial Installation (Integration Services) -this references having a standalone installat of just SSIS.

Quote from above link:

  SQL Server Integration Services is installed through the SQL Server
  2008 Setup program. You can install Integration Services alongside
  other SQL Server components or you can install Integration Services as
  a stand-alone component. Integration Services includes both client and
  server applications.

  To use a dedicated server for extraction, transformation, and loading
  (ETL) processes, we recommend that you install a local instance of the
  SQL Server Database Engine when you install Integration Services.
  Integration Services typically stores packages in an instance of the
  Database Engine and relies on SQL Server Agent for scheduling those
  packages. If the ETL server does not have an instance of the Database
  Engine, you will have to schedule or run packages from a server that
  does have an instance of the Database Engine. This means that the
  packages will not be running on the ETL server, but instead on the
  server from which they were started. As a result, the resources of the
  dedicated ETL server are not being used as intended. Furthemore, the
  resources of other servers might be strained by the running ETL
  processes.

Some additional information could be found here as well: Considerations for Installing Integration Services. You might want to take under consideration the licensing as well and still validate the standalone server is licensed properly.


How to change the backup and archive destination to a network drive in oracle 11g windows Due to space issue I want to change the backup and archive destination from FRA to a network drive in my system (I'm using windows). I've done the following steps to change the destination:

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO '\\192.168.20.200\HOME\DEVELOPMENT\JOE';

RMAN> configure channel device type sbt parms

sbt_library='//192.168.20.200/Home/Development/Joe/';

Now, when I'm trying to BACKUP DATABASE I'm getting the following error:

  ORA-19554: error allocating device, device type: SBT_TAPE, device name:
  ORA-27211: Failed to load Media Management Library

Network drives are managed by system as a drive, then for rman, it should be a disk device :

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\192.168.20.200\HOME\DEVELOPMENT\JOE';

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

I have never tested something like that, but I think it should work.


Querying a big database I am PHP programmer with basic-intermediate knowledge on databases. I am working on a project where I need to verify a given phone number's presence in India's Do not call database. Following is the structure of the table,

Phone Number | Flag 1 | Flag 2 | Flag 3 | Flag 4 | Flag 5 | Flag 6 | Flag 7

here each Flag represents a category.

I would be provided a phone number and a category, or maybe phone number and multiple categories.

What would be the best way to optimize the table structure? Which will be best to use? Mysql or Oracle?

Should the table structure be normalized? How should the query be designed?

Response time is a key factor for the solution. The database has about 150 Million records.

That is a very denormalized design, as you have pointed out yourself.  Especially if there isn't always a 1 : 7 ratio of Phone Numbers to Flags.  Depending on your business data, you could potentially have a vast amount of sparse data.

I'd recommend a thin, normalized structure like this:

create table Flag
(
    pkFlag int identity(1, 1) not null primary key clustered,
    FValue varchar(100) not null
)
go

create table PhoneNumber
(
    pkPhoneNumber int identity(1, 1) not null primary key clustered,
    PnValue varchar(20) not null
)
go

create table PhoneNumberFlag
(
    fkFlag int not null foreign key references Flag(pkFlag),
    fkPhoneNumber int not null foreign key references PhoneNumber(pkPhoneNumber)
)
go

This allows you to create a one-to-many relationship (or possibly a many-to-many relationship, I can't say for sure as you haven't explained the meaning of Flags or any part of the domain).

A simple query to get all flags for a particular phone number would be:

select
    f.fValue
from PhoneNumber pn
inner join PhoneNumberFlag pnf
on pn.pkPhoneNumber = pnf.fkPhoneNumber
inner join Flag f
on f.pkFlag = pnf.fkFlag
where pn.PnValue = '555-555-5555'

As for the RDBMS you are looking to go with, that is very dependent on MANY factors.  What's your environment?  What do you already have?  MySql is a very different type, as it is open source.  Oracle, SQL Server, these are not.  I'm not sure about Oracle, but SQL Server has a free edition (SQL Server Express) that you could leverage, but it is limited.  RDBMS licensing can get costly very fast.  There are too many questions that need answering before a sound recommendation on that front can be made.


Getting duplicate results in many to many query I have designed a bookstore app with these tables. There's a book with an M2M relation to author. Lastly, there's table reader which keeps track of which user has read the book i.e. an entry in the read table means that book has been read by the corresponding user.

Relation defined in SQLFiddle

I want to select the books with their author names and also keep a count of how many people have read a particular book. Here's my query :

Question : Count of the number of readers for a book is wrong (coincidentally equal to the number of authors). For a book with more than 1 reader, the author names are repeated.

  SELECT b.id, b.title, COUNT(r.user1_id) AS read_ct,
         array_agg(author.name)
  FROM book b
  LEFT OUTER JOIN reader r ON r.book_id = b.id
  LEFT OUTER JOIN book_author ba ON ba.book_id = b.id
  LEFT OUTER JOIN author ON author.id = ba.author_id
  GROUP BY b.id

Which of these solutions is better ?

Solution 1 : Use `DISTINCT clause i.e.

 SELECT b.id, b.title, COUNT(DISTINCT r.user1_id) AS read_ct,
     array_agg(DISTINCT author.name)

Solution 2 : Use subquery

 SELECT s.id, s.title, s.names, COUNT(r.used1_id) AS read_ct
 FROM (
   SELECT b.id, b.title, array_agg(author.name) AS names
   FROM book b
   LEFT OUTER JOIN book_author ba ON ba.book_id = b.id
   LEFT OUTER JOIN author ON author.id = ba.author_id
   GROUP BY b.id
 ) AS s
 LEFT OUTER JOIN reader r ON r.book_id = s.id
 GROUP BY s.id, s.title, s.names

Objective: Count book's readers.
Display book title, count, authors.

JOIN the (Subquery: book.array_agg(authors))
TO (Subquery: count book readers)
TO Book for id, title.

I think the logical error, as you mentioned Cartesian product, was aggregating two different aggregations at the same time...

Something like this should work:

PostgreSQL:

SELECT id, title, read_ct.Readers, Authors.Names
FROM book b
JOIN ( SELECT book_id, array_agg( name) as Names
       FROM book_author ba
       JOIN author a
       ON a.id = ba.author_id
       GROUP by book_id
      ) Authors
ON Authors.book_id = b.id
JOIN (
    SELECT book_id, count(r.user1_id) Readers
    FROM reader r
    GROUP BY book_id ) read_ct
on read_ct.book_id = b.id

SQL Server:

SELECT id, title, read_ct.Readers, STUFF(
                (SELECT ', ', a.name
                FROM book_author ba
                    JOIN author a
                    ON a.id = ba.author_id
                WHERE ba.book_id = b.id
                FOR XML PATH(''), TYPE)
                .value('.', 'varchar(max)'), 1, 2, '')
    FROM book b
    JOIN (
        SELECT book_id, count(r.user1_id) Readers
        FROM reader r
        GROUP BY book_id ) read_ct
    on read_ct.book_id = b.id


What does the term "projection" refer to in an SQL query (or view definition) In Oracle's document The Query Optimizer, under View Merging, I found the following information

  The view merging optimization applies to views that contain only
  selections, projections, and joins. That is, mergeable views do not contain
  set operators, aggregate functions, DISTINCT, GROUP BY, CONNECT BY, and so on.
  (emphasis mine)

Yet, I can only guess as what such a projection actually refers to.

In Relational algebra, projection means collecting a subset of columns for use in operations, i.e. a projection is the list of columns selected.

In a query optimiser step, the projection will manifest itself as a buffer or spool area of some description containing a subset of the columns from the underlying table or operator, or a logical view based on those columns that is used by subsequent operations.

In a view, a projection equates to the list of columns selected in the query underlying the view.


How long can this ALTER TABLE statement take? Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL

Suppose

there are 45 GB data space and 2GB index space;
about 3 million rows in this table;
column YYY is varchar(8000) now and can be updated (is writable).
the table has about 30 other columns.
There are about 3000G data from other database & tables on this machine.

Some other information:

99.99% of rows have NULL in this varchar(8000);
the web application may hit this table 5 times per minute;
the hardware is enterprise level (8 core CPU and 256GB RAM).
there are other tables and database on this machine, about 3000GB data.

Relevant @@VERSION details:

  Microsoft SQL Server 2014 - 12.0.4422.0 (X64) Enterprise Edition (64-bit)

Nobody can tell you with any degree of certainty or accuracy how long this operation will take on your system. However I suggest the change will likely have less impact than you think. I set up a quick test to compare changing a table from int -> bigint and from varchar(8000) -> varchar(max). First, two simple tables:

CREATE TABLE dbo.t0(a int primary key, b int);
GO
CREATE TABLE dbo.t1(a int primary key, b varchar(8000));
GO

Now, insert at least one non-NULL value, and then over 5 million rows (on my system; YMMV):

INSERT dbo.t0 VALUES(0,1);
INSERT dbo.t0 SELECT rn, NULL FROM
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me

INSERT dbo.t1 VALUES(0,'what');
INSERT dbo.t1 SELECT rn, NULL FROM
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me

Then I tested the ALTER change, with stats I/O enabled:

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t0 ALTER COLUMN b bigint NULL;
GO -- 9 seconds, 122,506 reads
SET STATISTICS IO OFF;

This finished in 9 seconds and required 122,506 reads.

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL;
GO -- 5 seconds, 8,562 logical reads
SET STATISTICS IO OFF;

This finished in 5 seconds and only required 8,562 reads.

So while neither operation happened online, and while your actual results may differ based on hardware and your table structure, this was on a lowly Windows VM on a MacBook, so I would expect your time to be better than this.

I also tested with a lot more data populated in the nullable column. Dropped the tables, re-created and re-populated with the above scripts, then ran this to put something in 100,000 rows:

;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = a;
GO
;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = REPLICATE(RTRIM(a), 1000);
GO

This took way longer than the subsequent ALTERs (over 3 minutes). The first ALTER still took 9 seconds, but the second one took 17, simply because the number of reads shot up (to 189,854). This is similar to what would happen if you had, say, 30 other columns in this table. I still think 17 seconds is pretty good for what is actually happening here, and again, this is far from an enterprise-level environment.

As Paul points out, starting with SQL Server 2016, you can get this to happen online, but there are restrictions and limitations. For more information see the docs.

ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL WITH (ONLINE = ON);

This option is not available in SQL Server 2014, though.

One way you could get this to happen more online is to simply move that column to a separate, related table. There are some intricacies here with how you cut over, and it might impact a bunch of code, but you can solve most issues with a view (at least temporarily).


Can we use TDE as an anti-injection technique TDE prevent unauthorized access to the data by restoring the files to another server, because it requires the original encryption certificate and master key.

If i applied a table level encryption using TDE to my database, and am accessing the database from my windows application, through proper authentication. Consider that an intruder is trying some injection technique, my question is that whether he receives the original data or the encrypted one? or can we use TDE as an anti-injection technique?
The query from my application may looks like:

Dim myQuery="Select * from myTable where some_id='" & txtUserId.Text & "'

so that they can inject sql by giving the text like sameValue' or 1=1 or ' will give the whole data. in this case whether he get the encrypted data or be the actual data

Note: i don't use such queries in my application, am using parameterized query and sp throughout my application. asking this for just clarification.

The attacker will get the data unencrypted.

The T in TDE stands for "transparent".  The user will never see encrypted data.  The database transparently decrypts it when it is read from disk and transparently encrypts it when writing to disk.  If your application is insecure, TDE doesn't help you plug those application security holes.  You need to fix those in the application (so use bind variables!)


Invalid object name 'SSISDB.catalog.customized_logging_levels'. (Microsoft SQL Server, Error: 208) I am moving data and SSIS packages from SQL Server 2014 database server to SQL Server 2016 server.

I began with building the new server and installing SQL Server 2016 Standard edition.  Then I backed up the data on the 2014 server and restored it onto the new server 2016 server.  Including SSISDB.

One of the items that needs to be done is to go into the SQL Server Agent jobs and modify any step that is of the type SQL Server Integration package and change the server parameter in the package to the name of the new server.

However when I try to do that I get the following error message:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  ADDITIONAL INFORMATION: Invalid object name 'SSISDB.catalog.customized_logging_levels'. (Microsoft SQL Server, Error: 208)

I get the same message when I try to look at the SSISDB Integration Catalog

One article I found recommended the following steps:

Backup SSISDB database from SSMS 2014.
Create Integration Service catalog on SSMS 2017 and restore backup.
Change the SSISDB database owner sa.
Right click on SSISDB under Integration Service Catalogs.
Select Database Upgrade-> Select Next -> Select I have taken backup of the SSISDB database. continue with the SSISDB upgrade and Select Upgrade.

The last step (SSISDB upgrade) fails with the following message:

  The SQL Server edition or version do not meet the minimum requirements to create a Master Data Services database. (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)

I would really appreciate any advice.

Thanks in advance

The first server is SQL Server 2014.

I discovered some additional information at http://radacad.com/ssis-catalog-changes-in-2016

There are a number of tables, views and stored procedures unique to SSIS 2016.  When I restored the SSIS database on the new server, these objects were overwritten.


Suggestion for Bulk Data Import We are working on a project which requires import data from Excel spreadsheet on daily basis. The data will be import from pre-define template and what we're thinking, first we will upload data in temporary table and perform cleaning operation (removing unnecessary information, add new rows in parent table and get their FK, etc.).

I need to know is there any tool or utility available which can reduce our efforts.
What is the best way to INSERT bulk record from different sources (mostly from Excel spreadsheets)?

SSIS is the way to go on this. If you've never built a package before, and you know your source files (read also: spreadsheets) are always going to be the same ones, what you can do is use SQL Server's Import/Export wizard. In SSMS right-click database and select Tasks > Import (or Export) Data...

This opens a wizard which walks you through the steps of selecting your source/destination files as well as destination tables (can create tables if they don't exist already). You'll have to map which columns go where but the wizard is pretty straight forward. When you're done it will ask you to run or save the package (or do both). Save the package. This will save your package in .dtsx format if you choose to save it to file system. Your other option is to save the package in SQL Server itself, which would then keep them in the msdb system database.

Once you've saved your package, you can create a SQL Agent job to run that package periodically (you specify the schedule) so that you can always load your tables using SSIS. If you'd like to learn more about SSIS, check out my company's free webinars (we cover the whole BI stack) at PragmaticWorks


Alternative to MakeValid() for spatial data in SQL Server 2016 I have a very large table of geography LINESTRING data that I'm moving from Oracle to SQL Server.  There are a number of evaluations that are executed against this data in Oracle, and they will need to be executed against the data in SQL Server, too.

The problem: SQL Server has tighter requirements for a valid LINESTRING than Oracle; "The LineString instance cannot overlap itself over an interval of two or more consecutive points".  It just so happens that a percentage of our LINESTRINGs do not meet that criterion, which means that the functions we need to evaluate the data fail. I need to adjust the data so that it can be successfully validated in SQL Server.

For example:

Validating a very simple LINESTRING that doubles back on itself:

select geography::STGeomFromText(
    'LINESTRING (0 0 1, 0 1 2, 0 -1 3)',4326).IsValidDetailed()

24413: Not valid because of two overlapping edges in curve (1).

Executing the MakeValid function against it:

select geography::STGeomFromText(
    'LINESTRING (0 0 1, 0 1 2, 0 -1 3)',4326).MakeValid().STAsText()

LINESTRING (0 -0.999999999999867, 0 0, 0 0.999999999999867)

Unfortunately the MakeValid function changes the order of the points and removes the third dimension, which makes it unusable for us.  I'm looking for another approach that solves this problem without reordering or removing the 3rd dimension.

Any ideas?

My actual data contains hundreds/thousands of points.

Let me caveat that I am playing with spatial data in SQL server for the first time (so you probably already know this first part), but it took me a while to figure out that SQL Server isn't treating (x y z) coordinates as true 3D values, it is treating them as (latitude longitude) with an optional "elevation" value, Z, which is ignored by validation and other functions.

Evidence:

select geography::STGeomFromText('LINESTRING (0 0 1, 0 1 2, 0 -1 3)', 4326)
    .IsValidDetailed()

24413: Not valid because of two overlapping edges in curve (1).

Your first example seemed weird to me because (0 0 1), (0 1 2), and (0 -1 3) are not collinear in 3D space (I'm a mathematician, so I was thinking in those terms). IsValidDetailed (and MakeValid) is treating these as (0 0), (0 1), and (0, -1), which does make an overlapping line.

To prove it, just swap the X and Z, and it validates:

select geography::STGeomFromText('LINESTRING (1 0 0, 2 1 0, 3 -1 0)', 4326)
    .IsValidDetailed()

24400: Valid

This actually makes sense if we think of these as regions or paths traced on the surface of our globe, instead of points in mathematical 3D space.

The second part of your issue is that Z (and M) point values are not preserved by SQL through functions:

  Z-coordinates are not used in any calculations made by the library and are not carried through any library calculations.

This is unfortunately by design. This was reported to Microsoft in 2010, the request was closed as "Won't Fix". You might find that discussion relevant, their reasoning is:

  Assigning Z and M is ambiguous, because MakeValid splits and merges spatial elements. Points often gets created, removed or moved during this process. Therefore MakeValid (and other constructions) drops Z and M values.

  For example:

DECLARE @a geometry = geometry::Parse('POINT(0 0 2 2)');
DECLARE @b geometry = geometry::Parse('POINT(0 0 1 1)');
SELECT @a.STUnion(@b).AsTextZM()

  Values Z and M are ambiguous for point (0 0). We decided to drop Z and M completely instead of returning half-correct result.

  You can assign them later on if you know exactly how. Alternatively you can change the way you generate your objects to be valid on input, or keep two versions of your objects, one that is valid and another one that preserves all your features. If you explain your scenario better and what you do with the objects maybe we could be able to give you additional workarounds.

In addition, as you've already seen, MakeValid can also do other unexpected things, like change the order of points, return a MULTILINESTRING, or even return a POINT object.

One idea I came across was to store them as a MULTIPOINT object instead:

  The problem is when your linestring actually retraces a continuous section of line between two points that was previously traced by the line. By definition, if you're retracing existing points, then the linestring is no longer the simplest geometry that can represent this pointset, and MakeValid() will give you a multilinestring instead (and lose your Z/M values).

  Unfortunately, if you're working with GPS data or similar then it's quite likely that you might have retraced your path at some point in the route, so linestrings are not always that useful in these scenarios :( Arguably, such data should be stored as a multipoint anyway since your data represents the discrete location of an object sampled at regular points in time.

In your case it validates just fine:

select geometry::STGeomFromText('MULTIPOINT (0 0 1, 0 1 2, 0 -1 3)',4326)
    .IsValidDetailed()

24400: Valid

If you absolutely need to maintain these as LINESTRINGS, then you will have to write your own version of MakeValid that slightly adjusts some of the source X or Y points by some tiny value, while still preserving Z (and doesn't do other crazy things like convert it into other object types).

I'm still working on some code, but give a look at some of the starting ideas here:

Converting linestring to points
Rob Farley Blog: Converting points to a path
Create geography polyline from points in T-SQL

EDIT Ok, a few things I found while testing:

If the geometry object is invalid, you just can't do much with it. You can't read the STGeometryType, you can't get the STNumPoints or use STPointN to iterate through them. If you can't use MakeValid, you're basically stuck with operating on the text representation of the geographic object.
Using STAsText() will return the text representation of even an invalid object, but doesn't return Z or M values. Instead, we want AsTextZM() or ToString().
You can't create a function that calls RAND() (functions need to be deterministic), so I just made it nudge by successively larger and larger values. I really have no idea what the precision of your data is, or how tolerant it is of small changes, so use or modify this function at your own discretion.

I have no idea if there are possible inputs that will cause this loop to go on forever. You have been warned.

CREATE FUNCTION dbo.FixBadLineString (@input geography) RETURNS geography
AS BEGIN
DECLARE @output geography

IF @input.STIsValid() = 1   --send valid objects back as-is
  SET @output = @input;
ELSE IF LEFT(@input.IsValidDetailed(),6) = '24413:'
--"Not valid because of two overlapping edges in curve"
BEGIN
  --make a new MultiPoint object from the LineString text
  DECLARE @mp geography = geography::STGeomFromText(
      REPLACE(@input.AsTextZM(), 'LINESTRING', 'MULTIPOINT'), 4326);
  DECLARE @newText nvarchar(max); --to build output
  DECLARE @point int
  DECLARE @tinynum float = 0;

  SET @output = @input;
  --keep going until it validates
  WHILE @output.STIsValid() = 0
  BEGIN
    SET @newText = 'LINESTRING (';
    SET @point = 1
    SET @tinynum = @tinynum + 0.00000001

    --Loop through the points, add a bit and append to the new string
    WHILE @point <= @mp.STNumPoints()
    BEGIN
      SET @newText = @newText + convert(varchar(50),
               @mp.STPointN(@point).Long + @tinynum) + ' ';
      SET @newText = @newText + convert(varchar(50),
               @mp.STPointN(@point).Lat - @tinynum) + ' ';
      SET @newText = @newText + convert(varchar(50),
               @mp.STPointN(@point).Z) + ', ';
      SET @tinynum = @tinynum * -2
      SET @point = @point + 1
    END

    --close the parens and make the new LineString object
    SET @newText = LEFT(@newText, LEN(@newText) - 1) + ')'
    SET @output = geography::STGeomFromText(@newText, 4326);
  END; --this will loop if it is still invalid
  RETURN @output;
END;
--Any other unhandled error, just send back NULL
ELSE SET @output = NULL;

RETURN @output;
END

Instead of parsing the string, I chose to create a new MultiPoint object using the same set of points, so I could iterate through them and nudge them, then reassemble a new LineString. Here's some code to test it, 3 of these values (including your sample) start invalid but are fixed:

declare @geostuff table (baddata geography)

INSERT INTO @geostuff (baddata)
          SELECT geography::STGeomFromText('LINESTRING (0 0 1, 0 1 2, 0 -1 3)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (0 2 0, 0 1 0.5, 0 -1 -14)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (0 0 4, 1 1 40, -1 -1 23)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (1 1 9, 0 1 -.5, 0 -1 3)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (6 6 26.5, 4 4 42, 12 12 86)',4326)
UNION ALL SELECT geography::STGeomFromText('LINESTRING (0 0 2, -4 4 -2, 4 -4 0)',4326)

SELECT baddata.AsTextZM() as before, baddata.IsValidDetailed() as pretest,
 dbo.FixBadLineString(baddata).AsTextZM() as after,
 dbo.FixBadLineString(baddata).IsValidDetailed() as posttest
FROM @geostuff


MySQL Circular Replication I have this scenario where I have 4 MySQL servers (A,B,C,D), as this:

Server (A) is always online, and serves as the main website.
Servers (B,C and D) are online on random periods of the day, and not concurrently.
Each of the servers can make changes to the tables, whether the state online or offline, the changes should be applied to the other servers.

I've followed this tutorial and everything went okay, until this happened: two servers were offline, the replication stopped, and I needed to reset the slave service on all servers from phpMyAdmin Control Panel in order to get the replication work again.

I need a solution that works automatically. How can I achieve that?

The tutorial you followed was mine and was the accepted answer.
However, the other answer given by AaronBrown had a very strong caveat.

Instead of having a single point of failure, now you have three.
What if one of the servers goes down? Now replication is broken everywhere:
How do you replace it easily and align the binary log positions? You can't without doing some very complicated and error prone binary log analysis.
So, now you have to restore all three servers from a backup. Wait, was that backup taken at the same moment in time? Nope, because there's no way to make a transactional backup across multiple servers. So, you're left with broken replication and attempting to fix it with pt-table-checksum and pt-table-sync...or rebuilding the entire cluster from scratch.

In your particular case, I adamantly advise against using pt-table-sync. Why ? Running pt-table-sync will create a script that you can run on the Slave so that the Slave will end up looking like its Master. THAT WILL DELETE DATA FROM THE SLAVE THAT ORIGINATED FROM THAT SLAVE IN ITS ROLE AS A MASTER !!! This is why I upvoted AaronBrown's answer and I wrote a comment on AaronBrown's answer as to why I liked his answer stating

I have to agree with your answer because I have had to do just what you are saying with some clients. In some cases, if you do not remember to precede the output of pt-table-sync with "SET SQL_LOG_BIN=0;", unintended changes may cascade and you end up chasing your tail like a crazed dog. Error prone, indeed !!!

WHAT TO DO NEXT ...
To sync each node, you will have to perform the following:
From ServerA (Master) to ServerB (Slave)
STEP 01) On the Master, create dumpfile with binary log disabled in your session as first line
STEP 02) mysqldump from Master without overwriting (using --insert_ignore)
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DUMPFILE=/path/to/MasterDumpFile.sql
MYSQLDUMP_OPTIONS="--single-transaction --no-create-info --insert-ignore"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN}`
echo "SET sql_log_bin = 0;" > ${DUMPFILE}
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> ${DUMPFILE}

STEP 03) Move the dumpfile to the Slave
STEP 04) Execute the DumpFile on the Slave
STEP 05) Repeat Steps 1-4 for ServerB (Master) and ServerC (Slave)
STEP 06) Repeat Steps 1-4 for ServerC (Master) and ServerD (Slave)
STEP 07) Repeat Steps 1-4 for ServerD (Master) and ServerA (Slave)
When done, all nodes should look identical.
Sorry I don't have an automatic solution, but this is the next best thing.


What is the advantages of using auto_increment in database as primary key over set of attributes? I have table as

CREATE TABLE circle
(
    circle_id int(11) auto_increment primary key not null,
    user_id int (11) not null,
    title varchar(255) collate  utf8_general_ci not null
);

CREATE TABLE circle_share1
(
    id int(11) auto_increment primary key not null,
    circle_id int(11),
    user_id int(11),
    start_time date
);

CREATE TABLE circle_share2
(
    circle_id int(11),
    user_id int(11),
    start_time date,
    primary key(circle_id , user_id)
);

What are the advantages of using table share1 over share2 ?

share2 has primary key of circle_id , user_id

and share1 has primary key id .

I checked 50 tables out of them none of the table is like share2  , Why developers are not preferred  share2 ?

circle_share1 does not require much in-depth design thought.  circle_share2 requires some actual brain cycles to determine if the primary key will have the desired performance.

Rows in circle_share1 will be written to the disk in the exact order they are inserted, thereby making inserts quicker.

Rows in circle_share2 may be inserted anywhere in the table, necessitating page-splits thereby fragmenting the data and possibly resulting in slower performance.

Neither way is the correct way in all circumstances - the best way depends on your data, and how it will be created in the user and circle tables and inserted into the circle_shareX table.


SQL Server + Reporting Services + SSIS + SSAS Licensing and Infrastructure Actually I am running 3 environments (Dev / UAT / PROD ) with SQL Server installations on them.

Development server has also Integration Services Service running on it an we are starting to actively using it.

We are thinking also on installing Reporting Service to start using the whole suite provided by Microsoft. And here comes my question.

I'm not really sure if having everything on same server with huge workloads and usage is a good idea and maybe isolating each component on its own server would be the best option. However, how is licensing applied in this case?

Is it possible to have:

1 MSSQL Server with Data Engine and Databases on it.
1 Server with Integration Services on it (Does it need Database or something?)
1 Server with Reporting Services on it (Does it need Database or something?)

You have to license all 3 servers?

Check out the Microsoft SQL Server 2014 Licensing Guide (PDF).

Page 11 says:

  The software components of a single SQL Server 2014 license cannot be separated for use in more than one OSE. If these components are running on a server other than the main database server, then an additional license is required for each additional OSE in which they are installed. For example, if the SQL Server DB is deployed in one OSE and SQL Server RS is deployed in another, both OSEs must be fully licensed for SQL Server 2014 accordingly.

What that means is that if you run the database engine, SSIS, and SSRS on three separate servers, then you have to license all three.


MariaDB Out-of-Order Parallel Replication and OPTIMIZE TABLE I am using MariaDB 10.4 and have question about Out-of-Order parallel replication.
I am optimizing databases(about 400GB of data) on Master every week. As optimizing DB on master do not have any negative impact, then replicated OPTIMIZE commands on slaves results in huge replication lags (up to half an hour). Can i use Out-of-Order parallel replication (https://mariadb.com/kb/en/parallel-replication/#out-of-order-parallel-replication) and set different gtid_domain_id for the OPTIMIZE commands on master to fix this ?
Please note that tables on master are being updated all the time. I am unsure if this is safe approach or not ?
Maybe it will be safer to NOT replicate OPTIMIZE commands on master and run them independly on all slaves ? Will this affect replication / lag ?

Anti-OPTIMIZE
If you are using InnoDB (which you should be using), then OPTIMIZE TABLE is almost totally useless.  Stop running that.
If you are DELETEing huge chunks of a table, and that led you to want to OPTIMIZE, then let's discuss alternatives.
Replication issues
It is possible to avoid replicating any command(s):
 SET SESSION binlog = OFF;   -- I forget the details
 OPTIMIZE TABLE ...;
 SET ...   -- restore replication

But it seems like you would want to OPTIMIZE on the Slaves, where you are doing SELECTs?
If you have a graceful way to take a Slave out of rotation, you could do a "rolling" OPTIMIZE across the slaves, one at a time.
ANALYZE
If the goal is to refresh the "statistics", then ANALYZE TABLE is much faster, hence less intrusive.
PARTITION
If "old" data is being purged, then Partitioning is much faster than DELETE, and has no need for OPTIMIZE; should we discuss that?


How to join 2 tables when second table will return more rows? im trying to join 2 tables with condition - if it matches than im expecting to get all results from another table.
Current querry that doesn't work :
SELECT (CASE WHEN ZL.NumerCzesci LIKE '%ZŁOŻENIE%'
  THEN (SELECT NumerCzesci FROM PodZlozenie WHERE NumerPodZlozenia = ZL.NumerCzesci) ELSE ZL.NumerCzesci END) AS [NumerCzesci] FROM Zlozenie ZL LEFT JOIN PodZlozenie PD ON ZL.NumerZlozenia = PD.NumerPodZlozenia

Table [Zlozenie] looks like :
CREATE TABLE [dbo].[Zlozenie] (
    [Id_Zlozenia]   INT          IDENTITY (1, 1) NOT NULL,
    [NumerZlozenia] VARCHAR (50) NOT NULL,
    [NumerCzesci]   VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id_Zlozenia] ASC)
);
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-001 - WKŁADKA GÓRNA');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-002 - WKŁADKA DOLNA');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-003 - UCHO ROTORA 11 RAM 3 SEKCJE');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-005 - RAMIĘ ROTORA 565mm');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1');

Table [PodZlozenie] looks like :
CREATE TABLE [dbo].[PodZlozenie] (
    [Id_PD]            INT          IDENTITY (1, 1) NOT NULL,
    [NumerPodZlozenia] VARCHAR (50) NOT NULL,
    [NumerCzesci]      VARCHAR (50) NOT NULL
);
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-001 - BOK POKRYWY GÓRNEJ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-002 - DEKIEL POKRYWY GÓRNEJ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-003 - KĄTOWNIK')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-004 - WSTAWKA UZUPEŁNIAJĄCA')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-010 - BLASZKA PROWADZĄCA POKRYWĘ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-011 - POKRYWA DOLNA TYŁ BA3-1')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-012 - PŁASKOWNIK BA3-1')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-013 - WZMOCNIENIE POKRYWY')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-014 - UCHWYT')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-015 - POKRYWA DOLNA PRZÓD BA3-1')

#EDIT
Querry should return desired output as follow:
IF Zlozenie.NumerCzesci CONTAINS "ZŁOŻENIE" THEN SELECT PodZlozenie.NumerCzesci FROM PodZlozenie WHERE Zlozenie.NumerCzesci = PodZlozenie.NumerPodZlozenia ELSE Zlozenie.NumerCzesci

Desired output:
[NumerCzesci]
300-000-001 - WKŁADKA GÓRNA
300-000-002 - WKŁADKA DOLNA
300-000-003 - UCHO ROTORA 11 RAM 3 SEKCJE
300-000-005 - RAMIĘ ROTORA 565mm
300-020-001 - BOK POKRYWY GÓRNEJ
300-020-002 - DEKIEL POKRYWY GÓRNEJ
300-020-003 - KĄTOWNIK
300-020-004 - WSTAWKA UZUPEŁNIAJĄCA
300-020-010 - BLASZKA PROWADZĄCA POKRYWĘ
300-020-011 - POKRYWA DOLNA TYŁ BA3-1
300-020-012 - PŁASKOWNIK BA3-1
300-020-013 - WZMOCNIENIE POKRYWY
300-020-014 - UCHWYT
300-020-015 - POKRYWA DOLNA PRZÓD BA3-1

So if I understand your examples correctly it looks like you want the unique list of NumerCzesci values from both tables, except when Zlozenie.NumerCzesci contains "ZŁOŻENIE". Then you want to use PodZlozenie.NumerCzesci instead. You should be able to achieve that with a FULL JOIN and a DISTINCT clause like so:
SELECT DISTINCT
    CASE
        WHEN Zlozenie.NumerCzesci LIKE '%ZŁOŻENIE%' OR Zlozenie.NumerCzesci IS NULL THEN PodZlozenie.NumerCzesci
        ELSE Zlozenie.NumerCzesci
    END AS NumerCzesci
FROM Zlozenie
FULL JOIN PodZlozenie
    ON Zlozenie.NumerCzesci = PodZlozenie.NumerPodZlozenia

(You can optionally add ORDER BY NumerCzesci to the end of the above query if you want the results sorted by NumerCzesci in ascending order.)


Is where x = 0 or x is null to be replaced by where isnull(x, 0) = 0 resp. where NVL(x, 0) = 0 Think of x as a column containing null, 0, 1.
Only less than 1 % of the rows contain 1.
There is no logical difference between 0 and Null.

Is it a good idea to insist, that isnull rather than NVL is used here (performance) ?

If you're looking at it purely from just what's better from a performance perspective ... I'd just test both of your methods and see what's faster for your data.

... but as you only have three states anyway, and this is going in a where clause, it might be even faster to just check that it's not the third case, and then you don't need the NVL test:

column <> 1

Or, if there really is no difference (and NULL isn't a case of 'we don't know, so we're going to treat it for all practical purposes that it's 0, but we occassionally need to know when it's unknown'), just update the table and set it to 0 where it's currently NULL.  (and adjust whatever's feeding in the NULLs)


Slow logs are not showing full details I am new to mysql DB.
I have set log_slow_verbosity value to full.

root@localhost/(none)> SELECT @@global.log_slow_verbosity;
+-----------------------------+
| @@global.log_slow_verbosity |
+-----------------------------+
| microtime,query_plan,innodb |
+-----------------------------+

But I am unable to see innodb,query_plan info in the mysqld-slow.log

(A possible answer.)

Setting a global variable (anyone, not just this one), does not take effect immediately:

If you set it in my.cnf (etc), it would take effect until after a restart
If it is set globally (SET GLOBAL @@... ), it won't take effect until someone logs in -- he then gets that setting.  (Caveat: Some globals have no "session" counterpart, so they don't act this way.)
If you set it in your session, it does not apply to anyone else.

If this does not answer the question, please show us precisely how you set it.  And provide SELECT @@version;


Differential Backup of A Table In Sql Server I will transfer the datas of a two different table to a database in reporting server Daily but i don't want to do it manually and also don't want to transfer all the datas to that databases everyday. Instead of doing it, i wonder that if i can take full backup and then day by day differential bakcup or not? Is there a way doing this logic in sql server?

Consider using Transactional Replication

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-ver15

It allows keep up-to-date copies of your tables at reporting server, without copying any other tables/data that you do not need


Return row with most matching parameters I'm trying to make a stored procedure who will return me the RowID with most matching parameters. Let's say we have a table:

+-------+------------+------------+-----------+
| RowID | DocumentID | EmployeeID | CompanyID |
+-------+------------+------------+-----------+
|     1 |         36 | 1          | 4         |
|     2 |         36 | null       | 2         |
|     3 |         36 | 4          | null      |
+-------+------------+------------+-----------+

And I send let's say values DocumentID = 36 ,EmployeeID = 5, ComanyID = 2,
it should return me second row since DocumentID and CompanyID exist.

Some other situation would be, if I send DocumentID = 36, EmployeeID = 5 and CompanyID=10, it should return me first in the table.

If there is too little information about this problem, or it's not clear, feel free to ask for more detail.

;WITH c AS
(
  SELECT RowID,
    c = CASE WHEN DocumentID = @DocumentID THEN 1 ELSE 0 END
      + CASE WHEN EmployeeID = @EmployeeID THEN 1 ELSE 0 END
      + CASE WHEN CompanyID  = @CompanyID  THEN 1 ELSE 0 END
  FROM dbo.TableName
)
SELECT TOP (1) RowID FROM c ORDER BY c DESC;


Select records as groups based on sequence and date I have data stored as:

CompID  | Seq | Udate     | Utime
--------+-----+-----------+-------
mx433   | 1   | 1/1/2012  | 13:54
mx433   | 1   | 1/2/2012  | 13:43
mx433   | 2   | 1/4/2012  | 14:23
mx433   | 3   | 12/1/2013 | 13:54

I want to archive the data based on older than 1 year, however I want to keep the last set of records for every company based on the highest sequence number. I can't come up with a SQL statement to select this in order to move to another table.

Simple approach (can split sets)
Chain DELETE and INSERT with a data-modifying CTE for best performance and to be safe:
WITH del AS (
   DELETE FROM tbl t
   WHERE  udate < (now() - interval '1 year')::date
   AND    EXISTS (  -- is not the greatest
      SELECT 1 FROM tbl
      WHERE  compid = t.compid
      AND    seq    > t.seq
   RETURNING compid, seq, udate, utime
   )
INSERT INTO archive(compid, seq, udate, utime)
SELECT * FROM del;

This is assuming all columns to be defined NOT NULL (table definition is missing).
Depending on data distribution, there may be faster solutions.
I formulated the predicate Udate < (now() - interval '1 year')::date to be sargable. You should have an index on Udate (at least).
Ignoring Utime. You should replace date and time with a single timestamp column: udatetime timestamp. Smaller, more efficient.
Avoid splitting sets
To keep rows older than a year if at least 1 row in the same set (compid, seq) is still younger than a year:
WITH del AS (
   DELETE FROM tbl t
   USING (
      SELECT compid, seq
      FROM   tbl d
      GROUP  BY 1,2
      HAVING max(udate) < (now() - interval '1 year')::date
      AND    EXISTS (  -- is not greatest seq
         SELECT 1 FROM tbl
         WHERE  compid = d.compid
         AND    seq    > d.seq
         )
      ) d
   WHERE  t.compid = d.compid
   AND    t.seq    = d.seq
   RETURNING t.compID, t.seq, t.udate, t.utime
   )
INSERT INTO archive(compid, seq, udate, utime)
SELECT * FROM del;

This first groups by compid, seq and checks if the maximum date in the group is old enough to be moved.
SQL Fiddle.


Can graph databases be easily distributed? I have never worked with graph databases such as neo4j, I would like to know if they support easily replication and clustered distribution as well as key-values databases, or if there inherent structure makes it really difficult/ slow

even that the way to distribute data in graph databases is not that easy as in key-value stores, where the keys are distributed by ranges, there are techniques to distribute a graph.

InfiniteGraph (made by Objectivity) for instance is a highly distributed graph database. It uses a P2P technology to increase read and write performance by growing the hardware. It allows the user to cluster a certain part of the graph or a certain kind of information as its needed and to distribute it over some machines as its needed too.

As I heard this process of clustering/distribution will be eased for the user in release 3.0. A parallel query mechanism is also planned, that allows to run one query concurrently on the machines.