Why can't C# SMO see extended properties on a column but Powershell SMO can?

I just want to shadow @BradC a little.
I tested his code on Visual Studio 2015 (Update 3) on a Windows 10 Pro x64 machine against an SQL Server 2014 instance and I can confirm you can get Extended Properties of a field without issues.

To replicate I use one of my local databases. I used the following code snip to define some extended properties.

USE MyLocalDb;
GO
EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Some EP example.',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'TABLE',  @level1name = tblATableWithAtblPrefix,
@level2type = N'COLUMN', @level2name = Forename;
GO

I created a new console project and added the following references:

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum

All from:

  \%Install Path%\Microsoft SQL Server\130\SDK\Assemblies\

So my whole code, based on @BradC's looks like:

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace ConsoleApplication7
{
    class Program
    {
        private static Database database;
        static void Main(string[] args)
        {
            Microsoft.SqlServer.Management.Smo.Server server;

            SqlConnection connection = new SqlConnection("Integrated Security=SSPI; Data Source=SIS_DBA");
            Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
                new Microsoft.SqlServer.Management.Common.ServerConnection(connection);

            server = new Server(serverConnection);

            database = server.Databases["ImmigSql"];

            foreach (Table table in database.Tables)
            {
                Console.WriteLine(" " + table.Name);
                foreach (Column col in table.Columns)
                {
                    Console.WriteLine("  " + col.Name + " " + col.DataType.Name);
                    foreach (var property in col.ExtendedProperties)
                    {
                        Console.WriteLine(" " + property.ToString() + "");
                    }
                }
            }
            Console.ReadLine();
        }
    }
}

Of course you can now access the properties and use their values.

The Count value that wasn't working on your example:


How to get the SQL Server Activity Monitor's output using T-SQL?

To get the exact output as Activity Monitor:

I have modified the given script as follows. You can play the activity monitor on one side and this script in another window and verify the output.

You can also view the currently running expensive queries by using this script and for that just need to do ORDER BY [Total CPU (ms)] desc.

This script will display the following things:

[Session ID]
[User Process]
[Login]
[Blocked By]
[Head Blocker]
[DatabaseName]
[Task State]
[Command]
[statement_text] --It will display the statement which is being executed presently and it can be from the SP or the normal T-sql .
[command_text] ----- It will display the Stored Procedure's Name.
[Total CPU (ms)]
'Elapsed Time (in Sec)'
[Wait Time (ms)]
[Wait Type]
[Wait Resource]
[Memory Use (KB)]
[Host Name]
[Net Address]
[Workload Group]
[Application]

You can also add or remove the columns whichever you need .

I have commented out some columns in the query, like: --[Open Transactions Count] = ISNULL(r.open_transaction_count,0), --[Login Time] = s.login_time, --[Last Request Start Time] = s.last_request_start_time, So if you want can also add or remove the columns as per your requirement and you can also filter out the data DatabaseName wise. I hope this script will help many of us.

    /* ACTIVITY MONITOR'S OUTPUT along with statement_text and command_text */ /* Processes */
SELECT [Session ID] = s.session_id,
       [User Process] = CONVERT(CHAR(1), s.is_user_process),
       [Login] = s.login_name,
       [Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
         [Head Blocker]  =
    CASE
        -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
        WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
        -- session is either not blocking someone, or is blocking someone but is blocked by another party
        ELSE ''
    END,
                        [DatabaseName] = ISNULL(db_name(r.database_id), N''),
                        [Task State] = ISNULL(t.task_state, N''),
                        [Command] = ISNULL(r.command, N''),
                        [statement_text] = Substring(st.TEXT, (r.statement_start_offset / 2) + 1,
                                            ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT)
                                            ELSE r.statement_end_offset
                                            END - r.statement_start_offset ) / 2 ) + 1), ----It will display the statement which is being executed presently.

 [command_text] =Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), ''), -- It will display the Stored Procedure's Name.

 [Total CPU (ms)] = r.cpu_time,
 r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)',
                                 [Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
                                 [Wait Type] = ISNULL(w.wait_type, N''),
                                 [Wait Resource] = ISNULL(w.resource_description, N''),
                                 [Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
                                 [Memory Use (KB)] = s.memory_usage * 8192 / 1024,
 --[Open Transactions Count] = ISNULL(r.open_transaction_count,0),
 --[Login Time]    = s.login_time,
 --[Last Request Start Time] = s.last_request_start_time,

 [Host Name] = ISNULL(s.host_name, N''),
 [Net Address] = ISNULL(c.client_net_address, N''),

 -- [Execution Context ID] = ISNULL(t.exec_context_id, 0),
 -- [Request ID] = ISNULL(r.request_id, 0),
 [Workload Group] = N'',
                     [Application] = ISNULL(s.program_name, N'')
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id
                                      AND r.request_id = t.request_id)
LEFT OUTER JOIN
  ( -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
 -- waiting for several different threads.  This will cause that thread to show up in multiple rows
 -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread,
 -- and use it as representative of the other wait relationships this thread is involved in.
 SELECT *,
        ROW_NUMBER() OVER (PARTITION BY waiting_task_address
                           ORDER BY wait_duration_ms DESC) AS row_num
   FROM sys.dm_os_waiting_tasks ) w ON (t.session_id = w.session_id)
AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id) OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

WHERE s.session_Id > 50 -- Ignore system spids.

ORDER BY s.session_id --,[Total CPU (ms)] desc ;


Modelling a relationship which has multiple one-to-many parents

I think your table design is good and normalized.

One thing that is not guaranteed yet is that if a photo references a bedroom, that bedroom should belong to the same house as the photo.

You could get that with an additional UNIQUE constraint:

ALTER TABLE bedroom
   ADD UNIQUE (houseid, bedroomid);

Then the foreign key constraint could be defined as:

ALTER TABLE photo
   ADD FOREIGN KEY (houseid, bedroomid) REFERENCES bedroom (houseid, bedroomid);

An extra index is not nice from a performance angle, but you want an index on the houseid column of bedroom anyway because of the foreign key, so an additional column shouldn't hurt.


Access to Server and Real Data for Performance Tuning

Below is not complete (and longer than a comment)- since this is a very open ended question and will be closed (I am voting it to close as well).

I have laid below down to give you guidance and help you get started.

You have to install sql server. Go for a Developer edition.
download and restore Adventure Works 2014 Sample Databases
If you want to work with lots of data then - Enlarge the AdventureWorks Sample Databases
You can have open source data - e.g. stackexchange data and then import it into sql server.
Highly recommend to read this answer - performance tuning is an art not science .. and you will slowly and gradually master it.
Among many book that I have read, I would highly recommend T-SQL Querying by  Itzik Ben-Gan

  Is there a company who provide education with server and real data?

Stackexchange does provide with data and an interface. Infact, Brent and his team utilizes this data to demonstrate query tuning.

Also, you can play with optimizer to lead it to believe you have a large data (this is very advance and would not recommend to any novice in SQL Server field)

UPDATE STATISTICS table_name WITH ROWCOUNT = 10000000000, pagecount = 100000000000


How do I get spatial index feature if I mainly use innodb?

Using MyISAM is probably your best and only bet to be able to use a spatial index. You might consider storing the data in an InnoDB table and then export data continuously or by batch, according to your needs, to a myisam table. Then use the myisam table for your queries.

I wouldn't trust myisam as storage engine if data is of importance.

Seems like you figured out the solution yourself.


Cannot perform alter on 'FunctionName' because it is an incompatible object type

A CLR TVF is a fundamentally different database object type than a SQL TVF so one must use 'DROP/CREATE' instead of ALTER to change the definition.

The type in sys.objects for a CLR TVF is 'FT' whereas a SQL TVF (not inline) is 'TF'.


Community wiki answer:

  Running SELECT NOW() gives me 2017-09-27 11:47:38, it's a DATETIME(19) field.

Not (19), the number is the number of decimal places in the seconds.

  What kind of transformation is needed so that I can insert the right time in my database?

MySQL has no concept of time zone representation.

That is there is no way to use (read or write) -07:00.


drop postgres table with minimal locking

My best guess about what you're talking about is that the process of obtaining locks on the tables to drop them takes too long. You're running the changes in a transaction, so you obtain an exclusive lock on some_table before moving on to try to obtain an exclusive lock on other_table. At this point, some_table is inaccessible to clients, but other_table still has queries running against it so the migration script can't get a lock yet. Queries start to block on some_table and production operations begin to stall. You might even have transactions deadlocking because they already have a read lock on other_table and are trying to acquire a read lock on some_table, the reverse order to your DDL migration script.

Presumably your migration script is going to re-create the tables with a new definition after dropping them, then re-populate them, since you wouldn't be dropping tables that are in production use otherwise.

