SQL Server 2012. Update second table based on input from first table I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the web site. When this happens, is there a way on the SQL Server backend to update/insert that value into Database02?

There are two options that come to mind. The first you have identified as a trigger that you could implement in TSQL for real-time synchronization of data. Depending on the activity of your system, the quality of your hardware (particularly I/O subsystem).

Synchronous approach

A trigger, implemented something like this. You would know better specifically what logic you need but the heart will be an AFTER trigger on the table in the system of record.

CREATE TRIGGER dbo.tr_TableSync
ON dbo.Table1
AFTER INSERT, UPDATE
BEGIN
    SET NOCOUNT ON;

    -- sync existing
    UPDATE
        T2
    SET
        RefVal = I.Val
    FROM
        T2
        INNER JOIN
            inserted AS I
            ON I.SK = T2.SK;

   -- Add new stuff
   INSERT INTO
       T2
   (
       SK
   ,   RefVal
   )
   SELECT
       I.SK
   ,   I.Val
   FROM
       inserted AS I
   EXCEPT
   SELECT
       T2.SK
   ,   T2.RefVal
   FROM
       T2;

   -- Do we need to account for deleted items?
   -- If so, need to add to declaration above
   -- and code here
END

Asynchronous approach

The challenge around the trigger is that it can have a heavy footprint on performance to data manipulation operations in that table. SQL Server offers two approaches for asynchronous operations that provide a lighter footprint. You can look at implementing Service Broker or creating a CLR trigger.

Service Broker is a message queue. You'd create a message in your AFTER trigger (unless you can guarantee all manipulation is only performed through a proc or some other codified method) which will then get parsed when the system has a "chance to breathe" but it is guaranteed to happen just not RIGHT NOW DO IT NOW I'M WAITING ARE YOU DONE like the synchronous approach above forces.

I've never had cause to implement a CLR trigger before but it's possible and in your case, it'd be much like implementing your own message queue.

Final thought

Everyone loves to say "it needs to be instantaneous/real-time" but when they learn what that can cost to implement correctly, near-real-time of a few minutes to a day (yes, I've had people go from it must be accurate to the nano-second to as long as it's within 24 hours, we'll be fine). If that can satisfy your business needs, then you have even more doors opened (a SQL Agent job that fires SQL statements or SSIS or a custom .NET app every N interval).


Enabling Edition Based Redefinition failed with ora-33819. Could I query why? I want to enable Edition Based Redefinition for a user. Executing this command gives an ORA-33819 exception:

ALTER USER user ENABLE EDITIONS;

I know there's an option to enable it with the parameter Force

ALTER USER user ENABLE EDITIONS FORCE;

but as this operation is retroactive and irreversible, I would rather see beforehand why it failed. Does anyone know how to get a list of problems before altering the user?

Working on an Oracle 12.1.0.1.0

You can query something like this:

select o.owner, o.object_name, o.object_type, edition_name, editionable from dba_dependencies d, dba_objects o
where o.owner = d.referenced_owner
and o.object_name = d.referenced_name
and d.owner = 'USER'
and o.editionable = 'Y'
and o.edition_name is NULL;

This is will list the objects referenced by user 'USER', that are editionable but dont have any editions - thus preventing you from enabling EBR for 'USER'.


How to update a field in a nested JSON document in Mongo? How to update a field in a nested JSON document in Mongo?

{
  _id : '123'
    friends: [
      {name: 'allen', emails: [
        {
          video: {
          name:'abc',
          subType:'video',
          status:'active'
              }
        },
        {
          video: {
          name:'abc',
          subType:'video',
          status:'active'
              }
        }
        ]}
    ]
 }

I have tried the following query but I am not able to update the document.

db.users.update ({_id: '123'}, {$set: {"friends.0.emails.0.video.status" : 'Inactive'} })

As per MongoDB documentaion here The update method can modify specific fields of an existing document or documents or replace an existing document entirely, depending on the update parameter.

By default, the update() method updates a single document. Set the Multi Parameter to update all documents that match the query criteria.

The update() method has the following form:

Changed in version 3.6.

db.collection.update(
   <query>,
   <update>,
   {
     upsert: <boolean>,
     multi: <boolean>,
     writeConcern: <document>,
     collation: <document>,
     arrayFilters: [ <filterdocument1>, ... ]
   }
)

For example to update the JSON documents through update command in mongo shell, which you have been mention.

Here I have created the test database & also users collection based on your mention documents.

> use test
switched to db test
>db.createCollection("users");
{ "ok" : 1 }
> db.users.insert({
...   "_id" : '123',
...     "friends": [
...       {name: 'allen', emails: [
...         {
...           video: {
...           name:'abc',
...           subType:'video',
...           status:'active'
...               }
...         },
...         {
...           "video": {
...           "name":'abc',
...           "subType":'video',
...           "status":'active'
...               }
...         }
...         ]}
...     ]
...  })
WriteResult({ "nInserted" : 1 })

Now I want to see the inserted documents in users collections in mongo shell through the command such as

> db.users.find().pretty();
{
        "_id" : "123",
        "friends" : [
                {
                        "name" : "allen",
                        "emails" : [
                                {
                                        "video" : {
                                                "name" : "abc",
                                                "subType" : "video",
                                                "status" : "active"
                                        }
                                },
                                {
                                        "video" : {
                                                "name" : "abc",
                                                "subType" : "video",
                                                "status" : "active"
                                        }
                                }
                        ]
                }
        ]
}

As you want to update the status : Inactive , which comes under the metadata of video.
The update statement command will be as follows

> db.users.update ({"_id": '123'}, {$set: {"friends.0.emails.0.video.status" : 'Inactive'} });
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

Again I will check through the find method , it has been modified or not.

> db.users.find().pretty();
{
        "_id" : "123",
        "friends" : [
                {
                        "name" : "allen",
                        "emails" : [
                                {
                                        "video" : {
                                                "name" : "abc",
                                                "subType" : "video",
                                                "status" : "Inactive"
                                        }
                                },
                                {
                                        "video" : {
                                                "name" : "abc",
                                                "subType" : "video",
                                                "status" : "active"
                                        }
                                }
                        ]
                }
        ]
}


How can I create a database where entries are compressed files searchable by index? I am trying to design a database solution for logging where the search indices are dates (days), and the values are zipped files. I am doing this because the sheer number of logs I am processing is too great to simply store in a database. The zipped files would be all logs within the date (day) index. Once a user queries for a date, I would like to be able to query the logs associated with that date. This would require the program to uncompress the zipped file and provide parameters for the log once the user specifies the date.

Or, is there way (similar to zgrep) to search through compressed files and get corresponding date values?

I could find no information on this online. I am open to anything, relation or non relational databases.

Have you tested the lz6 compression level?  lz6 provides less overall compression but it has a index on values which you can pull out without going through the overhead and cost of uncompressing the whole file.  I

If you cannot give up any compression at all, have you tried a simple json document store DB such as Mongo?  I feel using filestream in SQL Server might be overkill but it depends on your environment.  It seems like you're a linux shop from the terminology used, is that correct?

I feel you can use any DBMS to store the simple values such as customerid, date, and filepath then pass the filepath over to your app.  It depends on what solutions you already have in place or which you're very comfortable with that aren't overkill.  For example, Hadoop or something complex would be crazy for this but a key value store DB might be perfect.  Maybe you can make the zip files have the customer id or something in it and only store the date (key) and path (value) in your document store.


just 9 joins
if ID is indexed the join will be efficient
if you have gaps in ID that needs to be compressed then this does not work

select row1.*
from table row1
join table row2
      on row2.id = row1.id + 1
     and row1.value = 0
     and row2.value = 0
join table row3
      on row3.id = row1.id + 2
     and row3.value = 0
join table row4
      on row4.id = row1.id + 3
     and row4.value = 0
...
join table row10
      on row10.id = row1.id + 9
     and row10.value = 0

if no negative values

 select row1.id
   from table row1
   join table row2
         on row2.id >= row1.id
        and row2.id <= row1.id + 10
  group by row1.id
 having sum(row2.value) = 0


Function to update column value if the row was updated I am trying to create an function which will increment my version column value (int) if I update any column of my database row.
For instance, if i have a foo table that has columns (id, title, version) and the default version is 1, I want my vesrion to be incremented once I update a title of my single foo row. I wrote the following function but it throws an error once I test it:

create or replace function update_foo_version()
returns trigger as $body$
  begin
    update foo
    set version = version::int + 1
    where old.id=new.id;
    return new;
  end
$body$
language plpgsql;

My trigger for this function:

create trigger update_version
after update on foo
for each row execute procedure update_foo_version();

It's hard to tell where you're going wrong with the error message but I'll speculate on two things. You want a BEFORE trigger, and this won't work:    where old.id=new.id;

I think what you want is,

create or replace function update_foo_version()
returns trigger as $body$
  begin
    new.version = old.version + 1;
    return new;
  end
$body$
language plpgsql;

create trigger update_version
BEFORE update on foo
for each row execute procedure update_foo_version();


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

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

The Log file follows:

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

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

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

innodb_force_recovery = 4

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

What could be the issue here?

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

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


Using PL/PerlU with a non system perl lib psql -1f check_email.pl.sql ccushing postgres
SET
psql:check_email.pl.sql:7: ERROR:  Can't locate Email/Valid.pm in @INC (@INC contains: /usr/lib/perl5/site_perl /usr/share/perl5/site_perl /usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib/perl5/core_perl /usr/share/perl5/core_perl .) at line 3.
BEGIN failed--compilation aborted at line 3.
CONTEXT:  compilation of PL/Perl function "check_email"

My current @INC is

@INC:
  /home/ccushing/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/x86_64-linux
  /home/ccushing/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2
  /home/ccushing/perl5/perlbrew/perls/perl-5.14.2/lib/5.14.2/x86_64-linux
  /home/ccushing/perl5/perlbrew/perls/perl-5.14.2/lib/5.14.2

obviously my problem is that Email::Valid is not in my @INC, can I globally change Postgres @INC Paths? also related is postgres only capable of using the Perl it's built against? or can I make it use my perlbrew.

Can't you just push it into @INC before you use it? At the first line in your func? I'm pretty sure that should work.

PostgreSQL will only use the perl it's built against. You'd have to rebuild it (or at least plperl) against your custom perl.


pros/cons of different ways to store whether a record is one of two options? I am trying to store whether an address is a Work address or a Home address. There will never be another type of address.

I'm wondering what the pros/cons are of the different ways to store this, and if there is an accepted 'style' for this type of situation which is considered best practice,

Would it be better to just have a single
1)IsHome bool column, and if it's false, I just assume it's a work?
2) or both a IsHome and IsWork column,
3) or a AddressType column which is an ID that would correspond to another table which  has work and home with an ID?
4) or something I have not considered?

