How do I insert record only if the record doesn't exist?

You can do this with a unique constraint & insert or ignore.

Create table:

ironforge:tmp phil$ sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table PaymentInformation
   ...> (
   ...> ID int,
   ...> name varchar(100),
   ...> start date,
   ...> end date );
sqlite>

Add a unique constraint on name and start:

sqlite> create unique index PaymentInformation_name_start on PaymentInformation ( name, start ) ;

Add a row:

sqlite> insert or ignore into PaymentInformation values ( 1,'Phil','2017-01-01','2017-02-02' );
sqlite> select * from PaymentInformation;
1|Phil|2017-01-01|2017-02-02
sqlite>

Add another row with the same name & start date, and it gets ignored:

sqlite> insert or ignore into PaymentInformation values ( 2,'Phil','2017-01-01','2017-02-02' );
sqlite> select * from PaymentInformation;
1|Phil|2017-01-01|2017-02-02
sqlite>


Can we put an equal sign (=) after aggregate functions in Transact-SQL?

This is documented in UPDATE (Transact-SQL):

  SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

In your code example, sum is the (unwise) name of a column, not an aggregate.

db<>fiddle demo


SQL server 2008 Express x64 in windows 8.1 fail

You're attempting to use "SQLExpress" as a server name.  I'm guessing that's the instance name.  Connect to ".\SQLExpress" instead.


How to allow a certain domain user to create databases on a remote SQL instance?

Yes you would need a Login for that domain user to exist first. If all they need is the ability to manage databases (create, alter, drop, restore them) then the server role dbcreator should be sufficient.
If you anticipate multiple domain users needing access to this, then you should instead create a Login for a correlating Active Directory Group, so you don't have to continually manage setting up multiple Logins. You will then be able to map that AD Group to the server role dbcreator. (You can even use the built in "Domain Users" group if you wanted everyone on the domain to have access to creating databases.)
As far as how to accomplish this in C#, there's a multitude of ways, and that's perhaps a better question for StackOverflow because it's dependent on how you're accessing and querying your server instance. For example, with ADO.NET you could simply write a SQL query to check if a Login exists for the current user, if not create it, and assign the role. Or you can even create a procedure that takes in the current user of the application, and handles that logic for you (which will be more consumable depending on what database access framework you're using). But you'll likely have the easiest time managing this by creating an appropriate AD Group and mapping it to a Login with the dbowner role once.


Stored Procedure Processing and Error Log

Here is a structure that is at least very close.

There is no programmatic way to get the parameters (unfortunately). You need to format them into XML to pass in.

The Login that initiates a SQL Agent job seems to only be logged in the message column of msdb.dbo.sysjobhistory, for step_id = 0. This value can be extracted, but not during the execution of the job.

You get ObjectID to pass in from @@PROCID.

Below is the schema (2 tables) and stored procedures (3 procs). The concept is to separate "init", "in process", and "completed (success or error)" logging. This allows for setting certain columns only at the appropriate time (e.g. only need to set DatabaseID, StartedAt, etc at the very beginning).  Separating the type of event also makes it easier to have event-specific logic (yes, can have it even in a single proc, but then you still have all the input parameters when you only need a subset per each event-type).

A "process" record get updated via its IDENTITY (and clustered PK) value. This is another benefit of having the "event type" separation: it makes it much easier to handle capturing the SCOPE_IDENTITY() and passing it back to be used for the two other logging stored procedures. If a stored procedure fails and doesn't go to the CATCH block, then there is no need to worry about accidentally updating that process record as the next time any stored procedure (that is being logged) starts, it will get a new / unique ID to update.

Cleanup (optional) and Schema

/* -- optional cleanup
DROP PROCEDURE [dbo].[ProcessLogDemo];

DROP PROCEDURE [Logging].[ProcessLog_Log];
DROP PROCEDURE [Logging].[ProcessLog_Start];
DROP PROCEDURE [Logging].[ProcessLog_Stop];

DROP TABLE [Logging].[ProcessLog];
DROP TABLE Logging.[Status];

DROP SCHEMA [Logging];
*/

CREATE SCHEMA [Logging];
GO

Tables and Indexes

CREATE TABLE Logging.[Status]
(
  [StatusID] TINYINT NOT NULL
              CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED,
  [StatusName] VARCHAR(50) NOT NULL
);

CREATE TABLE [Logging].[ProcessLog]
(
  ProcessLogID  INT NOT NULL IDENTITY(-2147483648, 1) -- start at INT min value
                 CONSTRAINT [PK_ProcessLog] PRIMARY KEY CLUSTERED,
  DatabaseID INT NOT NULL,
  ObjectID INT NULL, -- NULL = ad hoc query
  SessionID SMALLINT NOT NULL
             CONSTRAINT [DF_ProcessLog_SessionID] DEFAULT (@@SPID),
  Step TINYINT NOT NULL, -- if you have more than 255 steps, consult psychiatrist
  StatusID TINYINT NOT NULL
            CONSTRAINT [FK_ProcessLog_Status]
                FOREIGN KEY REFERENCES [Logging].[Status]([StatusID]),
  Remarks NVARCHAR(MAX) NULL, -- or maybe VARCHAR(MAX)?
  Params XML NULL,
  RowsSelected INT NULL,
  RowsInserted INT NULL,
  RowsUpdated INT NULL,
  RowsDeleted INT NULL,
  StartedBy [sysname] NULL,
  StartedAt DATETIME2 NOT NULL
             CONSTRAINT [DF_ProcessLog_StartedAt] DEFAULT (SYSDATETIME()),
  UpdatedAt DATETIME2 NULL, -- use to show progress / "heartbeat"
  StoppedAt DATETIME2 NULL
);
GO

Stored Procedure to call at the very beginning of "logged" stored procedures

CREATE PROCEDURE [Logging].[ProcessLog_Start]
(
  @DatabaseID INT,
  @ObjectID INT,
  @Params XML,
  @ProcessLogID INT = NULL OUTPUT
)
AS
SET NOCOUNT ON;

-- First, capture the MAX "instance_id" from sysjobhistory if this process is a SQL
-- Server Agent job (use later to get the "invoked by" Login), else grab the Login.
DECLARE @StartedBy [sysname];

IF (EXISTS(
           SELECT *
           FROM   sys.dm_exec_sessions sdes
           WHERE  sdes.[session_id] = @@SPID
           AND    sdes.[program_name] LIKE N'SQLAgent - TSQL JobStep (%'))
BEGIN
  DECLARE @JobID UNIQUEIDENTIFIER;

  SELECT @JobID = CONVERT(UNIQUEIDENTIFIER,
                           CONVERT(BINARY(16),
                                   SUBSTRING(sdes.[program_name],
                                        CHARINDEX(N'(Job 0x', sdes.[program_name]) + 5,
                                             34), 1
                                  )
                          )
  FROM  sys.dm_exec_sessions sdes
  WHERE sdes.[session_id] = @@SPID;

--SELECT @JobID;

  SELECT @StartedBy = N'sysjobhistory.instance_id: '
                       + CONVERT(NVARCHAR(20), MAX(sjh.[instance_id]))
  FROM   msdb.dbo.sysjobhistory sjh
  WHERE  sjh.[job_id] = @JobID;
END;
ELSE
BEGIN
  SET @StartedBy = ORIGINAL_LOGIN();
END;

-- Now it should be safe to create a new entry
INSERT INTO [Logging].[ProcessLog] ([DatabaseID], [ObjectID], [Step], [StatusID],
                                    [Params], [StartedBy])
VALUES (@DatabaseID, @ObjectID, 0, 1, @Params, @StartedBy);

SET @ProcessLogID = SCOPE_IDENTITY();
GO

Stored Procedure to call after all but the final step

CREATE PROCEDURE [Logging].[ProcessLog_Log]
(
  @ProcessLogID INT,
  @Step TINYINT,
  @RowsSelected INT = NULL,
  @RowsInserted INT = NULL,
  @RowsUpdated INT = NULL,
  @RowsDeleted INT = NULL
)
AS
SET NOCOUNT ON;

UPDATE pl
SET    pl.[StatusID] = 2, -- In process
       pl.[Step] = @Step,
       pl.[UpdatedAt] = SYSDATETIME(),
       pl.[RowsSelected] = ISNULL(@RowsSelected, pl.[RowsSelected]),
       pl.[RowsInserted] = ISNULL(@RowsInserted, pl.[RowsInserted]),
       pl.[RowsUpdated] = ISNULL(@RowsUpdated, pl.[RowsUpdated]),
       pl.[RowsDeleted] = ISNULL(@RowsDeleted, pl.[RowsDeleted])
FROM   [Logging].[ProcessLog] pl
WHERE  pl.[ProcessLogID] = @ProcessLogID;

IF (@@ROWCOUNT = 0)
BEGIN
 RAISERROR('No initial or in-process record for ProcessLogID = %d !', 16, 1,
           @ProcessLogID);
  RETURN;
END;
GO

Stored Procedure to call after the final step and/or in a CATCH block

CREATE PROCEDURE [Logging].[ProcessLog_Stop]
(
  @ProcessLogID INT,
  @Step TINYINT,
  @StatusID TINYINT,
  @Remarks NVARCHAR(MAX) = NULL,
  @RowsSelected INT = NULL,
  @RowsInserted INT = NULL,
  @RowsUpdated INT = NULL,
  @RowsDeleted INT = NULL
)
AS
SET NOCOUNT ON;

UPDATE pl
SET    pl.[StatusID] = @StatusID, -- 3 = Success, 4 = Fail
       pl.[Step] = @Step,
       pl.[Remarks] = @Remarks,
       pl.[StoppedAt] = SYSDATETIME(),
       pl.[RowsSelected] = ISNULL(@RowsSelected, pl.[RowsSelected]),
       pl.[RowsInserted] = ISNULL(@RowsSelected, pl.[RowsInserted]),
       pl.[RowsUpdated] = ISNULL(@RowsSelected, pl.[RowsUpdated]),
       pl.[RowsDeleted] = ISNULL(@RowsSelected, pl.[RowsDeleted])
FROM   [Logging].[ProcessLog] pl
WHERE  pl.[ProcessLogID] = @ProcessLogID;

IF (@@ROWCOUNT = 0)
BEGIN
 RAISERROR('No initial or in-process record for ProcessLogID = %d !', 16, 1,
           @ProcessLogID);
  RETURN;
END;
GO

Demo stored procedure (input parameters are formatted as XML)

The reason for putting "StepNumber" in a variable is so that the value can get passed to the CATCH block. The @StepNumber variable is incremented prior to each operation. If the operation succeeds, that value is used to call the "Log" stored procedure that captures the number of rows affected for that step and the time it was called. If the operation fails, that same @StepNumber value is used to call the "Stop" stored procedure that marks the process as "failed" and passes in the error message. This makes the data less confusing since the Step column on failed records will be the step it was actually working on when the error occurred.