If that's the issue, your options are limited. You can rearrange your migration script into a set of individual transactions that don't depend on each other, like:

BEGIN;
DROP TABLE some_table;
CREATE TABLE some_table ( new_definition );
INSERT INTO some_table (...) VALUES (...);
COMMIT;

BEGIN;
DROP TABLE other_table;
CREATE TABLE other_table ( new_definition );
INSERT INTO other_table (...) VALUES (...);
COMMIT;

... or do the same thing with ALTER TABLE transformations instead of a drop, re-create and re-load.

Alternately, you can REVOKE access to the tables then query pg_locks and pg_cancel_backend all connections that currently hold locks on the tables you want to modify, apply the migration, and then GRANT access again. That'll ensure the migration runs promptly at the cost of aborting some queries. A well-written application will just re-try, so this shouldn't be particularly disruptive.


How do I report progress of a long running transaction in MySQL?

There are some caveats that apply to mixing transactional and non-transactional tables in the same query, but in this case, you're actually doing it because the side effects are desirable... so I see no problem with the following suggestion:

The MEMORY storage engine seems like a good fit for this application.  It's non-transactional, so your "status" inserts/updates would be visible immediately to other threads.

Data stored in a MEMORY table persists until the table is truncated or the rows are deleted, or until the server is restarted.  In a replication environment, a restarted master will also direct the slaves to empty their copy of a memory table when the master restarts, since the master's table will start empty.

Also, if you're doing this in a replication environment, a binlog_format of MIXED or ROW would likely be best, since those formats allow more deterministic replication, and determinism becomes more tricky when replicating a query with transactional and non-transactional tables.


Does DBMS (e.g. of SQL SERVER 2008) use virtual memory?

Virtual Memory is an indirection mechanism that map virtual addresses to physical addresses and the translation occurs in hardware layer through PTEs. A process can refer and address only virtual memory. Only privileged execution (kernel) can address physical memory. I recommend you read a book on the subject, the classic being Windows Internals by Russinovich/Solomon.

Your explanation is asking though a completely different question, whether OS 'Caches Manager' is circumvented. Virtual Memory is not a cache and there is never any sort of 'caching' in any discussion of a memory manager architecture (on any OS). It is not clear what 'cache manager' are you referring to. Does SQL Server do buffered IO (ie. uses OS file cache)? Generally no, see How It Works: Bob Dorr's SQL Server I/O Presentation.


Delete all row from all table from MYSQL DB with some ID clause

Quick Solution is to populate the ids to temp table and use Delete records from the table with Join to temp table.


SQL Server: query for getting TOP X groups of records which have highest subsequent values

If you run this:

select dbname, AVG(usage), STDEV(usage) from <your table> group by dbname

You'll notice that while the average is the same for both databases, the one you'd be interested is the one where the standard deviation is lower.  So what you'd want to do is then filter this by a certain threshold average usage or higher and a certain standard deviation or lower.


SQL Server 2012: set a column to only use a single case?

You can do this silently using an INSTEAD OF trigger:

CREATE TRIGGER dbo.tr_table_name
  ON dbo.table_name
  INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.table_name(col1, col2, col_you_care_about)
    SELECT col1, col2, UPPER(col_you_care_about)
    FROM inserted;
END
GO

If you want to raise an error, you can use a CHECK constraint:

CREATE TABLE dbo.blart(a VARCHAR(32));

ALTER TABLE dbo.blart ADD CONSTRAINT ck_col
  CHECK (CONVERT(VARBINARY(32), a) = CONVERT(VARBINARY(32),UPPER(a)));
GO

INSERT dbo.blart(a) VALUES('A'); -- succeeds
GO

INSERT dbo.blart(a) VALUES('a'); -- fails

Result:

  Msg 547, Level 16, State 0
  The INSERT statement conflicted with the CHECK constraint "ck_col". The conflict occurred in database "dba", table "dbo.blart", column 'a'.

But I have to agree with @swasheck above - what difference does it make how it's stored? If you just want it displayed in upper case, do that at rendering time, not storage time.


Slow performance matching columns to lists of values

Based on the ORDER BY and the fact that you only care about non-NULL trafficlights, I would consider either:

...ON dbo.Results(UserId, ObjectiveId, DateUploaded DESC, TrafficLight)

Or even adding a filter on that index:

...WHERE TrafficLight IS NOT NULL

Whether the filter makes sense or not depends on what percentage of the table is NULL vs. NOT NULL and how often you run this exact query compared to one that doesn't filter. It may not be intuitive to have TrafficLight in the index, but this is due to some optimizer blindness.


How to COUNT co-tags from a tag_map table in mysql?

SELECT t1.TagID AS TagID1
    ,t2.TagID AS TagID2
    ,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID

UPDATED: index on ArticleID may help to improve query performance


There's an awful lot to be said for keeping things simple:

begin;

insert into table123 ( id, val, num ) values ( nextval('table123seq'), 2, 42 );
insert into table123 ( id, val, num ) values ( nextval('table123seq'), 3, 42 );
insert into table123 ( id, val, num ) values ( nextval('table123seq'), 4, 42 );

insert into table456 ( id, num, fkey_id )
   select next( 'table546seq' ), 333, id
   from table123
   where table213.num = 2 ;

select * from table456 ;
commit ;

Alternatively, make your id columns auto-increment and forget all about them entirely.

If that's not what you're after, then a plain-English description of the Problem you're trying to solve - not the difficulties with your current attempt at solving it - would be a good place to start.


How can I correctly use the timezone format specifier in a TO_DATE call in Oracle

Datetime Format Models

  TZR - Time zone region information. The value must be one of the time
  zone region names supported in the database. Valid in timestamp and
  interval formats, but not in DATE formats.

select to_timestamp_tz
  (
    '1970-01-01 00:00:00 UTC',
    'YYYY-MM-DD HH24:MI:SS TZR'
  )
  as result
from dual;

RESULT
----------------------------------------
01-JAN-70 12.00.00.000000000 AM UTC

If you want a DATE type, you need to convert it:

select cast(to_timestamp_tz
  (
    '1970-01-01 00:00:00 UTC',
    'YYYY-MM-DD HH24:MI:SS TZR'
  ) as date)
  as result
from dual;

RESULT
----------------------------------------
01-JAN-70


Does installing SQL Server Database Engine and Reporting Services in Azure require two virtual machines?

To purely answer your question:

If you set up two different VMs one running the SQL Server and the other one hosting the SQL Server Reporting Services, you would be charged twice the per-hour price of a Virtual Machine, but only once for your the SQL Server License.

Best Practice

I guess the "best practice" answer would be to rely on SQL Azure and SQL Azure Reporting Services, since they provide licensing, installation, maintenance, redundancy, scalability and security built-in.

But since this doesn't seem to be an option to you, here is what I would do based on my experience:

Put the database that hosts the data the reports are built on in SQL Azure. There you can restrict access to the database to only other Windows Azure components (Websites, Hosted Services, VMs) or a given IP-Range
Setup the SQL Server Reporting Services on a Virtual Machine (not a Hosted Service), because Hosted Services lack data persistence
Host your web application on Azure Websites (they are for free at the moment). You can later switch to Hosted Services if you need more performance.

Hope this answer helps.


SSMS 2016 Query Store: Missing Index Details error: DocumentFrame (SQLEditors)

It is happening to me from Query Store query plan screens on SSMS 13.0.16106.4 as well.

Workaround that I found, until they fix it:

Press "New Query" button for a blank new tab
Switch back to the Query Store tab and right-click, select "Missing Index Details" again
It'll open up the missing index details in a third tab

When you look at another, you may have to repeat the steps, even if you still have the blank tab open.


There's a few ways to determine if MRP is running.  One is to query open_mode from v$database.  If MRP is running, you will get:

READ ONLY WITH APPLY

If not, you will get:

READ ONLY

You can run the following query which will show what MRP processes are running:

select process,status,sequence#
from v$managed_standby;

Look for the process MRPn

You could look in V$DATAGUARD_STATUS.MESSAGE for various things, including:

MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery Canceled

Another way is on the primary, to check

select recovery_mode from v$archive_dest_status where dest_id = n;

If it's IDLE, it's not doing anything.  With real-time apply enabled, it returns MANAGED REAL TIME APPLY.


Is there a way to get disc space remaining using an SQL query on an Oracle database?

You should look here at Managed Tablespace Alerts, notably this bit where you can monitor by two criteria with warning and critical thresholds.

    By percent full

For both warning and critical thresholds, when space used becomes greater than or equal to a percent of total space, an alert is issued.

    By free space remaining (in kilobytes (KB))

For both warning and critical thresholds, when remaining space falls below an amount in KB, an alert is issued. Free-space-remaining thresholds are more useful for very large tablespaces.


