SQLServer - Multiple PIVOT on same column I have a table data like the below with Type Regular/Irregular from January to December.
I need a query to do this data like the below table: January with below divided into two-jRegular & jIrregular. whole the year data using the PIVOT or Cross tab.
    CREATE TABLE [dbo].[Details](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Type] [nvarchar](10) NOT NULL,
        [Header] [nvarchar](50) NOT NULL,
        [SubHeader] [nvarchar](50) NOT NULL,
        [January] [decimal](18, 2) NOT NULL,
        [February] [decimal](18, 0) NOT NULL,
        [March] [decimal](18, 2) NOT NULL,
        [April] [decimal](18, 2) NOT NULL,
        [May] [decimal](18, 2) NOT NULL,
        [June] [decimal](18, 2) NOT NULL,
        [July] [decimal](18, 2) NOT NULL ) ON [PRIMARY]

        GO SET IDENTITY_INSERT [dbo].[Details] ON

        GO INSERT [dbo].[Details] ([ID], [Type], [Header], [SubHeader],
    [January], [February], [March], [April], [May], [June], [July])
    VALUES (2, N'Regular', N'Item-1', N'A.Sub-Item1',
            CAST(64.92 AS Decimal(18, 2)), CAST(57 AS Decimal(18, 0)),
            CAST(89.67 AS Decimal(18, 2)),
    CAST(99.20 AS Decimal(18, 2)), CAST(76.34 AS Decimal(18, 2)),
    CAST(23.33 AS Decimal(18, 2)), CAST(32.23 AS Decimal(18, 2)))
    GO

    INSERT [dbo].[Details] ([ID], [Type], [Header], [SubHeader],
    [January], [February], [March], [April], [May], [June], [July])
    VALUES (3, N'Irregular', N'Item-1', N'A.Sub-Item1',
   CAST(43.45 AS Decimal(18, 2)), CAST(65 AS Decimal(18, 0)),
   CAST(65.88 AS Decimal(18, 2)),
    CAST(56.77 AS Decimal(18, 2)), CAST(45.65 AS Decimal(18, 2)),
    CAST(56.87 AS Decimal(18, 2)), CAST(67.55 AS Decimal(18, 2)))
    GO

    SET IDENTITY_INSERT [dbo].[Details] OFF
    GO

    SELECT Header, SubHeader,
      Regular, Irregular,
      FRegular, FIrregular,
      MrRegular, MrIrregular
      FROM (SELECT Header, SubHeader, Type,
           'F'+Type AS FType,
           'Mr'+Type as MType,
           'Ap'+Type AType,
           'Ma'+Type as May,
      [January], [February], [March]
      from [CashFlow].[dbo].[Details] ) p

      PIVOT (MAX(January) FOR Type in (Regular, Irregular)) AS JPVT
      PIVOT (MAX([February]) FOR FType in (FRegular, FIrregular)) AS FPVT
      PIVOT (MAX(March) FOR MType in (MrRegular, MrIrregular)) AS MrPVT

Any one can help me.

Group by Header and Subheader and take the sum like below
SELECT Header, SubHeader,
  Sum(Regular), Sum(Irregular),
  Sum(FRegular), Sum(FIrregular),
  Sum(MrRegular), Sum(MrIrregular)
  FROM (SELECT Header,
               SubHeader,
               Type, 'F'+Type AS FType,
               'Mr'+Type as MType,
               'Ap'+Type AType,
               'Ma'+Type as May,
               January], [February], [March]
  from [CashFlow].[dbo].[Details] ) p

  PIVOT (MAX(January) FOR Type in (Regular, Irregular)) AS JPVT
  PIVOT (MAX([February]) FOR FType in (FRegular, FIrregular)) AS FPVT
  PIVOT (MAX(March) FOR MType in (MrRegular, MrIrregular)) AS MrPVT
Group by Header, SubHeader


Will update occur even if the join fails? I'm trying to debug some issues in an application and stumbled upon the following Query:

SET o.[IsProcessed] = 1,
    o.[Status] = f.[Flag],
    o.[IsProcessedTime] = GETDATE()
FROM [Customs].[dbo].[Out] o
INNER JOIN [Storage].[dbo].[Facts] AS f
ON o.Msgd = f.MsgID
WHERE o.IsProcessed = 0

Question, will this Query still update the Out table with the 1 and GETDATE() values even if the join fails due to missing corresponding data in the table f?

No, if nothing is found in the join, nothing will be updated.


Could not continue scan with NOLOCK due to data movement SQL Server 2005 I am working on a sql database and it got corrupted due to power failure while I am doing some data entry on the system.

I set my database to emergency mode so that I could get some data out of it before dumping the database but I keep getting the error:

  Could not continue with NOLOCK due to data movement

I set the database to offline mode and now when i try to turn it online.It is giving me this error

Set offline failed for Database 'xyz'.

The log scan number (1224:41:1) passed to log scan in database 'xyz' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
An error occurred during recovery, preventing the database 'xyz' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Database 'xyz' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9003)

The database is corrupt.  Setting it to offline sealed your fate.

If it's mission-critical data that you need to recover, contact Microsoft Support.  It's going to cost $500, but it's worth a shot if you need that data.

Otherwise, restore from backup.


Can I have more than one relationship between two tables? I want to make database for report maintenance.

I have 3 tables:

employees
header_maintenance_report
detail_maintenance_report

employees table I have employee_id and then in the header_maintenance_reporttable I use the employee_id ( Foreign Key ) as report maintenance maker.

Can I use another employee_id in the detail_maintenance_report table?

I use employee id in detail_maintenance_report table as team maintenance from report maintenance maker.

Every maintenance requires a lot of employees so I use detail_maintenance_report.

Yes. You have two different relationships here.

A one-to-one relationship between employees and header_maintenance_report via employee_id on each table.
A many-to-many relationship between employees and header_maintenance_report via the keys on detail_maintenance_report.

So long as you know what these two relationships represent (e.g. report author and report viewers) then there's no problem with that, however... I'd suggest you think carefully about naming. From your wording it's not clear exactly what the relationships represent but you might want to think twice about the use of "header" and "detail" in the table names as it sounds like the "detail" table is actually a joining table between the other two.

When using many-to-many relationships a common approach is to use the names of the two tables you're joining as the name for the join table, e.g.

employees
maintenance_reports
employees_to_maintenance_reports

Edit, see this example: http://sqlfiddle.com/#!9/03743/1


Transforming multi column table to different structure table I am a software developer who got data in the following format:

TABLE [dbo].[SensorValues](
    [timestamp] [datetime] NOT NULL,
    [Sensor1] [float] NULL,
    [Sensor2] [float] NULL,
    [Sensor3] [float] NULL
)

I would like to transform it to another table, corresponds to the scheme my application expect:

TABLE [dbo].[SensorValuesNormalized](
    [Id] [smallint] NOT NULL,
    [TimeStamp] [datetime2](3) NOT NULL,
    [Value] [real] NOT NULL
)

Id is the number of the column in the original table, the X column will be transform with X as an id.

I can use C# / Python to go over each line on the source table, and generate rows for Sensor1, Sensor2 and Sensor3.

I wonder if there is an SQL Query I can use to transform data from the first table to the second while retaining the data ?

e.g.

[timestamp] [Sensor1] [Sensor2] [Sensor3]

2012-10-17 05:47:40.387 | 5.0 | 7.3 | NULL

I would like the output to be:

[Id] [TimeStamp] [Value]

1    2012-10-17 05:47:40.387  5.0
2    2012-10-17 05:47:40.387  7.3

EDIT: I have more than 200 sensors, timestamp is unique in the original table, and not unique in the destination.

As you have a large number of sensors, I would probably create a mapping table.  Here's a fuller demo using UNPIVOT, eg

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.Sensors') IS NOT NULL DROP TABLE dbo.Sensors
IF OBJECT_ID('dbo.SensorValues') IS NOT NULL DROP TABLE dbo.SensorValues
IF OBJECT_ID('dbo.SensorValuesNormalized') IS NOT NULL DROP TABLE dbo.SensorValuesNormalized
GO

CREATE TABLE dbo.Sensors(
    sensorId    SMALLINT PRIMARY KEY,
    sensorName  VARCHAR(20) UNIQUE NOT NULL,
    dateAdded   DATETIME DEFAULT GETDATE()
)
GO

CREATE TABLE dbo.SensorValues(
    [timestamp] DATETIME PRIMARY KEY,
    Sensor1     FLOAT NULL,
    Sensor2     FLOAT NULL,
    Sensor3     FLOAT NULL
)
GO

CREATE TABLE dbo.SensorValuesNormalized(
    Id          SMALLINT NOT NULL,
    [timestamp] DATETIME2(3) NOT NULL,
    Value       FLOAT NOT NULL,

    PRIMARY KEY ( [timestamp], id )
)
GO