The third option seems a little cleaner however needing to join every time seems inefficient.

If you are really sure that those types don't change (or at least not often) you can also use a variation of 3) using a column AddressType of type varchar with a check constraint that limits the values to 'work' and 'home'.

This is less flexible than a lookup table, but still better than a boolean that has some implicit meaning when set to false. And you wouldn't need the join (although I doubt a join against a table with two rows will be noticable)


SQL Server many active create procedure processes While troubleshooting performance issues on a SQL 2008 instance, I've noticed there are several SPIDs which appear to be executingcreate procedurestatements for already existing procedures.

Is this part of SQL's normal internal functioning, or do I have some odd application logic going on here? I can find these procedures listed in SSMS, and it says they were created years ago.

Thanks.

This is not running the create procedure statements. The text you are receiving is the body of the procedure that has been called, not the actual call (I assume you are catching SP:Completed and not SP:StmtCompleted). If you are looking at for example sys.dm_exec_sql_text() you should also get start_offset and end_offset values that will allow you to pinpoint the actual text that is being executed as part of the call, and there are also ways to see the actual call but it depends largely on how you are monitoring now and how you are sending your procedure calls to SQL Server.


Ola Hallengren IntegrityCheck Excludes TempDB According to SQL Server Maintenance Solution - SQL Server Integrity Check, the SYSTEM_DATABASES value includes only master, msdb, and model.

Why is TempDB excluded, when it too can have hardware issues causing page corruptions?

Interestingly enough, DBCC CheckDB FAQ: Check for Corruption in SQL Server by Kendra Little talks about the need to check TempDB but points to the Ola Hallengren script, which does not appear to be checking TempDB by default.

Answer based on a comment left by Randi Vertongen:

Ola mentioned a long time ago (+5 years) on SQL Server Central that this is by default:

  This is by design. DatabaseIntegrityCheck is not doing checkdb on tempdb.

and a way around it by adding it explicitly.

  In the latest version you can do like this:

EXECUTE dbo.DatabaseIntegrityCheck
      @Databases = 'SYSTEM_DATABASES,tempdb',
      @CheckCommands = 'CHECKDB'

He goes on to say:

  When I developed the scripts I was looking at how Microsoft was doing in the Maintenance Plans. They are not doing checkdb for tempdb, so I did also not do that. Some time ago I added support for checking of tempdb, but not as the default. You need to specify tempdb explicitly.

Not entirely sure if this is still the case.


Transaction Time Tables in MSSQL and Constraints I am trying to implement transaction time tables on a MSSQL Server 2016 following the book by Richard Snodgrass (Developing Time-Oriented Database Applications in SQL).
A table with a sequenced primary key through a trigger could be implemented like this:
CREATE TABLE test_table (
a [tinyint] NOT NULL,
b [date] NOT NULL,
c [float] NOT NULL,
[tt_start] [datetime2](7) NOT NULL,
[tt_end] [datetime2](7) NOT NULL)
GO

CREATE TRIGGER Seq_Primary_Key_tt ON test_table FOR INSERT, UPDATE, DELETE AS
  BEGIN
    IF (( EXISTS ( SELECT * FROM test_table AS b1
    WHERE 1 < (SELECT COUNT(b2.a) FROM
        test_table AS b2
        WHERE b1.a = b2.a AND
        b1.b = b2.b AND
        b1.tt_start < b2.tt_end AND b2.tt_start < b1.tt_end) )))
    BEGIN
        RAISERROR('Transaction violates sequenced constraint', 1, 2)
        ROLLBACK TRANSACTION
    END
 END

 GO

The trigger is pretty slow on larger tables. Therefore, using multiple INSERTs leads to long waiting times. Furthermore, many operations like updates require more than one operation (e.g. INSERT, then UPDATE). This doesn't work with this trigger, because the trigger would start already after the INSERT and fail, although the operation would perfectly work out if the trigger would start after the UPDATE.
That is why Snodgrass writes that the constraints/assertions (or here the trigger) have to be DEFERRABLE INITIALLY DEFERRED. In that way, the constraints would be checked after all operations have finished. This would at the same time also increase the performance of multiple INSERTs.
However, as far as I see, MSSQL Server doesn't implement DEFERRABLE INITIALLY DEFERRED. How could a similar constraint or trigger be then implemented? Or could the "temporal tables" feature be a replacement for what I am trying to do?

The trigger is pretty slow on larger tables.

You don't have any indexes, and you aren't limiting the check to the rows affected by the statement using the INSERTED virtual table.  See: Use the inserted and deleted tables

many operations like updates require more than one operation

In scenarios like this you're better-off managing the updates through a stored procedure, rather than trying to handle everything with a trigger.  For instance if you insert an adjoining interval, you'd probably want to coalesce them.


How to handle large database updates with node js I have a program which is time based. The users have all day to enter data that will affect their score, however if you haven't entered anything by midnight then your score is negatively affected. I had thought about setting up a chron job to handle this at midnight, but am then faced with a batch process that may be updating hundreds of thousands of records all at once.

I've decided one way to reduce this would be to divide the batch into 1000 records and then release the event loop, stack on another 1000. Is this a good way to go about something so massive? Would creating a second server instance and offloading the work to that server be a better idea?

Please provide proof that 500k rows is a lot for your database.

Hint In most cases,  it isn't.

Although this answer could befit a Comment, this is a valid Answer.

Databases deal with MILLIONS of rows at a time without a hiccup.  500k is nothing.

Things you need to be aware of when testing the single DML approach