That particular function is a SQLCLR User-Defined Aggregrate (UDA). It is being called multiple times, once per each output row (pre-HAVING clause, if one is present). A UDA maintains state between executions so in some respects it does access all rows within a particular group, but it only gets one row at a time, and needs to store info for future calls.

Otherwise, SQLCLR functions cannot accept multiple rows. T-SQL functions, however, do have the ability to accept multiple rows via a Table-Valued Parameter (TVP).


The database is using Oracle Automated Storage Management, so the data files are not located in a normal file system where you can "cd" to the directory and see them. You would need to have privileges within the ASM management database and use a tool like "asmcmd" to see more information. As long as the database recognizes the file as online, you are fine. You do not need to take anything offline or jump through any other hoops. See this link for more info: http://www.oracle-wiki.net/startdocsusefuloraclecommands


The only maximum permissible storage amount is the amount of storage your disk has. When you're running out of space, postgreSQL will simply stop. (with a PANIC message)

You have to monitor your disks free space to prevent it to happen...

Sadly documentation is quite light on it:
https://www.postgresql.org/docs/9.6/static/diskusage.html


Using Pt-online schema change on multi master MYSQL

If you use a dsn table as a recursion method, then pt-online-schema-change can do the job for you. This is documented here:

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-recursion-method

If you use the hosts method, though, pt-osc will detect that SHOW SLAVE STATUS returns more than 1 row, but it considers the topology is using replication channels. You would get this error:

This server returned more than one row for SHOW SLAVE STATUS but "channel"
was not specified on the command line at bin/pt-online-schema-change line 4494.

For setting up topologies to test different circumstances, dbdeployer is a useful tool https://github.com/datacharmer/dbdeployer#replication-topologies.

Disclosure: I work for Percona


"Armstrong's axioms are a set of axioms (or, more precisely, inference rules) used to infer all the functional dependencies on a relational database"(link).

R: ABCDE
F: C->AB, D->A, BE->CE, E->B

BE->CE can be split in BE->C and BE->E. The trivial functional dependency BE->E can be skipped. BE->C can be replaced by E->C because from BE->C and E->B one can deduce E->C. Therefore the set of functional dependencies can be reduced to:

R: ABCDE
F: C->AB, D->A, E->C, E->B

A and B cannot be member of a key because they cannot be found on the left site of a functional dependency. So it is sufficient to search all keys of the relational system (remove the Attributes A B from R and all functional dependencies containing A and B)

R: CDE
F: E->C

C is not on the left side of a functional dependcy so again one can reduce the system to

R: DE
F:

So the list F of functional identities is empty. The only key of this relational system is DE which is also the only key of the unreduced systems. So your answer of your first question is true.

You can use this page to find your candidate keys

To check if the system is in BCNF it is not necessary to find all candidate keys. It is sufficient to find one functional dependency which has a left side that is no a key.

C->AB is such a functional dependency: C is not a key because the closure of C is C. This means that no further attributes can be generated by applying functional dependencies on C.

You can use this page to find check if a relation is in normal form.


You need to use derived column transformation and with help of expressions perform required conversion operation on original columns.

To replace null with default value use REPLACENULL(expression 1,expression 2) in derived column transformation.

REPLACENULL returns the value of second expression parameter if the value of first expression parameter is NULL; otherwise, returns the value of first expression.

REPLACENULL(SHIPPED_DATE, "1900-01-01")

Or with ISNULL

(DT_DBTIMESTAMP) (ISNULL(SHIPPED_DATE) ? “1900-01-01” : SHIPPED_DATE)

ISNULL([SHIPPED_DATE]) ? "UnknownDate" : [SHIPPED_DATE]

To deal with date part, you can use Concatenate and make into valid date-time format.

Say you have 01/07/2014 as original field value then make it to "01/07/2014 00:00:00"

OracleDateField + "00:00:00"

If you have to perform both operations at same time(checking nulls and dealing with date part ) then it's good to use script component rather than derived column transformation.


Does this simple database design work even if it's cyclic?

So, just to clarify:

You want 1 Report to have Many Observations. The Observations then become either a Case or NoCase. Now, each case/nocase is linked by the ObservationID and the ReportID (or the Observation itself belongs to a ReportID), right?

It wouldn't be a circle: Report->Observations->Case/NoCase (I can see the Case and NoCase entities as one specialization).


DB2 10.5 - When inserting, the database is trying to reuse index keys on GENERATE ALWAYS

Determine the largest current ID value:

select max(TIMESHEET_ID) from PROJECTMGMT.TIMESHEET

then reset the identity value:

alter table PROJECTMGMT.TIMESHEET
  alter column TIMESHEET_ID restart with <whatever the max value is + 1>


The basic answer is yes, it is possible to chain is-a relationships.  In your case the relationship between Product and Product type 1 or Product type 2 is a case of subtypes.  See the questions tagged with this tag for answers to previously asked questions.   The relationship between Item and Product is, perhaps, a trivial case of subtyping, because there is only one subtype in your model (up to this point).

In the world of ER modeling, subtypes go by the terms "generalization and specialization".  You can look up these two terms for more background info. What you may find is that this feature of ER modeling was added to the ER model at some point in history, and this was known as "Extended ER"  (EER).

It's in the world of relational data modeling that things start to get really interesting.  If you are building a relational database, you are going to want to transform your ER model into a relational model before actually building the database.  In simple learning exercises, this can be done without drawing a separate diagram, just by adding details like foreign keys to the ER diagram.  In cases where there are over a hundred different relational tables and many relationships, a more formal approach may be necessary.

The basic relational model has no mechanism for inheritance.  Therefore, when you go to implement a case of generalization/specialization, you have to create tables that somehow mimic the behavior of inheritance well enough for data management purposes.

Two design patterns are available as alternatives.  They are summarized by Martin Fowler here:

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html
http://martinfowler.com/eaaCatalog/classTableInheritance.html

Notice that in Fowler's example,  is-a relationships have been chained, where cricketer is a subtype of player and a supertype of bowler.  That's the short answer to your question.

Another design pattern that is often useful with is-a relationships is shared primary key.  Here are some questions over in SO tagged with this pattern:

https://stackoverflow.com/tags/shared-primary-key/hot

You can use shared primary key with is-a relationships even if it's not really a case of subtyping.


MySQL 5.7: deprecated partitioning method warning error

As of MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is removed in MySQL 8.0, when the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do.

Use of tables with nonnative partitioning results in such warning written in error log.The MySQL server checks tables that using nonnative partitioning at startup.

To disable this check, use the --disable-partition-engine-check option.

Tables with nonnative partitioning should be changed to use an engine that provides native partitioning as shown below.

ALTER TABLE table_name ENGINE = INNODB;

Reference:
Partitioning


Is it okay to stop my repair on my mysql database if it's not showing in processlist?

Its odd that your SHOW PROCESSLIST doesn't include the REPAIR TABLE. This suggests its in some form of hung state. Check with a new connection and SHOW PROCESSLIST again. If it also doesn't show the REPAIR TABLE, I'd restart the mysqld service and try again.


Triggers After Insert and After Update - Good Practice

From a performance point of view if the are doing the same thing then there will be no difference. If your trigger performs tests to see what needs to be done (i.e. needs to treat updated rows differently to newly inserted ones) then there could be an impact if query plans that are sub-optimal for common cases get cached and reused for those cases.

For a code maintenance PoV the decision is also split by what differences there are in the behaviours. If the action taken in each case is identical (or near identical) then it makes sense to have one piece of code than two (so if you fix a bug in one you don;t have to remember to replicate the fix in the other location) - in this case using one trigger for both actions is preferred unless there is a significant performance difference. If the actions of each trigger for the trigger are noticeably different then keep them separate, again for code clarity reasons.

Of course it also depends what database system you are using: different ones will have different performance caveats that you may need to be concerned about.


How can I aggregate multiple results from another table using something like BOOL_AND?

select
    u.id,
    u.name,
    bool_or (a.admin) as is_admin,
    bool_and (a.admin) as is_complete_admin
from
    users u
    inner join
    associations a on u.id = a.user
group by u.id, u.name


MySQL Design - What is best primary key for our needs?

"Random" keys (UUIDs, MD5s, etc) can cause a performance problem, so let's avoid them as indexes (including PKs) of tables.

What is exposed?  Stuff in the url parameters?  Nothing else?

Well, the exposed "numbers" could be encrypted copies of the AUTO_INCREMENT values that work nicely otherwise.  Pass the encrypted stuff wherever a competitor might see it, then decrypt it when you need the simple id.

That is, I want to solve the problem at a higher level than in the bowels of the schema.

One approach is

AES_ENCRYPT(CONCAT(id, '-my-salt'), 'my-secret-string')