-- Populate sensor mapping table
;WITH cte AS (
SELECT TOP 250 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.sensors ( sensorId, sensorName )
SELECT rn, 'Sensor' + CAST( rn AS VARCHAR(5) )
FROM cte
GO

-- Create some dummy sensor data for testing
;WITH cte AS (
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.sensorValues ( [timestamp],  Sensor1, Sensor2, Sensor3 )
SELECT DATEADD( second, rn, '1 Sep 2014' ), RAND() * rn, RAND() * rn * rn, RAND() * rn * rn * rn
FROM cte
GO

-- Remove a few nulls
UPDATE TOP(100) dbo.sensorValues
SET Sensor3 = NULL

UPDATE TOP(10) dbo.sensorValues
SET Sensor2 = NULL

INSERT INTO dbo.SensorValuesNormalized( id, [timestamp], value )
SELECT s.sensorId, x.[timestamp], x.value
FROM
    (
    SELECT
        sourceColumn, [timestamp], value
    FROM SensorValues sv
    UNPIVOT ( value for sourceColumn In ( Sensor1, Sensor2, Sensor3 ) ) upvt
    ) x
    INNER JOIN dbo.Sensors s ON x.sourceColumn = s.sensorName
GO

SELECT *
FROM sensorValues
WHERE [timestamp] = '2014-09-01 00:00:01.000'

SELECT * FROM dbo.SensorValuesNormalized
ORDER BY [timestamp], id

NB, the UNPIVOT will remove NULLs.


Unknown Windows SID Mapped to Proxy During SSIS Package Execution I'm running SSIS packages stored in a SQL Server 2014 catalog via scheduled SSA jobs. I'm reviewing my SQL Audit capture and noting calls to SSISDB are coming from the proxy I created when looking at the value for [session_server_principal_name], but the SID showing up in [server_principal_name] doesn't correspond to the Active Directory SID. My proxy has an AD SID prefixed with the typical 'S-1-5...' but the audit is returning a SID with a prefix of 'S-1-9...'. Is that indicative of a contained user for SSISDB dynamically created at job runtime to execute the SSIS task?

Check the blog post I wrote concerning how to identify this S-1-9 SID.

From that post:

  This can happen as a result of using EXECUTE AS USER = 'SomeUser'; to switch context to another user that doesn’t have a server-level account.

I imagine you'd see this exact scenario for contained users.

There is also an MSDN post from 2006 showing this SID.


Deleting all the content from the database We want to clean a database (delete all the records) with around 25 tables and a size of around 650+ GB in production. We are currently using MS SQL 2016 Standard Edition with AlwaysON HA.
Is truncating all the tables the best option, if not what is the best and fastest way to do this?
What is the potential time it is going to take for cleaning approx 650 GB of data?

Use DBCC CLONEDATABASE to generate a new copy without data, drop the original database an rename the generated clone.


Postgres: change database name without interruption? I have a production database that I'm told is "named incorrectly". Is there a way to change the name without interruption?

The database in question is named something like prod_api_db and should be named api_db.

I was hoping there would be a way to create an "alias" so to speak, such that both names are valid and point to the same database for a time period while all of the app configurations are updated with the new name. Once everything's updated, the old name could be removed.

 -- Stage 1, create alias "api_db":

apps ==> prod_api_db
                    > actual_database
              api_db

-- Stage 2, Update app configs:

    prod_api_db
               > actual_database
apps ==> api_db

-- Stage 3, Remove old name:

apps ==> api_db > actual_database

Does that sort of functionality exist in postgres?

Just for background, this is postgres 11. I do have dev, qa, and production servers to work with, so I can test before apply a change.

Pardon me if I'm being very ignorant about this. I'm not a DBA... just an SRE working for a small company who's been given charge of some database, and I haven't found a good answer yet.

No, a database can have only a single name.

You will have to disconnect all sessions before you can rename the database.

You will have to update the database name in all connection strings. The latter can be avoided if you use LDAP connection string lookup, but I guess you don't want to run an LDAP server in a small organization.


How do I access the full text of an error in SSIS GUI? I have an Excel task which is providing error text through the GUI, but I don't know how to access the error task without the GUI and the error disappears if I move the cursor from the X

I have the project property Run64BitRuntime set to False (though it is not editable so even if I wanted to, couldn't change it to true)

Design-time errors, warnings, and informational messages show up in the Error List pane, which can be shown by going to View | Error List.


How do I use an OR in SQL Profiler for Column Filters I am setting up a SQL Profiler and I wanted to have it filter on any items that have a duration > 30000 OR CPU > 10000 OR Writes > 1000

It seems like if I put those into the "column filter" area that it is an AND (i.e. I only get items when the duration, cpu and writes are over those limits).

Is there a way to have those be OR so that I get any items that fit any one of those criteria?  I know I could start up three different profilers, each with the specific criteria, but it would be nice to have that all in one window/output.

I am currently on SQL 2008 R2, but hoping there is a general answer to this item.

You can't. SQL Server Profiler is a bit restricted in the way it displays and filters data.

Solution

You would be better off storing the traced data into a separate table on the same server and then querying the data at given intervals. The big benefit being you have the data in the table and can perform any complex SELECT statement you wish. Ensure you don't capture too much data by specifying one or two filter criteria.

You could also consider running the SQL Server Profiler from the command prompt which will reduce the overhead of displaying the data constantly in an interface.

The parameters are:

---------------------------
SQL Server Profiler
---------------------------
Usage: PROFILER.EXE
    [/S<SQL Server instance name>]
    [/A]<Analysis Services instance name>
    [/D<database>]
    [/B<trace table to load>]
    [/E] trusted connection
    [/U<login id>]
    [/P<password>]
    [/F<trace file to load>]
    [/T<template file to load or use when starting a new trace>]
    [/L<locale id>]
    [/M<trace stop time>]
    [/O<trace output file name>]
    [/Z<maximum output file size (MB)>]
    [/R<use rollover files>]

Reference: Profiler (Microsoft Docs)

Steps

You would create a template for your standard profiling and use this in the command-line startup
Start SQL Profiler via command-line referencing your template
Query data stored in table

Alternatives

Seeing as you might be looking at a migration in the near future and because SQL Server Profiler is going out of fashion as mentioned by Microsoft, you could consider bringing your skills up-to-date by looking at Extended Events.

  IMPORTANT!!
  We are announcing the deprecation of SQL Server Profiler for Database Engine Trace Capture and Trace Replay. These features are available in SQL Server 2016 but will be removed in a later version.+
  The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects will also be deprecated.

Reference: SQL Server Profiler (Microsoft Docs)

Extended Events

Extended Events is a light weight performance monitoring system that uses very few performance resources. Albeit with the slight drawback that there was no GUI for SQL Server 2008 (ok, there is an add-on for SSMS, which can be found on codeplex.)

An introduction into Extended Events can be found on Microsoft's site.


SQL Server Plan Guide I have a query that needs a plan guide, but I am having a hard time setting it up.

Query below from the procedure cache...

(@state nvarchar(14),
 @jobName nvarchar(18),
 @jobGroup nvarchar(28),
 @oldState nvarchar(6))

 UPDATE JOB_TRIGGERS
 SET TRIGGER_STATE = @state
 WHERE JOB_NAME = @jobName
 AND JOB_GROUP = @jobGroup
 AND TRIGGER_STATE = @oldState

SQL Server chooses to perform a clustered index scan vs a non clustered index seek. I am having sporadic deadlock issues with this update statement and a certain select statement on the table. I understand why SQL is choosing an clustered index scan on the table....Rows < 100 and PageCount < 25.

The table has a large of amount of activity, and since its a 3rd party product I don't have the ability to modify the query and supply an index hint. The query cost of using the non clustered index is more, but I believe it will improve concurrency based on testing....

I need to tell it to use the non clustered index below

WITH (INDEX (ix_jobname_jobgroup_triggerstate))

Help setting this up would be much appreciated..

Ideally, we would like to use a plan guide to add a TABLE HINT, so the guided query becomes:
UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState
OPTION (TABLE HINT (JOB_TRIGGERS, INDEX(ix_jobname_jobgroup_triggerstate)));

Unfortunately, this is not possible because the UPDATE is written without a FROM clause:

Msg 8724, Level 16, State 1, Line 45
Cannot execute query. Table-valued or OPENROWSET function 'JOB_TRIGGERS' cannot be specified in the TABLE HINT clause.

You can work around this by capturing the XML showplan for the equivalent:
UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
FROM JOB_TRIGGERS WITH (INDEX(ix_jobname_jobgroup_triggerstate))
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState

Note this form of the query has a FROM clause to support the index hint. The query must be written exactly as above, without the usual alias for the target table.
You can then use this XML (without the opening <?xml version="1.0" encoding="utf-16"?>) element as the @hints parameter in sp_create_plan_guide.
Example
Given the table and indexes:
CREATE TABLE dbo.JOB_TRIGGERS
(
    JOB_TRIGGERS_ID integer PRIMARY KEY,
    JOB_NAME nvarchar(18) NOT NULL,
    JOB_GROUP nvarchar(28) NOT NULL,
    TRIGGER_STATE nvarchar(6) NOT NULL,
);

CREATE NONCLUSTERED INDEX
    ix_jobname_jobgroup_triggerstate
ON dbo.JOB_TRIGGERS
    (JOB_NAME, JOB_GROUP, TRIGGER_STATE);

The plan guide (using the captured XML from the index-hinted form above) is:
EXECUTE sys.sp_create_plan_guide
    @name = N'UPDATE JOB_TRIGGERS using nonclustered index',
    @stmt = N'UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@state nvarchar(14), @jobName nvarchar(18), @jobGroup nvarchar(28), @oldState nvarchar(6)',
    @hints = N'<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.4411.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130" StatementSubTreeCost="0.0365109" StatementText="DECLARE&#xD;&#xA;    @state nvarchar(14),&#xD;&#xA;    @jobName nvarchar(18),&#xD;&#xA;    @jobGroup nvarchar(28),&#xD;&#xA;    @oldState nvarchar(6);&#xD;&#xA;&#xD;&#xA;UPDATE JOB_TRIGGERS&#xD;&#xA;SET TRIGGER_STATE = @state &#xD;&#xA;FROM JOB_TRIGGERS WITH (INDEX(ix_jobname_jobgroup_triggerstate))&#xD;&#xA;WHERE JOB_NAME = @jobName &#xD;&#xA;AND JOB_GROUP = @jobGroup &#xD;&#xA;AND TRIGGER_STATE = @oldState" StatementType="UPDATE" QueryHash="0xA993366BDAC14B06" QueryPlanHash="0x21B868F786AB4C56" RetrievedFromCache="false" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="320">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="52428" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="6723992" />
            <RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0365109">
              <OutputList />
              <Update DMLRequestSort="false">
                <Object Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Index="[PK__JOB_TRIG__CF66DD90CB121F34]" IndexKind="Clustered" Storage="RowStore" />
                <Object Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Index="[ix_jobname_jobgroup_triggerstate]" IndexKind="NonClustered" Storage="RowStore" />
                <SetPredicate>
                  <ScalarOperator ScalarString="[Sandpit].[dbo].[JOB_TRIGGERS].[TRIGGER_STATE] = RaiseIfNullUpdate([Expr1002])">
                    <ScalarExpressionList>
                      <ScalarOperator>
                        <MultipleAssign>
                          <Assign>
                            <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                            <ScalarOperator>
                              <Intrinsic FunctionName="RaiseIfNullUpdate">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Column="Expr1002" />
                                  </Identifier>
                                </ScalarOperator>
                              </Intrinsic>
                            </ScalarOperator>
                          </Assign>
                        </MultipleAssign>
                      </ScalarOperator>
                    </ScalarExpressionList>
                  </ScalarOperator>
                </SetPredicate>
                <RelOp AvgRowSize="25" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0165089">
                  <OutputList>
                    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                    <ColumnReference Column="Expr1002" />
                    <ColumnReference Column="Expr1008" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1008" />
                        <ScalarOperator ScalarString="[Expr1008]">
                          <Identifier>
                            <ColumnReference Column="Expr1008" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="25" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0165089">
                      <OutputList>
                        <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                        <ColumnReference Column="Expr1002" />
                        <ColumnReference Column="Expr1008" />
                      </OutputList>
                      <ComputeScalar>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Expr1008" />
                            <ScalarOperator ScalarString="CASE WHEN [Expr1005] THEN (0) ELSE (1) END">
                              <IF>
                                <Condition>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1005" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Condition>
                                <Then>
                                  <ScalarOperator>
                                    <Const ConstValue="(0)" />
                                  </ScalarOperator>
                                </Then>
                                <Else>
                                  <ScalarOperator>
                                    <Const ConstValue="(1)" />
                                  </ScalarOperator>
                                </Else>
                              </IF>
                            </ScalarOperator>
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp AvgRowSize="22" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0165088">
                          <OutputList>
                            <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                            <ColumnReference Column="Expr1002" />
                            <ColumnReference Column="Expr1005" />
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1002" />
                                <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(6),[@state],0)">
                                  <Identifier>
                                    <ColumnReference Column="ConstExpr1006">
                                      <ScalarOperator>
                                        <Convert DataType="nvarchar" Length="12" Style="0" Implicit="true">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="@state" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Convert>
                                      </ScalarOperator>
                                    </ColumnReference>
                                  </Identifier>
                                </ScalarOperator>
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="Expr1005" />
                                <ScalarOperator ScalarString="CASE WHEN [Sandpit].[dbo].[JOB_TRIGGERS].[TRIGGER_STATE] = CONVERT_IMPLICIT(nvarchar(6),[@state],0) THEN (1) ELSE (0) END">
                                  <IF>
                                    <Condition>
                                      <ScalarOperator>
                                        <Compare CompareOp="BINARY IS">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="ConstExpr1006">
                                                <ScalarOperator>
                                                  <Convert DataType="nvarchar" Length="12" Style="0" Implicit="true">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Column="@state" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Convert>
                                                </ScalarOperator>
                                              </ColumnReference>
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Condition>
                                    <Then>
                                      <ScalarOperator>
                                        <Const ConstValue="(1)" />
                                      </ScalarOperator>
                                    </Then>
                                    <Else>
                                      <ScalarOperator>
                                        <Const ConstValue="(0)" />
                                      </ScalarOperator>
                                    </Else>
                                  </IF>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp AvgRowSize="21" EstimateCPU="0.00010056" EstimateIO="0.013125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Eager Spool" NodeId="5" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0165087">
                              <OutputList>
                                <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                                <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                              </OutputList>
                              <Spool>
                                <RelOp AvgRowSize="21" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                                  <OutputList>
                                    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                                    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                  </OutputList>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Index="[ix_jobname_jobgroup_triggerstate]" IndexKind="NonClustered" Storage="RowStore" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_NAME" />
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_GROUP" />
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="[@jobName]">
                                                <Identifier>
                                                  <ColumnReference Column="@jobName" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="[@jobGroup]">
                                                <Identifier>
                                                  <ColumnReference Column="@jobGroup" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="[@oldState]">
                                                <Identifier>
                                                  <ColumnReference Column="@oldState" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                  </IndexScan>
                                </RelOp>
                              </Spool>
                            </RelOp>
                          </ComputeScalar>
                        </RelOp>
                      </ComputeScalar>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </Update>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>';

Submitting the query:
EXECUTE sys.sp_executesql
    @stmt = N'UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState',
    @params = N'@state nvarchar(14), @jobName nvarchar(18), @jobGroup nvarchar(28), @oldState nvarchar(6)',
    @state = N'', @jobName = N'', @jobGroup = N'', @oldState = N'';

Gives the desired plan:

The plan properties show the plan guide was used:

You cannot just use the exact plan guide above - it is just an example that worked on the toy schema I created to reproduce your issue. Nevertheless, the general process outlined should work for you.


SQL command to clean job queue in pgAdmin 4 I have a Postgres 9.6 installation on a developer pc and a restore seems to have failed. Even after reinstalling (remove Postgres and PgAdmin completely, incl. database and installed new version) the boxes are popping up (even though these tables do not even exist anymore). How to remove these scheduled jobs? Is there an SQL statement to stop these and cleanup the queue? Both jobs are running endless (3000000s+ already).

The file

C:\Users...\AppData\Roaming\pgAdmin\pgadmin4.db

is an sqlite-database.

Open it (e.g. with https://sqlitebrowser.org/) and delete all rows in the process table. Then restart pgAdmin - the boxes should be gone.


How do I get a graph of request traffic history in MS SQL Server 2008 R2 We're having difficulty with an application. One of the potential causes on the table is a regular spike in database traffic overloading the database, causing DB-dependent services to back up.

I would like to get a graph of the number of requests received per second over the last several days, and can't figure out how to get Server Management Studio to give me one. Or any historical performance graph, for that matter. Plenty of performance reports but none of them showing differences over time. Is this something it can't do, or am I just not looking in the right place?

SQL server doesn't track that information historically.

Going forward, you'll have to do one of the following:

Set up a PERFMON trace on the server with the appropriate counters
Set up a custom SQL agent job that queries the current values and stores them in a table
Use a 3rd-party SQL monitoring product, like Solarwinds DPA or something like that.


Is there any command to optimize all MySQL tables one by one automatically? I execute below command line statement to optimize a table:
optimize table tablename;

Is there any command or statement which can optimize all the tables one by one of the selected database?

I usually use mysqlcheck with --optimize and --databases combination. mysqlcheck is a command-line interface for administrators to check, optimize and repair tables.

mysqlcheck -uroot -p --optimize --databases myDatabase

Here is the MySQL doc : http://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html

Max.


What tasks can only sysadmin's do in SQL Server 2000? I'm cleaning up permissions on my SQL Server 2000, and I want to verify that a particular account still has sysadmin privileges.
What non-destructive tasks can only sysadmins do that I can use to confirm this?

You don't have to perform any task (destructive or non-destructive) at all.

You can use the built-in function IS_SRVROLEMEMBER to find if a certain LOGIN is a memeber of the sysadmin server role:

SELECT IS_SRVROLEMEMBER('sysadmin','<LoginName>');

Note that for roles other than sysadmin the result will be positive (=1)  for implicit membership as well.

You can use the built-in stored procedure sp_helpsrvrolemember to get a list of all the explicit members of a certain role:

EXEC sys.sp_helpsrvrolemember @srvrolename = 'sysadmin';

Or a complete list of all explicit members of all server roles:

EXEC sys.sp_helpsrvrolemember;

For SQL Server 2000 there is also the option to sort or filter the result set using:

CREATE TABLE #ServerRoles
(
  ServerRole VARCHAR(20),
  MemberName sysname,
  sid VARBINARY(85)
);

INSERT INTO #ServerRoles
(ServerRole, MemberName, sid)
EXEC sys.sp_helpsrvrolemember;

SELECT SR.MemberName, SR.ServerRole
FROM #ServerRoles SR
WHERE 1 = 1                            -- Change this to any filter you want
ORDER BY SR.MemberName, SR.ServerRole; -- Change this to any sorting you want

DROP TABLE #ServerRoles;
GO

For SQL Server 2005+ there is already a system view to be queried directly:
sys.server_role_members which is a many-to-many relationship table between roles and logins.


continuously extending a table in SQL Server We have a large data warehouse database where we continuously get new rows inserted in 5 different tables, at the left-hand side of the b-tree (=at end of the table)

This cause a lot of page splits.
Paul Randall calls these "good" page splits as in they do not cost much when inserting the data.

But when we query sys.dm_db_index_physical_stats we see an avg_fragment_size_in_pages
just under 8, namely the 8 8kB pages that can fit into one 64kB extend. A table with 50000 pages has 6300 fragments.
Not very "good" page splits anymore!

The only way I know we can bring these extends together (in order to get better read-ahead), is to REBUILD the table (or table partition).

Question 1)
Are there other options?

We have one table that in addition to the new inserts get a lot of updates to the newer rows. I.E we get 6 rows every hour, and once a week all rows for the entire week are recalculated (=updated). Fillfactor only works for index REBUILDs

Question 2)
Is there any way I can get SQL Server to use a FillFactor look-alike when inserting rows on the left-most page in the B-Tree?

when we query sys.dm_db_index_physical_stats we see an avg_fragment_size_in_pages just under 8, namely the 8 8kB pages that can fit into one 64kB extend. A table with 50000 pages has 6300 fragments. Not very "good" page splits anymore!

If your server has a RAID disk system, or is using SAN-based storage, or has SSD storage, index fragmentation will have no appreciable effect, either way.  If you are storing the database on a single hard drive, then table fragmentation will have an effect that is most noticeable when scanning an index sequentially that spans multiple 64KB extents.  If your system is not querying large portions of the given table(s) sequentially, index fragmentation will have a low impact.

You are much more likely to see gains in performance by ensuring you have the most efficient indexes for the actual work load present on the system.  Oh, and for less than 1/5th the cost of the typical SQL Server Enterprise license you can have the highest performing PCI-attached SSD cards commonly available that can pump hundreds of thousands of I/Os per second.

Look at http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ for more information about the index fragmentation issue.


What is the recommended my.cnf settings for 32 GB Server I am just moving my website to a new server and ı want to know what is the recommended settings for 32 GB server?

Mysql tunning for my old server (16 GB)

https://pastecode.xyz/view/577e824e

Suggestions to consider for your my.cnf [mysqld] section

thread_cache_size=100  # to reduce threads_created count of 16K in 42d per MSTuner
table_open_cache=6000  # from 2500 to reduce opened_tables count
innodb_open_files=6000  # from 2500 to be paired with table_open_cache

Welcome to dba.stackexchange.com


OR does not optimize well; don't use that form.
One of these indexes will be optimal; add both:

INDEX(company_id, period_code, paydate)
INDEX(period_code, company_id, emp_id)

{d '2020-05-01'} can be simply '2020-05-01'
You might find this easier to deal with

    paydate >= '2020-05-01'
AND paydate  < '2020-05-01' + INTERVAL 1 MONTH

COUNT(loan_payment) checks for NULL; perhaps you want simply COUNT(*)?


Start service error code 3417 with SQL Server 2012 I cannot start the SQL Server 2012 service. The event log says the service-specific error code is 3417.

In the Event Viewer:

  The SQL Server (MSSQLSERVER) service terminated with the following service-specific error:
  WARNING: You have until SQL Server (MSSQLSERVER) to logoff. If you have not logged off at this time, your session will be disconnected, and any open files or devices you have open may lose data.

I've changed the service logon account in the service properties but this error happened again.

I think the problem is in the master database, but I do not know what to do.

EDIT:

I've try Rebuild the Resource database  but this error happened again!

That error is usually associated with the Master database, so you are on the right track. Does the service account have access to the directory where the Master database resides?

There should be some additional errors in either the SQL Server error log or the Windows event log that may narrow down the issue. Look for access denied messages or cannot find file messages associated with the master database and other system databases.

One other thing I have found on other sites is folder compression being a problem. Make sure SQL Server does not reside on a compressed folder or drive.

If that doesn't work, then rebuild as suggested.


Recommendations for my.cnf on MySQL Replicant I have replication environment running in a master-slave configuration. The replicant is doing all writes and doing selects only very rarely - and none in which high performance is a requirement.

Aside from the replication options themselves which have to be set, do the db administrators here have any recommendations for configuration of a mysql replicant that would be different from the default my.cnf values on a master server which is doing both selects and writes?

I can provide my own mysql-tuner results and my.cnf configuration, but I guess I was hoping for a more general overview on the configuration best practices for a replicant that is doing mostly writes.

There are no hard-and-fast rules for such Slaves. Let's look at two scenarios

SCENARIO #1

Some standardize all DB Servers to have the same configuration. The rationale behind this is promote a Slave to a Master should have to fail over to it.

SCENARIO #2

A scaled down Slave being used as a data backup. No reads from that Slave.

COMPARISON OF SLAVES

For a Slave whose sole job was backup and simple data redundancy, using less optimization is fine but can come at a premium. How so ?

If you have Master that has high write volume, this can result in MySQL Replication having lots of Replication Lag (Seconds_Behind_Master > 0 and increasing). This can occur when a Master has 20 INSERTs, UPDATEs, and DELETEs happening in parallel. Those 20 commands are serialized in the Master's binary logs and are serially processed by the SQL thread on the Slave. If you scale down the hardware and MySQL config settings on the Slave too much, that Replication Log can get worse than the aforementioned serialization of updates.

CONCLUSION

The volume of writes on the Master should be inversely proportional to scale down of the Slave:

the higher the volume of writes on the Master

the less the scale down of the Slave
the lower the tolerance for Replication Lag (preferably none)

the lower the volume of writes on the Master

the more the scale down of the Slave
the higher the tolerance for Replication Lag (expected to be infrequent)

CAVEAT

In this context, scale down means

less RAM required
less need for CPU/cores
lower cache settings

innodb_buffer_pool_size
innodb_log_file_size (default is OK)
innodb_log_buffer_size (default is OK)
key_buffer_size

lower max_connections

UPDATE 2015-02-02 12:53 EST

Based on the question in your comment about writes, you can increase the following on the Slave

innodb_write_io_threads to tweak the CPU for writes
innodb_log_buffer_size to cache writes


Is it possible to COPY tables into PostgreSQL without setting structure first? I'm just getting to grips with PostgreSQL 9.6.

I'm wondering if there is a simple way to add a .csv table to a database without first creating the table structure in psql or pgAdmin?

Or...I've not used scripts before but if someone could suggest one and give me a quick run through, I'd be very grateful.

If you can live with all columns being text and if the CSV file has at least a header line with column names, then one ugly solution could be:

Create a table with a single column. Then import the first row of the CSV file into that table. You can then get a list of the names and generate a CREATE TABLE statement from that.

Something like:

create table header_names (header_line text);

Then import the first row into that table.

Something like (untested!):

\copy header_names from (program 'head -n 1 data.txt')

\copy and COPY do not have a way of limiting the number of rows that are imported, so you need to use head to just get the first line.

Then use:

select 'create table import ('||string_agg(col_name||' text', ', ')||');'
from header_names
   cross join lateral unnest(string_to_array(header_line,',')) as x(col_name);

The second parameter to the string_to_array() function is the delimiter of your CSV file.

Assuming you have a header like:

id,first_name,last_name

The above will generate:

create table import (id text, first_name text, last_name text);


When was the stored procedure created and altered? I am about to update a MySQL server in operation with the changes made on a development server.
I want to be sure that I don't miss any changes.
Are there any logs telling things like when the stored procedure was created and altered om a MySQL server?
I read the documentation about server logs but it does not seem to cover this issue.

The easiest way is to check information_schema.routines metadata table.

You can get the basic list of all procedures and functions with:

SELECT routine_name, routine_type, created, last_altered
  FROM information_schema.routines

You can include some other fields as needed - the exhaustive list is in the MySQL 5.7 reference - 24.21 The INFORMATION_SCHEMA ROUTINES Table

Information schema contains tables that contain the "database metadata" and describe the database's entities: tables, indexes, procedures and many others.
Every relational database has some kind "database catalog" as per Rule 4 of Codd's 12 rules for RDBMSs.

So whenever you are needing some info about database objects on any RDBMS product you can always check the manual for the specifications of the "database dictionary", "database catalog" or similar.

Check here for other of the INFORMATION SCHEMA tables on Mysql 5.7.


How many parallel workers can I have on Postgres? I working on a server with Ubuntu, 320 GB SSD, 6 cores and 16GB Ram, but Postgres is having issues some queries that take very long time to run, and with parallel queries, that are increasing the server load a lot.
Some Postgres conf:
max_connections = 150
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 109MB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
effective_io_concurrency = 200

seq_page_cost = 1
random_page_cost = 1.1
cpu_index_tuple_cost = 0.030
cpu_operator_cost = 0.0150
cpu_tuple_cost = 0.06

#parallel_tuple_cost = 0.1      # same scale as above
#parallel_setup_cost = 1000.0   # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6

The server is on Linode and i get some info:

#Larger bs
$ dd bs=16k count=10240 iflag=direct if=./test_file of=/dev/null;
167772160 bytes (168 MB, 160 MiB) copied, 1,01442 s, 165 MB/s

#Smaller bs
$ dd bs=2048 count=80000 iflag=direct if=./arquivo_teste of=/dev/null;
163840000 bytes (164 MB, 156 MiB) copied, 9,91975 s, 16,5 MB/s

lsblk -o NAME,MOUNTPOINT,MODEL,ROTA
sda  /          QEMU HARDDISK       1

Linux myClientName 5.7.6-x86_64-linode136 #1 SMP PREEMPT Wed Jun 24 15:41:07 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux

Not sure if the dd test will help, but i find interesting how large is the difference between the small BS and the big BS tests.
If i not wrong QEMU is vitualized system, and the HD may still be and SSD despite the ROTA = 1

Is there any way to be sure how many parallel workers can I have on a Postgres server ?

The limit of concurrent parallel workers for the whole cluster is max_parallel_workers, which must be ≤ max_worker_processes. The limit of parallel workers per query is max_parallel_workers_per_gather.
Adjusting these parameters, you can start as many workers as you please. Just keep in mind that performance will degrade once you are using more parallel workers than your CPU and I/O can handle. Also, don't forget that there may be other queries running, so don't over-allocate resources.
Don't forget alternative approaches: perhaps the query can be rewritten or indexed to be faster.


Analysis service database security at application level I have got a SSAS Cube, we have written an windows application which would issue MDX queries to access the data from the cube and show it in the reports .

This windows application is a licensed application, meaning only those who have got license from us could be able to run the application and then could be able to access the data from the cube.

The problem we are having is what some unlicensed users tries to access our cubes through other cube browser application like Excel.

so we are generally wondering how to protect the cubes and data present in the cube so that only who has got license to our application could be able to access.

Could anyone please advise that how to give this type of security to SSAS database?

If your users succeed to connect to the cube with their domain users, than the server permission is too lax.

The security model of the SSAS is actually build around Windows authentication (using Windows logins). In Management Studio, right click on the SSAS server -> Properties -> Security. You'll see there who is an administrator of this instance of SSAS. All logins here will be able to do everything they want (process the cube, browse the database, backup, script..whatever).

If you want to limit the permissions, you have to go further and create roles within your SSAS db. There you'll grant whatever privileges you need for the application.

Please see further details in the following answer (and especially in the references section).

But if some server administrator is granting admin permissions to another user, you can't really do much. There was a very good advice in another question (regarding obfuscation of DB code), this should be part of the licensing process and the EULA. If the users break it, you can at least sue them.


Will this trigger always complete before returning results of Insert to client? I have the following trigger for Auditing.

CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EntityState AS SMALLINT;
    SET @EntityState = (CASE
                        WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 3 -- Updated
                        WHEN EXISTS(SELECT * FROM INSERTED) THEN 4 -- Inserted
                        WHEN EXISTS(SELECT * FROM DELETED) THEN 2 -- Deleted
                    ELSE NULL END)
    IF @EntityState = 4
    BEGIN
        UPDATE Cust
        SET Created = GETDATE(), Updated = GETDATE()
        FROM INSERTED I
        WHERE Cust.BinId = I.BinId
    END
    ELSE IF @EntityState = 3
    BEGIN
        UPDATE Cust
        SET Updated = GETDATE()
        FROM INSERTED I
        WHERE Cust.BinId = I.BinId
    END
    IF @EntityState <> 2
    BEGIN
        INSERT INTO AuditEntries (SomeColumns..)
        SELECT SomeColumns..
        FROM INSERTED I;
    END
    ELSE
    BEGIN
        INSERT INTO AuditEntries (SomeColumns..)
        SELECT SomeColumns..
        FROM DELETED I;
    END
END;

When the client(API Server) call writing to the table Cust is complete, the next call inserts userIds to the AuditEntries table.

Extra info:

There exist no other triggers anywhere.
Client uses EntityFramework Core so time in question is after SaveChangesAsync() returns.
The PrimaryKey of the AuditEntries table is the ROWVERSION of dbo.Cust

The question is: are the AuditEntries guaranteed to exist at this time or is this a race I need to handle?

Other research:

sql-server-trigger-execution

An answer to: Are Sql Triggers synchronous or asynchronous

UPDATE: The above trigger does not enter a loop via direct recursion on any update operations due to the default T-Sql configuratoin.  See: Recursive Triggers

Found an SO this am that answers my original question: Does a trigger in a transaction only fire when the transaction is committed?

DELTE the UPDATE from your trigger

CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust
    AFTER INSERT, DELETE
AS
BEGIN

When you delete the UPDATE trigger Case 4 and 3 wouldn't fire the  trigger again.

So only when you INSERT or DELETE, the trigger would run, and update Cust or insert into AuditEntries

With this AFTER UPDATE TRiGGER you would create a loop.

so you should write another triigger only for UPDATES, if you need one.

To answer your question

The trigger would run after the INSER or DELETE of dbo.Cust and will  run completely before starting another command, that is because you define  your trigger  as FOR EACH STATEMENT

As Example

INSERT INTO dbo.Cust VALUE (1,),(2,),(3,),(4,),(5,)
TRIGGER dbo.TR_MyTrigger
INSERT INTO dbo.Cust VALUE (6,),(7,),(8,),(9,),(10,)
TRIGGER dbo.TR_MyTrigger
DELETE dbo.Cust ...
TRIGGER dbo.TR_MyTrigger#

Every step is completed before the next


Most efficient way to perform table operations (spec. drop) on tables having table names that match a certain criteria I am performing a schema dump of certain tables. This accompanied by dropping the tables that will be dumped. For example I need to drop all tables that start with, let's say, "SRC_". You guys think of a more efficient way of dropping those tables than the script below? Thanks.

begin
  for rec in (select TABLE_NAME
                from ALL_TABLES
               where TABLE_NAME like 'SRC|_%' escape '|'
                 and OWNER = 'SOME_SCHEMA')
  loop
    execute immediate 'drop table ' || rec.TABLE_NAME;
  end loop;
end;
/

Why does the code need to be more efficient?  It seems unlikely that you would be dropping large numbers of tables frequently or that it would take enough time to drop tables that it would be worth optimizing.

What trade-offs are you willing to make to cause the code to be more efficient?  And what do you mean by "efficient" in this context-- wall clock time?  The resources consumed on the server?  Something else? The code that you have written is very straightforward and easy to follow.  You could complicate things by, for example, submitting multiple jobs that each drop a subset of the tables so that you can have multiple threads working simultaneously.  But that would involve making the code much more complicated-- you'd need to assign work to different threads and then coordinate the responses from each thread to verify that all the tables were dropped successfully.  It seems most unlikely that this would be a trade-off that you would really want to make though it would reduce the wall clock time needed to drop all the tables.


(MySQL) How to change the results of a view according to the user that's viewing it? I have a table, named servers, with a single column id.

This table is accessed by various machines each with a different username for the database.

I want to make it so each server does not see all the information on this table, but only a subset I designate to it in order to partition the workload among the app servers.

My original idea is to rename the table (say, to "servers_table"), add another "user" column to it, and create a view (with the original name) that shows the results based on the username currently connected to the server.

My table currently looks like this:

id  user
 1  server1
 2  server1
 3  server2
 4  server1
 5  server2

My attempt was (as "server1"):

CREATE VIEW server AS
SELECT id
FROM server_orig
WHERE usuario=SUBSTRING_INDEX(CURRENT_USER(),'@',1);

This, however, does not work, since CURRENT USER() is evaluated at CREATE VIEW time, not at SELECT time.

Therefore, running:

mysql -userver1 database -e "select * from server;"

yields:

+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+

BUT so does running it as server2..

I cannot (since the program is closed source) change the original select statement (and that would also incur eventually changing the insert/update statements too).

Is there a way this can be accomplished purely on the MySQL side?

A hint from the current_user() docs:

  For stored procedures and functions and views defined with the SQL SECURITY INVOKER characteristic, CURRENT_USER() returns the object's invoker.

On the other hand, in PostgreSQL you can do the same (with the current_user function, which behaves exactly as desired), and with good design and config the performance wouldn't be any worse - however, I don't think that this feature would make migrating to PostgreSQL any sense (especially given that your app code is closed...)


Could not allocate space for object in database because the Filegroup is full Today, I was hit by the below error on my SQL Server DB having multiple files in multiple filegroups.

  Event ID: 1105. Could not allocate space for object  in database
  because the 'PRIMARY' filegroup is full. Create disk space by deleting
  unneeded files, dropping objects in the filegroup, adding additional
  files to the filegroup, or setting autogrowth on for existing files in
  the filegroup.

  MSSQL 2014: Could not allocate space for object in database because
  the Filegroup is full

It occurred when my index maintenance job was running. Specifically during index reorg of one of the index. Upon investigation, I found that all my filegroups had at least one file with auto-growth enabled and all the auto-growth enabled files residing on respective drives had enough free space available. So there was no question of space issue or file autogrowth(considering I have at least one file in each filegroup with auto-growth enabled).Upon further drill down, found below from one of the MS link https://msdn.microsoft.com/en-us/library/aa337441.aspx

  When an index is located on several files, ALTER INDEX REORGANIZE can
  return error 1105 when one of the files is full. The reorganization
  process is blocked when the process tries to move rows to the full
  file. To work around this limitation perform an ALTER INDEX REBUILD
  instead of ALTER INDEX REORGANIZE or increase the file growth limit of
  any files that are full.

So in order to fix my issue I went ahead and rebuild my index which fixed the issue. However, I am looking out for ways to avoid it as it may occur for different index in future and doing manual index rebuild all the time isn't a good way to fix it? I would love to hear your suggestions on this.

I'm looking for ways to avoid it in the future...

Don't have just a single file in the filegroup that has any free space. Set your files to the same size, grow them at the same time to the same sizes, and let the round robin with proportional fill algorithm do its' thing.

  But, the auto-growth restricted files in filegroups are restricted due to space issues on the drives they reside. So enabling auto-growth for them or increasing their initial size to keep same across all files would be difficult.

Then they should be moved to different drives where there is space, or moved around so that all drives have space, or the drives expanded.

This really comes down to administration. This will be an ever present issue while the data files are vastly different in free space and size. The reason comes down to proportional fill and round robin allocations.

A very quick summary of proportional fill in SQL Server is that  the file in the filegroup with the most free space should be allocated from more often than the ones with less free space. If there is a file with little to no free space, it'll be skipped. This means when you only have one file in your filegroup that really has any space, you're effectively hot-spotting all allocations there. The round robin part is just the allocations going between the files.

Since you're only keeping a single file with unbalanced amounts of free space, this will become a single hot spot for all allocations. This is why I advised the above in equaling your files as much as possible.


How do I add a MySQL linked server to SQL Server? I am trying to add a linked server on SQL Server to MySQL Server.

Note the SQL Server is installed on a separate machine with a different domain name.

The SQL Server is 10.0.1.1 on old_domain.com.

The MySQL server is 10.0.1.2 on new_domain.com.

I have added an ODBC Data Source on server 10.0.1.1 and tested the connection with no problems reported.

Then I opened Microsoft SQL Server Management Studio and navigated to SQL SERVER > Server Object > Linked Servers.

I got to the wizard to add a linked server and I configured it like so:

Linked Server: "MySQLNewServer"
I selected "Other data source" Provider "Microsoft OLE DB Provider for ODBC" Product name "Connection to MySQL" Data source "MySQL" the same name at the Data Source that was added in the first step.
On the security tab I selected "Be made using this security Context" and I put SQL Server user name and SQL Server password
I hit "Ok"

Note: I followed the instruction on this link http://dbperf.wordpress.com/2010/07/22/link-mysql-to-ms-sql-server2008/

However, I get this error:

  OLE DB Providor "MSDASQL" for linked Server "MySQLNewServer" returned message "[MySQL][ODBC 5.2(w) Driver] Can't connect to MySQL server on 10.0.1.1 (10055) Microsoft SQL Server Error: 7303.

Can someone please tell me what am I doing wrong? How can I get these 2 server to communicate?

We have a linked server on SQL pointing to a MySQL server.
Creating the linked server is a pretty straightforward thing.

The tricky part is getting the permissions sorted out on MySQL side.

If memory serves my right you need to create a local account on the SQL server and create an login on the MySQL server for the full computer name where the linked server will run.

On the SQL server I have a login for Sqltest. This is a SQL account

On the linked server properties I have "Security" > Be made using this security context > MySQL login username and password

On the MySQL box I have a login for Sqltest@myserver.com. This is where my linked server failed for quite a long time.


PT-Kill not logging to file Run command for pt-kill

/usr/bin/pt-kill --ignore-state User lock --rds --match-command Query --victims all --match-user phppoint --daemonize --busy-time 10 --kill --print --log /var/log/pt-kill.log h=HOST,u=master,p=PaSS,P=3306

Queries are killed but not logged. Runs as root on Amazon Linux and file /var/log/put-kill.log exists and has 600 permissions

The command seems right.

You can try to debug using PTDEBUG:

PTDEBUG=1 (your complete pt-kill command with daemonize and log ) 2>/var/log/pt-kill.err

Be careful to watch disk space; don't let it run in debug mode indefinitely.


When refreshing materialised views does the index automatically refresh When refreshing materialised views does the index automatically refresh

Yes.

A refresh changes the table storing the data that is materialized from the materialized view query.  The indexes for these tables are updated just as indexes for "normal" tables would be updated when they change.


How do I run a stored procedure only on newest data loaded? My database has a table where the data is loaded from an Excel upload. Then, a stored procedure is run on the data to validate it and insert it to another table.

Currently, my stored procedure will run the validation on all the data again when it's executed.

How do I make it so that it runs the query on the newly loaded data only?

My current stored procedure has the following query:

INSERT INTO dbo.Data_Pass(
   [ACC_NO]
  ,[ORGN]
  ,[DEST]
  ,[CTRY]
  ,[CRNCY]
  ,[PRDCT_NAME]
  ,[PRDCT_CODE]
  ,[DETAILS]
)
SELECT
   [ACC_NO]
  ,[ORGN]
  ,[DEST]
  ,[CTRY]
  ,[CRNCY]
  ,[PRDCT_NAME]
  ,[PRDCT_CODE]
  ,[DETAILS]
FROM dbo.SOURCE_TBL AS ST
JOIN UPLOAD_DATA AS UD ON ST.ACC_NO = CAST(UD.ACC_NO AS nvarchar)
    AND ST.CTRY = CAST(UD.CTRY AS nvarchar)
WHERE ST.[PRDCT_CODE] = '102'
    AND CAST(UD.[PRDCT_CODE] AS nvarchar) LIKE '%%2'
    AND ST.Val LIKE ('%' + CAST(UD.CorrectionVal AS nvarchar)+ '%');

UPDATE Data_Pass
SET REPORT_TY = 'Correction',
    CHECK_DT = GETDATE(),
    CHECK_SRC = 'Correction Report',
    Data_Pass.STATUS = 'Validated'
WHERE REPORT_TY IS NULL AND CHECK_DT IS NULL AND CHECK_SRC IS NULL

I also have an id field in Data_Pass which is a primary key and is auto incremented.

The loaded data can be around 200,000 or sometimes more in one go, so I'm looking for the most efficient way to do this.

Edit:
Added in my create table statement:

CREATE TABLE [dbo].[Data_Pass](
    [id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [ACC_NO] [nvarchar](50) NULL,
    [ORGN] [nvarchar](50) NULL,
    [DEST] [nvarchar](max) NULL,
    [CTRY] [nvarchar](max) NULL,
    [CRNCY] [nvarchar](50) NULL,
    [PRDCT_NAME] [nvarchar](50) NULL,
    [PRDCT_CODE] [nvarchar](50) NULL,
    [DETAILS] [nvarchar](max) NULL,
    [REPORT_TY] [nvarchar](max) NULL,
    [CHECK_DT] [datetime2](7) NULL,
    [CHECK_SRC] [nvarchar](max) NULL,
    [STATUS] [nvarchar](50) NULL
)

| id  | ACC_NO    |  ORGN | DEST | CTRY |  CRNCY |  PRDCT_NAME  | PRDCT_CODE |  DETAILS |  REPORT_TY   |  CHECK_DT  |      CHECK_SRC      |   STATUS   |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 979 | 900000000 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 980 | 900000001 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 981 | 900000002 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 982 | 900000003 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 983 | 900000004 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   09:09.0  |   Correction Report |  Validated |

Community wiki answer - please edit to improve:

Don't load directly to dbo.Data_Pass.

Instead, load to a staging table, and run the SP against that table. Then insert the final, validated data into dbo.Data_Pass.


How to convert a VARCHAR DD/MM/YYYY H:MM:SS AM into datetime and then to Json date? So I know this question is crazy. I just spent days trying to solve this but i think it's impossible.

I have a table with a CreatedDate as a VARCHAR field.

10/7/2019 7:12:59 AM

What I need to to is to convert this date into those dates we see on MongoDB or Json files. It should be like this:

2019-07-04T15:35:41.958UTC-03:00

is there a way to do this? I tried to convert to datetime, I tried substring, left right. I can force the -03:00 at the end.

SELECT CONVERT(VARCHAR(33), getdate(), 126)

With this I can convert a datetime field and thats the problem.

somewhat old but still ...

https://stackoverflow.com/questions/10735447/is-there-a-way-to-convert-a-varchar-to-datetime-in-sql-server-2008

convert(datetime,'24/05/2012 09:56:06',103)

set your column containing the varchar as the second parameter - 103 is for the --/--/---- format ..

I tested above command against a SQL Server 2014 and it works when you omit leading zeroes - and also with AM / PM addition so perfect for your case

The only thing that needs some more work is your desired addition of the timezone, but I highly suggest adding that only for display, not in the actual database


Optional oneToOne relations and criteria field Introduction:
Visitors can fill reports on my website.
Those report can either be filled by a legalPerson or a naturalPerson.

Constraint: I don't like empty fields and I would like to normalize as much as possible my database.
Hence, I have those relations:

Question: For these kind of optional relations, should I add the field notifier_type (we could call it a criteria field) in Report Object? I mean I can obtain a close result with:

if($this->getNaturalPerson) {
    $notifierType = 'naturalPerson';
} ...

The only drawback would be that I can't query on notifier_type if it's not in DB.

What is the best practice here?

I'd put person_id as second field of Report and get rid of notifier_type. Then create a relation persons (persona?) with id field, then make foreign key from Report.person_id to persons.id.

This relation persons (persona?) would have all of fields from both your legal- and naturalPerson relations, plus one additional - person_type. You would have NULLs when a field would be not applicable for given type of person (namely legal_Statut would be NULL for natural person and birthday would be NULL for legal person).

I would then exclude headquarters_address and home_address and make additional relation addresses with person_id field - again, foreign key to persons.id. You would then have a possibility of assigning multiple addresses to persons, mark them as current or previous, whatever you wish. That's what I would do.

EDIT: Visualization


database design help with multiple table relationships Been scratching my head on how to set this up and looking for your help. Trying to create an API and not 100% how my tables should relate to one another.

So lets say I have users, food, and food details.  The user is able to post what he wants based on the food he selects. But they all should be able to be viewed in a single view if needed. not pizza only or pasta only. Each post should be able to stream together. pizza posts + pasta posts...

Here is what i am thinking below-

User Table: user_id, email, pass
Post Table: post_id, user_id
Pizza Table: post_id, peperoni, sausage, peppers
Pasta Table: post_id, sauce, cheese, onions

Would this way make sense? But then how would i know what pizza or pasta table to look into with just a post_id... This is the part i am a scratching my head over.

Thanks in advance.

Citti

Edit:

api uri would be something like: /api/v1/posts

Example output for this would be a feed per post:

post_id 1 | Steve wants Pizza with Peperoni and Peppers @ 12:40
post_id 2 | Steve wants Pizza with Sausage and Peppers @ 12:35
post_id 3 | Kevin Wants Pizza with Peperoni, Sausage, and peppers @ 12:20
post_id 4 | Steve wants Pasta with Sauce and Cheese @ 12:15

Consider it this way. Products and possible options of products comprise system dictionary. Orders and specs are constraint by dictionary.

Products
--
Pizza
Pasta
;
Options
--
peperoni
sausage
peppers
sauce
cheese
onions
;
ProductOptions
--
Pizza peperoni
Pizza sausage
Pasta cheese
Pasta onions
;
Orders
--
1 Steve Pizza 12:40
2 Steve Pizza 12:35
;
OrderSpecs
--
1 Peperoni
1 Peppers
2 Sausage
2 Peppers
;


MySQL - When Command column in Processlist shows "Prepare" or "execute" Can someone please highlight when a "Command" shows "Prepare" or "Execute" for "Show Full Processlist"

Any example which shows this would be helpful.

The command status you refer to is pretty self explanatory:

1) The thread is preparing a statement, for example when the following:

PREPARE STATEMENT my_stmt FROM 'SELECT * FROM countries WHERE id = ?';

2) The thread ist executing a formerly prepared statement:

EXECUTE my_stmt;

You can also refer to the documentation: Thread Command Values


Adding an IF based comparison column I have a table which contains the following columns Table6
ID Place Name Money,

From this I am trying to create a table which contains the following column
Place ,Avg_Money_Place ,High_or_Low , the aim for high/low column is to show equal when this is avg of Place is equal to overall avg, low when it is lower than overall_avg and high elsewhere.

I have a vague idea of how to calculate this with Where structures but am struggling to do with select structures. Here is my feeble attempt
I am struggling to this till now I have tried this

Create table Table7 AS
   Select Distinct(Place) , Avg(Money)
     from Table6
    order by ID

This gives me the places and the corresponding averages but I am unable to get the hig/low column

I tried using the following function

INSERT INTO TABLE7 (
  Select High_Low
    CASE
      WHEN AVG(money) < (select AVG(Money) from Table6 ) THEN 'LOW'
      WHEN AVG(money) > (select AVG(Money) from Table6 ) THEN 'HIGH'
      WHEN AVG(money) = (select AVG(Money) from Table6 ) THEN 'EQUAL'
)

This did not work !
Please help

The query

SELECT Place,
       AVG(Money)
FROM Table6
GROUP BY Place

gives us a place and average money for it.

The query

SELECT AVG(Money)
FROM Table6