Undo tablespace (or MySQL's equivalent)
possibly temp tablespace (for indexes)
locks on a heavily modified table.

If you have proof that one (or more) of those is causing issues, then you should consider Chunking your UPDATE statement.

You haven't given any indication that any of those is a problem.  So, the answer is:  Neither.  Use a single DML.

As far as "process on separate server"...I don't see the need.  I foresee too many headaches with synchronizing the processed rows with any modified data that occured during the Processing.


Store VarChar(Max) [Notes] Column on Same or Separate Table Disclaimer

I had and idea for a table design a little while ago that made sense to me at the time.  In recent history I have realized I have a habit of "over engineering" or spending too much time trying to "over optimize" solutions.  I am assuming that this is one of those instances where I am over thinking the solution and will create extra overhead with little to no actual gain.

Question

Assume for an object (a Shipment table for example) there is some kind of NOTE varchar(MAX) data element we want to keep track of.  The NOTE column has the opportunity to have data pushed into an overflow and dramatically increase the size of the row (thus limiting the number of rows that can be saved on the page).  As I understand it, this then negatively impacts the execution time of various operations on the table as a whole.

Is there ever an instance where we should push that column into a separate stand alone ShipmentNote table instead of keeping it as a column on the Shipment table?  The theory being, if we push the NOTE column into a separate table it saves the pages on the Shipment table which allows all operations on the Shipment table to perform better.  Because the row size is smaller and now you can fit more rows onto the same page.

(See schema examples below):

The main use case where this might be a good idea is if:

The Note column is regularly over 8000 characters (which I think is when we start using the extra paging)
The Note column only returned in SELECT operations and is rarely or never used as part of a JOIN or WHERE
The remaining columns on Shipment are going to be queried independent of Note on a regular basis (IE: most of our operations, will not utilize Note, be that JOIN or WHERE conditions happening on other columns in `Shipment)

The disadvantages I am seeing (outside of this may not actual make noticeable improvements in working with the Shipment table outside of the Note column):

It now becomes impossible (or at least requires a trigger or something else) to ensure that there is always some kind of value for NOTE (IE: because it is now on a separate child table we can't ensure that NOTE is NOT NULL for every row in Shipment
Any operation utilizing NOTE will now take extra effort, because of the need to do a JOIN to make sure we are working with the correct record

While its great that you are even thinking about this - I would consider it to be micro-optimisation. SQL Server already has optimisations built into it for handling NVARCHAR(MAX) and so generally I would say to not second guess it until it becomes a problem. However, there are two main points that came to mind when reading your question.

The first is (as you touched on with your last point) would there actually be any performance gained by splitting the data out into another table over letting SQL Server optimise the pages and data itself? I don't have hard evidence with me right now but I suspect not. The reason is that, as you mention, your data is regularly over 8000 bytes and therefore will be moved to LOB pages in your Note table anyway. You would just be moving the perceived problem from one table to another. Add the overhead of performing a JOIN when having to fetch the data and you are likely end up slightly worse off than you were to begin with.

Secondly, as you tagged the question with database design, my actual recommendation is that you do split out the notes into another table. However, my reasoning is not so much driven by the performance optimisation but more specifically adding flexibility to the application. It is highly likely that a shipment may need more than one note attached to it (for example if it is delayed on transit, or a customer calls in etc...). If you have the note on the actual shipment itself it makes it harder to do this without duplicating data. If you extract it into its own table you will be able to assign multiple notes to a shipment, and track who entered them and when. This makes your application and database more flexible.

If you want to optimise then I would recommend removing the need to query the notes unless someone specifically wants to view them (such as having a dedicated form in your application to do so). That way, you only need to perform the join/lookup when somebody explicitly wants to view the data. Even if you didn't externalise the notes to another table, not including the column in the query if it is not needed will remove the need for SQL Server to read the LOB page. If possible I'd always optimise queries to return the least amount of information required before optimising at a micro level.


Permissions to be granted to developers to investigate jobs, queries, and packages I have just installed a new sql server 2014 server, migrated some databases there and connected some applications to them.

These servers have also SSAS and SSIS.

I have a group of developers there were helping with the connections and doing all the last developments on packages, and data warehousing stuff.

Now that everything is working, I want to remove the sysadmin permissions that I had previously granted them.

Now, these guys they have some requirements,

that I am willing to grant them, so that it is less weight on my shoulders:

  1) They need to see query plans, 2) they need to identify missing
  indexes and other expensive operations like key lookups, etc

  3) they need to query sys views and tables - monitor what procedures
  are running, collect WAIT STATS, etc

  4) they also need to troubleshoot JOBS - so they need to be able to
  see them, see the job history, in order to find out what went wrong

  5) they also need to see everything in the SSIS integration catalog,
  so that when something goes wrong inside one of there packages they
  are able to figure it out by themselves

I have granted them with the following SERVER permissions:

  1) view server state

  2) view any definition

on the MSDB database:

I got some ideas from "SQL Server Agent Fixed Database Roles"

and finally on the SSISDB database

Anything else that I should be aware of?

Don't grant ssis_admin if the dev shouldn't be sysadmins:

  Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin.

Source: https://msdn.microsoft.com/en-us/library/ms141053.aspx

Personally I would never let the devs create jobs with their accounts. As soon as they leave the company and someone disables the AD account, all their jobs stop working...

Btw: don't grant server rights directly but with a serverrole.


How to view the full, flattened query underlying a Postgresql view? If I have a view on a view on a view (etc.), is there a way to show the full query that this expands/flattens to?

Not in PostgreSQL at this time. PostgreSQL doesn't actually build a single giant SQL statement; instead, it uses the plan tree of the view(s) and inserts that into the top level query's plan tree. To turn that back into SQL would require a deparser, something PostgreSQL doesn't have a the moment.

You can kind-of do-it-yourself by replacing references to the view with the text of the view query, but you need to understand that the effect isn't exactly the same, particularly where there are multiple references to the view.

You're best off using explain and/or explain analyze to observe what the query does.


How to dive into the PostgreSQL's optimizer and trace each step it takes to optimize the query? Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Does PostgreSQL have the similar structure which records steps the optimizer takes?

No, there's no detailed optimizer audit/reporting structure or function.

Your best tool is 'gdb'. Unlike MS SQL Server, PostgreSQL is open source; you can just build with debug symbols enabled, attach a debugger, and trace exactly what it's doing.

That doesn't mean it's easy, of course. A canned set of breakpoints and gdb macros to produce a trace of what the planner/optimiser are doing would be very nice. I'm not aware of any such tool at this point.

gdb's tracepoints, or perf userspace tracepoints, will be very useful to you.

You can also enable detailed debug logging and some of the trace parameters in postgresql.conf then examine the resulting detailed log files.

There's some documentation (which I've added info on dumping plan trees, etc, to) in the PostgreSQL developer FAQ: https://wiki.postgresql.org/wiki/Developer_FAQ . This may be useful. You should also read the main developer docs.


Few Questions about SSAS Cubes I am newbie to SSAS 2012 and here are my questions:

Can we have measures of complex types (table)?
Is it possible to order the results by a specific measure or attribute?
My cube can be modeled as one dimensional hierarchy, is this generally good?
Can I shard cubes using remote partitions on a range of members?
Do we have remote table partitions with SQL Server 2012 databases?

Thank you!

Answers to your questions, in order:

First point: I'm not sure what you're getting at here.  A measure can only be something that will display in a single cell.  You can aggregate stuff up to create a measure.  You can also have multiple fact tables (called measure groups) in a cube.  If you slice by common attributes they measure groups will slice by those attributes.  If you slice by something that one of the measure groups does not have in common, the values will be repeated for each slice of the other group.
Yes.  You can order by attributes or values of measures.
Probably OK.  I can't see any harm in that.  Can't see a lot of point in it either, but if you really only have one drill down path there's no reason that why that can't be implemented in a cube.
Yes, you can do distributed partitions.  Never built a system with remote paritions personally, but there's a technet article here..
SQL Server will do distributed partitioned views over partitions on multiple separate instances with Enterprise Edition.  There is a shared-nothing version as well, but you can only buy that in a hardware bundle from an OEM.  It doesn't seem to be getting a lot of traction in the market.


SQL Server not using all NUMA memory with 20 core limit and affinity mask I have a physical SQL Server 2016 SP1 machine with this spec:

CPU Sockets:      2
Cores per socket: 44
Multithreading:       Yes
Number of cores:  88
RAM:  256 GB
SQL Server Max Memory:    240 GB
NUMA: CPU1    128 GB
CPU2    128 GB

We are running SQL Server 2016 Enterprise Edtion CAL licensing, not Core, so with licensing limitations we can only utilise 20(40) cores.
By default affinity mask enables the first 40 cores on CPU1. This means that CPU2 and the NUMA node memory are not used.

We have changed the affinity mask to enable CPU1 and CPU2 and therefore make the memory on both NUMA nodes available to SQL Server.

ALTER SERVER CONFIGURATION

SET PROCESS AFFINITY CPU = 1 TO 20, 65 TO 84;

Info and selects from Glenn Berry https://www.sqlskills.com/blogs/glenn/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes/

-- SQL Server NUMA node information

SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
   active_worker_count, avg_load_balance, resource_monitor_state FROM sys.dm_os_nodes WITH (NOLOCK) WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

-- SQL Server schedulers by NUMA node

SELECT parent_node_id,   SUM(current_tasks_count) AS [current_tasks_count], SUM(runnable_tasks_count) AS [runnable_tasks_count],   SUM(active_workers_count) AS [active_workers_count],   AVG(load_factor) AS avg_load_factor FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE [status] = N'VISIBLE ONLINE' GROUP BY parent_node_id;

Info and selects from David Alcock https://www.sqlshack.com/monitoring-memory-clerk-and-buffer-pool-allocations-in-sql-server/

SELECT TOP 10 [type], SUM(pages_kb) / 1024 AS SizeMb FROM sys.dm_os_memory_clerks GROUP BY [type] ORDER BY SUM(pages_kb) / 1024 DESC

This indicates that around 90 GB of memory is being used.

Databases on this server add up to 285 GB.
sys.dm_os_buffer_descriptors reports that the buffer pool is using 65 GB with 10 GB empty.

select * from sys.dm_os_process_memory

physical_memory_in_use_kb   large_page_allocations_kb   locked_page_allocations_kb  total_virtual_address_space_kb  virtual_address_space_reserved_kb   virtual_address_space_committed_kb  virtual_address_space_available_kb  page_fault_count    memory_utilization_percentage   available_commit_limit_kb   process_physical_memory_low process_virtual_memory_low
249659780   0   0   137438953344    489700832   251863768   136949252512    715604512   100 47434048    0   0

Is there a way to tell for definite if all memory is being used? Or does the fact that 4 nodes are offline mean that is the case?

Many Thanks

1) Look at the results of sys.dm_os_memory_nodes. If you configured SQL Server correctly you should see rows for memory_node_id 0 and 1. That means that SQL Server has access to all of the memory on your server.

I have memory_node_id 0 and 1.

2) Look at the results of sys.dm_os_memory_clerks for your favorite memory clerk that is NUMA aware. For example, you can see if the buffer pool is spread over multiple NUMA nodes:

SELECT memory_node_id, pages_kb FROM sys.dm_os_memory_clerks WHERE [type] = N'MEMORYCLERK_SQLBUFFERPOOL';

You are likely to see pages for both memory node 0 and 1. It's possible that you won't, and that doesn't necessarily represent a problem.

This shows pages for both node 0 and 1.

3) Look at the results of sys.dm_exec_query_resource_semaphores. Target_memory_kb should be around 70-80% of max server memory. If it's closer to 96 GB than 192 GB then you might have a problem.

target_memory_kb is 150-180 GB for resource_semaphore_id 0. The higher value is 70% of max server memory.

This all indicates that all memory is available to SQL Server, even if it isn't all currently being used. And the bufferpool usage will increase over time.

Thanks

In general, SQL Server grows used memory only as needed. You cannot conclude that memory local to a NUMA node is not available to SQL Server because each NUMA node has 128 GB of memory and a query suggests that only 90 GB of memory is in use. If you'd like to validate your configuration I know of a number of ways to do that, but not all of them are production friendly. In all cases, you can compare the results that you get now with what you had with the default affinity mask.

1) Look at the results of sys.dm_os_memory_nodes. If you configured SQL Server correctly you should see rows for memory_node_id 0 and 1. That means that SQL Server has access to all of the memory on your server.

2) Look at the results of sys.dm_os_memory_clerks for your favorite memory clerk that is NUMA aware. For example, you can see if the buffer pool is spread over multiple NUMA nodes:

SELECT memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE [type] = N'MEMORYCLERK_SQLBUFFERPOOL';

You are likely to see pages for both memory node 0 and 1. It's possible that you won't, and that doesn't necessarily represent a problem.

3) Look at the results of sys.dm_exec_query_resource_semaphores. Target_memory_kb should be around 70-80% of max server memory. If it's closer to 96 GB than 192 GB then you might have a problem.

4) Restart SQL Server after enabling TF 834 as a startup parameter. With trace flag 834, SQL Server upon startup grows the buffer pool using large pages to the maximum size that it is able to do. You might not see max server memory if the memory within the guest OS is fragmented. Enabling this trace flag will have some effect on your workload, so I can only recommend running a test like this in a non-production scenario. However, it might be a good option if you truly need to see SQL Server using all of the memory that it can with your own eyes.


Powershell script executing within SQL Server Agent I am trying to use Powershell within a SQL Server Agent job to download a zip file. The script uses PuTTY (PSCP.exe) to download a zip file from a SFTP site.

The issue I am having is that when the job runs it connects to the SFTP site and PuTTY sends a prompt back about storing the server's host key in the registry. I don't want to do this so I am trying to pipe the echo n command to PuTTY. This doesn't seem to be working though.

$SrcPath = "/somedirectory/somewhere/files/"
$DstPath = "D:\Download\"
$currentDate = (Get-Date).ToString('yyyyMMdd')
$FileName = "$currentDate.zip"
$ArchivePath = "D:\Archive\"

$File = "$SrcPath$FileName"

Set-Location $DstPath

echo n | C:\"Program Files (x86)"\PuTTY\pscp -P 99999 -pw password username@{IP_ADDRESS}:$File $DstPath
# Check the file is there
If (Test-Path "$DstPath$FileName")
{
  # Unzip the contents
  C:\"Program Files"\7-Zip\7z.exe e "$DstPath$FileName"
  #Move the zip file to the archive directory
  Move-Item $DstPath$FileName $ArchivePath -force
}

If I open up a command window and do this manually it works fine.

e.g. Typing this in the command window

C:\"Program Files (x86)"\PuTTY\pscp -P 99999 -pw password username@{IP_ADDRESS}:/somedirectory/somewhere/files/20121025.zip D:\Download\

and then pressing n when the prompt appears, downloads the files correctly.

When the job runs via SQL Agent, it errors initially complaining about the host key and then states that the file does not exist.

Can anyone point me in the right direction?

Your first problem is that Putty/PSCP want to store the host key for each user for security reasons (stop Fred from storing a fake host key that can be used to con George into trusting a fake server). The -batch option won't override that as it's seen as a flaw in the security process.

So it's fine when you run it interactively, as you can accept the key for your account. When you run it via SQL Agent, then it wants to store it for the user running the SQL Agent service.

If you are running SQL Agent under a 'normal' user account, then one way around this is to log in interactively with that account, run the pscp command and accept the host key. This will then be stored for future runs.

The other option is to look at using another tool for the SCP functionality. Personally I'm a fan of winscp ( http://winscp.net/ ) for this. WinSCP allows you to specify the host key as part of the connection string (here is a PowerShell example - http://winscp.net/eng/docs/library#powershell ).


Is there any drawback of MIXED replication mode? So, recent mysql server allows three types of replication mode: STATEMENT, ROW, MIXED. They all have pros/cons, and I pick the mode according to site characteristics.

When I get a replication error I sometimes need to examine the binlog files using mysqlbinlog, so I tend to use MIXED mode. ROW based replication doesn't let you see it because contents is not very readable. I'm thinking MIXED mode is 'best of both worlds'.

I haven't yet seen a lot of discussion regarding MIXED mode, much less drawbacks of using it. Can someone tell me few reasons to be cautious?

Here are some disadvantages

For you MyISAM users out there, concurrent inserts are prohibited.
Data changes cannot be read unless you use mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose.
BLOB data is written "as is" rather than the SQL that created the BLOB
For more information, see the MySQL Documentation


Does PostgreSQL/PostGIS use R-Tree or R*-Tree? PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a bit different than the original R-Tree, an observation being that the split technique is the Korotkov split. Furthermore, on PostGIS source, there are comments suggesting R*-tree ideas. I am somewhat unsure...

So my question is: is the "R-Tree behaviour" offered in PostgreSQL the vanilla R-Tree? If not, what are some of the differences? Is this data loaded insert by insert or is there a bulk loading mechanism in place?

I'm not sure what is the vanilla R-Tree but there is internal citations inside the source file you linked.  Also, the R-Tree inside of PostGIS is directly copied from PostgreSQL with these minor modifications (as in the FAQ),

  Why aren't PostgreSQL R-Tree indexes supported?

  Early versions of PostGIS used the PostgreSQL R-Tree indexes. However,
  PostgreSQL R-Trees have been completely discarded since version 0.6,
  and spatial indexing is provided with an R-Tree-over-GiST scheme.

  Our tests have shown search speed for native R-Tree and GiST to be
  comparable. Native PostgreSQL R-Trees have two limitations which make
  them undesirable for use with GIS features (note that these
  limitations are due to the current PostgreSQL native R-Tree
  implementation, not the R-Tree concept in general):

  R-Tree indexes in PostgreSQL cannot handle features which are larger than 8K in size. GiST indexes can, using the "lossy" trick of substituting the bounding box for the feature itself.
  R-Tree indexes in PostgreSQL are not "null safe", so building an index on a geometry column which contains null geometries will fail. [GiST indexes are null-safe]


SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed? We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.)

Both databases are identical with many foreign-key constraints on tables.

Within a transaction we insert rows to various tables. The whole transaction will leave the tables in a state where all constraints are fulfilled.

But it may very well be, that in an intermediate state of the transaction a row gets inserted with a FK of another table that has not been inserted yet.

Is there a way to let SQL Server accept this and only check for violations once the whole transaction is committed?

Thanks for your help!

No, server checks Foreign keys for every statement inside transaction

The way is to disable all the FKeys in target DB just after the beginning of transaction and enable them back just before commit.

Useful script:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + N'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id)+N'].['+OBJECT_NAME(fk.parent_object_id)+N'] NOCHECK CONSTRAINT ['+fk.NAME+N'];'+NCHAR(13)+NCHAR(10)
FROM sys.foreign_keys fk

PRINT @sql


MySQL - how to dynamically add columns with subquery in Select clause based on date range I have the following statetment:

 Select a.Data AS Data_A,b.Data AS Data_B,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = x) and
  (Month(c.TimeStamp) = y)
) AS MonthYear

From Table1 a  Left Join Table2 b on (a.ID = b.ID)

Ist there any way to add a column to the select clause based on my desired range of date? (where Year(c.TimeStamp) < 2019 and Month(c.TimeStamp) <= 12)

Example1 - i want to see the data for 1 month:

Select a.Data AS Data_A,b.Data AS Data_B,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = 2018) and
  (Month(c.TimeStamp) = 1)
) AS Jan2018

From Table1 a  Left Join Table2 b on (a.ID = b.ID)

Example2: i want to see the data for 3 months

Select a.Data AS Data_A,b.Data AS Data_B,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = 2018) and
  (Month(c.TimeStamp) = 1)
) AS Jan2018,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = 2018) and
  (Month(c.TimeStamp) = 2)
) AS Feb2018,
(Select Round(Sum(C.Value)) From Values c
  Where
  (Year(c.TimeStamp) = 2018) and
  (Month(c.TimeStamp) = 3)
) AS Mar2018

From Table1 a  Left Join Table2 b on (a.ID = b.ID)

I hope my question is understandable. :)

Thank you for reading.

You must construct different queries to get 1 column of output versus 3 columns.  That is best done in your application language.  If you are trying to do it entirely in SQL, it would require a messy Stored Procedure.


Is this database design correct? I'm trying to represent this:

A message can be sent to one or more recipients.
A user (recipient) can receive zero or more messages (but he/she could receive a
message only once).

Is my database design correct?

In nutshell that what you are looking for.
You do need composite primary key "binding" those 2 FK in intersection table.
(all attributes are omitted for brevity)

USERS can receive zero - or - more messages
Messages can be sent to one - or - more users.

ps.

Tool i used to generate image is a bit buggy - line bewteen mail_box and Users should be dotted.


SQL Server 2016 Force Encryption Option We are using SQL Server 2016 and want to use the Force Encryption option.  From what I read, is it just installing the certificate on the Server itself and then setting the ForceEncyption to "Yes"?  Is there anything needed on the client side like updating the connection string that it uses to connect?  Or does setting the ForceEncryption take care of it?

Sorry, I'm new to encryption and we want to implement that on our SQL servers and it seems straight forward but I'm getting conflicting instructions as to whether the data connection string needs to be modified or not.
We are also using AAG as well so will implementation be different as well?

According to this StackOverflow post, if you have the server-level settings (referenced below) configured with ForceEncryption set to Yes, then that will enforce an encrypted connection regardless of the SSMS GUI Encrypt connection option being checked or not prior to making connections to that server. This may be a sufficient workaround for people where the "encrypted connection" is of more importance than the actual option being checked within the SSMS GUI.

According to Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager), you do this as such:

  To configure the server to accept encrypted connections

  In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for , and then
  select Properties.
  In the Protocols for Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for
  the Certificate box, and then click OK.
  On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
  Restart the SQL Server service.

  To configure the client to request encrypted connections

  Copy either the original certificate or the exported certificate file to the client computer.
  On the client computer, use the Certificates snap-in to install either the root certificate or the exported certificate file.
  In the console pane, right-click SQL Server Native Client Configuration, and then click Properties.
  On the Flags page, in the Force protocol encryption box, click Yes.

  source


Changing Charset to utf8 for all the fields and tables in mysql How should I use the mysql and awk to change the charset for all the fields and tables for a specific database? I did not include username or password, so for sure it will not work.

Should I run it as it is and wait or does it need to be modified to work with authentication?

mysql --database=dbname -B -N -e "SHOW TABLES" \
| awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql --database=dbname &

First backup the database. Sometimes character conversions can give unexpected results.

I have had good success using these instructions:

SELECT CONCAT('ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') INTO OUTFILE '/tmp/altertablesstatements.sql'
FROM
information_schema.tables
WHERE
    table_schema='<database-name>'
AND table_collation != 'utf8'
GROUP BY table_name;

Add disable foreign_key_checks to first line of sql.

echo 'SET foreign_key_checks = 0;' >> /tmp/altertablesstatements.sql

Import the changes

mysql DATABASE  < /tmp/altertablesstatements.sql

Converting Columns

SELECT CONCAT(  'ALTER TABLE <DATABASE>.', table_name,  ' CHANGE ',COLUMN_NAME,'  ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8 COLLATE utf8_general_ci',
IF(IS_NULLABLE='NO',' NOT NULL;',' NULL;')) INTO OUTFILE '/tmp/altercolumnsstatements.sql'
FROM information_schema.COLUMNS AS tb
WHERE TABLE_SCHEMA =  '<DATABASE>'
AND character_set_name != 'utf8';

Add disable foreign_key_checks to first line of sql.

echo 'SET foreign_key_checks = 0;' >> /tmp/altercolumnsstatements.sql

Import the changes

mysql DATABASE  < /tmp/altercolumnsstatements.sql

https://confluence.atlassian.com/jirakb/how-to-change-all-columns-and-tables-collation-to-utf8_bin-in-mysql-601456761.html


How to migrate data between "all-surrogate" databases? With some periodicity this needs to be done:

Inserting data from a production database into a test/development database, so that programmers have fresh data to test on.
Migrating data from a database into another, for whatever reason.

Note: databases are relational.

The problem is:

Databases were modeled with a "all-PK-are-surrogate" policy.
Moving data from a database to another involves programming to avoid colision of PK values ( generated by sequences ).
A table pairing the values of the PKs of origin and destination database's tables is needed in order to do the "equivalency" between surrogate keys.
Said table has to be created in advance of migration, by matching business keys (which are not PK, so don't govern FKs)
Data migration even of a single table in not trivial, as opossed to tables with natural keys.

What's the easiest way of copying rows from one database to another when all tables have surrogates keys ?

We came across this problem (or at least a similar one) not long ago at work, and our solution is to divide the migration process into steps:

Insert the new data to a temporary staging table(s)
Update the surrogate keys in the staging table(s) so that there are no conflicts
Insert the data into the target table(s)
Delete the staging table(s)

The second part is the tricky part, but in our case we were able to follow this algorithm for each table:

If there are any foreign keys against this table, set up foreign keys in the staging table with ON UPDATE CASCADE
Update the primary keys of all rows to a new id range. We used negative ids. E.g. UPDATE STAGINGTABLE SET ID=-ID-1; Now there is no risk of id clashes in the next step.
Now, for each row in STAGINGTABLE, generate and assign a new id

Repeat for each staging table. When all staging tables are processed, INSERT the data into the target tables.

One assumption this relies on is that all data is new and independent from the data already in the database.


Why MySQL performs so poor on SHOW PROCEDURE STATUS? I've installed MySQL server with many databases that use the InnoDB engine. With lots of procedures in each DB, mysql.proc table contains more than 250,000 rows and it becomes ultimately slow when I need to run a query like this one:
SHOW PROCEDURE STATUS WHERE db LIKE '%SomeDB%';

The above query takes more than 5 seconds to execure and tweaking MySQL configuration parameters such as myisam_sort_buffer_size or key_buffer_size (which where already large enough) made no difference. I've 256GB memory installed on my server and I don't think it is because of any kind of memory shortage, maybe some misconfiguration that I have no Idea about it. Any hint?

The "meta" commands (SHOW...) are generally not designed for performance.  The presumption is that you rarely, if ever, need to run them.  And when you do, it is more of a "one-time" operation.
Please migrate from MyISAM to InnoDB.  (This will probably have no effect on the Question at hand, but is important for many other reasons.)
Before 8.0, the information for many of the SHOW commands was scattered around various files.  For example, each table had a .frm file containing, effectively, the CREATE TABLE information.  "File open" took time, leading to slowness.
8.0 moved all that info into InnoDB tables so, in theory, it should be straightforward to fetch all the data more efficiently.  However, the implementation seems to involve layers of VIEWs and inadequate indexing.
In any case, 250K of anything takes time.  I could have mentioned the leading wildcard (%) on the LIKE because it prevents the use of an index.  However, I am not convinced that there is a relevant index.


Changing MySQL Master in Replication: with or without MASTER_LOG? Let's say I have server A,B,C. Both B and C have Master A
A - Master
B - Slave
C - Slave

If I am going to change C's Master to Master B, can I change it immediately with query without Master Log information (edit: and also without dumping and importing the database)?

CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='rep', MASTER_PASSWORD='password';

Or do I still need the Master Log Information ?

CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='rep', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000100', MASTER_LOG_POS=100;

If I need to Master Log information when switching Master, the MASTER_LOG_POS is running fast. How can I ensure that the number is not running?
If I enter the wrong MASTER LOG information, what will happen to my database?

Thank you for reading.

Assuming that no one can write (right now, before the change) in servers B and C you could try this:

First, lock writes on Server A, so there's nothing to replicate in the Slaves.

Server A (You'll lock all tables, NOTE: You should not exit this CLI, or it'll release the lock):

mysql> FLUSH TABLES WITH READ LOCK;

Wait to Server B and Server C are up-to-date with the replication, check that both server are in the same Master Log File and Master Log Position!

Server B:

mysql> SHOW MASTER STATUS;

From that one you can get your new MASTER_LOG_FILE and MASTER_LOG_POS.

Server C:

mysql> STOP SLAVE;

So you can restart your slave from its new master. Then you can UNLOCK your tables in Server A (In the CLI that you had before):

mysql> UNLOCK TABLES;

Now the only thing left is to do your CHANGE MASTER on Server C with the new MASTER_LOG_FILE and MASTER_LOG_POS that you got from Server B and everything should work fine.

IMPORTANT: You should check that Server B is running with log_slave_updates = TRUE and that you have log-bins too, since you'll need them for it to be a new Master.


Sql: Count Null and Non-Null values for set of tables and fields dynamically I would like to create dynamic sql query where for the set of tables and fields from information schema I will count null and non-null values of these fields.
I have idea to use it something like this (if I am not mistaking)
select distinct DB_NAME() as db_n,
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE as DataType

     into #baseNew
from INFORMATION_SCHEMA.COLUMNS
where left(COLUMN_NAME,4) ='new_'
order by TABLE_NAME,COLUMN_NAME

this next part that should be dynamic would be something like this
select distinct b.db_n,
                TABLE_NAME,
       COLUMN_NAME,
                (select case when column_name is null then 1 else 0 end as NullValues
                        from table_name) NullValues,
                (select case when column_name is not null then 1 else 0 end as NotNullValues,
                from table_name) NotNullValues,
                (select count(1) as numRows from table_name) TotalRows

  from #baseNew b

(and probably insert in some table or data frame for python)

And if that is possible to be run in python script to be run on multiple DBs

The best way to do this would be to construct the query in one shot using STRING_AGG and sys.columns (you should avoid INFORMATION_SCHEMA, it's for compatibility only).
We start off with a basic query for say: Table1 (Col1, Col2), remember that COUNT(something) only counts up non-null values
SELECT
  TableName = 'Table1',
  TotalRows = COUNT(*),
  Col1 = COUNT(Col1),
  Col2 = COUNT(Col2)
FROM Table1

We then unpivot that:
SELECT
  TableName,
  TotalRows,
  NonNullsRows,
  NullsRows = TotalRows - NonNullsRows
FROM (
    SELECT
      TableName = 'Table1',
      TotalRows = COUNT(*),
      Col1 = COUNT(Col1),
      Col2 = COUNT(Col2)
    FROM Table1
) t
UNPIVOT (NonNullsRows FOR Col IN (
    Col1, Col2
)) p

We can then dynamically construct the above for all tables and columns
DECLARE @sql nvarchar(max) =
(
    SELECT STRING_AGG(TableQuery, N'
UNION ALL
')
    FROM (
        SELECT N'
SELECT
  TableName,
  TotalRows,
  NonNullsRows,
  NullsRows = TotalRows - NonNullsRows
FROM (
    SELECT
      TableName = ' + QUOTENAME(t.name, '''') + N',
      TotalRows = COUNT(*),
      ' + STRING_AGG(CAST(QUOTENAME(c.name) + ' = COUNT(' + QUOTENAME(c.name) + ')' AS nvarchar(max)), ',') + N'
    FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + N'
) t
UNPIVOT (NonNullRows FOR Col IN
    ' + STRING_AGG(CAST(QUOTENAME(c.name) AS nvarchar(max)), ',') + N'
) p'

        FROM sys.tables t
        JOIN sys.columns c ON c.object_id = t.object_id
        WHERE c.name LIKE 'new_'   -- do you want this filter
           -- you may also want AND c.is_nullable = 1
        GROUP BY t.object_id, t.name, t.schema_id

    ) AS t(TableQuery)
);

PRINT @sql;  -- for testing

EXEC sp_executesql @sql;


Selecting Records From two tables with many to many relationship I have two tables user and rights.I want to fetch each user with its assigned right as I attached the screen shot of tables plus result I want.FYI I use PostgreSQL.
Any solution is highly appreciated.
Thanks In advance.

SELECT u.uid, u.login, STRING_AGG(r.code, ',') code
FROM UserTable u
/* LEFT */ JOIN Rights r ON u.uid = r.uid
GROUP BY u.uid, u.login


Randomizing table contents and storing them back in the table I have a table with at least a million records in it. These rows were created by a custom app that reads several SharePoint site collections and stores the item urls in the table. Now, since we read the site collections in a serial manner, first few thousands of rows belong to first site collection, next few thousands belong to second site collection, and so on.

I have another app that reads this table in a sequential manner. However, this way I end up sending HTTP requests to the same site collection for a longer time.

I know I could get random results from the table in my second app. But, that is not an option. I cannot change the way the second app works.

Now, the question is: How can I take all rows in the table, shuffule them and store it back in the table?

Update: SQL Server 2008 R2 is my database Server

If the calling app is explicitly setting a particular order in its query (if you are running MSSQL you can check this by having a profiler session running while the app does its thing, other DMBSs will have similar logging options) then there is nothing you can do and if it isn't you can not completely guarantee any particular order.

If no explicit ORDER BY clause is given then the data will come out in an order that is officially "undefined" - it will be what-ever order the server finds most convineint. For a single table query this will most likely be the order of the primary key. In MSSQL if you have a clustered index the results will most likely come out in that order for a single table query. For multi-table queries it is even less clear cut as it depends which way around the query planner choses to go to get your results (which without explicit index hints could vary over time as the balance of data in the tables, as estimated by the index stats the server keeps, changes).

If the table has no clustered index or primary key then the data is likely to come out in an arbitrary order similiar to the order the data was inserted. In this case you could try:

SELECT * INTO temp_table FROM table_to_be_reordered
DELETE table_to_be_reordered
INSERT table_to_be_reordered SELECT * FROM temp_table ORDER BY NEWID()

or this may be faster

INSERT table_to_be_reordered SELECT * FROM temp_table ORDER BY NEWID()
DROP TABLE table_to_be_reordered
EXEC sp_rename 'temp_table', 'table_to_be_reordered'

In the above NEWID() is MSSQL's function to return a UUID and it uses random rather than sequential IDs by default - in other DMBSs you should find a similar function that you can use. Be careful with your choice of function: for instance under MSSQL the RAND() function is evaulated once per query, not once per row, so SELECT * FROM somewhere ORDER BY RAND() would not have the desited effect (you can see why by running something like SELECT RAND(), * FROM some_table).

If you are using MSSQL (your question didn't state which DBMS you are targeting) and do not already have a clustered index on the table, and either have a sufficiently random column (a UUID column for instance) or could add one without upsetting the calling app, you could create a clustered index on that which would be quicker than the SELECT INTO / DELETE / SELECT INTO above. But again: this will have no effect at all if the app is explicitly asking for the results in a particular order and may not have any effect anyway otherwise.


ORA-14224 Deferred Segment Creation is not supported for this schema I'm trying to use deferred segment creation and not having any success.

My deferred_segment_creation parameter is set to TRUE, but new tables do not defer.  I've also tried explicitly stating SEGMENT CREATION DEFERRED in my create table statement but receiving the following error:

CREATE TABLE NEWTABLE3 (n1 number) SEGMENT CREATION DEFERRED TABLESPACE MYTBS
*
ERROR at line 1:
ORA-14224: Deferred segment creation is not supported for this schema

Can anyone provide some guidance regarding what I'm doing wrong?

Thanks!

Sure, do not use SYS for such operations.

SQL> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

SQL> conn bp/bp
Connected.

SQL> CREATE TABLE NEWTABLE3 (n1 number) SEGMENT CREATION DEFERRED;

Table created.

SQL> conn / as sysdba
Connected.
SQL> CREATE TABLE NEWTABLE3 (n1 number) SEGMENT CREATION DEFERRED;
CREATE TABLE NEWTABLE3 (n1 number) SEGMENT CREATION DEFERRED
*
ERROR at line 1:
ORA-14224: Deferred segment creation is not supported for this schema

Restrictions on Deferred Segment Creation

  This clause is subject to the following restrictions:

  You cannot defer segment creation for the following types of tables: clustered tables, global temporary tables, session-specific temporary
  tables, internal tables, external tables, and tables owned by SYS,
  SYSTEM, PUBLIC, OUTLN, or XDB.
  Deferred segment creation is not supported in dictionary-managed tablespaces.
  Deferred segment creation is not supported in the SYSTEM tablespace.
  Serializable transactions do not work with deferred segment creation. Trying to insert data into an empty table with no segment
  created causes an error.


Is it possible to tell when databases, schemas and tables were created in Postgres? Is it possible to tell when databases, schemas and tables were created in Postgres?

I've serached through PgAdmin and tried the command line interface with no joy. Also can't find any reference to this functionality anywhere online. Maybe I'm missing something obvious!

From this post:

;WITH CTE AS
(
    SELECT
        table_name

        ,
        (
            SELECT
                MAX(pg_ls_dir::int)::text
            FROM pg_ls_dir('./base')
            WHERE pg_ls_dir <> 'pgsql_tmp'
            AND  pg_ls_dir::int  <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name)
        ) as folder

        ,(SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name) filenode

    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
    AND table_schema = 'public'
)

SELECT
    table_name
    ,(
        SELECT creation
        FROM pg_stat_file(
            './base/' || folder || '/' || filenode
        )
    ) as creation_time
FROM CTE;

But, from the comment to this post here, this method is sometimes unreliable. Use at your own risk! You might also be interested in this - it appears that PostgreSQL is finally going down the road of an Oracle wait interface.

If you'd care to file a feature request and post the link in a comment here (with @Vérace in the body), I'd be happy to file a "me-too".


Unable to open BCP host data-file error Having problem using BCP via xp_cmdshell.

I exported the table data to a file as .dat but when I try to import the file to a new table, I am getting the below error. The SQL Server service and the account that I am using has full access on that drive. I am not sure why I am still having issues.

  Error = [Microsoft][SQL Server Native Client 10.0]  Unable to open BCP
  host data-file.

Here are the scripts that I am running.

exec master..xp_cmdshell 'BCP database.dbo.tabel OUT d:\tabledata.dat -T -c'
exec master..xp_cmdshell 'BCP database.dbo.tabelnew IN d:\tabledata.dat -T -c'

I had the same - somehow a windows or SQL Server update changed something and a previously successful build started failing.
In my case it was access permissions to the folder containing the dat files that was failing.
I could run the bcp from the command line and it worked but it failed from SQL Server.
The SQL Service needs access to the folder, basically.
To test this, try running

EXEC master..xp_cmdshell 'DIR <your-dat-folder>'

and if that fails check your access permissions (and grant as needed).


Is filtering on int better than filtering on date I have a system where most of our table has the current datetime as primary key and we filter most of the data based on the datetime.

The question, if I move this datetime to another new table and have an auto id in the new table. for eg.

Dates
id datetime
1  11/6/2019
2  11/7/2019
3  11/4/2019
4  11/8/2019

Now all the other tables will have id instead of datetime and filtering will be based on the id.

Will this improve performance or filter on a datetime is equal to filter on int?

I am using SQL Server 2017.

I would answer with: Keep it as it is with datetime!

One of the reasons is simple really, data type precedence. So, if you were ever to compare a datetime to an integer, the engine will decide that rather than converting date->int then compare int = int, it will convert int->datetime and compare datetime = datetime. Which stands the same for every single data type (like varchar)  except for:

user-defined data types
sql_variant
xml

in that order of precedence.

Date comparison functions are also optimized to a crazy degree and they run blitzing fast, so you won't be gaining much in terms of performance, in fact you might end up losing performance since you have to do a join operation and then (again) do a where(how else would you filter by dates after all, generated ID != date you're comparing with).

So you need to ask yourself, is it really necessary to move date into a separate table? If you're planning on having a Data Warehouse, it might help with making the TimeDim table but other than that I see no reason to do so.

If you are interested in data precedence, you can see the list here (highest to lowest, top to bottom): https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017


How to show missing rows with default values I have a table Category which has a few missing records for a few months. The table looks like this

Category    Month       Amount
Opt         January     12.00
Opt         February    0.00
Opt         March       -1042000.00
Opt         April       0.00
Opt         May         0.00
Opt         June        0.00
Opt         July        -782663.00
Opt         November    0.00
Opt         December    0.00

This table has missing rows for a few months. I would like to query and show this data as well.

So I created a month table

MonthNameLong   MonthNameShort
January         Jan
February        Feb
March           Mar
April           Apr
May             May
June            Jun
July            Jul
August          Aug
September       Sep
October         Oct
November        Nov
December        Dec

So that I can do a right join on this and get the missing details as well. The query I wrote is

select s.Category, m.MonthNameLong, coalesce(s.Amount, 0.0) as Amount
from Category s
RIGHT JOIN Month m
ON s.Month = m.MonthNameLong

And the result I get is

Category    MonthNameLong   Amount
Opt         January         12.00
Opt         February        0.00
Opt         March           -1042000.00
Opt         April           0.00
Opt         May             0.00
Opt         June            0.00
Opt         July            -782663.00
NULL        August          0.00
NULL        September       0.00
NULL        October         0.00
Opt         November        0.00
Opt         December        0.00

But instead of NULL category is there a way I can get Opt as the category.

I have attached the fiddle here https://www.db-fiddle.com/f/7sycoGZ4sYxNzdYeTvJauC/0

This is very similar to SQL join query to show rows with non-existent rows in one table question but that has all the data in the table so there isn't missing rows.

I have updated your fiddle with:

add one more category for to see the query is correct when there is more than 1 category
alter amounts making category amount sets different and altering zero values for to see the records inserted additionally.

Edited fiddle.

The final query is

With
Categories AS (
  SELECT DISTINCT Category
  FROM Category
  ),
MonthCategories AS (
  SELECT m.MonthNameLong, cat.Category
  FROM Month m, Categories cat
  )

select mc.Category, mc.MonthNameLong, coalesce(cat.Amount, 0.0) as Amount
FROM MonthCategories mc
LEFT JOIN Category cat ON cat.Month = mc.MonthNameLong
                      AND cat.Category = mc.Category;

Of course you may "pack" the query combining CTEs to one and/or moving CTEs to subquery.

PS. If there is only one category in the table you may simply replace s.Category with MAX(s.Category) OVER (ORDER BY s.Category) as Category in output list of your initial query.


List of variable defaults for InnoDB engine I would like to list the defaults for the global variables that are innodb specific.

The problem is that the following commands doesn't list any innodb variables:

/usr/sbin/mysqld --no-defaults --verbose --help
/usr/sbin/mysqld --verbose --help

However I can see the runtime ones with:

mysql -e "show global variables"

SHOW COMMAND

mysql --table -Ae "SHOW GLOBAL VARIABLES LIKE 'innodb%'"

INFORMATION_SCHEMA (MySQL 5.1+)

SQLSTMT="SELECT * FROM information_schema.global_variables"
SQLSTMT="${SQLSTMT} WHERE variable_name LIKE 'innodb%'"
mysql -Ae"${SQLSTMT}"

my.cnf Settings

cat /etc/my.cnf | grep innodb

MySQL Documentation

MySQL 5.6
MySQL 5.5
MySQL 5.1
MySQL 5.0

As for the runtime settings and /etc/my.cnf diff, here it is

mysql -uroot --table -p -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb%'" > InnoDBRuntime.txt
cat /etc/my.cnf | grep innodb | sed 's/=/ /g' | awk '{print $1}' > InnoDBConfigs.txt
for X in `cat InnoDBConfigs.txt`
do
    grep "${X}" InnoDBRuntime.txt
done

As for the runtime settings and actual defaults, it's a little different

STEP 01) Install mysql on a DevServer with no /etc/my.cnf

STEP 02) Run mysql -uroot -hDevServerIP -p -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb%'" > InnoDBDefault.txt

STEP 03) Run mysql -uroot -p -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb%'" > InnoDBRuntime.txt

STEP 04) Run the Diff

diff InnoDBDefault.txt InnoDBRuntime.txt

Give it a Try !!!


PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19) Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations:

change timestamp to char(19)
tinyint to ENUM  (I noticed it's overzealous with ENUMS in general)
change varchar to char(N)

I can see the point of changing varchar to char when appropriate, but the first two recommendations strike me as odd. Thoughts?

CHAR has a nasty side.  ANALYSE() predates character sets, and its code was probably not updated to take into account that English text in CHAR(...) utf8mb4 wastes 3/4 of the space!  Also, there was some utility of MyISAM row_format=fixed, but such is now useless in the default InnoDB.

Bottom line:  ignore its advice about CHAR unless you (1) really have fixed length strings and (2) have specified an appropriate CHARACTER SET.

I would not change TIMESTAMP to CHAR(19) because (1) the explosion of space due to utf8, and (2) you would lose the timezone adjustment that exists currently in the table, and (3) CHAR(19) is a lot bigger than the footprint of TIMESTAMP (4 bytes previously, now 5-8), and (4) ANALYSE() probably has not been updated to understand microseconds.

DATETIME, on the other hand does not have the timezone issue.  But points 1,3,4 still apply.


How do I upload the .mdf file and associated .ldf file from visual studio to a host using WebsitePanel? I have created an ASP.NET MVC 3 project in Visual Studio and the database is a SQL Server Express database BeerRecommenderContext.mdf with a log file BeerRecommenderContext.ldf. I can upload these to the standard App_Data directory but the connection string doesn't seem to work. I think it's because my host doesn't recognize the files as SQL Server files. My host, dotnet-host.com is using WebsitePanel. My current connection string is

Server=mssql0921.dotnet-host.com;Database=BeerRecommenderContext;UserID=timoteo1097;Password=●●●●●●●●

(of course the password is actually filled in properly)

The

You can check with them, but it's highly unlikely this method of connection is supported by the host.

The preferred way of getting your database set up is to restore a backup (you can do this through WebsitePanel), and follow their instructions for connecting. A host will normally have example connection strings that you can copy/paste into your configuration file.


Delayed replication false alerts and migration delays I am running mongo 2.6.1 on a 3-shard cluster.

Configuration is: 3 data nodes and 2 arbiters.

The one data node is hidden and delayed for 3 hours (slavedelay).

I have hidden the node in order to start/stop during backup and the 3 hours delay is for safety during new deployments.

The issue that I am facing is: During the weekend there are no write operations on the cluster (that is the nature of application). On Monday morning when the writes begin, the monitoring system reports that the delayed data node is behind for more than 40 hours.

I guess that is because the oplog that has no records during the weekend and when a record gets in on Monday, it needs 3 hours to reach the delayed slave.

Please share your opinion on the matter, is my thoughts correct here or should I check on a different direction?

The way I understand it, the hidden secondary will always be behind by the number of seconds defined by slaveDelay. In your case, at any moment in the day, the hidden secondary will always be behind by three hours from the last write to the oplog. If there is no more write activity, then the hidden secondary will be completely caught up in three hours. You can check the status of replication in Mongo directly by connecting to each node and running rs.printReplicationInfo()

I configured a 3 sharded cluster using a hidden secondary that is exactly 180 seconds behind, using 2.6.3

Here is the configuration of the two secondaries, the last is hidden and 180 seconds behind.

{
        "_id" : 1,
        "host" : "myhost:27001"
},
{
        "_id" : 2,
        "host" : "myhost:27002",
        "priority" : 0,
        "slaveDelay" : 180,
        "hidden" : true
},

Priority is set to 0 to keep the hidden secondary from participating in elections.

Started inserting documents on the shard and am now looking at the oplog using db.printReplicationInfo().

MongoDB's documentation on db.printReplicationInfo()

  If run on a secondary, the method calls
  db.printSlaveReplicationInfo(). See db.printSlaveReplicationInfo()

Visible Secondary mongo --port 27001

shard2:SECONDARY> db.printReplicationInfo()
configured oplog size:   50MB
log length start to end: 592secs (0.16hrs)
oplog first event time:  Tue Aug 26 2014 08:46:45 GMT+0000 (UTC)
oplog last event time:   Tue Aug 26 2014 08:56:37 GMT+0000 (UTC)
now:                     Tue Aug 26 2014 08:58:45 GMT+0000 (UTC)

Hidden Secondary mongo --port 27002

shard2:SECONDARY> db.printReplicationInfo()
configured oplog size:   50MB
log length start to end: 722secs (0.2hrs)
oplog first event time:  Tue Aug 26 2014 08:43:46 GMT+0000 (UTC)
oplog last event time:   Tue Aug 26 2014 08:55:48 GMT+0000 (UTC)
now:                     Tue Aug 26 2014 08:58:48 GMT+0000 (UTC)

180 seconds later, the hidden secondary catches up mongo --port 27002

shard2:SECONDARY> db.printReplicationInfo()
configured oplog size:   50MB
log length start to end: 592secs (0.16hrs)
oplog first event time:  Tue Aug 26 2014 08:46:45 GMT+0000 (UTC)
oplog last event time:   Tue Aug 26 2014 08:56:37 GMT+0000 (UTC)
now:                     Tue Aug 26 2014 09:02:18 GMT+0000 (UTC)

So whether you are writing to the oplog or not, the secondary will continue to keep up by N seconds as defined in slaveDelay. If your monitoring tool is saying 40 hours, verify first that you are getting the same information using mongo's replicationInfo commands.

db.getReplicationInfo()

db.printReplicationInfo()

rs.printReplicationInfo()

The output of db.printReplicationInfo() is identical to that of rs.printReplicationInfo()

Update

http://docs.mongodb.org/manual/core/replica-set-delayed-member/

Concerning your question about delayed chunk migrations

Sharding

  In sharded clusters, delayed members have limited utility when the
  balancer is enabled. Because delayed members replicate chunk
  migrations with a delay, the state of delayed members in a sharded
  cluster are not useful for recovering to a previous state of the
  sharded cluster if any migrations occur during the delay window.

If I understand your comment correctly.

If secondaryThrottle were set to false, the balancer could continue to migrate chunks without waiting for a delayed member. I'm not sure that's 100% accurate, but that's how I interpreted their documentation about secondaryThrottle and delayed members.


Is there a good reason to create an additional table to realize a 1:N relation? I have the following tables:
Users
_____
UserID
Data
...

Roles
_____
RoleID
Data
...

UsersRoles
_____
UserID
RoleID

Users and Roles are in a M:N relationship. As such I have the UsersRoles table to connect them.
Not connected to the previously mentioned tables, I also created the following tables:
Invoices
_____
InvoiceID
Data
...

Items
_____
ItemID
InvoiceID
Data
...

In this case I did not create an additional table since I am realizing a 1:N relationship here. My question is, if there is any reason for me to remove the InvoiceID from Items and create an InvoicesItems table similar to the UsersRoles table to connect them, even though I would not need it.

even though I would not need it.

No. If you add it without any expectation of needing it, then it only serves to:

Add overhead when querying the data
Add overhead when adding or updating data
Cause confusion for other people working on the DB
Be a potential source of inconsistent data and other bugs

On the other hand if you think it is likely that there will be a need for the relationships to become many-to-many in future, for instance a line item being on multiple invoices due to partial payments or refunds, then there may be a case for adding the extra structure now to save on refactoring effort later but I would be wary of adding it from the start because of the above reasons unless I was very sure it was going to be needed at some point soon.
If you do add the extra structure but don't want to allow many-to-many to be a thing just yet, you have extra work to define constraints that enforce the desired one-to-many behaviour until it is no longer needed.


Referral table structure I need to make a simple referral system and I have a database table set out as follows:

mem_id | date | refer_email | accepted

And the meaning of such columns is the following:

mem_id: The member unique identifier who is making the referral.
date: The timestamp value of the request.
refer_email: The contact email address owned by the person being referred.
accepted: A CHAR value of Y or N that indicates if the referred person received acceptance.

I am not sure if this is best structure for this kind of scenario. I will be required to tally the number_of_users referred by each person mainly, and I expect a few more simple queries on the horizon.

I would add the following to that schema:

refer_mem_id - The member unique identifier of the person who has accepted the referral.

Remove the accepted column, the value in refer_mem_id gives you this information.


SQL Server Performance hit after cache rebuild After I rebuild my indexes and update my statistics, SQL Server will have to rebuild the execution plan cache to get up to speed with the current setup. So, will the SQL server do this immediately after my stats are updated or will it wait until a corresponding query is executed to rebuild the cache ?

And if it chooses to wait until a query is executed to rebuild the execution plan cache, will there be any slight performance degradation since it has to build the cache from start again before executing the query ?

Let me know if I am missing anything here. Thanks!

So, will the SQL server do this immediately after my stats are updated or will it wait until a corresponding query is executed to rebuild the cache ?

NO, SQL Server would not do it immediately. Rebuilding indexes does lot of page movements and so does stats update. There is quite a chance that pages already present in memory, before index operation, would have been thrown back to disk. Now when you run the query again SQL Server optimizer will prepare new plan looking at new statistics and would request I/O to bring pages into memory which were thrown back to disk.

  Will there be any slight performance degradation since it has to build the cache from start again before executing the query ?

This can cause a bit of more time being spent by optimizer in preparing new better plan and I/O bringing pages into memory but this would just be the first time. But there would not be much performance degradation. In many cases the new plan would execute query much faster and this would outweigh the time taken by optimizer to build new plan and I/O to get pages into memory.

Since you are interested about plan cache I suggest you read Plan Cache and Reuse. As per the document among other following two can also cause plan to recompile

Updates on statistics used by the execution plan, generated either
explicitly from a statement, such as UPDATE STATISTICS, or generated
automatically.
Dropping an index used by the execution plan.

BUT note that plans do not always get invalidated when you update stats. There are quite few articles describing this

What caused plan to go horribly wrong
Can update stats cause recompile when no data has changed


How to use pgloader to transfer sqlite to postgres with table name in Uppercase I can use pgloader to transfer my sqlite database to postgres as described in this quickstart page.

But the result is all my tables' names are lowercases. e.g. TestName -> testname

Is there any way I can preserve the uppercase in my table names?

In SQLite, table names always are case-insensitive (even when quoted).

In PostgreSQL, unquoted identifiers are folded to lower case, but then the search for the table is done case-sensitively. So the only way to get the same behaviour as in SQLite queries is to use unquoted names, which implies that the actual names must be lower case.
In other words: the table name must be testname in order for a query like SELECT * FROM TestName to work correctly.


Truncate partitioned table with values from partitioned column? I have partitioned a table on a column with varchar datatype.

The documentation on truncate table with partitioning uses the partitionids:

Truncate table parttable with (partitions (1,2,5))

I only have a list of the actual values from the partitioned column.

How can I get the partitionids from a list of values so I can use it in the truncate table statement?

Background:

I’m still very new to partitioning and need to validate my plan.

I have a fact table that is partitioned on a column with varchar datatype.

I have an etl process that loads data fully in the beginning of the month.

The data loads after that only contain a subset (the partitioned column) and needs to fully replace the existing data in the fact table.

My plan is:

Load the data first to a staging table.
Truncate the fact table on the partitioned values in the staging table.
Insert the data in the staging table to the fact table.

Am I doing this correctly?

Since you didn't provide a minimal, complete, and verifiable example, I've created one:

DROP TABLE IF EXISTS dbo.pt;
IF EXISTS (SELECT 1
    FROM sys.partition_schemes ps
    WHERE ps.name = N'ps'
    )
BEGIN
    DROP PARTITION SCHEME ps;
END
IF EXISTS (SELECT 1
    FROM sys.partition_functions pf
    WHERE pf.name = N'p'
    )
BEGIN
    DROP PARTITION FUNCTION p;
END

CREATE PARTITION FUNCTION p
(
    int
)
AS RANGE RIGHT
FOR VALUES (10, 20, 30);

CREATE PARTITION SCHEME ps
AS PARTITION p ALL TO ([DEFAULT]);

CREATE TABLE dbo.pt
(
    i int NOT NULL
        CONSTRAINT pt_pk
        PRIMARY KEY
        CLUSTERED
) ON ps(i);

INSERT INTO dbo.pt (i)
SELECT TOP(30) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc;

That will create a partition scheme, a partition function, and a partitioned table with 30 rows inserted across the three paritions.

The following query determines which values lie on what partitions:

;WITH rowsource AS
(
SELECT pt.i
    , plc.file_id
    , plc.page_id
    , plc.slot_id
FROM dbo.pt
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc
WHERE pt.i = 1
    OR pt.i = 22
)
SELECT rs.*
    , dpa.partition_id
FROM rowsource rs
OUTER APPLY sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.pt', N'U'), NULL, NULL, 'DETAILED') dpa
WHERE dpa.allocated_page_file_id = rs.file_id
    AND dpa.allocated_page_page_id = rs.[page_id]
ORDER BY rs.i
    , dpa.partition_id;

Note in the Common Table Expression (CTE), there is a WHERE clause limiting the output to the values (1) and (12) in the i column of the dbo.pt table.

The output looks like:

╔════╦═════════╦═════════╦═════════╦══════════════╗
║ i  ║ file_id ║ page_id ║ slot_id ║ partition_id ║
╠════╬═════════╬═════════╬═════════╬══════════════╣
║  1 ║       1 ║      40 ║       0 ║            1 ║
║ 22 ║       1 ║     328 ║       2 ║            3 ║
╚════╩═════════╩═════════╩═════════╩══════════════╝

As you can see, those values lie on partitions 1 and 3 respectively.

You could extend the query above by joining to a #temp table with the list of values you need the partition_id values for.

The following code will automatically truncate the partition for the row containing the value 14:

DECLARE @cmd nvarchar(max);
DECLARE @partitions nvarchar(max);

SET @partitions = N'';
;WITH rowsource AS
(
SELECT pt.i
    , plc.file_id
    , plc.page_id
    , plc.slot_id
FROM dbo.pt
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc
WHERE pt.i = 14
)
SELECT @partitions = STUFF(q.p, 1, 2, N'')
FROM (
SELECT ', ' + CONVERT(nvarchar(max), dpa.partition_id)
FROM rowsource rs
CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.pt', N'U'), NULL, NULL, 'DETAILED') dpa
WHERE dpa.allocated_page_file_id = rs.file_id
    AND dpa.allocated_page_page_id = rs.[page_id]
GROUP BY dpa.partition_id
ORDER BY dpa.partition_id
FOR XML PATH(N'')
) q(p);

SET @cmd = N'TRUNCATE TABLE dbo.pt WITH (PARTITIONS (' + @partitions + N'));';
PRINT @cmd;
--EXEC sys.sp_executesql @cmd; --uncomment this line to actually truncation the partitions


Stuck at optimizing "not in clause" for a medium-sized query across two tables I have a system to teach English. For each word someone learns, I record:

LearnedWords
   -- Id
   -- UserId
   -- WordId

And for each new text that I give to a learner, I want to indicate what words are new for him.

So, imagine a learner has learned 15000 words. So there are 15000 records for him only in the LearnedWords. Now he chooses to read a new text that has over 2000 words. All I have is textual data here, that is, words.

From these 2000 words, let's say only 300 words are new. How can I find them? The only way that I can think of is to do set operations based on Venn Diagram. That is, using not in clause. So, I create a procedure that takes the 2000 words as input, creates a temporary table and populates it with the given text words, selects learned words from database, and then executes the not in query:

declare @textWords table
(
    Word nvarchar(100)
)

-- populate @textWords with 2000 given words here

declare @learnedWords  table
(
    Word nvarchar(100)
)

declare @newWords table
(
    Word nvarchar(100)
)

insert into @learnedWords (Word)
select Words.[Text]
from LearnedWords
inner join Words
on LearnedWords.WordId = Words.Id
where LearnedWords.UserId = 1 --> user with 15000 learned words

insert into @newWords (Word) -- filters 300 new words for the given user
select Word
from @textWords -- has 2000 records
where Word not in
(
    select Word
    from @learnedWords -- has 15000 records for the given user
)

However, this procedure takes 14 seconds to complete. It's a nightmare. I ran Tuning Advisor and applied recommendations. But it only improved to 13 seconds. I'm stuck at how to meet this business requirement in data layer with a good performance. Any help?

Consider the database that you're querying against. It contains a data model that someone designed. It likely contains indexes that someone added to improve query performance. It contains statistics maintained by the engine to improve query performance. You effectively throw all of that away when you join table variables to each other. SQL Server doesn't allow for statistics on table variables and those queries won't benefit from all of the work that went into improving your data model. The best use cases for table variables are to retain data after a transaction is rolled back or to avoid unnecessary recompiles in stored procedures that are executed hundreds of times per second or more.

Fundamentally, comparing 15000 rows to 2000 rows can be a very fast operation in SQL Server as long as you avoid fighting with the database. I mocked up some data similar to the quantities mentioned in the question:

CREATE TABLE dbo.Words (
    Id INT IDENTITY (1, 1),
    [Text] NVARCHAR(100),
    PRIMARY KEY (Id)
);

INSERT INTO dbo.Words WITH (TABLOCK)
SELECT DISTINCT LEFT(Text, 25)
FROM sys.messages;
-- (203013 rows affected)

CREATE TABLE dbo.LearnedWords (
    UserId INT,
    WordId INT
);

CREATE CLUSTERED INDEX CI ON dbo.LearnedWords (UserId);

INSERT INTO dbo.LearnedWords WITH (TABLOCK)
SELECT 1, Id
FROM dbo.Words
WHERE ID % 100 <= 7;
-- (16247 rows affected)

Suppose that you were able to create a table that contains the distinct words used in each "text". I used the following:

CREATE TABLE dbo.TextWords (
    TextId INT,
    WordId INT
);

CREATE CLUSTERED INDEX CI ON dbo.TextWords (TextId);

INSERT INTO dbo.TextWords WITH (TABLOCK)
SELECT 1, Id
FROM dbo.Words
WHERE ID % 1000 <= 9;
-- (2039 rows affected)

Finding new words can now be expressed with a single SELECT query without any table variables:

select w.[Text]
from TextWords t
INNER JOIN dbo.Words w ON t.WordId = w.Id
Where t.TextId = 1
AND NOT EXISTS (
    SELECT 1
    FROM LearnedWords l
    WHERE t.WordId = l.WordId
    AND l.UserId = 1
);

That query finishes in less than 20 ms on my machine. If I run your original code it takes 3800 ms in total. If you need to use the table variables then at the very least try adding a RECOMPILE hint to the final query. That drops the execution time to 1600 ms on my machine.

With the right data model you can meet your business requirements in a simple and efficient way. There's a bit of ambiguity in your statement about texts. Perhaps you aren't able to store unique words for each text in the database ahead of time. In that case the time taken for your procedure should entirely depend on how much time it takes to populate a temporary structure with the unique words used in that text. You don't have that code posted here so it's impossible for us to help you with that.