together with a similar AES_DECRYPT.  See MySQL, PHP, and other documentation.  The "salting" is to avoid a certain level of easy decoding.

You should probably do the en/decryption in the client and assume the connection between the client and MySQL is secure.  (After all, 'my-secret-string' is quite visible to a hacker on the client or server or network between them.)

AES strings are, I think, 8-bit values.  It would probably be good to use BASE64-encoding to make them nearly url-friendly.  You probably still need to "urlencode" to feed to urls.

Note also,...

MD5 is not reversable.
Random numbers are too likely to duplicate.
Truncate AES strings can't be reversed; I tried to minimize the length of the full AES string, but they will still be somewhat fatter than the minimum.


Kris,

  •Do we really think that we will see a measurable performance gain
  here? If so, how can we quantify it aside from looking at the space
  used in the query above?

That depends, but my gut instinct with the data you've given is - no. Sure, you'll potentially save some of that space as a plan stub will still take memory just not all that much (compared to your 1 MB plans). So you'll net memory, we get that. However, we don't know how many of those plans were executed a single time, and then some point later while still in cache executed again. This brings up the question about compilations/recompiles and the cpu utilization to go along with it. If you have a good bit of headroom then it may be a trivial issue (pun intended).

If your server isn't under memory pressure, I would not expect to see too much of an improvement in terms of "performance" depending on how you want to classify that. If you're swapping and having some slight memory pressure this could alleviate it for a few moments - though upping the VM memory would have the same effect at a much faster implementation without negative side effect cost.

  •Understandably, I hesitate throwing switches like this on production
  systems. What implications should I be cautious of? Will the procedure
  cache wipe itself and rebuild? Are there any issues that I should be
  made aware of when turning this on?

According to BOL it will not affect anything currently in your plan cache: "Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected." http://msdn.microsoft.com/en-us/library/cc645587(v=sql.105).aspx

You may see the potential (depending on eventual re-use) of cpu hits for plan compiles (since it would have to do it twice, once for the original execution and then one for the second when it is stored).

Some other "weirdness" would include monitoring tools, especially if they are grabbing execution plans as plan stubs do not have any associated with them. Some odd results may come from tools that expect there to be one associated all the time.

I'm not extremely familiar with Dynamics but IIRC, it has a specific Microsoft setup guide like SharePoint. I'd double check this won't invalidate your supportability for the product.


creat three replica set with different port number and then try


mongodump download collections only if changed

As at MongoDB 4.0, collection metadata doesn't include a last modified timestamp and there is no record of when mongodump last ran (or what collections were dumped).

You could write a custom script if you have a means of identifying relevant changes in your deployment since your last backup timestamp, but there are some potentially tricky aspects such as documents that have been removed.

One approach would be to use Change Streams to keep track of relevant collections that have changed since the last backup time, but this would require a replica set deployment. It is definitely possible to configure a single member replica set deployment if that suits your requirements.

It would be more ideal to use a supported backup method if your actual goal is incremental backups rather than per-collection mongodump.


Azure Data Sync Between Two Azure Databases

Yes, you can sync up to 500 tables on a sync group (per user). So you just need to create a sync group and specify which tables you want to sync. You can have up to 5 sync groups per database.