gives us an average money over whole table.

This 2 queries allows us to compare AVG for place and AVG for all (because there are more than 1 table we must add table alias to each field)

SELECT t6.Place,
       AVG(t6.Money) avg_place,
       CASE WHEN AVG(t6.Money) > subquery.avg_all THEN 'High'
            WHEN AVG(t6.Money) < subquery.avg_all THEN 'Low'
            ELSE                                       'Equal'
       END avg_rank
FROM Table6 t6, ( SELECT AVG(t6_1.Money) avg_all
                  FROM Table6 t6_1 ) subquery
GROUP BY t6.Place

And now we can to store the result in new table

CREATE TABLE Table7
AS
SELECT t6.Place,
       AVG(t6.Money) avg_place,
       CASE WHEN AVG(t6.Money) > subquery.avg_all THEN 'High'
            WHEN AVG(t6.Money) < subquery.avg_all THEN 'Low'
            ELSE                                       'Equal'
       END avg_rank
FROM Table6 t6, ( SELECT AVG(t6_1.Money) avg_all
                  FROM Table6 t6_1 ) subquery
GROUP BY t6.Place


Many-to-many relationship with a one column ‘junction’ table? Basically trying to get my head around how to enforce primary / foreign key constraints on what is essentially a many-to-many (M:N) relationship. Or if I should ignore them in this instance...