CREATE PROCEDURE [dbo].[ProcessLogDemo]
(
  @Param1 INT,
  @Param2 DATETIME,
  @Param3 NVARCHAR(50) = NULL
)
AS
SET NOCOUNT ON;

DECLARE @ProcessID INT,
        @DB_ID INT = DB_ID(),
        @Params XML,
        @StepNumber TINYINT;

SET @Params = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3]
   FOR XML PATH(N'Params')
); -- missing elements mean the value == NULL
--SELECT @Params;

BEGIN TRY

  EXEC [Logging].[ProcessLog_Start]
    @DatabaseID = @DB_ID,
    @ObjectID = @@PROCID,
    @Params = @Params,
    @ProcessLogID = @ProcessID OUTPUT;

  SET @StepNumber = 1;

  -- do something

  EXEC [Logging].[ProcessLog_Log]
    @ProcessLogID = @ProcessID,
    @Step = @StepNumber,
    @RowsSelected = @@ROWCOUNT;

  SET @StepNumber = 2;

  -- do something else

  EXEC [Logging].[ProcessLog_Log]
    @ProcessLogID = @ProcessID,
    @Step = @StepNumber,
    @RowsUpdated = @@ROWCOUNT;

  SET @StepNumber = 3;

  -- do final thingy

  EXEC [Logging].[ProcessLog_Stop]
    @ProcessLogID = @ProcessID,
    @Step = @StepNumber,
    @StatusID = 3, -- success
    @RowsInserted = @@ROWCOUNT;

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();

  EXEC [Logging].[ProcessLog_Stop]
    @ProcessLogID = @ProcessID,
    @Step = @StepNumber,
    @StatusID = 4, -- fail
    @Remarks = @ErrorMessage;
END CATCH;
GO

NOTES:

With regards to getting the "invoked by" Login for SQL Server Agent jobs: the step_id = 0 record (which is the only place that this info exists) does not exist until the job completes (success or failure). Hence, it isn't available while the stored procedure is running, let alone at the beginning. For now we capture the MAX(sjh.[instance_id]) FROM msdb.dbo.sysjobhistory sjh for the currently executing job for the current session. Later (i.e. after the job completes), that can get replaced with the job invoker Login.
I would generally recommend against adding this type of logging to Stored Procedures that are executed very frequently as the additional read and write operations will have a negative impact on performance.

ADDENDUM

Here is an Inline Table-Valued Function (ITVF) to get the job outcome info (including the "invoked by" user or schedule or whatever) based on the instance_id value that was captured into the ProcessLog.StartedBy column. The instance_id value returned in the result set is the row for step_id = 0.

CREATE FUNCTION dbo.GetSqlServerAgentJobOutcome
(
  @InstanceID INT
)
RETURNS TABLE
AS RETURN

WITH cte AS
(
  SELECT TOP (1)
         sjh.[instance_id],
         sjh.job_id,
         sjh.[message],
         sjh.[run_date],
         sjh.[run_time],
         sjh.[run_duration],
         sjh.[run_status],
         sjh.[sql_message_id],
         sjh.[sql_severity],
         (CHARINDEX(N' was invoked by ', sjh.[message]) + 16) AS [invoker_begin],
         CHARINDEX(N'.  The last step to run', sjh.[message]) AS [invoker_end]
  FROM   msdb.dbo.sysjobhistory  sjh
  WHERE  sjh.[job_id] = (SELECT sjh2.[job_id]
                         FROM   msdb.dbo.sysjobhistory sjh2
                         WHERE  sjh2.[instance_id] = @InstanceID)
  AND    sjh.[step_id] = 0
  AND    sjh.[instance_id] >= @InstanceID
  ORDER BY instance_id ASC
)
SELECT [instance_id], [job_id],
       --[message],
       [run_date], [run_time],
       [run_duration], [run_status],
       [sql_message_id], [sql_severity],
       SUBSTRING([message], invoker_begin, ([invoker_end] - [invoker_begin]))
          AS [InvokedBy]
FROM   cte;
GO


Your problem is that you are working with two different data types. ISNULL(field,'') implies that you are working with a character field.  2 however is a number.  Which do you need?

You can do something like this:

ISNULL(field,'') = '2'

But if field happens to be '02' then the result will be false.

Or if field is actually a number then you could do something like this:

ISNULL(field,0) = 2

But if field is a string data type that you are using to hold numbers and you just happen to have an 'Q' in the field then you are going to get a conversion error.

I'm assuming the first option is best since you are getting the error on 'E'.

When you use the code you actually have it is doing an implicit conversion behind the scenes.  If you look at the XML execution plan for the following code:

CREATE TABLE #DataTypeTest (Col1 varchar(50))

INSERT INTO #DataTypeTest VALUES ('1'),('2')

SELECT *
FROM #DataTypeTest
WHERE ISNULL(Col1,'') = 2