You must have a hub database (it must be a SQL Azure Database) and you can have one or more member databases (let's say one on Azure SQL Database and another member SQL Server database on your local machine).


Add host to existing user @ Mysql with the same permission

If you can install or already have the MySQL Utilities, then you can use the mysqluserclone script.

Reference: 5.28 mysqluserclone — Clone Existing User to Create New User

The command is then a simple:

shell> mysqluserclone --source=root@localhost --destination=root@localhost \
      source_user@localhost target_user:secret1@192.168.1.1

In the above example you are connecting to root@localhost (as the source) and root@localhost (as the target) and are then copying the permissions from source_user@localhost to target_user@192.168.1.1.

So you could use:

shell> mysqluserclone --source=root@localhost --destination=root@localhost \
      Reynaldi@localhost Reynaldi:secret1@192.168.1.1

Further details can be found in the rather limited documentation.


Failover and Failback SQL Server Log Shipping

If i understand correctly, you want to reinitialize log shipping from B to A?

If yes, then once you bring the DR site database (B) online you automatically break the LSN chain hence the restore log files will be skipped. The only way to reestablish log shipping is to remove log shipping setup completely, create backup of database B, restore at database A in stand by/readonly and then setup log shipping from B.


Msg 2534 : DBCC CHECKDB allocation errors

If the sequence is:

DBCC CHECKDB reported errors.
REPAIR_REBUILD fixed the database, no errors reported.
DBCC CHECKDB reporting errors again.

You probably have failed or failing disks in an array or some other component of the IO subsystem is broken, or breaking. Personally, I'd want off the problem server ASAP!

Take a tail-log backup.
Restore last known good full backup and diff/log sequence to a different server.
Switch service to the second server.
Get a fresh cuppa and start diagnosis on the problem server.

Addendum: Root cause of the problem:

as it turned out, I found that as we are using Diskeeper, we are (for corporate reasons) still using an old version : 14.0.896. With this version we run into messages like :

The operating system returned error 1784(failed to retrieve text for this error. Reason: 15100) to SQL Server during a write at offset 0x000000010a4000 in file 'D:\Application-Data\MSSQL\Data\<Database>.mdf:MSSQL_DBCC17'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3313, Level 21, State 2, Server <Server>, Line 1
During redoing of a logged operation in database '<Database>', an error occurred at log record ID (2349664:1503:4). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 1823, Level 16, State 1, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 2, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Server <Server>, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Server <Server>, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Server <Server>, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details

Found an explanation on Paul Randal's blog here

Link to DiskKeeper Fix found here

Interesting Diskeeper white paper on database defragmentation here


Is it possible for unique column to contain multiple null values SQL Server 2008

You can use a Filtered Index to remove the NULL values from consideration regarding uniqueness. You would first drop the existing UNIQUE CONSTRAINT and then replace it with a UNIQUE INDEX. These two objects are essentially the same given that a UNIQUE CONSTRAINT actually exists via a UNIQUE INDEX (the documentation for creating a column constraint states that UNIQUE is a "constraint that provides entity integrity for a specified column or columns by using a unique index.").

CREATE UNIQUE NONCLUSTERED INDEX index_name
ON SchemaName.TableA (CIN ASC)
WHERE [CIN] IS NOT NULL;


I was partially able to reproduce your bug when having PRAGMA foreign_keys = ON;, but setting the domain column in the domains table as PRIMARY KEY removed any problem in my case. (Still keep the pragma on).


LDF files vs. TRN files vs. Tempdb.MDF+LDF files.:

A few notes here:

You definetely have to separate the files logically using different partitions (I recommend separate partitions for: system, data files (mdf/ndf), transaction log files (ldf) and tempdb files (mdf/ndf). Where you put the tempdb transaction log file is up to you (tempdb drive or log drive), I'd go with log drive.
Backup files should not be on the located same device and especially the same VM (I recommend a different SAN/NAS).
Placing your data, log and tempdb files on different drives is mostly a way of improving performance. Transaction log is heavily written and is probably the slowest component of SQL Server. The second bottleneck is by design the tempdb database. If these 2 components are placed on different drives (especially if they reside on SSD/RAID10 disks), the performance will be definitely better.
You can keep the transaction log and tempdb on the same array as the user database files if you aren’t experiencing performance problems (at least not yet). I would look at the sys.dm_io_virtual_file_stats DMV to verify if there really is a problem with IO performance.
If you want to move these files to a different disk in a virtual environment you can create a separate virtual disk for every partition you want to move and assign these disks to different LUNs in the array.


Close all connections to postgres without having a connection

PostgreSQL doesn't offer anything like that. You need to keep a second connection slot spare, or have a way to request termination via a side channel provided by the server host; say a web page that runs pg_terminate_backend as superuser to kill your connection.

Alternately, you can find and kill the client side of the connection if you have local access to the client host.

A final, albeit somewhat cumbersome, option is to run a pgbouncer instance. Always connect to the pgbouncer, and have the pgbouncer instance connect to the main DB. You can then restart the pgbouncer if you have issues.


How Do I Troubleshoot Login Failed Errors In SQL Server?

How Do I See the Full Error Message?

The SQL Server error log on the instance you are trying to connect to will have any errors that SQL Server generated about the logon failure. If SQL rejected the connection/failed the logon for some reason it will generate an 18456 error typically. On the SQL side, this error will have an error state that gives clues to the cause of the error. On the client side, however, you may or may not see the useful details - it likely that you'll just see an error state of 1.

To see the full error message it is best to look at the SQL Server that you are trying to connect to and look in the SQL Server Error Log. You should see the error for the connection attempt if the connection is making it to SQL Server. From there the information is more useful than some other error messages if you know where to look.

What If There is no Error in the SQL Server Error Log?

This could be caused by several factors but it is quite likely that you are not dealing with a logon failure. Logon failures do a good job of presenting themselves in the error log. In this case it is quite possible that you are dealing with a failure to connect to SQL Server. You can troubleshoot this problem first and at least verify that you are connecting to SQL Server in the first place and that there are no issues here. Rather than get into all the potential steps for troubleshooting that here in this answer, this Technet Twiki article is an excellent resource to at least get you started.

How do I Read the Error Messages in the Log?

There are several resources for the meaning of the various error states and error messages. The below is pasted from this msdn link to get you started with some of the more common states of the 18456 error. These states may change in future versions or in the version you are on so it is a good idea to pay attention to both the message and the state and to dig deeper to understand the issue.

State ---- Meaning

2  ---- User ID is not valid.
5  ---- User ID is not valid.
6  ---- An attempt was made to use a Windows login name with SQL Server Authentication.
7  ---- Login is disabled, and the password is incorrect.
8  ---- The password is incorrect.
9  ---- Password is not valid.
11 ---- Login is valid, but server access failed.
12 ---- Login is valid login, but server access failed.
18 ---- Password must be changed.

There are many other states as well and in edits I'll add more or in other answers you'll see more. This link has a lot of other states, most of which are valid for most versions of SQL Server above 2000. The important take away here is to look in your error log on the server side and read the message - with logon failures the messages tend to be useful a lot more often than not.

And again State 1 is a generic message on the client.

Other Errors
There are other errors for SQL Server login or connection failures. In future edits or answers from other people more information can be added as well.

What Do I Do With That Data?

It depends..

The Obvious States

States 2 & 5 signify an invalid User ID. Check the user you are working with. Does your login exist? Are you specifying the database and does the login exist as a user - has it been mapped as a user in that database.

State 6 - Oops! You are trying to pass an Active Directory login name in a manner that uses SQL authentication.. Basically trying to pass Login Name of 'FunDomain\Mwalsh' but you are connecting with SQL authentication, not trusted. This is sort of like a 2 or a 5, just a bit more descriptive.

State 7 - Login is disabled.. See where it is disabled, why it is disabled and rectify the situation.

States 8 & 9 - Bad password. Is it mistyped? Has the password changed?

State 18 - I've never encountered this one in the wild. But it is telling us that our password is set to be changed. I suppose you could see this if you are connecting from an application that uses SQL authentication (like Great Plains...) and have setup a new account with a password that needs to be changed on first use - but your application doesn't understand that or have a functionality to change the password. If you just use SSMS, you just get a dialog to change the password and all is well.

Less Obvious States

11 & 12 sort of mean the same thing- These are ones you have to pay attention to the message closely with.

Is the word "Token" in the error message? Like "Login failed for user 'myDomain\UserName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors."

This can be a case of your SQL Server Service running as local system instead of a domain account with access to domain info potentially.

It can also be a case of needing to delegate authority through kerberos - for instance when accessing a Linked Server through Windows Authentication and passing the credentials - that double hop requires Kerberos, not NTLM. So you'll need to work through Kerberos.

Even this message can also mean other things - I've seen this recently after a migration to a server with an already existing logon - it was a very old logon at my client's environment. Dropping and recreating the logon resolved the issue. Why? It could be a few things but this post from Simon Sabin a couple years ago is an excellent resource here.

UPDATE After answering this I wanted to look an error code about default databases and I found a great blog post from Aaron Bertrand who answers a lot of questions here :-) Go check out his resource for more error messages including many that aren't in SQL Server 2012 or 2008 but still in the versions in use today. Great stuff there :-)


Thanks for the useful suggestion.

Now the query work as in this way:

select DISTINCT t3.uid,latitude,longitude
from (
  SELECT DISTINCT
  t2.*,
  MAX(CASE WHEN t2.datakey = 'latitude' THEN t2.data END) AS latitude,
  MAX(CASE WHEN t2.datakey = 'longitude' THEN t2.data END)AS longitude
FROM `#_social_fields_data` AS t1
INNER JOIN `#_social_fields_data` AS t2 ON t1.uid  = t2.uid
GROUP BY t1.uid
     ) as t3
WHERE latitude > 0

Thanks, as suggested i have optimized the query removing self_join:

select DISTINCT t2.uid,latitude,longitude
from (
  SELECT DISTINCT
  t1.*,
  MAX(CASE WHEN t1.datakey = 'latitude' THEN t1.data END) AS latitude,
  MAX(CASE WHEN t1.datakey = 'longitude' THEN t1.data END)AS longitude
FROM `#_social_fields_data` AS t1
GROUP BY t1.uid
     ) as t2
WHERE latitude > 0


Pivot Multiple Columns with a Cross Apply

I eventually managed to get this working. Had to use a Cross Apply to flatten for the multiple pivots:

WITH CoreGateway AS
(
SELECT      pg.ProjectId
,           g.GatewayTitle
,           ggs.GatewayGatewayStepId
,           cc.tC02
,           cc.CarbonCost
FROM        pmp.ProjectGateway      pg
LEFT JOIN   pmp.CarbonCalculated    cc  ON  cc.ProjectGatewayId         = pg.ProjectGatewayId
JOIN        ref.GatewayGatewayStep  ggs ON  ggs.GatewayGatewayStepId    = pg.GatewayGatewayStepId
JOIN        ref.Gateway             g   ON  g.GatewayId                 = ggs.GatewayId
JOIN        ref.GatewayStep         gs  ON  gs.GatewayStepId            = ggs.GatewayStepId
WHERE       gs.GatewayStepTitle     = 'Carbon Calculated'
)
, CarbonData AS
(
SELECT      cg.ProjectId
,           cg.GatewayTitle + CarbonType AS GatewayTitle
,           CarbonValue
FROM        CoreGateway     cg
CROSS APPLY (
            VALUES  (cg.tC02, 'tC02')
            ,       (cg.CarbonCost, 'CC')
            ) c (CarbonValue, CarbonType)
)
SELECT      p.ProjectId
,           p.SolutionId
,           p.NeedId
,           p.ProjectCode
,           p.ProjectDescription
,           ISNULL([Gateway 1CC], 0.0)      AS Gateway1_CarbonCost
,           ISNULL([Gateway 3CC], 0.0)      AS Gateway3_CarbonCost
,           ISNULL([Gateway 3CC], 0.0) -
            ISNULL([Gateway 1CC], 0.0)      AS Gateway3_CarbonCost_Diff
,           ISNULL([Gateway 5CC], 0.0)      AS Gateway5_CarbonCost
,           ISNULL([Gateway 5CC], 0.0) -
            ISNULL([Gateway 1CC], 0.0)      AS Gateway5_CarbonCost_Diff
,           ISNULL([Gateway 9CC], 0.0)      AS Gateway9_CarbonCost
,           ISNULL([Gateway 9CC], 0.0) -
            ISNULL([Gateway 1CC], 0.0)      AS Gateway9_CarbonCost_Diff
,           ISNULL([Gateway 1tC02], 0.0)    AS Gateway1_tC02
,           ISNULL([Gateway 3tC02], 0.0)    AS Gateway3_tC02
,           ISNULL([Gateway 3tC02], 0.0) -
            ISNULL([Gateway 1tC02], 0.0)    AS Gateway3_tC02_Diff
,           ISNULL([Gateway 5tC02], 0.0)    AS Gateway5_tC02
,           ISNULL([Gateway 5tC02], 0.0) -
            ISNULL([Gateway 1tC02], 0.0)    AS Gateway5_tC02_Diff
,           ISNULL([Gateway 9tC02], 0.0)    AS Gateway9_tC02
,           ISNULL([Gateway 9tC02], 0.0) -
            ISNULL([Gateway 1tC02], 0.0)    AS Gateway9_tC02_Diff
FROM        (
            SELECT  cd.ProjectId
            ,       cd.GatewayTitle
            ,       cd.CarbonValue
            FROM    CarbonData  cd
            )   cc
PIVOT       (
            MAX(CarbonValue) FOR GatewayTitle IN    (   [Gateway 1CC], [Gateway 3CC], [Gateway 5CC], [Gateway 9CC]
                                                    ,   [Gateway 1tC02], [Gateway 3tC02], [Gateway 5tC02], [Gateway 9tC02]
                                                    )
            ) carpvt
JOIN        pmp.Project p   ON p.ProjectId = carpvt.ProjectId;


Why doesn't SQL Server rollback the identity_insert state?

This is a session setting; there is nothing to roll back. If you had issued SET DATEFORMAT DMY; or SET LANGUAGE FRENCH;, would you expect an error to revert you to MDY or english_us? What if you had SET SHOWPLAN on, would you expect a rollback to stop showing you plans on subsequent queries?

To be fair, the IDENTITY_INSERT case is a little special, since it's the only session setting I can think of off the top of my head that interacts directly with a table. But it's still applicable only to your session. The thinking is probably that you could go on to insert many rows in multiple batches, even after an error has been raised (as long as it hasn't severed the connection).

As an aside, you're using SQL Server 2012, so why are you using SQL Server 7.0 error handling techniques? You could use TRY/CATCH instead of SET XACT_ABORT and then turn the setting off in the CATCH if it's on (which you can check). For a good primer on error handling, see Erland Sommarskog's articles on Error and Transaction Handling in SQL Server:

Part One– Jumpstart Error Handling
Part Two - Commands and Mechanisms
Part Three - Implementation


If you have a running instance of mysql, throw some data at it, then compute
( Innodb_pages_read + Innodb_pages_written +
       Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / Uptime

Something like:
SHOW GLOBAL STATUS;
run some heavy I/O queries
SHOW GLOBAL STATUS;

Tediously subtract the 5 values and evaluate the expression.
This will be a crude number, and you can probably set the innodb_io_capacity* higher than what the expression gives you.


It depends on what's at the other end of the mount point. If the mounts are all LUNS spread across the same physical drives, then no gains. If the LUNS are all over a shared, slow, iSCSI channel, maybe no gains. If the LUNS are all under 1 controller...you see how many variables are at play. There's no one answer.

To determine the configuration of the mount points, see Locating Mount Points Using PowerShell by Boe Prox.

SQL Server has no problem with mount points. These are defined at the OS level and SQL Server "doesn't know1" they're not the same volume as the drive they appear to be mounted in.

Some Monitoring tools might give you bad information based on that last sentence, however.

For example if you have three mount points like

C:\SQLData\SQL_Data where all your .MDB files are stored
C:\SQLData\SQL_Logs where all your .LDF files are stored
C:\SQLData\SQL_Backups where all of your .BAK and .TRN backup files are stored

Then SQL Server will work without any issues. But if you run some sort of tool that warns you when disks are low in space, it may check the C: and not the mounted volumes below it, so you may not know when those mount points are low on disk space. Also, various "best practices" queries will throw false warnings telling you that you shouldn't have your data, logs, and backups all on the same disk because SQL Server thinks they are on the same disk. Those are false flags, and can be ignored.

But you'd basically want to set up some additional steps in your server monitoring to make sure the C: drive has enough space and that each mount point does, too.

The times I've used mount points in SQL Server, that's been the only issue I've run into: SQL Server system health reports that give false data about free space available, and false errors saying you shouldn't have all of your data on the same drive. Since you know those are false errors, they are easy enough to ignore.

1 SQL Server has data that makes it aware of the mount point, but from a practical use viewpoint, there's no difference in behavior. It "just works," trusting the OS to handle the specifics. Just as it trusts the OS to handle the specifics for iSCSI LUNs that are connected as local drives.


Passing variable as parameter to json_query function

It is possible in SQL Server 2017.

From JSON_QUERY (Transact-SQL)

  In SQL Server 2017 and in Azure SQL Database, you can provide a
  variable as the value of path.

In SQL Server 2016 you have to build the query dynamically.


If you're going to use SSIS, may I suggest you use it? ;)

What I believe you are attempting to do is insert some data that doesn't already exist in the target. You have identified what data needs to go using your min/max logic and you attempted to do a similar operation for the target.

For starters, the OLE DB Command is something you should use sparingly. It performs singleton operations and so the volume of rows you want this thing handling should be in the tens, maybe hundreds. Definitely not 80 million.

Your data flow should look something like this

I have an OLE DB Source (ADO.NET can be used but if you need to parameterize your query, it's a little more complex). You can use your existing query or if you need to chunk it up because you have active systems, so be it.

The Lookup Component, LKP RM_DimEmplo... is the key to this operation. When the package starts, it is going to run the source query in that component and cache all the values. That sounds expensive, so don't let it be. Don't just select dbo.destinationTable. Write your query to bring back only the keys you need to make a match. It should be those IDs. That will be very narrow and shouldn't be so bad. The idea here is that you will have your source rows and you will compare those to the cache of the target table based on those keys. Since you're not worried about detecting differences, you know that anything that doesn't match needs to go, so on the "General" tab, change the "Specify how to handle rows with no matching entries" to use Redirect rows to no match output. Otherwise, this will blow up when it doesn't make a match.

If you find it's still impactfull on your systems, then you need to go to a more advanced approach of using Expressions on the Lookup Component to filter the range of data to match the IDs coming in. Thus, you have identified the source range is 100 to 200. There's no need to pull in the full 0 to 1000 range of keys in the destination, you would simply make the lookup cache the keys that are in the operational range. 80M shouldn't be terribly taxing unless you have just no RAM to spare on the machine.

The OLE DB Command does singleton, one-off operations. The OLE DB Destination can either do singleton inserts (default for 2005 - Table or view) or set/batch based updates (Table or View - fast load) which can be screaming cheetah wheelies fast. Since you're loading a lot of data, unless you want your admins to hate you, change the Max insert commit size to something other than the default of 2147483647. That basically says don't commit nothing until it's all been inserted. That can cause your transaction log to get big in a hurry.

The nice thing about your problem is that you can run this to your heart's content. Say 1M rows get loaded and something bad happens. Just restart. However much got committed and when it restarts, it might pull the same source data but your lookup will have the most recent set of keys from the target so the result will be no operation performed for those. I know, you'd think everyone loves duplicates in their target system but for some reason, that just doesn't fly at some places...

Wrapup

I can't recommend the Stairway to Integration Services series enough. Specifically for this problem, Level 3


Access: Making a Pivot Table with Multiple, Same Values as Columns

It's apparent that you intended to create a single Crosstab, but I couldn't help but notice the actual final goal:  "I might be able to calculate exactly how many hours".  Indeed, if you had the final data as you showed, it would be possible to get total times, but there are other normalized forms that allow for sums without special code for handling multiple breaks and/or differing number of breaks.  Below is a sequence of queries that gives you total times.  If you still prefer your Crosstab format, the first few queries provide a form which allows you to pivot on different break times as desired.

This set of queries may seem like overkill, but each has its purpose and this style of query coding is compatible with treating the data as a set.  This is simply a sequence of operations on the set.  A desire to do all things in a single query is an artificial requirement.  Some of these could be combined as nested queries, but that removes some convenience within Access Query Designer, and Access tends to throw fits with nested queries for TRANSFORMS as I've discovered.

Named query [BreakSeq].  This same query could produce combined labels like "BreakOut1" with small modifications:

SELECT Data.Naem, Data.Daet, Data.Time,
    IIf([Auth]='BreakIn','In','Out') AS InOut,
    1 + DCount("[Naem]","[Data]","[Naem]='" & [Naem] & "' AND [Daet]=#" & [Daet]
    & "# AND [Auth]='" & [Auth] & "' AND [Time] < #" & [Time] & "#") AS [Number]
FROM Data
WHERE (Data.Auth Like "Break*")
ORDER BY Data.Naem, Data.Daet, Data.Time;

Named query [BreakNorm].  Transform requires an aggregate function, but the assumptions along with the groupings guarantee unique values, so FIRST() will return such unique values.  Also, had your table been normalized with this form, subsequent queries would have been easier to produce:

TRANSFORM First(BS.Time) AS FirstOfTime
SELECT BS.Naem, BS.Daet, BS.Number, "Break" AS Type
FROM BreakSeq AS BS
GROUP BY BS.Naem, BS.Daet, BS.Number
ORDER BY BS.Naem, BS.Daet, BS.Number
PIVOT BS.InOut;

Named query [BreakDetail].  This produce negative minutes useful for adding to overall CheckIn and Out spans:

SELECT BN.*, DateDiff("n",[In],[Out]) AS Minutes
FROM BreakNorm AS BN;

Named query [BreakSum]:

SELECT BD.Naem, BD.Daet, BD.Type, Sum(BD.Minutes) AS BreakMinutes
FROM BreakDetail AS BD
GROUP BY BD.Naem, BD.Daet, BD.Type;

Now for the CheckIn and CheckOut... Named query [CheckSeq]:

SELECT Data.Naem, Data.Daet, IIf([Auth]='CheckIn','In','Out') AS InOut, Data.Time
FROM Data
WHERE (Data.Auth Like "Check*");

Named query [CheckNorm]:

TRANSFORM First(CS.Time) AS FirstOfTime
SELECT CS.Naem, CS.Daet, "Check" AS Type
FROM CheckSeq AS CS
GROUP BY CS.Naem, CS.Daet
ORDER BY CS.Naem, CS.Daet
PIVOT CS.InOut;

Named query [CheckDetail]:

SELECT CN.*, DateDiff("n",[In],[Out]) AS Minutes
FROM CheckNorm AS CN;

Now combine the results with a total sum of minutes worked per name and date:

SELECT BS.Naem, BS.Daet, [CD].[Minutes]+[BS].[BreakMinutes] AS WorkingMinutes
FROM BreakSum AS BS INNER JOIN CheckDetail AS CD
    ON (BS.Daet = CD.Daet) AND (BS.Naem = CD.Naem)
ORDER BY BS.Naem, BS.Daet;

This solution assumes the following.  If any of these assumptions are not guaranteed, exceptions should be identified and resolved, otherwise you'll need to code special logic to avoid errors and bogus numbers:

There is precisely one CheckIn and one CheckOut entry per name and date.
For each BreakOut entry there is a corresponding BreakIn entry, so that there are no unmatched pairs.
The in and out times are proper, so that the CheckOut time is after the CheckIn time and BreakOut times precede corresponding BreakIn times and that there are no overlapping break times.
CheckIn, CheckOut, BreakIn, BreakOut are the only authentication types.
Text columns [Naem] and [Auth] don't contain apostrophes (').  Otherwise, special care must be taken to properly delimit those values in some queries.

If you insist on the final form you showed, here's a solution with fewer queries.  Sorry if I made it too instant.

Named query [AllSeq]:

SELECT Data.Naem, Data.Daet, Data.Time, Data.Auth,
   IIf([Auth] Like "Break*","Break","Check") AS Type,
   IIf([Auth] Like '*In','In','Out') AS InOut,
   1 + DCount("[Naem]","[Data]","[Naem]='" & [Naem] & "' AND Daet=#"
      & [Daet] & "# AND Auth='" & [Auth] & "' AND Time < #" & [Time]
      & "#") AS [Number],
   IIf([Auth] Like "Break*",[Auth] & [Number],[Auth]) AS AuthNum
FROM Data
ORDER BY Data.Naem, Data.Daet, Data.Time;

Named query [AllCross]:

TRANSFORM First(AZ.Time) AS FirstOfTime
SELECT AZ.Naem, AZ.Daet
FROM AllSeq AS AZ
GROUP BY AZ.Naem, AZ.Daet
PIVOT AZ.AuthNum;

Named query [AllCrossSorted]:

SELECT AC.Naem, AC.Daet, AC.CheckIn, AC.BreakOut1, AC.BreakIn1,
       AC.BreakOut2, AC.BreakIn2, AC.BreakOut3, AC.BreakIn3, AC.CheckOut
FROM AllCross AS AC;


How to tell if there are currently any long running queries executing?

WhoIsActive from Adam Machanic is an excellent tool/sp to see what's currently happening on your server.

The below tutorial from Brent Ozar shows how to periodically log sp_whoisactive to a table for later analysis.

https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/


SQL server 2012 how to apply a check constraint on multiple columns

You can create a table-level check constraint with multiple columns specified in the expression in order to avoid separate check constraints.

This example will not allow an empty string, all spaces, or NULL in these columns:

CREATE TABLE dbo.Buyer(
      BuyerId int NOT NULL
        CONSTRAINT PK_Buyer PRIMARY KEY
    , BuyerName varchar(50) NOT NULL
    , BuyerSurname varchar(50) NOT NULL
    , BuyerEmail varchar(50) NULL
    , BuyerAddress varchar(50) NOT NULL
    , CONSTRAINT CK_Buyer_BuyerName_BuyerSurname_BuyerEmail_NotEmpty CHECK
        ('' NOT IN (BuyerName,BuyerSurname,BuyerEmail) )
    );


Is there a difference in performance between @date and getdate()?

The answer is - you have to test it to find out.
I did a test of my own on a table which has ~8,000,000 rows

DECLARE @date DATETIME
SET @date = GETDATE()
;
SELECT T.DateCol, DATEADD(dd,-100,@date)
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,@date)
;
SELECT T.DateCol, DATEADD(dd,-100,GETDATE())
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,GETDATE())
;

In my case the use of the variable caused SQL Server to estimate an expensive plan:

the reason is that SQL Server builds the query plan during compilation,
at that time the value of the variable is not yet known and SQL Server can not use the statistics.
I suggest you read about parameterization

There are other cases where the use of the variable will cost less then calling a function many times,
so here it is again - TEST TEST, and TEST :)