The schema design below is based on the following.

Shops sell fruit (item)
The fruit for each shop can be retrieved from certain warehouses that have stockcodes
Multiple shops have access to multiple warehouse
Multiple warehouses supply multiple shops

--Fruit Stock levels (PK surrogate key, item and stockcode unique constraint)
DECLARE @stock TABLE(item VARCHAR(50),stockAmount INT,stockCode VARCHAR(5))
INSERT INTO @stock( item, stockAmount, stockCode )
VALUES   ( 'apples',  10, 'SC01' )
        ,( 'bananas', 15, 'SC01' )
        ,( 'apples',  10, 'SC02' )
        ,( 'oranges', 50, NULL ) --no stock code as 50 avaibale from any warehouse

--Stock allocated into stockpiles/warehouses. Each warehouse has a code e.g SC01 Shops have access to certain warehouses. Table keys currently (Composite key on code & shop, can change this?)

DECLARE @stockForShop TABLE(code VARCHAR(5),shop VARCHAR(50))
INSERT INTO @stockForShop( code, shop )
VALUES   ( 'SC01', 'shop1')
        ,( 'SC01', 'shop2')
        ,( 'SC01', 'shop3')
        ,( 'SC02', 'shop9')
        ,( 'SC03', 'shop1')

--1 column ‘junction’ table to enforce primary and foreign key constraints
DECLARE @stock_stockForShop TABLE (code VARCHAR(5))
INSERT INTO @stock_stockForShop(code)
VALUES ('SC01'), ('SC02'), ('SC03')