You'll see this line (talking about the ISNULL)

 <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,isnull([tempdb].[dbo].[#DataTypeTest].[Col1],''),0)=(2)">

You can see how it's actually converting the ISNULL in to an INT data type.  This is because you are comparing it to a integer.  It doesn't convert the 2 to a string because INT is higher than VARCHAR on the data type precedence list.

Now when it tries to convert the ISNULL formula into an integer it of course fails when the column contains an E.


Postgres get rows where a field stopps growing

Use the window function lead() in a subquery:

SELECT id, device_id, uptime
FROM  (
   SELECT *, lead(uptime) OVER (PARTITION BY device_id ORDER BY id) AS next_uptime
   FROM   tbl
   ) sub
WHERE  next_uptime < uptime

BTW, "where a field stops growing" would translate to <=, but "where the next row's timestamp is smaller" requires <.


Incorrect Sum values the quantity is multiplied by the number of rows

SELECT
info.[OFNumber]
--Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
,SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'

-- Get sum of item quantity from the same category (Only quantity produced) PS_DailyProduction table
,SUM(Case WHEN info.[FK_PSCategory]=2 then p.Quantity ELSE 0 END) as 'qty'

FROM
(
   SELECT [FK_OF], Sum(Quantity)
   FROM [dbo].[PS_DailyProduction]
   GROUP BY [FK_OF]
) p
Left JOIN PS_ProjectInfo info on info.id=p.[FK_OF]

GROUP BY info.[OFNumber]

BTW, not quite understand why you use left join, since PS_ProjectInfo seems a must


Tables for Product Components AND Component Combinations

My personal recommendation would be to have a separate table four each of the for attributes listing the different options. This is purely to make it easy to validate data entry.

Then a product for each combination that is required - store transactions against this.

Then a product line table that has several products of different sizes/colors whatever.

Reason for this approach is that a colour/size combination represents a product that should be tracked distinctly from any other size/color of the same thing. A product line might be 'army cargo trousers', and could come in lots of sizes and colours.


(OK, assuming that files bigger than 4GB must be stored in a table...)

Going past 4GB requires chunking of some form, since that is the LONGBLOB limit.

There are many settings that will trip you up.  The settings occur in MySQL server, mysqldump, MySQL replication, Apache, networking, various timeouts, etc, etc.  A 1MB chunk might be safe for most situations; 1GB is very likely to hit some limit that will be hard to get around.  max_allowed_packet, for example, has a hard limit of 1GB, but a practical limit much lower.  Anyway, there is not much code or speed difference between 200 chunks of 1GB and 200K chunks of 1MB.  In fact, smaller chunks might be faster.

When I implemented something similar a decade ago, I decided that a BLOB of 50KB was a good chunk size, and I compressed each chunk separately with client code.  My setup involved replication, so I was careful to insert each chunk separately so as not to hog the replication stream.

Shoveling 50GB around will take a lot of time.  Do not do everything in a single InnoDB transactions.  Instead, store chunks optimistically in separate transactions and worry about trash only if you crash before the final store of the master record.

Most image formats are already compressed, so it is a waste to re-compress such.

Yes, your 2 tables are approximately 'correct'.  No, I don't see a 5x problem.  However, there might be 2x degradation over simply storing as a file.


Since this is easier to elaborate on in an answer than in a comment.....

We tended to install our instances to /inst/<instance-name>. I leave the instance naming scheme up to you. If you make it too similar than technically you have introduced a security risk as the instance names are eaiser to guess.

Then you could so something like the following:

/inst/<instance-name>/bkp
/inst/<instance-name>/offline
/inst/<instance-name>/online
/inst/<instance-name>/data
/inst/<instance-name>/dbdir
/inst/<instance-name>/logs
/inst/<instance-name>/archlogs

You can work with your server admins or your SAN admins or do the work yourself to decide which of these mounts is physically separate disk, LUNs, etc.

This is just something we did. We did it to provide separation of concerns of where the instance was installed, vs where the system tables vs the data tables, vs the logs, vs the archive logs, vs the backups. Whether or not it works for your organization, you will have to discuss.

Whatever you do, be consistent as much as is possible.


How to tell if Azure SQL DW is using a secondary index?

As you have chosen to hash distribute your table on EmailAddress, this will mean all email addresses with the same value will end up with the same hash and consequently the same distribution - where SQL DW always has 60 distributions distributed across a number of nodes.  As such you won't be making best use of the compute available to you.

Having said that, can you confirm what DWU you are running at, what resource class is associated with the user you are running as and that you have created the relevant statisics ( ie on Email )?

Looking at your secondary index, it only contains one column, so is best suited to queries only containing that column, or for small point-lookups (assuming SQL DW behaves in a similar manner to SQL Server on this which isn't necessarily true).  Even it did this, it would then have to fetch the other columns from the main columnstore index to service your SELECT *.

Have a look at this article for advice on hash distributing large tables:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-best-practices#hash-distribute-large-tables

If this is one of your business-critical queries, you could consider a different hashing column, or even experiment with round robin distribution.  Is that something you join on for example?  In in this simple example, I create a copy of the table using ROUND_ROBIN distribution and run the query against that table:

CREATE TABLE FactBusinessEvent_rr
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM dbo.FactBusinessEvent;
GO

-- Create the required statistics
CREATE STATISTICS _st_FactBusinessEvent_Email_rr ON dbo.FactBusinessEvent_rr ( EmailAddress );
-- other stats here, ie columns you will join on, use in WHERE clause, or aggregate on
-- ...
GO

SELECT *
FROM dbo.FactBusinessEvent_rr
WHERE EmailAddress = 'test@test.com'
OPTION ( LABEL = 'email round robin query' );


Transfer of data from table to other database table have the same primary key

There are a couple of ways to do this.  It depends on your setup.  As per the previous question,  if you just need a copy of everything you can backup and restore the database onto a new instance or, on the same instance with a different name.

If you need to physically pipe data with identity columns, into a new database thats already setup, you could use the import export tool.  With that you can easily select just the tables you want data from, and the destination.  Its quick and easy for a one time load.  If you need to keep Identity columns, remember to select Column Mappings, and check the box to enable Identity insert per the article on Microsoft's website.

This is just one option to moving the data.  A quick google and you will find many other options, including BCP, Linked Servers, SSIS, etc.  It all depends on how often you want to do this load.  How often do you need to do this?


Left Joining Tables with tables that have subquery aggregate

SELECT per.personID,
       id.lastName,
       id.firstName,
       id.middleName,
       pos.StartDate,
       cal.[name],
       en.grade,
       pos.eligibility
FROM dbo.Person per
     INNER JOIN dbo.[Identity] id ON per.personID = id.personID
     INNER JOIN dbo.Enrollment en ON per.personID = en.personID
     INNER JOIN dbo.Calendar cal ON en.calendarID = cal.calendarID
     OUTER APPLY
(
    SELECT TOP 1 p.eligibility,
                 StartDate = CONVERT( VARCHAR(10), p.startDate, 101)
    FROM dbo.POSEligibility AS p
    WHERE p.personID = per.personID
    ORDER BY p.startDate DESC
) AS pos
WHERE en.grade = '01'
      AND cal.[name] LIKE '%BES'
      AND en.active = '1'
      AND en.endYear = '2017';

Note that the filter on cal.[name] is not sargable. This means that even if that column is indexed, the index will not be used for a seek due to the leading "%" in the predicate.


-- sample data
with data as
(
  select 34 as id, 1 as an_id, 'FIRST NAME' as title,
    'ANDY' as full_title from dual union all
  select 34 as id, 1 as an_id, 'LAST NAME' as title,
    'COOL' as full_title from dual union all
  select 34 as id, 1 as an_id, 'FIRST & LAST' as title,
    'ANDY COOL' as full_title from dual union all
  select 34 as id, 2 as an_id, 'FIRST NAME' as title,
    'SCHUMACK' as full_title from dual union all
  select 34 as id, 2 as an_id, 'LAST NAME' as title,
    'LAST' as full_title from dual union all
  select 34 as id, 2 as an_id, 'FIRST & LAST' as title,
    'SCHUMACK LAST' as full_title from dual union all
  select 32 as id, 1 as an_id, 'FIRST NAME' as title,
    'MARIO' as full_title from dual union all
  select 32 as id, 1 as an_id, 'LAST NAME' as title,
    'SANTI' as full_title from dual union all
  select 32 as id, 1 as an_id, 'FIRST & LAST' as title,
    'MARIO SANTI' as full_title from dual
)
-- query
select * from data
pivot (min(full_title) for title in ('FIRST NAME', 'LAST NAME', 'FIRST & LAST'))
order by id desc, an_id
;

        ID      AN_ID 'FIRST NAME'  'LAST NAME'   'FIRST & LAST
---------- ---------- ------------- ------------- -------------
        34          1 ANDY          COOL          ANDY COOL
        34          2 SCHUMACK      LAST          SCHUMACK LAST
        32          1 MARIO         SANTI         MARIO SANTI

Note that PIVOT requires an aggregate function, I could have used for example MAX as well.


Python code in T-SQL environment gives "access a socket in a way forbidden by its access permissions" Error

Open the Windows Firewall and look for a rule called "Block network access for R local user accounts in SQL Server instance MSSQLSERVER" (assuming you have a default instance).  This Firewall rule blocks network access from SQL Server external scripts.  You can disable the rule if you want to allow network access.


Is there a way to upgrade to SQL Server 2012 from SQL Server 2008 R2 without installing any of the three service packs?

According to this Microsoft link, you have to be at least at Sql 2008 R2 (SP1) to upgrade to Sql 2012.


SQL Server 2014 - AlwaysOn HA - Always Synchronizing

Since you have configured AlwaysON as Asynchronous, it will be in synchronizing state.

  Should it say Synchronized at some point?

If you want to do a planned failover, you should alter your availablity group to synchronous and then do a failover.

When you change the synchronization method to synchronous and when the secondary has catch-up with primary, it will show synchronized.

Refer  to my answer : How far is secondary behind primary ? as well as AlwaysON professional blog - Determine Availability Group Synchronization State, Minimize Data Loss When Quorum is Forced


Thread Count is too high-what can I do in short term?

Default formula for [max workers] (at or below 64 logical CPUs): 16*(logical CPUs - 4) + 512

Here are two potential justifications if you want to raise [max workers]. If CPU is not saturated when DOP downgrades occur due to reserved worker count, negative consequences are limited. YMMV.

General: in sql server 2019 (maybe before? i’ve Not checked) if above 64 logical cpu/user schedulers, the multiplier for default is changed from 16 to 32. for the systems i care about, expected intensity of work per scheduler is the same whether 8 vcpu or 80. So why not always use 32?
specific to positioning re: hyperthreading.
again, for my workloads, expected intensity of work per core is the same whether HT or not. But the VMs have 1 vcpu per pcpu. And some physical servers have HT disabled. In those cases, why not use the “theoretical” logical cpu/scheduler count for the system if HT were enabled?


Trigger with multiple inserts based on JSON data

This is not valid JSON; strings require double quotes.

Anyway, the table-valued function json_each() allows to enumerate a dynamically-sized array:

SELECT * FROM json_each('[["c2","c1","clone"],["c1","n0","clone"],["n0","p0","offspring"],["p0","a2","offspring"],["a2","a1","offspring"],["a1",null,null]]');
key         value                type        atom        id          parent      fullkey     path
----------  -------------------  ----------  ----------  ----------  ----------  ----------  ----------
0           ["c2","c1","clone"]  array                   1                       $[0]        $
1           ["c1","n0","clone"]  array                   5                       $[1]        $
2           ["n0","p0","offspri  array                   9                       $[2]        $
3           ["p0","a2","offspri  array                   13                      $[3]        $
4           ["a2","a1","offspri  array                   17                      $[4]        $
5           ["a1",null,null]     array                   21                      $[5]        $

For extracting the values from an array with a known size, we can use json_extract():

SELECT json_extract(value, '$[0]'), json_extract(value, '$[1]'), json_extract(value, '$[2]')
FROM json_each('[["c2","c1","clone"],["c1","n0","clone"],["n0","p0","offspring"],["p0","a2","offspring"],["a2","a1","offspring"],["a1",null,null]]');
c2          c1          clone
c1          n0          clone
n0          p0          offspring
p0          a2          offspring
a2          a1          offspring
a1

This can then simply be plugged into an INSERT … SELECT … statement.


PostgreSQL return second 'Group' as an array

Maybe something like this:

with ranked_visits as (
  SELECT w.website_id,
         v.visitor_id,
         count(wv.visit_id) as visits,
         row_number() over (partition by w.website_id order by count(wv.visit_id) desc) as rnk
  FROM website_visits wv
    JOIN websites w ON wv.website_id = w.website_id
    JOIN visitors v ON wv.visitor_id = v.visitor_id
  GROUP BY w.website_id, v.visitor_id
  ORDER BY w.website_id ASC, count(wv.visit_id) DESC
)
select website_id, string_agg('visitor_id: '||visitor_id||',visits:'||visits, ', ')
from ranked_visits
where rnk <= 10
group by website_id;


ForeignKey setup clarification on updating related fields

You could solve this with a two-column foreign key and cascading update, but that requires a redundant two-column unique constraint on report, which will harm DML performance and use extra storage space.
The proper solution is to remove the redundant column from report_years. This concept of removing redundancy from a data model to improve consistency is so important that there is a name for it: normalization.


I cannot add a new Server Role "SQL Server 2008 R2"

CREATE SERVER ROLE only apply to SQL Server 2012 through current version.

See on MSDN: CREATE SERVER ROLE (Transact-SQL)


What's the best paradigm or design pattern for exception handling in a mission-critical DB driven web application?

Of course! Like most interesting problems in mission-critical applications, this was actually solved by IBM in the 1970s, but the IT industry is always chasing the shiniest new thing and has no respect for the great engineers of the past. I am referring of course to message oriented middleware, or as it was known back in the day, TPF. The principle of this is actually very simple, and has been proven time and time again, by banks, airlines, telcos, you name it.

The fundamental operations are, to place a message on the queue, and to take a message off the queue. So when your application needs to do some work, it packages that as a message and places it on the queue for the next service. The TPM then dequeues that message and attempts to get work done on it. If this fails, the message simply goes back onto the queue to be tried again, and if it continues to fail, gets shunted across to the queue that handles failures. The message bus also handles routing, load balancing and all that good stuff. You can buy these capabilities off the shelf: MQSeries and Tibco Rendezvous are the "serious" ones, but there are plenty of others around, many of which are compatible with JMS so you aren't tied into one vendor (of course if you go JMS then you are also bound to the lowest common denominator). You spec your message bus to be ultra-reliable and fault-tolerant, then you just hang the services off the back of it, and put your app on the front. SOA is another example of an old idea that's been reheated with new buzzwords...


DB2 Express-C 10.1 database move to DB2 Work group Server/Enterprise Edition

If I understand you right, you are going from DB2 10.1 Express-C edition to DB2 10.1 Work Group Server Edition or Enterprise Edition.

If that is case then you shouldn't have any issues. The binaries for DB2 are basically the same across editions. It is just a matter of a license file that enables or disables certain features and enables/disables use of more/less CPU and memory. (The only exception to this I have seen is the DB2 Spatial Extender-for GIS type storage-which normally would be in Express-C, but somehow isn't. So that one you have to download (for free) from IBM's site. But if you did a regular Enterprise Edition install, it is installed.)

Anyway, if you are "migrating" the entire instance, I take that to mean you just need to apply the Work Group Server edition license file or the Enterprise Edition license file to the database instance and Express-C will automatically turn into one of the other editions. If you are talking about moving data from an Express-C environment to another environment, there should be even less issues (pending the database version is the same). If that is the case you could do a backup from one and a restore to the other.


Can a killed application process cause blocking of the server?

By default, an Oracle database does not detect dead clients, you need to enable it explicitly by setting SQLNET.EXPIRE_TIME in sqlnet.ora.

SQLNET.EXPIRE_TIME

Since the database does not detect dead clients, it maintains its session and locks, so the answer is yes, killed application processes can cause blocking.

This parameter is often misinterpreted. Setting this parameter to 10 does not mean dead connections will disappear at most in 10 minutes. This means the database server sends a probe packet every 10 minutes. The time required to detect a dead client depends on the operating system as well. For example on Linux, an already established TCP connection is valuable, so even if there is no answer from the other side, the server keeps trying to reach it. When the database server sends the probe packet and receives no answer, it will try again, 15 times in total (because of the tcp_retries2 kernel parameter), and the total time needed to detect a dead client can be way more than 10 minutes.


PostgreSQL >= 10 does not generate WAL if there are no transactions

That must be because of commit 6ef2eba3f:

  Skip checkpoints, archiving on idle systems.

  Some background activity (like checkpoints, archive timeout, standby
  snapshots) is not supposed to happen on an idle system. Unfortunately
  so far it was not easy to determine when a system is idle, which
  defeated some of the attempts to avoid redundant activity on an idle
  system.

If you really need to have regular WAL activity, the cheapest way is to have a cron job that runs SELECT txid_current() regularly.

If you want to monitor replication, it is better to measure the lag in bytes, that is immune to this problem.


Name of not null constraint is missing in error message

So far as I can tell the bit that goes CONSTRAINT NN_import_event__filename is just ignored.

the name nn_import_event__filename is not present when I dump the database, only the not-null attribute remains.

This does not match the the example given in the documentaion  which claims that it is possible to name a not null constraint.

  Define two NOT NULL column constraints on the table distributors, one of which is explicitly given a name:

CREATE TABLE distributors (
   did     integer CONSTRAINT no_null NOT NULL,
   name    varchar(40) NOT NULL
);

So, you've found a bug.  if you report it it will probably be corrected fairly quickly, but the fix may be merely an update to the documentation.


Converting CHAR to DATETIME so I can use DATEADD()

Well, first off, you should fix your table and store date/time data using the right kind of column, and not breaking it up for reasons unknown. Whose decision was it to store a time as a CHAR(6)? Can you think of a single good reason for that? Where do you store the date? Was that 1 PM today, last Tuesday, or October 2012 sometime? This really should be a single datetime column. Have the "designers" please read this and this.

But barring that (funny how every time you suggest a table change, they say they can't change the table):

DECLARE @x TABLE(y CHAR(6));

INSERT @x(y) VALUES('131329');

SELECT DATEADD(MINUTE,5,CONVERT(TIME(0),STUFF(STUFF(y,5,0,':'),3,0,':')))
  FROM @x;

Result:

13:18:29

If you find that query ugly and tedious, good! There's a good reason: You're storing your data wrong.

And this won't be the last problem you have with this design, either. What is stopping someone from inserting 967286 or foobar or <empty string> in your CHAR(6) column? If you need to work around that problem, you can use TRY_CONVERT() in 2012 and up (please always specify version with a version-specific tag), or a CASE expression in earlier versions.

DECLARE @x TABLE(y CHAR(6));

INSERT @x(y) VALUES('foobar');

-- 2012:
SELECT DATEADD(MINUTE,5,TRY_CONVERT(TIME(0), STUFF(STUFF(y,5,0,':'),3,0,':')))
  FROM @x;

-- older:
SELECT DATEADD(MINUTE,5,CONVERT(TIME(0),
CASE WHEN ISDATE(STUFF(STUFF(y,5,0,':'),3,0,':'))=1 THEN
    STUFF(STUFF(y,5,0,':'),3,0,':') END))
  FROM @x;


Create table permissions issues with SQL Server

Create a new schema that they own.  Eg

CREATE SCHEMA ANALYST
ALTER AUTHORIZATION ON SCHEMA::ANALYST TO [COMPANY\ANALYST USERS]

There’s no way to grant them the ability to create tables in a schema and reliably limit their permissions on other objects in that schema.


How to sum measure value for all records between two dimension values

First you need two cube-dimensions because you cannot use one dimension on more than one axe of a MDX-select. You can use role-playing dimensions, that means there is only one "Week" dimension in the database but it is used twice with different names for the cube.

Second each Week dimension has to be connected to a measure group. If you use only one measure group with one column "WeekId" you can only get a measure not null when you select the same week from both dimensions.

One solution is to create a facts-table with the cartesian product of all combinations Week1Id, Week2Id and a value precalculated to the sum you wanted.

Or you use two measure groups, one for "FromWeek", one for "ToWeek" and store a rolling sum of the metric, here in one table:

Week  SumFrom   SumTo
1       0         6
2       6        11
3      11        15
4      15        18
5      18        20

and create a calculated

MEMBER [measures].[result] AS ([ToWeek].CurrentMember, [Measures].[SumTo]) - ([FromWeek].CurrentMember, [Measures].[SumFrom])


Migration from Cassandra 2.x to 3.x: schema_keyspaces table is gone. How do you query system keyspaces?

There is a new system keyspace called "system_schema", but there are only two tables in it:

Ok something must have gone wrong in your upgrade, because that's not right.  When I check my system_schema 3.4, I see this:

[cqlsh 5.0.1 | Cassandra 3.4 | CQL spec 3.4.0 | Native protocol v4]
Use HELP for help.
aploetz@cqlsh> use system_schema ;
aploetz@cqlsh:system_schema> desc tables;

tables     triggers    views    keyspaces  dropped_columns
functions  aggregates  indexes  types      columns

There are definitely more than two tables in that keyspace.

  How do I query to see what keyspaces are available?

The new way to do this, is to query system_schema.keyspaces:

aploetz@cqlsh:system_schema> SELECT * FROM keyspaces;

 keyspace_name          | durable_writes | replication
------------------------+----------------+-------------------------------------------------------------------------------------
        zeroreplication |           True |       {'DC1': '0', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
            system_auth |           True | {'class': 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
          system_schema |           True |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
          experfy_class |           True |       {'DC1': '1', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
                 system |           True |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
          stackoverflow |           True |       {'DC1': '1', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
              eqcontrol |           True |       {'DC1': '1', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}

The main difference between system.schema_keyspaces and system_schema.keyspaces, is that system_schema.keyspaces only has 3 columns instead of two (strategy_class and strategy_options were combined into replication).


Consistent logical backup of databases that use MyISAM and InnoDB engines

With mysqldump you can only safely use --single-transaction if all your tables are InnoDB, otherwise your backup is inconsistent.

If you have the requirement for a hybrid backup, then you need the lock-tables on all tables in the backup (default), which will be safe for all engines.  It's also worth mentioning that the default options will make sure your backup is safe, you don't need to turn any special flag on.

Note: If you do have a hybrid mix, perhaps look at xtrabackup.  It will only be locking during the MyISAM phase of the backup.


First of all, some tables are seperated but really hold the same kind of data.

For example, video, audio and images are all saved as urls, so you can join them in one table instead of 3.

This will greatly simplify your schema, and allow you to permit several types of content in one collection (for example, a wedding album that contains images & video).

Second, if users upload the content, how come you can keep it in urls? Where is the data actually saved? My first thought when seeing this was that you need to keep a binary object column for the content itself.

Third, did you mean to have a many-to-many connection between content and albums? It means every item can be in several albums. If you would like to limit an item to only one album, you would not use a "connector" table, but instead keep an album id in the content table itself (a foreign-key to the album table).

And last - as a "social media file sharing" schema, it seems to be missing the social aspect (where is sharing information saved?).


join value in a RETURNING which is not inserted

Perhaps there's a better alternative but I can only think of joining back to the 2 tables.

This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable:

with ins (id, x, y) as
( insert into new_table (x, y)
  select A.x, B.y
  from A join B on A.w = B.z
  returning id, x, y
)
-- insert into another_table (id, z)
select
    ins.id, B.z        -- whatever columns from the 3 tables
from ins
  join A on A.x = ins.x
  join B on B.y = ins.y and A.w = B.z ;


Does SQL Server 2017 CU10 include CVE-2018-8273 hotfix?

Yes, the security fix is in the CU. Direct but private comment from a reliable source within Microsoft:

  Security fixes always roll-up to any subsequent CU. That's been the case for years.

And from another colleague at Microsoft:

  all CU servicing releases for a given baseline are 100% cumulative of all previous Security Updates, CUs, and On Demand hotfixes released to date for that baseline (RTM or SP).  This is mechanically mandated by the fact that we use the same physical CU source repository, we do not have any fix specific release repositories, and we do not remove fixes once they are released.

With very few historical exceptions, cumulative updates always include the fixes in lower builds from the same branch, security or otherwise. Prior to SQL Server 2017, this could be different because of the way service packs were versioned (e.g. service pack 2 RTM has a higher build number than sp1 cu28 even though the latter is 6 months newer). But that is just a cosmetic thing - it still holds true for the branch, but it doesn't always hold true if you are ignoring service pack level and only comparing @@VERSION numbers.

  Has Microsoft explicitly documented anywhere that the security fix is included in CU10? I'd like to be able to prove to management that the fix is there.

I've asked multiple times for more transparency about what fixes are included (or not included) in a specific CU, especially when something like this happens -- a security hotfix with its own set of issues was released between CUs. They have taken the feedback and I do hope to see some official documentation at least on the Release Services Team blog posts announcing each new release.

All I can say is that's not a fast-moving machine over there, and both automated processes and lawyers can sometimes get in the way of what can be disclosed in automatically-generated content like CU KB articles. For now you're going to have to take my (and their) word for it.


If the column never has an underscore other than the one you want to remove, you can use SUBSTRING_INDEX().

update yourtable set yourcolumn = substring_index(yourcolumn,'_',1);


convert legacy showplan text into graphical format

I have been told by colleagues at Microsoft that this is not possible, as the legacy SHOWPLAN_TEXT does not have enough details to display in a graphical format.


How to store file paths, which collation to use?

Not until MySQL 8.0 is there a "case-insensitive, accent-sensitive" collation:

mysql> set names utf8mb4 collate utf8mb4_0900_as_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a'='A', "a" = "à" ;
+---------+------------+
| 'a'='A' | "a" = "à"  |
+---------+------------+
|       1 |          0 |
+---------+------------+
1 row in set (0.00 sec)


MS-SQL 2014 Log Shipping: Access permission question

The people or security contexts running the expensive queries on the wrong instance should be identified and then notified to change their processes on the instance and DB to start running their stuff on the instance you want those run on. Since the secondary is read-only/standby, we would assume these expensive queries are SELECT statements only where the primary DB that's not in standby would have to run queries that update data or modify objects regardless of the expense.  You could also look into why some of these queries are so expensive in the first place, see if adding indexes would help, rewriting queries for performance tuning, etc.  I think that'd be a good root cause type solution.

It seems like it'd be tricky to do this with one of more AD groups though as the changes are replicated from primary to secondary and secondary would be in standby to accept transaction log changes from primary when those are applied so I'm not sure how you would accomplish this in that sort of configuration with log shipping.

I'm also not certain how often your tran logs are being restored to secondary as I thought when LSRestore jobs run, it disconnects all session on secondary until those transactions are committed, so I assume it's once a day or not too often or people would be screaming about their queries, etc. getting disconnected during normal hours.  If once-a-day restores are occurring for LSRestore jobs on secondary, then this would mean the  data gotten from that server is 24 hours old or however long between your LSBackup, LSCopy, and LSRestore jobs.  So who can use which DB with this regard may depend on how fresh their query results need to be from the business side, etc.

Lots of factors to consider here but getting to the root cause and having bad performing queries tuned, adding indexes, etc. may be the best solution as well as having the people with access take responsibility with their processes to not hose up the performance for others when they run their stuff.


Which one to consider for upgrade SQL Server 2008 vs SQL Server 2014 or Compatibility level 100 vs 110

Compatibility levels are the way to tell SQL Server how to interpret the T-SQL and how the database will behave. Paul Randal wrote up a short comparison about the differences between version and compatibility levels about 9 years ago.

In a nutshell he says there is no relationship between compatibility versions and a database versions.  But you may have access to the new features in an older compatibility version of a database they just may not behave as expected.  One extra thing of note is that if you have multiple databases on the same server you can set each of them to their own compatibility level.


If I use '""' as NULL, I get:

I get these issues all the time. PostgreSQL has made a really massive and stupid mistake from a UI perspective by having two modes

CSV
NON-CSV

CSV according to them is RFC4180. However, it's not all that useful to adhere to a spec if no one else does and RFC4180 is probably the least used spec on the internet. Start using it people.

Anyway to solve these problems the easy method is to create a test table...

CREATE TABLE foo ( x int, y int, z int );
INSERT INTO foo (x,y,z) VALUES (1,null,2);

And then to get to work brute forcing options for both modes. That's why I do anyway because I find it so unintuitive. It's pretty quick though.

COPY foo TO STDOUT NULL AS '""' DELIMITER ',';

That seems to do what you want. You should be able to read it with the same options.

COPY foo FROM '/tmp/test.csv' NULL AS '""' DELIMITER ',';


The first thing that jumps out to me is the difference in table access method on Socios. Both queries use the 79271_79270_Socios index but they use them in very different ways. The slow query does a seek to find all rows with Tarjeta not equal to ''. The query optimizer expects to read 6496920 rows out of 6498640 total rows in the table. A predicate is applied to filter out rows not equal to '0'. A filter operator is applied restrict the rows down to those that equal @Tarjeta OR @TarjetaAux, which are NVARCHAR(MAX) variables. This strategy seems odd and a not very efficient use of an index. You said that changing the data type helped so let's do some simple testing to see if SQL Server handles NVARCHAR(MAX) predicates in some special way.

I'm going to assume that the Tarjeta column has a data type of NVARCHAR(50) but I don't think that's particularly important for this demo. Here is my sample data:

create table #demo (col nvarchar(50));

INSERT INTO #demo WITH (TABLOCK)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 1000
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

create index ix on #demo (col);

For this query I get a nice, simple index seek that has a low cost and only expects to read and return 1000 rows:

SELECT col
FROM #demo
WHERE col = '123';

This query has the same plan:

SELECT col
FROM #demo
WHERE col <> '0' AND col = '123';

It looks like the optimizer is smart enough to eliminate the col <> '0' filter because it's redundant.

If I cast my filter value to NVARCHAR(MAX) things take an unexpected turn:

SELECT col
FROM #demo
WHERE col = CAST('123' AS NVARCHAR(MAX));

The performance of the query is fine. It still only reads 1000 rows from the index. However, the plan is much more complicated. It looks like some information is missing from the XML and I can't figure out how to properly read it. Within the XML I found this:

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes(CONVERT(nvarchar(max),'123',0),CONVERT(nvarchar(max),'123',0),(62))">

Which seems like a hint that SQL Server has to do some kind of conversion to deal with the nvarchar(max) constant value. You can find more information about this in Dynamic Seeks and Hidden Implicit Conversions.

If I make the query a little more complicated I get a similar access pattern to what you experienced with the bad query:

SELECT col
FROM #demo WITH (FORCESEEK)
WHERE col <> '0' AND col = CAST('123' AS NVARCHAR(MAX));

The FORCESEEK hint was an attempt to make the query plan better but it didn't work. SQL Server will read all of the rows from the index and this plan has a much higher cost than before. Note that if we prevent SQL Server from applying an index seek on the <> '0' filter we can get a much more reasonable plan:

SELECT col
FROM #demo
WHERE LEFT(col, 50) <> '0' AND col = CAST('123' AS NVARCHAR(MAX));

However, a better solution is to change the datatype if possible. When switching to NVARCHAR(50) as you did I get a nice, simple index seek with this code:

DECLARE @Tarjeta nvarchar(50) = '123';

SELECT col
FROM #demo
WHERE col <> '0' AND col = @Tarjeta;

In conclusion, the unnecessary use of VARCHAR(MAX) appears to be resulting in extra work in some cases and leading to unnecessarily inefficient plans in others. Based on the information that we have, changing the variable's type seems to be the right preventative step.

Regarding the No Join Predicate warning, you always see that when SQL Server implements part of your query as a cross join. Check out StarJoinInfo in Execution Plans for further information on why that might happen. Sometimes cross joins are good for performance and the right thing to do. Sometimes they may not be ideal but the cross join may be part of a plan that is good enough or the best plan that was found before the optimizer ran out of moves. The query optimizer assigned a very low cost to the cross join based on row estimates. Joining 414 rows to 1 row in a cross join isn't really a big deal, but it could be a problem if those estimates are inaccurate.

It's also hard to say why this plan is suddenly worse than before, especially with what we observed with the simple NVARCHAR(MAX) testing above. Perhaps there was parameter sniffing and SQL server cached a plan generated from unlucky parameters. It's important to realize that SQL Server doesn't know anything about the values of the local variables in your plan without a RECOMPILE hint. The better query that you posted has hardcoded values which isn't a fair comparison to the other query which required SQL Server to guess or to use a cached plan.


Is a relational database with a dynamic number of tables a good design?

Is this a bad design?

Yes.  See comment by LowlyDBA.

Is there a limit to the number of tables in a database in RDBMS ...

Each RDBMS has a different maximum number of tables.  There is a Q&A for this is on Stack Overflow.

Should I use any different datastore to achieve this other than RDBMS?

No. RDBMS should be fine if you design your schema correctly.

Any suggestions?

From your description, a single table with Entity_Name as part of the Primary Key should work. A table with 100M rows is not a problem for today's databases.
create table data_for_entity (
  entity_name     varchar(50),
  entity_id       int,
  entity_value    text, -- or whatever PostgreSQL uses for JSON
  constraint data_for_entity_pk Primary Key (entity_name, entity_id)
);


Is there anyway to search PL/SQL reserved words with contains function?

You can't use contains because there is no Oracle Text index on the database source.  You could, I suppose, write a query that copied the data from dba_source to a custom table, create an Oracle Text index on that table, and search that table using the contains function.

It would generally make more sense, though, to just query dba_source with a like query

SELECT *
  FROM dba_source
 WHERE lower( text ) like '%for %';


sp_BlitzFirst: blocking_session_id not displayed in list of session_ids

It's most likely that the blocking SPID is sleeping. If you run sp_BlitzFirst again, look in the second section down, and you may see warnings for sleeping sessions with open transactions.

By default sp_BlitzWho filters out sleeping SPIDs. We did recently add a parameter to show them, but we haven't bubbled it up to sp_BlitzFirst yet.

I'm on my phone so I can't post screen caps at the moment. Leave a comment if you need more information.

Thanks!


Why I cannot execute this auto generated script with mysql workbench

Your dump is somewhat weird, but your "repaired" part seems good (only missing one space, I suppose typo in question) - did it give different error? Column types do not match:

`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT NULL,

One is UNSIGNED and the other is not, that is one problem. Make them both either signed or unsigned (usually unsigned is the right one for autoincrement)

I just added missing space and some backticks to be safe:

CONSTRAINT `user_id` FOREIGN KEY (`user_id`)
    REFERENCES `motivian`.`user`(`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION);

Check it at http://sqlfiddle.com/#!9/9dca1/1


How to Search values Integer column alias in a WHERE Clause

You cannot use an alias in your where clause

Without questioning any parts of the query, you could do this:

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
FROM BASE_Chq
WHERE (IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') = 'PARTY');

A better approach would be directly filtering on the IS NOT NULL:

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
FROM BASE_Chq
WHERE ChequeInvoicePaymentId IS NOT NULL;

A small DB<>Fiddle example

Why

The filter in the where clause can be passed to the table as a (seek) predicate before computing the IIF function in the SELECT.

More info on this processing order in an answer by Erland Sommarskog:

  You can't do that because a SELECT statement is computed in this
  order:

  FROM-JOIN
  WHERE
  GROUP BY
  HAVING
  SELECT
  ORDER BY

  So when the WHERE clause is evaluated, the aliases defined in the
  SELECT list are not defined yet. ...


If my understanding of your question is correct and you are only wanting to return records from tbloffer if there are also records in tblRecord matching those buyer ids, then you should be able to add a clause within your join so that it will only return those records.

I think this will cover it for you.

SELECT  COUNT(DISTINCT r.RecordID) AS Lines
      , COUNT(r.RecordID) AS Offers
      , MONTH(r1.Submitdate) AS M
      , DAY(r1.submitdate) AS D
FROM    tblReq r1
        INNER JOIN tblRecord r
            ON r1.ReqID = r.ReqID
        INNER JOIN tblOffer o
            ON r.RecordID = o.RecordID
            AND r.BuyerID = o.InsertedID
WHERE   r.BuyerID IN (36, 160499)
        AND o.OfferTypeID IN (1, 3)
        AND r1.Submitted = 1
        AND r1.SubmitDate >= '2015-07-01'
GROUP BY MONTH(r1.Submitdate)
      , DAY(r1.submitdate);

Hopefully I am understanding your question correctly and this is able to help you.


How to update group no incremental by 1 based on every group from 1 to 3?

Check if it works for you. Please, notice this code is meant to work with groups containing no more than 3 steps each.
;WITH CTE_Replacement
AS
(
    --Creates the groups by selecting only the rows with step 1
    SELECT PartIDC,
        PartIDX,
        FlagStatus,
        HasReplacement,
        ROW_NUMBER() OVER(ORDER BY PartIDC) AS groupId,
        step
    FROM #Replacement
    WHERE step = 1

    UNION ALL

    --Gets steps 2 that are lelated to the groups with step 1 from the query above
    SELECT R.PartIDC,
        R.PartIDX,
        R.FlagStatus,
        R.HasReplacement,
        CTE.groupId, --Uses the group created before
        R.step
    FROM #Replacement R
        INNER JOIN CTE_Replacement CTE ON R.PartIDC = CTE.PartIDX
    WHERE R.step = 2
)
--Adds the last step of each goup
SELECT R.PartIDC,
        R.PartIDX,
        R.FlagStatus,
        R.HasReplacement,
        CTE.groupId,
        R.step
FROM #Replacement R
    LEFT JOIN CTE_Replacement CTE ON R.PartIDX = CTE.PartIDX
ORDER BY CTE.groupId, CTE.step;


Checking if 2 tables ( on different servers ) have the same exact data

I believe you're looking for the tool tablediff which lets you do exactly that - compare two replicated tables for differences. You might find this article useful, to get  started.

Here is a GUI for tablediff


How to get the oracle dataguard log transfer and applying status?

Oracle Dataguard does not transfer transactions, it transfers log records. Physical standby knows nothing about transactions (logical things), it knows only about changes to the files (physical changes).

From the standby side, you can check v$dataguard_stats view. It gives information for "transport lag" and "apply lag" in terms of time.

From primary and standby you can see  what logs were archived (transmitted) and what were applied to the standby:

select sequence#, to_char(completion_time, 'dd.mm.yyyy hh24:mi:ss') completion_time,
       deleted, applied, dest_id, STANDBY_DEST, fal, round(blocks*block_size/1024/1024) mb
  from v$archived_log
 where completion_time > sysdate - 3/24
 order by sequence#, dest_id;

If you have logical standby, then you can look into v$logstdby_progress, etc;

https://docs.oracle.com/cd/B19306_01/server.102/b14239/manage_ps.htm#CHDFFFAJ
https://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ls.htm#g1057004


Db design for a quiz with variable number of answer choices, and each worth different score in a different category?

Consider a data model like this:

You have a list of quesitons in the QUESTION table.  Each question can have two or more possible options for answers in the OPTION table.  For each option there are one or more category score changes which are noted in OPTION_CATEGORY.  When a USER takes the quiz their answers (i.e. choices of options) are recorded in ANSWER.

Note that with this design you can change someone's score in multiple categories with a single answer (such as in your example of reading WIRED magazine) and you also have the option of changing someone's score in a category by an increment other than 1 (as in your example of not having taken an art class).  You could even change someones score by more than 1, for example in your question 1 you could score +10 in the science category for prefering to read Philosophical Transactions of the Royal Society!


Moving tables to new tablespace needs temp space or any kind of addictional space? (Oracle 11g)

Moving a table does not use the temporary tablespace.

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     64
pga_aggregate_target                 big integer 256M
sga_target                           big integer 1G

SQL> select bytes/1024/1024 as bytes_mb, tablespace_name from user_segments where segment_name = 'SALES2';

  BYTES_MB TABLESPACE_NAME
---------- ------------------------------
      2240 EXAMPLE2

SQL> select file_id from dba_temp_files;

   FILE_ID
----------
         1

SQL> alter database tempfile 1 drop;

Database altered.

SQL> select file_id from dba_temp_files;

no rows selected

SQL> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
191

SQL> select serial# from v$session where sid = 191;

   SERIAL#
----------
       415

SQL> alter table sales2 move tablespace example3;

Table altered.

SQL> select bytes/1024/1024 as bytes_mb, tablespace_name from user_segments where segment_name = 'SALES2';

  BYTES_MB TABLESPACE_NAME
---------- ------------------------------
      2240 EXAMPLE3

SQL> select max(temp_space_allocated), count(*) from v$active_session_history where session_id = 191 and session_serial# = 415;

MAX(TEMP_SPACE_ALLOCATED)   COUNT(*)
------------------------- ----------
                        0         33


SQL Server: Pass table name into table valued function as a parameter

Table-Valued Parameters (TVPs), and T-SQL in general, does not work like that. There is no way to define a partial schema as an interface and then pass in table that matches that interface. For TVPs, you need to pass in a table variable created from that table type. So clearly not the way to go here.

Also, you have said that all of these tables have those two columns, but not that all of these tables have all of the same columns. This is an important distinction because table-valued functions are required to return a consistent result set; the result set cannot be dynamic like it can be with stored procedures. I am mentioning this because your example function does a SELECT * from the TVP, implying that you might be expecting this function to return whatever columns are in whatever table name you pass in. For this function, you will need to return only the two columns that all of the tables have in common: Id and Revision.

That being said, there are two ways you can accomplish this: pure T-SQL and SQLCLR.

PURE T-SQL

The function uses a CASE statement to determine which sub-query to execute. Each sub-query is the same except for the table name (well, in my example the second column name, which is also used in the ORDER BY clause, is different, but in your situation it will always be Revision). Sub-queries can only return a scalar value, so I am using FOR XML to package up the two required columns into one value that can then be unpacked in the outer SELECT.

USE [tempdb];
GO

-- DROP FUNCTION dbo.MaxRevision;
CREATE FUNCTION dbo.MaxRevision(@TableName sysname)
RETURNS TABLE AS
RETURN

SELECT tab.BaseData.value(N'/row[1]/@object_id', N'BIGINT') AS [ObjectID],
       tab.BaseData.value(N'/row[1]/@some_id', N'BIGINT') AS [SomeID]
FROM (
    SELECT CASE @TableName
             WHEN N'objects' THEN (SELECT TOP (1) [object_id], [schema_id] AS [some_id]
                                   FROM   master.sys.tables
                                   ORDER BY [schema_id] DESC FOR XML RAW, TYPE)
             WHEN N'indexes' THEN (SELECT TOP (1) [object_id], [index_id] AS [some_id]
                                   FROM master.sys.indexes
                                   ORDER BY [index_id] DESC FOR XML RAW, TYPE)
             WHEN N'columns' THEN (SELECT TOP (1) [object_id], [column_id] AS [some_id]
                                   FROM master.sys.columns
                                   ORDER BY [column_id] DESC FOR XML RAW, TYPE)
           END AS [BaseData]
     ) tab;
GO

The tests:

SELECT * FROM dbo.MaxRevision(N'objects');

SELECT * FROM dbo.MaxRevision(N'indexes');

SELECT * FROM dbo.MaxRevision(N'columns');

SELECT * FROM dbo.MaxRevision(N'none');
-- 1 row of NULL, NULL

SQLCLR

SQLCLR allows for Dynamic SQL in a TVF, so you can pass in the table name, concatenate it into the query (after you verify that it is, in fact, a table name!), and that's pretty much it.

This option allows for a little more flexibility than the pure T-SQL option, but is also a bit more complicated to implement. Stick with the pure T-SQL option unless you need more flexibility, such as dynamically defining the queries, reading from temporary tables, etc.


System-level way to enforce that reserved words may not be used for Oracle column names?

You could create a system trigger like this:

create or replace trigger no_reserved_words
   BEFORE CREATE OR ALTER ON SCHEMA
DECALRE

  CURSOR Cols IS
  SELECT r.*
  FROM V$RESERVED_WORDS r
      JOIN ALL_TAB_COLUMNS ON keyword = UPPER(column_name)
  where RESERVED = 'Y'
      and owner = ora_dict_obj_owner
      and table_name = ora_dict_obj_name;

BEGIN
   IF ora_dict_obj_type = 'TABLE' THEN
      FOR aCol in Cols LOOP
         RAISE_APPLICATION_ERROR(-20010, aCol.keyword ||' not allowed as column name');
      END LOOP;
   END IF;
END;

BEGIN

However, there are several problems. As user with ADMINISTER DATABASE TRIGGER system privilege (e.g. DBA) ignores the RAISE_APPLICATION_ERROR, it he can do it anyway.

Another problem is: What so you consider as "reserved word"? Check this example:

SELECT *
FROM V$RESERVED_WORDS r
WHERE keyword IN ('PASSWORD','NAME', 'DATE', 'SYSDATE')

+-------------------------------------------------------------+
|KEYWORD |LENGTH|RESERVED|RES_TYPE|RES_ATTR|RES_SEMI|DUPLICATE|
+-------------------------------------------------------------+
|NAME    |4     |N       |N       |N       |N       |N        |
|SYSDATE |7     |N       |N       |N       |Y       |N        |
|PASSWORD|8     |N       |N       |N       |N       |N        |
|DATE    |4     |Y       |N       |N       |N       |N        |
+-------------------------------------------------------------+

You see DATE is clearly reserved, but PASSWORD has the same attributes like NAME which should be no problem, I assume.


Can't load local file into mysql database

Watch carefully, everything is up my sleeve.

mysql> SELECT 'abc
    '>
    '>
    '> def';
+-----------+
| abc

def |
+-----------+
| abc

def |
+-----------+
1 row in set (0.00 sec)

Notice that my first line has a quote, but not the matching quote.  When I hit return; it prompted me with '> -- That's the clue that I need to balance the quotes.  Eventually, I did so, and the query terminated.

Look around, I suspect there is a stray quote on the preceding or following line.  Or the quotes around the filename aren't really the same.


SSMS 2014 does not launch Graphical Execution Plan from clicking hyper-linked query plan - ShowPlanXML

You need to ensure the System Registry contains the correct file-extension-mapping for .sqlplan files.

Open the Registry Editor, navigate to the following key:

Computer\HKEY_CLASSES_ROOT\.SQLPlan

Confirm the (Default) value contains something like:

ssms.SQLPlan.14.0

(the version number might be different for SSMS 2014).

This is a screenshot of Registry Editor showing the key and value:

You'll also need to ensure the following value is set correctly:

When I say "correctly", I mean appropriately for your system.  If this approach doesn't work, I'd try simply re-installing SSMS 2014.  Of course, I'd actually recommend installing the latest version of SSMS, v18, which is available for free directly from Microsoft.


How to repair database consistency errors

I would like to know if there is a way to fix this

These consistency errors may be fixable with the REPAIR_REBUILD option of DBCC CHECKDB:

  Performs repairs that have no possibility of data loss. This can
  include quick repairs, such as repairing missing rows in non-clustered
  indexes, and more time-consuming repairs, such as rebuilding an index.

As Shanky's answer mentions, any DBCC repair should also be performed inside a transaction, so you can inspect the changes before committing to them.

As always, please ensure you have a completely recoverable set of backups (including the log tail if applicable) before running the rebuild. If you have a complete set of valid backups (including the log tail as applicable) and you can afford the downtime, restoring might be the preferred option. Be sure not to overwrite the current database if you do this, just in case the restore fails, or it is not as complete as you expected. Of course, it's quite likely the restored database would contain the corruption again, depending on how and when it occurred :)

  or a way to get a more detailed information about this errors

Details of the four consistency errors are in the DBCC CHECKDB output, before the summary section at the end. You should review these to ensure you understand the problem, and what may have caused it, before attempting any repair.

You can reduce the amount of DBCC CHECKDB output using the WITH NO_INFOMSGS option.

Add the DBCC error message details to your question if you need help analyzing the errors. It is important to identify and correct any underlying hardware problem that might have caused the corruption.

Depending on the details of the corruption, there may be other ways to fix the problems (such as manually rebuilding a nonclustered index).

If the repair or rebuild is successful, you will need to check the database again with DBCC CHECKDB with the fullest set of checks supported by your version of SQL Server.


Sometimes ago, I faced a similar issue and I solved that issue with the help of my friend. How I did was I changed the collation to uft8_turkish_ci in MySQL. Also from MySQL website, I found the following document, which may be helpful for you,

MySQL Character set

Thanks.


MySQL rows in explain extended keeps increasing while the rows are being deleted continuosly

Is this the real query?
select count(*) from JobTable where userid="1" and status="ready"

If so, here's how it works when you have INDEX(userid, status) or INDEX(status, userid)

It drills down the index's BTree to the first entry with that pair of values.
Count 1.
Scan forward until the pair no longer matches (else back to step 2).

The only thing that can change is the details of the BTree.  INSERTs and DELETEs may lead to block splits and block merges.
Don't trust the numbers in EXPLAIN.
In this case, there is no benefit in using FORCE/USE INDEX; the index to use is too obvious.  (Or do you have a case where it used some other index?  If so, let's see SHOW CREATE TABLE.)
While we are at it, neither ordering in the INDEX is better.  That is, the cardinality of each of the two columns does not matter.
ANALYZE TABLE JobTable will refresh the statistics; it is fast, but imprecise, for InnoDB.


You are making unwarranted assumptions here.  You don't need an ANALYZE for a CTE, a CTE will use the stats of the base table(s), not its own stats.

However, before version 12, CTEs are optimization "fences". The CTE will always run to completion almost as if it were an entirely separate query.


Back-end and front-end are emerging separations of concerns in development, typically these days "web development". In some shops you have two types of developers, often on separate teams. These different groups of developers often specialize (intentionally or not) and this specialization creates a pretty useful mental model,

Back-end developers are responsible for things not pertaining to the user-interface. This is usually half the work. Their development stops at some fairly arbitrary hand-off point.
Traditionally back-end developers were the only kind of developers because the user-interface was massively simpler and the pool of people that had a complete understanding ("full stack" in modern parlance) was the norm. As the user-interface became more complex and a litany of vocational schools and camps emerged training people for a specific tract of programming there was a need to place those from this pigeonholed and often spoon-fed pool of the workforce. The work load was removed from back-end developers, and the front-end developer was born. Back-end developers today are likely to be skilled with the database, SQL, Perl, Python, C, C++, and will have a thorough understanding of Networking Administration, and System Administration (and may have themselves migrated from when those forms of Administration were more or less employable on their own merits).

Front-end developers are responsible for everything at the hand-off-point forward. Today, professional front-end developers are largely of two camps: Angular and React, previously they were oriented to jQuery, and Backbone like technologies. Today the front-end entails massive libraries, especially Angular, and they take a substantial investment in time to learn, and to stay up to date with. Typical front-end developers are skilled with Angular or React and have extensive knowledge of REST-clients, JavaScript, HTML, CSS, Reactive design, Translation and i18n, Fluid design (mobile vs desktop) and UX concepts.

Some languages straddle both camps pretty well and you'll find a lot of cross over, like Java/Dalvik on Android which creates pretty well rounded programmers, and also Node.JS which permits JavaScript front-end programmers to transition to and from the backend.
As far Database Administrators are concerned, you're likely a Back-End developer if you're here. There are databases (often hosted solutions) that cater to front-end developers which lack a firm understanding of how a database works. One such example is Google Firebase, but shy of being employed at Google you're likely not a Database Administrator in any useful sense for merely using the hosted Firebase service.


I'm pretty sure the problem is the "partial" index:

CREATE TABLE `smalltable` (
  ...
  KEY `ix_name` (`name`(10))
)

Try running the query after adding an index on the full length of the column:

ALTER TABLE smalltable
    DROP INDEX ix_name
  , ADD INDEX ix_name_full (name) ;


All a primary key is is a value that we have determined is the value that is of utmost importance in a record. Whether that key is a signed int, an unsigned int, a string, a blob (actually, there are limits) or a UUID (or whatever name it takes today), the fact still stands that it is a key, and that it is the thing of utmost importance.

Since we're not constrained to use only positive oriented numbers for our keys, it makes sense to consider that a signed int will only go to ~2 billion, whereas an unsigned int will go to ~4 billion. But there's nothing wrong with using a signed int, setting the initial value to ~ -2 billion and setting an increment of one. After ~2 billion records you'll hit "zero" and then you'll continue to ~2 billion.

As to why it would be helpful to have "negative keys" in a table, that's the same question as "why is it helpful to have keys in a table". The "value" of a key has no impact on its status as a key. A key is a key is a key.

What is important is if the key is valid.

As to why it would be useful to allow keys that were negative, I can suggest some reasons:

What if you wanted to indicate returns in a sales system as negative sales order numbers, that matched the positive sales order number, thus making correlation easy (this is naive, and poorly designed, but it would work in a "spreadsheet" sense).

What if you wanted to have a users table, and indicate that the ones with negative numbers were system controlled (SO does this very thing, for chat feed users).

I could go on, but really the only reason why the number being negative is of importance is if you or I assign importance to it. Aside from that, there is no great reason for the value of a key to have any bearing on the key itself.


Does MySQL lock full table in case of update query on specific rows?

Locking 3.6M rows, especially when it is 90% of the table, has multiple burdens on the system.  It may "feel like" the entire table is locked.

This sounds like a 1-time fix; perhaps you should simply wait for it to finish -- which could take many minutes.

If you need a better, but more complex, solution, do the UPDATE in chunks of 100-1000 rows.  More details:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks


Fill percentage of total field with MS Access SQL

You need UPDATE, not INSERT:

UPDATE MyTable
SET F = f / (SELECT SUM(f) AS fSum FROM MyTable) ;

or:

UPDATE
    MyTable AS my,
    (SELECT SUM(f) AS fSum FROM MyTable) AS total
SET my.F = my.f / total.fSum ;

As mendosi commented, having two columns with same name in different (upper/lower) case, like f and F is not recommended in any DBMS and (not sure) probably not even allowed in Access.

The above statements however do not work in version 2013. I think they worked in old (1997) version but some change in the Jet engine (3.5? 4.0?) made the queries throw error instead.

Using the DSUM() function instead, to do the summation, works:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable") ;


Creating a database for a scenario involving students, teachers and courses

"Roles" has a different meaning.
A bridging ('mapping') table is how specify a 'relationship' between two tables that is "many-to-many".  Example:  Students:Classes.
1-many works differently.  It needs a column in one table to link to the other.  Example:  Worker:Manager

In designing an "Entity-Relationship" ("ER") schema do something like this:

One table per "entity":  Student, Teacher, Course, Class.  (This assumes there are no 'student teachers'.)  Each entity table would have numerous columns for attributes such as name (most tables), date (for class),
Establish the relations between them.  Many:many:  Student:Class, Teacher:Class; 1:many: Class:Course

Building a list of students as 101,103,... is best done via a JOIN and GROUP BY and GROUP_CONCAT(), not by having a column with such a list.

Since a "class" might multiple times a week need to be another table session, and 1:many for class:session.


How to make slave is in sync with master in tungsten replication?

You can try mk-slave-prefatch

Slave prefetch is an increasingly popular technique for speeding up native MySQL replication, with several tools already published to enable it, such as mk-slave-prefetch and Replication Booster.  Tungsten Replicator is now joining the fray.   This article explains how our implementation works, how to install and tune it, and how well it performs compared to unaided MySQL native replication as well as Tungsten parallel replication.

For more information on this please refer:
http://scale-out-blog.blogspot.in/2012/02/solving-replication-lag-with-tungsten.html


SQLSERVER FullText search troubleshooting

Whilst trying to re-create the problem with a fresh table in a separate DB, I ended up comparing the Full-Text catalogue settings. In the DB where I saw the erroneous behaviour the Table Full-Text Pending Changes value was 21219 (!).

I have then checked the FT log and found that the Asset table hasn't been updated for a long time:

  An internal query to load data for a crawl on database '-' and table
  'Asset' failed with error code 1205. Check the sql error code for more
  information about the condition causing this failure. The crawl needs
  to be restarted after this condition is removed.

and also

  Error: 30059, Severity: 16, State: 1
  A fatal error occurred during a full-text population and caused the population to be cancelled.
  Population type is: AUTO; database name is Media (id: 6); catalog name
  is - (id: 6); table name Asset (id: 1076966963). Fix
  the errors that are logged in the full-text crawl log. Then, resume
  the population. The basic Transact-SQL syntax for this is: ALTER
  FULLTEXT INDEX ON table_name RESUME POPULATION."

I had to manually resume the FT updates. After that the search has returned 3 items!


Faster query with pattern-matching on multiple text fields

For your kind of pattern matching you best use a trigram index. Read this first:

How is LIKE implemented?

I assume there's a typo in your expression (first_name || '' || last_name), which makes no sense with an empty string, and you really want (first_name || ' ' || last_name) - with a space character.

Assuming that either column can be NULL, you would need NULL-safe concatenation, the simple solution is concat_ws():

Combine two columns and add into one new column

But that function is not IMMUTABLE (explanation in the linked answer), so you cannot use it directly in an index expression. You could use an IMMUTABLE function wrapper:

CREATE OR REPLACE FUNCTION f_immutable_concat_ws(s text, t1 text, t2 text)
  RETURNS text AS
$func$
SELECT concat_ws(s, t1, t2)
$func$ LANGUAGE sql IMMUTABLE;

The wrapper can be IMMUTABLE because it only takes text parameters.
Either way, this is more verbose but has less internal overhead and is considerably faster:

CREATE OR REPLACE FUNCTION f_immutable_concat_ws1(s text, t1 text, t2 text)
  RETURNS text AS
$func$
SELECT CASE
         WHEN t1 IS NULL THEN t2
         WHEN t2 IS NULL THEN t1
         ELSE t1 || s || t2
       END
$func$ LANGUAGE sql IMMUTABLE;

Or, with hard-coded space character:

CREATE OR REPLACE FUNCTION f_concat_space(t1 text, t2 text)
  RETURNS text AS
$func$
SELECT CASE
         WHEN t1 IS NULL THEN t2
         WHEN t2 IS NULL THEN t1
         ELSE t1 || ' ' || t2
       END
$func$ LANGUAGE sql IMMUTABLE;

Basing the index on this function, I suggest:

CREATE INDEX people_special_gin_trgm_idx ON people
USING gin (f_concat_space(first_name, last_name) gin_trgm_ops, email gin_trgm_ops);

I added email as second index column for several considerations.

Creating the index will take a while for 20M rows, best not during top load, or maybe use CREATE INDEX CONCURRENTLY .... A GIN index is considerably bigger than a plain btree index and also more expensive to maintain. Be sure to run the latest version of Postgres, there have been major improvements for GIN indexes in recent versions.

Then your slightly adapted and simplified query should be fast and correct:

SELECT *
FROM   people
WHERE  f_concat_space(first_name, last_name) ILIKE '%bat%man%' OR
       email ILIKE '%bat%man%'
LIMIT  25;

You only need the one index for this query.

Basics for pattern matching:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
How to create an index to speed up an aggregate LIKE query on an expression?


Log Shipping Backup grey in Transaction Log Shipping Status

Currently the backup,copy and restore works fine in my server just
  backup status not show at report transaction log shipping.

If backup, copy and restore is working fine then its objectivity is obtained and one site to another site is in sync however in case you are unable to see only the backup status, you may check this at individual job level by clicking on job history at primary server and see whether it is successful or has completed with error.

Alternatively, you may use script to check the current status of log-shipping at primary and secondary server:

--Primary

select * from msdb..log_shipping_monitor_primary

--Secondary

select * from msdb..log_shipping_monitor_secondary

Primary will give you information about backup whereas secondary will have details on both the jobs i.e. copy as well as restoration.

You may try below stored procedure also which will return result however un-formatted:

use master
go
exec sys.sp_help_log_shipping_monitor
go

  The scripts work fine but the primary database not show in secondary
  server log shipping report if I select
  [msdb].[dbo].log_shipping_monitor_primary in secondary server my
  primary database not insert at that table.

This is supposed to return rows at primary server and not at secondary server, it seems you are mistake about this table.

Procedure sp_help_log_shipping_monitor can be run on both the nodes i.e. primary and secondary and will return result for backup on primary whereas on secondary this would give details about copy and restore.

Hope above helps.


Restoring a Database in an Availability Group

Yes, you need to populate databases on secondary nodes in an availability group yourself, they do not appear "automatically".  The full documentation is found on MSDN, with multiple ways to execute.  The short version is:

Your primary database needs to be in FULL recovery mode.
You must restore the database in NORECOVERY mode on the secondary node. Logs must be applied so that the secondary database is as close to the current transaction state of the primary as possible.
Once you have done this, you need to join the secondary database to the availability group: ALTER DATABASE [foo] SET HADR AVAILABILITY GROUP = [bar];


Please try it.

SELECT f.leaderId, f.followerId, c.ccId, c.ccTitle, c.ccCity, c.ccCountry, c.ccFlag, c.uId, c.ccStartingDate, c.ccCreatedAt, l.uId, l.ccId
        FROM  conferencecreate c
left join followingrelations f on c.uId = f.leaderId
left join likesbyuser l on  (c.ccStartingDate >= 'current_time_and_date_now' or l.uId = f.leaderId )
        WHERE f.followerId = 'login_user_id'
        AND c.ccFlag = 1
        GROUP BY c.ccTitle
        ORDER BY c.ccId DESC


How can can I get object definitions stripped of all comments?

You could do this using T-SQL and various string functions, but you will need to account for comments using /**/ and --.

In other words, there is no easy way to get back a stored proc definition stripped of all comments. You would essentially need to build your own parsing tool.


Permanent count of waiting tasks (2) in Activity monitor

Apart from Resources waits tab i believe you should expand the tab above which says processes.The Processes pane shows the information about the currently running processes on the SQL databases, who runs them, and from which application.

Also, you can simultaneously use below script to find the info on whats running and causing waits thereby:

SELECT
      [spid] = session_Id
    , ecid
    , [blockedBy] = blocking_session_id
    , [database] = DB_NAME(sp.dbid)
    , [user] = nt_username
    , [status] = er.status
    , [wait] = wait_type
    , [current stmt] =
        SUBSTRING (
            qt.text,
            er.statement_start_offset/2,
            (CASE
                WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt.text)
                ELSE er.statement_end_offset
            END - er.statement_start_offset)/2)
    ,[current batch] = qt.text
    , reads
    , logical_reads
    , cpu
    , [time elapsed (ms)] = DATEDIFF(mi, start_time,getdate())
    , program = program_name
    , hostname
    --, nt_domain
    , start_time
    , qt.objectid
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              -- Ignore system spids.
AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
ORDER BY 1, 2
GO


You must be operating in some restricted mode

The MySQL Documentation for ALTER USER says the following:

  Password expiration for an account affects the corresponding row of the mysql.user system table: The server sets the password_expired column to 'Y'.

  A client session operates in restricted mode if the account password has been expired. In restricted mode, operations performed within the session result in an error until the user establishes a new account password:

mysql> SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> SET PASSWORD = PASSWORD('new_password');
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

SUGGESTION : Try the aforementioned code

mysql> SET PASSWORD = PASSWORD('new_password');

If this does not works due to old_passwords, do the hash algorithm manually

mysql> SET PASSWORD = CONCAT('*',UPPER(SHA1(UNHEX(SHA1('new_password')))));

I learned about this hash algorithm from PalominoDB (last bought by Pythian).


pgpool-II configuring multiple master databases

OK your first decision is WHERE to split the silos out, in the application layer, or in the db layer. If your application will keep track of the silos then you can run individual pgbouncer or pgpool servers on different ports for each silo.

If you want the application to not have to know or deal with how siloing is done, then something like plproxy can work wonders. Basically you have 1 or more plproxy servers that distribute all the queries / data based on some pre-determined modulo math or some other method for breaking it up, and your app just calls the plproxy server with it's queries and the plproxy server is where the logic for siloing exists.

Each method has it's pluses and minuses. The nice thing about it being in the app layer is that your database layer is fairly simple. just a bunch of master/slave pairs. If you add a new silo you just edit the config file for it in the app and it goes online. The disadvantage is that all apps hitting your data HAVE to be silo aware etc.

With plproxy the advantage is that the app never knows its siloed. If you write a fairly simple SOAP type interface to talk to the plproxy servers then every app your write can use that single interface and never have to be told which silo does what. Now though the complexity is moved into the db layer. Any changes to the farm structure require work on the db level. Also plproxy works by basically wrapping all your database work into functions ahead of time. If you want to select data from a table joined to another table you need to create a function to do that and call that function on the plproxy machine. It seems overly complex at first but actually it's not that hard, just quite different from slinging random SQL at your db servers.


Reasons for db2 foreign key error (-667) despite all rows appearing sane?

The DB2 optimizer relies on foreign key constraints, which by default are enabled for optimization even though they may not be enforced. The optimizer may choose not to read the rows that should not exist according to the constraint.

Try disabling query optimization on the RI constraint:

ALTER TABLE xyz ALTER FOREIGN KEY fkname DISABLE QUERY OPTIMIZATION


An Account belongsTo a Person. A person can exist without an account.

If a Person hasOne Account, then Account's primary key should also be a foreign key to People.

create table people (
  person_id int primary key,
  meta1 text,
  meta2 text
);

create table accounts (
  person_id int primary key references people(person_id) on delete cascade,
  meta1 text
);


Historically, I'd shy away from storing files in the database and instead include pointers like in your alternative 3.  With SQL Server 2012, however, you might look into the FileTable feature.  The files are still stored within SQL Server (meaning that you'd have to be aware of the implications on your backup strategy), but also are easily accessible through the standard Windows API so you can manipulate them in Windows Explorer or PowerShell.  If you actually need to search within the text files as well, FileTable can do that.

On the other hand, storing 25-30 "extra" TB of data within a SQL Server database will be a challenge to maintain.  If this is just for compliance/auditing purposes (i.e., the probability of somebody actually looking at a particular file is low) and files aren't inserted very often, I'd probably stick with the pointers-to-files approach.  But if you need access frequently or there are a lot of changes, the advantage goes to FileTable because it takes care of the pointer-handling part of the process.


query SUM function issue/ grouping issue

Use your main query as a subquery:

SELECT EmployeeNumber,
       JobCode,
       [Date],
       SUM(Hours) as [Hours]
FROM
(
SELECT        tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
FROM          AdjustUserRoleRequestStatusLookup
CROSS JOIN    tServiceJobCodes
INNER JOIN    tServiceHours
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode
INNER JOIN    tServiceReports
ON            tServiceHours.ReportNo = tServiceReports.ReportNo
INNER JOIN    tServiceReps
ON            tservicereports.employee = tservicereps.repid
INNER JOIN    tRegions
ON            tServiceReports.Region = tRegions.RegionCode
where         Date between '2017-10-03' and '2017-10-03'
and           EmployeeNumber = '10245'
GROUP BY      tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
) ctHours
GROUP BY EmployeeNumber, JobCode, [Date];


The NLS parameters can be quite tedious to understand and I recommend searching for the term "Setting Up a Globalization Support Environment" in the official documentation.

NLS is short for National Language Support and is the beginning of multiple parameters that are available in the various Oracle products.

NLS_LANG

NLS_LANG is described as a variable that defines the <LANGUAGE>_<TERRITORY>.<CHARACTER_SET> of your client environment.

An example of a valid NLS_LANG setting would be:

NLS_LANG=AMERCICAN_AMERICA.US7ASCII

Language

The individual parts of the variable define how Oracle displays information. If your <LANGUAGE> is AMERICAN then messages, days and months will be displayed using American spelling. Sorting is also affected by this part of the parameter

Territory

Territory affects things like the default date, settings for money values (e.g. $ sign), and number formats (e.g. 3,000,000.12 to display three million point 12)

Character_Set

This is the bit where it becomes pretty interesting. This part of the NLS_LANG parameter is described as the

  ...character set used by the client application.

Summary NLS_LANG

So in essence you are configuring how you client would display data stored in the database. If your database contains the following data:

 Employee     | Employment Start    | Salary
--------------+---------------------+--------
 Michael Ward | 2018-01-01          | 100240

...then if your client has AMERICAN_AMERICA.US7ASCI as the NLS_LANG setting, then querying this table would return:

 Employee     | Employment Start     | Salary
--------------+----------------------+-------------
 Michael Ward | 01-JAN-18            | 100,240

...whereas having a setting of FRENCH_FRANCE.WE8ISO8859P1 would result in:

 Employee     | Employment Start     | Salary
--------------+----------------------+-------------
 Michael Ward | 01/01/18             | 100'240

Lets follow up with the other parameter...

NLS_CHARACTERSET

The NLS_CHARACTERSET parameter is setup during the creation of your database instance and shouldn't be changed thereafter. (It can be altered but it can cause data corruption even in the DATA DICTIONARY objects). This parameter defines the encoding of the data in the ...

NLS_CHARACTERSET Parameter

... CHAR, VARCHAR2, LONG and CLOB columns of a table.

NLS_NCHAR_CHARACTERSET Parameter

... NCHAR, NVARCHAR2 and NCLOB columns of a table.

Summary NLS_CHARACTERSET

The parameter is used to tell the RDBMS system with which encoding it shold store data in certain columns.

I have intentionally left a large portion of the explanation regarding NLS_CHARACTERSET out, because it gets really complicated when comparing encodings between AL8UTF8, AL32UTF8 and AL16UTF16 (which can only be use in conjunction with the NLS_NCHAR_CHARACTERSET parameter) and all the other possible Unicode settings.

Comparison

When retrieving data from a database column NLS_LANG will add all the bells and whistles (date formatting, time formatting, number formatting) to the data that is being retrieved.

The NLS_CHARACTERSET parameter defines with which encoding the data is stored in the columns. (ASCII, Unicode, ...)

Answering your Question

  Does that mean that the two encodings can be different?

Yes, you can have two totally different encodings. You could store you data using a setting of NLS_CHARACTERSET = AL32UTF8 and still display and transport the data to the database with NLS_LANG=American_America.US7ASCII.

  Display vs. Storage


How to achieve Dynamic SQL Query with Multiple parmeters to handle null values also

There is no harm in having unused parameters passed into sp_executesql.

You approach above is correct.  Declare and pass in all possible parameters for sp_executesql.  You will still get all the benefits of dynamic sql even with unused parameters.