Transaction log backup after full backup is too big

My understanding is that full backups do not truncate transaction logs - only transaction log backups do (e.g., the full backup is fairly independent of the transaction logs).
However, my guess is that the re-organise all indexes does a lot of data processing - it is likely that these go into the transaction logs and are causing your issues.
You could test this by disabling or delaying the re-organise indexes one day, and see what happens to the transaction logs.


The solution that worked for me was to use Advanced Security, with netmgr or directly edit the sqlnet.ora by adding the parameters:

SQLNET.ENCRYPTION_SERVER = REQUIRED

SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)

SQLNET.ENCRYPTION_CLIENT = REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)

Once this is done it is necessary to restart the listener, for access to heterogeneous data base the sslmode = require parameter must be added in odbc.ini for postgresql, the ssl access must be previously configured in the postgres server and a registry with hostssl in the file pg_hba.conf ... I have not tried for sql server yet.
Regards


MySQL is slow with subqueries except when they are in the FROM clause, so you move your subquery to a JOIN like this:

SELECT users.ID, IFNULL( CONCAT( users.firstname, ' ', users.surname ) , '' ) AS Name, email
FROM users
JOIN (
    SELECT userID
    FROM userData
    WHERE paramName = 'Skills' AND paramValue IS NOT NULL AND paramValue != ''
    UNION
    SELECT users.ID as userID
    FROM users
    INNER JOIN contributors ON contributors.name = users.email
) t ON t.userID = users.ID
WHERE users.enabled= "Y"