--SELECT  * FROM @stock
--SELECT  * FROM @stockForShop

--Query to get shops and what stockpile the products can sell to via  from
--using ‘junction’ table (enforce primary key on actual tables)
SELECT * FROM @stock s
LEFT JOIN @stock_stockForShop ssfs ON s.stockCode = ssfs.code
LEFT JOIN @stockForShop sfs ON ssfs.code = sfs.code

--no ‘junction’ table
SELECT * FROM @stock s
LEFT JOIN @stockForShop sfs ON s.stockCode = sfs.code

I would include the table you're calling @stock_stockForShop (though I might change the name to something like @stockpile or even @warehouse if that actually makes sense in context.

A stockCode represents a unique entity in your schema; it defines a set of inventory that is available to a set of stores. If you do not store it and enforce it as a foreign key, you have to allow new codes to be created via @stock and @stockForShop, meaning that a typo could create a stockCode that is not used anywhere else, leaving the associated stock to potentially go to waste. This is, perhaps, the most important point - ensuring that all stockCodes are well established and usable, and that new ones are only created deliberately.

In addition, a stockCode might also have important attributes that you might want to be able to search on from an application: it could be tied to a specific geographic location (such that it would make sense to connect to shops within 100 miles of it, but no farther), or contact info (phone/fax/email).

So, I would have the @stock_stockForShop table, and would make IDs referring to other tables as foreign keys (again, making sure that typos don't create virtual new stockCodes).

Terminology note: I would consider @stockForShop to be your linking/mapping table (which is how I read "junction"), as it ties a shop to one or more warehouses, and a warehouse to one or more shops.


HSQLDB get next value for IDENTITY column I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into.

I did some research and couldn't find anything sadly.

Example:

Suppose I have a table like this:

CREATE TABLE IF NOT EXISTS Names (
  id INTEGER IDENTITY PRIMARY KEY,
  name VARCHAR(16)
)

Now I can insert names without having to specify the id. Instead it gets added automatically and increments itself with each insert.
Now I'd like to get whatever value will be next (if I don't manually set it).

Edit:

I think I found a solution:

SELECT
  IDENTITY_START
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'NAMES'
  AND COLUMN_NAME = 'ID'

You can get the last identity value already inserted, but not the next one.

Use the IDENTITY() function. The value is useful for inserting into another table which has a foreign key referencing the Names table. For example,

  INSERT INTO OtherTable VALUES DEFAULT, IDENTITY(), 'A reference to Names table'

See the Guide, http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_system_functions


function checking type of input and reacting I have a function that can check if each element in an array is present in a given column of a table, and it works fine. I would now like to extend the function so that it can handle json/jsonb arrays as well as SQL arrays.
In this MWE (PSQL-11) the commented lines are psudo code for what I would like to do. Keeping them commented out lets you run the code but it then only works for SQL arrays.