Import indexes from MongoDB v2.4 to MonDB v3.6.4

I had to manually remove "options": { "create": "collection" }, from *.metadata.json file and rest worked fine.


How can I get Performance Statistics to work in MySQL Workbench?

The MySQL Workbench Performance requires the connected database to be version 5.6+. Although 5.5 has most of the capability for it, the MySQL team didn't believe it was mature enough to allow it


Master - Master setup on Postgresql 10 + Ubuntu 18.04 + Pgpool II

you can set up master - slave replication using pgpool, master-master is not supported.
To set up active - active replication you will need to use tool like SymmetricDS.
Cheers!


to copy column from one table to another in psql

You don't need an insert here, as this is about adding new rows. You want rather to make an update. Something like
create table a(id int, v varchar(10));
create table b(id int, v varchar(10));

insert into a
select 1, 'a'
union all
select 2, 'b';

insert into b
select 1, null
union
select 2, null;

update b set v = a.v
from a where a.id = b.id;


Locate the smallest missing element based on a specific formula

There are a few challenges with this question. Indexes in SQL Server can do the following very efficiently with just a few logical read each:

check that a row exists
check that a row doesn't exist
find the next row starting at some point
find the previous row starting at some point

However, they cannot be used to find the Nth row in an index. Doing that requires you roll your own index stored as a table or to scan the first N rows in the index. Your C# code heavily relies on the fact that you can efficiently find the Nth element of the array, but you can't do that here. I think that algorithm isn't usable for T-SQL without a data model change.

The second challenge relates to the restrictions on the BINARY data types. As far as I can tell you cannot perform addition, subtraction, or division in the usual ways. You can convert your BINARY(64) to a BIGINT and it won't throw conversion errors, but the behavior is not defined:

  Conversions between any data type and the binary data types are not
  guaranteed to be the same between versions of SQL Server.

In addition, the lack of conversion errors is somewhat of a problem here. You can convert anything larger than the biggest possible BIGINT value but it'll give you the wrong results.

It's true that you have values right now that are bigger than 9223372036854775807. However, if you're always starting at 1 and searching for the smallest minimum value then those large values cannot be relevant unless your table has more than 9223372036854775807 rows. This seems unlikely because your table at that point would be around 2000 exabytes, so for the purposes of answering your question I'm going to assume that the very large values do not need to be searched. I'm also going to do data type conversion because they seem to be unavoidable.

For the test data, I inserted the equivalent of 50 million sequential integers into a table along with 50 million more integers with a single value gap about every 20 values. I also inserted a single value that won't properly fit in a signed BIGINT:

CREATE TABLE dbo.BINARY_PROBLEMS (
    KeyCol BINARY(64) NOT NULL
);