CREATE TABLE IF NOT EXISTS animals
(
    animal text COLLATE pg_catalog."default" NOT NULL,
    animal_doc jsonb,
    CONSTRAINT animals_pkey PRIMARY KEY (animal)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE animals
    OWNER to postgres;

CREATE OR REPLACE FUNCTION check_is_animal(anyelement) RETURNS boolean AS $$
DECLARE
   i text;
   arr text[];
BEGIN
--    IF ((SELECT pg_typeof($1)) = 'jsonb') THEN
--        arr = jsonb_array_elements($1);
--    ELIF ((SELECT pg_typeof($1)) = 'json') THEN
--        arr = json_array_elements($1);
--    ELSE
        arr = $1;
--    END IF;
    FOREACH i IN ARRAY arr LOOP
        IF i NOT IN (SELECT animal FROM animals) THEN
            RAISE NOTICE '% is not a known animal', i;
            RETURN FALSE;
        END IF;
    END LOOP;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

INSERT INTO animals (animal) VALUES ('Cat') ON CONFLICT ON CONSTRAINT animals_pkey DO NOTHING;
INSERT INTO animals (animal) VALUES ('Dog') ON CONFLICT ON CONSTRAINT animals_pkey DO NOTHING;

DO $$
BEGIN

IF check_is_animal(array['Cat','Dog', 'Horse']) THEN
  RAISE NOTICE 'All good';
ELSE
  RAISE NOTICE 'Failed';
END IF;

/*
IF check_is_animal(jsonb('["Cat","Dog", "Horse"]')) THEN
  RAISE NOTICE 'All good';
ELSE
  RAISE NOTICE 'Failed';
END IF;
*/
END $$;

The SELECT pg_typeof($1) was found here.
Any suggestions for how the get this to work?

The result of pg_typeof() is an oid, if you want to compare that with a string value, you need to cast it, e.g. if pg_typeof($1)::text = 'jsonb' then...

But once you checked if the input is a JSON you also need to verify if it's a JSON array, because {"foo": "bar"} will also be reported as json(b).

The way you iterate over the array is also quite inefficient as you are running the same select statement over and over again. You can check this with a single statement.

There is no ELIF in PL/pgSQL. As documented in the manual it needs to be ELSIF

So the function would look something like this:

CREATE OR REPLACE FUNCTION check_is_animal(anyelement)
  RETURNS boolean
AS $$
DECLARE
   arr text[];
   l_result boolean;
BEGIN
  -- check if the argument is a text array
  -- this could be expanded to check for character varying[] as well
  IF pg_typeof($1)::text = 'text[]' THEN
    arr := $1;
  ELSEIF pg_typeof($1)::text = 'jsonb' THEN
    IF jsonb_typeof($1) = 'array' THEN
      -- check if the array contains strings or objects:
      IF jsonb_typeof($1 -> 0) = 'object' THEN
        -- assuming the key is always name:
        arr := array(select jsonb_array_elements($1) ->> 'name');
      ELSE
        arr := array(select jsonb_array_elements_text($1::jsonb));
      END IF;
    ELSE
      return false;
    END IF;
  ELSEIF pg_typeof($1)::text = 'json' THEN
    IF json_typeof($1) = 'array' THEN
      arr := array(select jsonb_array_elements_text($1::jsonb));
    ELSE
      return false;
    END IF;
  ELSE
    -- no array whatsoever
    return false;
  END IF;

  select count(*) = 0
    into l_result
  from unnest(arr) as x(name)
  where not exists (select *
                    from animals a
                    where x.name = a.animal);

  return l_result;
END;
$$ LANGUAGE plpgsql;

If the JSON array contains unknown key names and you can't use the hardcoded name, you can use this instead:

    arr := array(select x.v
                 from jsonb_array_elements($1) as t(e)
                 cross join jsonb_each_Text(t.e) as x(k,v));

The final select statement checks if all elements in the array exist in the table. By using a NOT EXISTS condition, the statement can stop as soon as one non-existing animal is found.

Online example


How does PostGIS make its data types compatable with Postgres? How did Postgres extend to accommodate PostGIS data types?

I am trying to create a spatial system. All the code is written in C++. I do not want to create separate data type in Postgres using CREATE TYPE command.

Is there a way using bytea or any other stuff that I can directly store my instances in Postgres.

I also have some methods I want to define on the C++ data types, is there a way to achieve this without operator class and CREATE FUNCTION command?

Is there a way using bytea or any other stuff that I can directly store my instances in Postgres.

Yes, sure you can store anything as bytea. However, don't expect the database to know how to work with it. PostGIS is an extensive system that creates types, operators, access methods, indexes, and a slew of other stuff. You'll have to recreate all of that stuff for your type.

Alternatively, you can create something that reads a PostGIS types and experts one of your own types. This would be a C function. you can then create a cast from PostGIS's type, to your type and call all of your stuff -- your library -- working only on your native implementation.

This allows you to use all that is in PostGIS, and then still access all of the functionality you've got at the cost of a cast.


Help with CTE Aggregating Children Recursively I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised of raw materials that can be assembled to form that finished good. So the available to sell is the current available Finished Good (FG) qty plus the minimum of the components that can be made into the FG.

Example:

Say we are selling a laptop kit that is comprised of a laptop and a carrying bag. If we have 2 kits already made, and 6 laptops and 3 carrying bags, our available to sell of these kits is 2 + 3 = 5 total. The carrying bags are the limiting factor in this case. Even though we have 6 laptops, we can only make 3 more kits because of the bags limiting us.

I've gotten this far and the calculations work from the lowest level 2 up to level 1, but level 0 is not correct. So in this case, the calculation for the laptop kit is correct (11 on hand + we can make 4 more = 15 avail to sell). But the top level Laptop & Bag Kit is not correct. The least Avail to Sell of the direct children of the top level (Laptop & Bag Kit) is 15 + 3 of that kit is on hand = 18, not 14.

I'm thinking maybe I need to add a second recursive CTE instead of the left join I have in the final select?

SQL Fiddle

CREATE TABLE Item (
  Id INT,
  ParentId INT,
  DisplaySeq INT,
  DisplayText VARCHAR(30),
  OnHandQty INT
  );

INSERT INTO Item (Id, ParentId, DisplaySeq, DisplayText, OnHandQty) VALUES
(9, NULL, 0, 'Laptop & Bag Kit', 3),
(8, 9, 5, 'Laptop Kit', 11),
(7, 8, 10, 'Laptop', 5),
(6, 8, 15, 'Power Supply', 4),
(26, 9, 20, 'Bag', 23)
;

;WITH items AS (
  SELECT
    Id
    , 0 as ParentId
    , Id as RootId
    , 0 AS Level
    , CAST(DisplaySeq AS VARCHAR(255)) AS Path
    , CAST('---' AS varchar(100)) AS LVL
    , CAST(DisplayText as VARCHAR(255)) as DisplayText
    , OnHandQty
  FROM Item
  WHERE ParentId IS NULL

  UNION ALL

  SELECT
    child.Id
    , child.ParentId
    , parent.RootId
    , Level + 1
    , CAST(parent.Path + '.' + CAST(child.DisplaySeq AS VARCHAR(255)) AS VARCHAR(255)) AS Path
    , CAST('---' + parent.LVL AS varchar(100)) AS LVL
    , CAST(parent.LVL + child.DisplayText as VARCHAR(255)) as DisplayText
    , child.OnHandQty
  FROM
    Item child
      INNER JOIN items parent
      ON parent.Id = child.ParentId
  )

SELECT
  t.Path
  , t.RootId
  , t.Id
  , t.ParentId
  , t.Level
  , t.DisplayText
  , t.OnHandQty
  , COALESCE(s.MaxCanMake, t.OnHandQty) as MaxCanMake
  , t.OnHandQty + COALESCE(s.MaxCanMake, 0) as AvailToSell
FROM
  items t
    left join (
        Select
          ParentId,
          MIN(OnHandQty) as MaxCanMake
        FROM items
        GROUP BY ParentId
      ) as s
      ON t.Id = s.ParentId

ORDER BY t.Path

Unroll the hierarchy into a temporary table first (note the computed column):

CREATE TABLE #Items
(
    Id integer PRIMARY KEY,
    MPath varchar(255) NOT NULL,
    DisplayText varchar(30) NOT NULL,
    OnHandQty integer NOT NULL,
    [Level] integer NOT NULL,
    ParentId integer NOT NULL,
    MaxCanMake integer NULL,
    AvailToSell AS OnHandQty + MaxCanMake
);

WITH Items AS
(
    SELECT
        I.Id,
        MPath = CONVERT(varchar(255), I.DisplaySeq),
        I.DisplayText,
        I.OnHandQty,
        0 AS [Level],
        0 AS ParentId
    FROM dbo.Item AS I
    WHERE I.ParentId IS NULL

    UNION ALL

    SELECT
        I.Id,
        CONVERT(varchar(255), Parent.MPath + '.' + CONVERT(varchar(11), I.DisplaySeq)),
        I.DisplayText,
        I.OnHandQty,
        Parent.[Level] + 1,
        I.ParentId
    FROM Items AS Parent
    JOIN dbo.Item AS I WITH (FORCESEEK)
        ON I.ParentId = Parent.Id
)
INSERT #Items
    (Id, MPath, DisplayText, OnHandQty, [Level], ParentId)
SELECT
    I.Id, I.MPath, I.DisplayText, I.OnHandQty, I.[Level], I.ParentId
FROM Items AS I
OPTION (MAXRECURSION 0);

-- Useful index
CREATE INDEX i
ON #Items (ParentId, AvailToSell);

That gives us:

╔════╦════════╦══════════════════╦═══════════╦═══════╦══════════╦════════════╦═════════════╗
║ Id ║ MPath  ║   DisplayText    ║ OnHandQty ║ Level ║ ParentId ║ MaxCanMake ║ AvailToSell ║
╠════╬════════╬══════════════════╬═══════════╬═══════╬══════════╬════════════╬═════════════╣
║  9 ║ 0      ║ Laptop & Bag Kit ║         3 ║     0 ║        0 ║ NULL       ║ NULL        ║
║ 26 ║ 0.20   ║ Bag              ║        23 ║     1 ║        9 ║ NULL       ║ NULL        ║
║  8 ║ 0.5    ║ Laptop Kit       ║        11 ║     1 ║        9 ║ NULL       ║ NULL        ║
║  7 ║ 0.5.10 ║ Laptop           ║         5 ║     2 ║        8 ║ NULL       ║ NULL        ║
║  6 ║ 0.5.15 ║ Power Supply     ║         4 ║     2 ║        8 ║ NULL       ║ NULL        ║
╚════╩════════╩══════════════════╩═══════════╩═══════╩══════════╩════════════╩═════════════╝

Now compute MaxCanMake per level, starting with the deepest:

DECLARE @Level integer =
(
    SELECT MAX(I.[Level])
    FROM #Items AS I
);

WHILE @Level >= 0
BEGIN
    UPDATE I
    SET I.MaxCanMake =
        ISNULL
        (
            (
                SELECT TOP (1)
                    I2.AvailToSell
                FROM #Items AS I2
                WHERE I2.ParentId = I.Id
                ORDER BY
                    I2.AvailToSell ASC
            ),
            0
        )
    FROM #Items AS I
    WHERE I.[Level] = @Level;

    SET @Level -= 1;
END;

The computed column in the temporary table automatically reflects the change in AvailToSell.

The final display query is then:

SELECT
    DisplayText = REPLICATE('---', I.[Level]) + I.DisplayText,
    I.OnHandQty,
    I.MaxCanMake,
    I.AvailToSell
FROM #Items AS I
ORDER BY
    I.MPath;

╔════════════════════╦═══════════╦════════════╦═════════════╗
║    DisplayText     ║ OnHandQty ║ MaxCanMake ║ AvailToSell ║
╠════════════════════╬═══════════╬════════════╬═════════════╣
║ Laptop & Bag Kit   ║         3 ║         15 ║          18 ║
║ ---Bag             ║        23 ║          0 ║          23 ║
║ ---Laptop Kit      ║        11 ║          4 ║          15 ║
║ ------Laptop       ║         5 ║          0 ║           5 ║
║ ------Power Supply ║         4 ║          0 ║           4 ║
╚════════════════════╩═══════════╩════════════╩═════════════╝

dbfiddle


Tempdb is getting full very quickly in microsoft sql server 2008 I am developing a log analyzer tool and After queering it and
generating reports Tempdb is getting full very quickly .

Is there any solution to clean the tempdb file in microsoft sql server
2008 on a timely basis other than restarting the server ?

Thank you so much in Advance

Tempdb is growing like this because something is using it.  No attempt to fix, clean or manipulate tempdb is going to work as long as this is going on.

To address this problem, you need to find out what is causing it to grow like this and then fix that problem first.  This alone may resolve the whole issue and remove your need to do anything else with tempdb.  In my experience, the most likely cause for something like this is an out-of-control Cross Join query (though there are lots of other possibilities).


Where is the information about what files each database stored for SQL Server 2008 R2 I need to find where SQL Server 2008 R2 stores what files back each database. The kicker is that the server has completely crashed (hardware failure). I've managed to recover all the .mdf/.ldf files.

I need to get a single database up and running on another server asap, but whoever set the server up originally didn't match the database names to the file names very well, so I need to somehow extract the mapping between the two without going through a full restore of SQL Server, i.e. I need to know the names of the files for database XXXXX as it was attached to SQL Server.

Is this possible? Was thinking about attached the recovered master database onto another SQL Server instance, but I cannot seem to find any information about data files in another master database, so it might be a waste of time.

Thanks

SQL Server maintains this in sys.master_files, after you have restored the master database
When SQL Server starts up, you'll see a lot of suspect databases.

You can attach the MDFs and LDFs using the info in sys.master_files


1:M relation exclude row if any row in M table does not contain specific value Consider I have following table products

group   ads status
X       ad1 ACTIVE
X       ad2 NOT_BUYABLE
X       ad3 PAUSED
Y       ad4 ACTIVE
Y       ad5 SUSPENDED
Y       ad6 PAUSED
Z       ad7 ACTIVE
Z       ad8 OUT_OF_BUDGET
Z       ad9 ARCHIVED

All I want is to filter distinct group

For example, I want to filter the group where

the product status != NOT_BUYABLE
AND the product status = ACTIVE

consider Group X:

ad1 = ACTIVE
ad2 = NOT_BUYABLE
ad3 = PAUSED

This filter would ignore Group X

consider Group Y:

ad4 = ACTIVE
ad5 = SUSPENDED
ad6 = PAUSED

This filter would return Group Y

consider Group Z:

ad7 = ACTIVE
ad8 = OUT_OF_BUDGET
ad9 = ARCHIVED

This filter would return Group Z

so final output should be

group
Y
Z

This means the user only wants to filter product that doesn't have any NOT_BUYABLE status values but has at least 1 ACTIVE status.

when I apply != operator I have to make sure none of the product under that group has those staus

This SQL query is wrong

Select  distinct `group`   from products
where status != 'NOT_BUYABLE' and status = 'ACTIVE';

Let me know what will be the SQL query

Plan A:

SELECT a.group
    FROM ( SELECT DISTINCT `group` FROM tbl ) AS a
    WHERE
         EXISTS ( SELECT * FROM tbl WHERE group = a.group
                                      AND status = 'ACTIVE' )
     AND
     NOT EXISTS ( SELECT * FROM tbl WHERE group = a.group
                                      AND status = 'NOT_BUYABLE' )

Plan B:

SELECT a.group,
       GROUP_CONCAT(status) AS stati
    FROM tbl AS a
    GROUP BY `group`
    HAVING      FIND_IN_SET('ACTIVE',      stati)
        AND NOT FIND_IN_SET('NOT_BUYABLE', stati)

(To get rid of the second column, you need to wrap that in another SELECT.)

(Suggest changing your column name away from the keyword "group".)

Recommend this for both queries:

 INDEX(`group`, `status`)

(I don't know which Plan will be faster.)