INSERT INTO dbo.BINARY_PROBLEMS WITH (TABLOCK)
SELECT CAST(SUM(OFFSET) OVER (ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BINARY(64))
FROM
(
    SELECT 1 + CASE WHEN t.RN > 50000000 THEN
        CASE WHEN ABS(CHECKSUM(NewId()) % 20)  = 10 THEN 1 ELSE 0 END
    ELSE 0 END OFFSET
    FROM
    (
        SELECT TOP (100000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
        FROM master..spt_values t1
        CROSS JOIN master..spt_values t2
        CROSS JOIN master..spt_values t3
    ) t
) tt
OPTION (MAXDOP 1);

CREATE UNIQUE CLUSTERED INDEX CI_BINARY_PROBLEMS ON dbo.BINARY_PROBLEMS (KeyCol);

-- add a value too large for BIGINT
INSERT INTO dbo.BINARY_PROBLEMS
SELECT CAST(0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008000000000000000 AS BINARY(64));

That code took a few minutes to run on my machine. I made the first half of the table not have any gaps to represent a sort of worse case for performance. The code that I used to solve the problem scans the index in order so it will finish very quickly if the first gap is early on in the table. Before we get to that let's verify that the data is as it should be:

SELECT TOP (2) KeyColBigInt
FROM
(
    SELECT KeyCol
    , CAST(KeyCol AS BIGINT) KeyColBigInt
    FROM dbo.BINARY_PROBLEMS
) t
ORDER By KeyCol DESC;

The results suggest that the maximum value that we converts to BIGINT is 102500672:

╔══════════════════════╗
║     KeyColBigInt     ║
╠══════════════════════╣
║ -9223372036854775808 ║
║            102500672 ║
╚══════════════════════╝

There are 100 million rows with values that fit into BIGINT as expected:

SELECT COUNT(*)
FROM dbo.BINARY_PROBLEMS
WHERE KeyCol < 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007FFFFFFFFFFFFFFF;

One approach to this problem is to scan the index in order and to quit as soon as a row's value doesn't match the expected ROW_NUMBER() value. The entire table does not need to be scanned to get the first row: only the rows up until the first gap. Here's one way to write code that is likely to get that query plan:

SELECT TOP (1) KeyCol
FROM
(
    SELECT KeyCol
    , CAST(KeyCol AS BIGINT) KeyColBigInt
    , ROW_NUMBER() OVER (ORDER BY KeyCol) RN
    FROM dbo.BINARY_PROBLEMS
    WHERE KeyCol < 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007FFFFFFFFFFFFFFF
) t
WHERE KeyColBigInt <> RN
ORDER BY KeyCol;

For reasons that won't fit in this answer, this query will often be run serially by SQL Server and SQL Server will often underestimate the number of rows that need to be scanned before the first match is found. On my machine, SQL Server scans 50000022 rows from the index before finding the first match. The query takes 11 seconds to run. Note that this returns the first value past the gap. It's not clear which row you want exactly, but you should be able to change the query to fit your needs without a lot of trouble. Here's what the plan looks like:

My only other idea was to bully SQL Server into using parallelism for the query. I have four CPUs, so I'm going to split the data up into four ranges and do seeks on those ranges. Each CPU will be assigned a range. To calculate the ranges I just grabbed the max value and assumed that data was evenly distributed. If you want to be smarter about it you could look at a sampled stats histogram for the column values and build your ranges that way. The code below relies on a lot of undocumented tricks that aren't safe for production, including trace flag 8649:

SELECT TOP 1 ca.KeyCol
FROM (
    SELECT 1 bucket_min_value, 25625168 bucket_max_value
    UNION ALL
    SELECT 25625169, 51250336
    UNION ALL
    SELECT 51250337, 76875504
    UNION ALL
    SELECT 76875505, 102500672
) buckets
CROSS APPLY (
    SELECT TOP 1 t.KeyCol
    FROM
    (
        SELECT KeyCol
        , CAST(KeyCol AS BIGINT) KeyColBigInt
        , buckets.bucket_min_value - 1 + ROW_NUMBER() OVER (ORDER BY KeyCol) RN
        FROM dbo.BINARY_PROBLEMS
        WHERE KeyCol >= CAST(buckets.bucket_min_value AS BINARY(64)) AND KeyCol <=  CAST(buckets.bucket_max_value AS BINARY(64))
    ) t
    WHERE t.KeyColBigInt <> t.RN
    ORDER BY t.KeyCol
) ca
ORDER BY ca.KeyCol
OPTION (QUERYTRACEON 8649);

Here is what the parallel nested loop pattern looks like:

Overall, the query does more work than before since it'll scan more rows in the table. However, it now runs in 7 seconds on my desktop. It might parallelize better on a real server. Here's a link to the actual plan.

I really can't think of a good way to solve this problem. Doing the calculation outside of SQL or changing the data model may be your best bets.


Accessing Administrative Share Using Listener Name

You cannot use the listener name to access administrative shares on the cluster nodes.  When accessing a cluster network name (which is what a listener is), shares are scoped to clustered shared folders only.

Please see Deploying a two-node clustered file server for reference.


Massive Transaction Logs - what are my options?

Maybe you could have a look to minimal logging in SQL Server (but this requires changing database recovery model to simple or bulk-logged) and means that you cannot restore/recover database using point in time recovery (I have never used Azure so I don't know if this is possible).


How to exporting a databse structure (or many tables) for import

You can use the Generate Script feature to output what you need.

Right click on the database and select Task -> "Generate Scripts..."
Choose "Select specific database objects" and check the "Tables" box to script out all tables (and any other necessary objects, like schemas or database roles)
On the next dialogue, click the "Advanced" button and ensure that "Script Foreign Keys" is set to True.
Finish the wizard and output your script to a new window, clipboard, or file.

This will automatically order the object creation in the proper order so that you can run the entire script once to generate all tables/constraints/views/etc. that you need (tables, then constraints, then foreign keys, then extended properties, and so on).


Running queries one after the other in psql/scripting environment?

As @a_horse_with_no_name said, if you want to restrict the order on db level, use sequential statements, either with psql -f queries.sql or :

psql <<EOF
select..;
select..;
EOF

In your example you call psql for each query. You don't send them to background so each psql waits previous to end before calling next. Concurrency should be sane here as well. Things that you have different here: lack of control over transaction, you open/close many connections instead of one and you put sql code into bash variable


MariaDB Cluster - data consistency for updates and auto_increment

1 - Yes, Galera takes care of auto increments for you, but you will get lots of missed ids, so make sure your fields are big enough (e.g. you might need a smallint somewhere whereas without missing ids you could have got away with a tinyint).

2 - Yes, no changes should be needed to the application, except for occasionally you might get deadlocks. You should come up with a system to check error messages automatically and if you get errors like Deadlock found when trying to get lock; try restarting transaction and Lock wait timeout exceeded; try restarting transaction then redo the query and it should go through ok. The data will remain consistent. There is also a Galera setting for automatic retries, but we found it was better to try them again yourself. Obviously you shouldn't be using mysql_query() at all, but for the purposes of a simple to follow example, you could swap your queries to use something like this instead of your usual query function

function vio_mysql_query_deadlock_safe($query, $db = null) {
    $deadlock_retries_done = 0;
    $deadlock_retries_max = 10;
    while ($deadlock_retries_done < $deadlock_retries_max) {
        if (is_null($db)) {
            $q = mysql_query($query);
        } else {
            $q = mysql_query($query, $db);
        }
        if ($q === false) {
            $error = mysql_error();
            if ($error == 'Deadlock found when trying to get lock; try restarting transaction' || $error == 'Lock wait timeout exceeded; try restarting transaction') {
                $deadlock_retries_done++;
                continue;
            } else {
                throw new Exception($error . '. Query: ' . $query);
            }
        }
        return $q;
    }
    throw new SQLException($error . '. Re-tried with deadlock ' . $deadlock_retries_done . ' times. Query: ' . $query);
}


How do I disable MySQL on Linux from starting on boot or statup?

MariaDB use systemd as does MySQL. To disable MySQL or MariaDB, from starting on bootup, run

systemctl list-unit-files '*mariadb*' '*mysql*'

If you see mysql.service, try disabling that one first.

sudo systemctl disable mysql

Now you can start and stop MySQL with

sudo systemctl stop mysql.service
sudo systemctl start mysql.service


Posts, comments, replies, and likes database schema

Your question would be easier to read, if you included the references for your foreign keys.

I'm guessing that user (foreign key) refers to field id in users table (not displayed), and similarly with post.

With those two, as well as with the other foreign keys, I would attach _id to the name, so that it's clear they are ids and not names.

You don't specify primary keys. Did you already designate id in each table to be the primary key, and has auto-increment? If not, make sure you do.

Other than that, I think it's pretty good.