Update Top 1 Row In SQL Server I know that typically you would have an autonumber field - but in this instance there is not, and it honestly does not matter which row get's updated as long as it is only 1.

I have sample DDL like such one table that contains requested qty, and one table that contains avaliable qty.  I want to update @Test1.amtonhand with the value from @Test2.amtonhand and join on @Test1.nrs=@Test2.nrs but only update the 1 record in @Test1

How is this achieved in SQL Server 2008

    Declare @Test1 Table
(
    nrs varchar(100)
    ,amtrqst varchar(100)
    ,amtonhand varchar(100)
)

Declare @Test2 Table
(
    nrs varchar(100)
    ,amtonhand varchar(100)
)

Insert Into @Test1 (nrs, amtrqst) Values
('abc', '10'), ('abc', '12'), ('abc', '13'), ('def', '5'), ('def', '12')

Insert Into @Test2 (nrs, amtonhand) Values
('abc', '10'), ('def', '5')

EDIT
My desired results from the update would be

@Test1 --
abc, 10, 10
abc, 12
abc, 13
def, 5, 5
def, 12

If you don't care which row gets updated, you can use ROW_NUMBER with an arbitrary order:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY nrs ORDER BY (SELECT NULL))
    FROM @Test1
)
UPDATE a
SET a.amtonhand = b.amtonhand
FROM CTE a
INNER JOIN @Test2 b
    ON a.nrs = b.nrs
WHERE a.RN = 1;

The results are:

╔═════╦═════════╦═══════════╗
║ nrs ║ amtrqst ║ amtonhand ║
╠═════╬═════════╬═══════════╣
║ abc ║      10 ║ 10        ║
║ abc ║      12 ║ NULL      ║
║ abc ║      13 ║ NULL      ║
║ def ║       5 ║ 5         ║
║ def ║      12 ║ NULL      ║
╚═════╩═════════╩═══════════╝


Migration path to unify column names in backward compatible way We have several naming schema for columns (like id/key or created/creation_date/create_date) and want to unify it.

Also we want to do it in backward compatible way. We want to be able to run old code together with new one until we get rid of old code, so following:

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;

is a breaking change.

Is there some form of aliasing for columns like views for tables (for Postgresql)?

ANSWER @CL (tnx for tip!) https://www.postgresql.org/docs/12/ddl-generated-columns.html

  A generated column cannot be written to directly. In INSERT or UPDATE commands, a value cannot be specified for a generated column, but the keyword DEFAULT may be specified.

So if an idea is to keep columns until you update all report script - it is fine. But if you want a rollback to previous app version - you are out of luck ((

Currently we are on Google Postgres Cloud offer: https://cloud.google.com/sql/docs/postgres/db-versions and v12 is in beta only.

There is no easy way to do this, but the only way I can think of, is to use a layer of views to keep the old names.

Create two schemas e.g. "old" and "new" or something alike.

The "new" schema contains the real tables with the new column (and maybe table) names. Have applications that can deal with that set the search_path to only the new schema.

For every table that has renamed columns (or if the table itself got renamed), create a view in the "old" schema with the old names, e.g.

create view old.table_name
as
select new_column_name as column_name,
       some_other_column
from table_name;

This view will be automatically updateable without the need to write triggers for it.

Make applications that can only deal with the old names set their search_path to old, new. An unqualified reference to table_name will then use the view old.table_name with the columns that the code expects. As the view is updateable, this will work for DML just as well.

Obviously this won't work if the application code checks for the presence of real tables for some reason. Or tries to detect constraints for those tables.

It also requires that you can change the search path when connecting to the database (e.g. when using JDBC, you can achieve this by passing it in the connection URL)


Why can members of db_datareader SELECT sys views In SQL Azure my understanding of db_datareader is that it is allowed to SELECT all user tables.

I couldn't find a definition of exactly what a user table is, but I assumed it was all of the non-builtin tables and views, and excluded all the views and tables that are in the sys schema.

Why then can members run the following statements?

SELECT TOP (5) * FROM [sys].tables

SELECT TOP (5) * FROM [sys].[all_views];

SELECT * FROM sys.dm_exec_sessions;

SELECT TOP (5) * FROM [sys].objects

... and more

Metadata visibility configuration doesn't work in the same was as permissions for User tables - the rights are included implicitly whenever rights to underlying objects are granted.

Take the following example from the docs:

SELECT name, object_id
FROM sys.tables
WHERE name = 'myTable';
GO

The above returns no results for users that have been granted no rights on those underlying tables. It therefore follows that if you grant db_datareader (IE SELECT on all tables / views), you're also implicitly granting access to sys.tables for all Tables / Views in that database.

This is true for all GRANT states (IE DENY won't cause the user to see metadata for related objects, but GRANT will)

In summary, granting access to a user object also grants access to the metadata for that object, exposed via the system catalog.


Estimated rows very wrong when using declared variables I am getting very, very wrong estimates for the clustered index seek step of  this query (estimated rows is 8,637,530; actual is 74,723):

Declare @StartDate as date = '10/1/2014';
Declare @EndDate as date = '10/2/2014';

select Sum(Quantity)
from Daily_GC_Items
where SalesDate between @StartDate and @EndDate;

If I use string literals for the dates like this, the estimate is almost perfect (estimated rows is 75,337.4; actual is 74,723):

select Sum(Quantity)
from Daily_GC_Items
where SalesDate between '10/1/2014' and '10/2/2014'

Table definition:

[SalesDate] [date] NOT NULL,
[Store] [varchar](10) NOT NULL,
[GuestCheckNumber] [smallint] NOT NULL,
[ItemSequence] [smallint] NOT NULL,
[Quantity] [smallint] NOT NULL,
(Several more columns)

Primary Key:

[SalesDate] ASC,
[Store] ASC,
[GuestCheckNumber] ASC,
[ItemSequence] ASC

What are the possible causes for this behavior?

...SSMS is not running this using sp_execute so I don't think this is being caused by parameter sniffing. What are the possible causes for this behavior?

The optimizer cannot 'sniff' the values of local variables, so the cardinality estimate is based on a guess. If you are using the original cardinality estimator, the fixed guess for BETWEEN is 9% of the table's cardinality.

If using the new cardinality estimator, the guess is computed using exponential backoff, applied to the separate >= and <= comparisons that BETWEEN is shorthand for. The guess for >= and <= is 30% (0.3) each, so the overall guess is 0.3 * SQRT(0.3) = 0.164317 (* table cardinality).

You have to decide between caching and reusing a reasonable plan for all future variable values, or recompiling on each execution to generate a new plan for the specific values each time. The second option makes use of the Parameter Embedding Optimization, which is only applied if you use the query hint OPTION (RECOMPILE). There is an obvious trade-off to be made here, between the cost of recompiling each time (the resulting plan is never cached for reuse) and using a not-quite-optimal reused plan for each execution.

There are additional options for you to consider, including specifying typical values in an OPTIMIZE FOR hint; see my SQLPerformance.com article for details.


SQL Server 2012 - The application has failed to start because its side-biy-side configuration is incorrect When opening Sql Server Management Studio I'm getting the following error message:

Then when I have a look at the Event Viewer the following message is displayed related to this:

Activation context generation failed for "c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe". Dependent Assembly debuggerproxy.dll,processorArchitecture="X86",type="win32",version="1.0.0.0" could not be found. Please use sxstrace.exe for detailed diagnosis.

Also the Installer won't work.

Any solution for this please ?

Thanks

Open an elevated command prompt (Run As Admin)
Type sfc /scannow

The sfc /scannow command will scan all protected system files, and replace corrupted files with a cached copy that is located in a compressed folder at %WinDir%\System32\dllcache. The %WinDir% placeholder represents the Windows operating system folder.


Error with FUNCTION ( SQL Server 2008 ) - ''Cannot find either column...' I have this Function:

CREATE FUNCTION [dbo].[fn_xxx]
    (
    @Nome VARCHAR(100),
    @Descricao VARCHAR(500)
    )
RETURNS @tbDados TABLE
    (
        Qtd INT
    )
AS
BEGIN
    DECLARE @CMD AS NVARCHAR(MAX);
    DECLARE @Tabela VARCHAR(100);
    DECLARE @CampoDescricao VARCHAR(100);

    --- *** Identifica os campos e tabelas
    SET @Tabela = 'tbl' + @Nome;
    SET @CampoDescricao = 'Desc' + @Nome;

    SET @CMD = '
    INSERT INTO @tbDados (Qtd)
    SELECT @Qtd = COUNT(*)
    FROM ' + @Tabela + '
    WHERE ' + @CampoDescricao + ' = ''' + replace(@Descricao, '''', '`') + ''';';

    EXEC sp_executesql @CMD;

    RETURN
END

I'm trying to help developers here, but with no success.

When we execute it ( select dbo.fn_xxx ('processador','teste')) it give us this error:

  Cannot find either column "dbo" or
  the user-defined function or aggregate "dbo.fn_xxx", or the name is
  ambiguous.

I've been searching through the internet, and found some results, but it was all about the code.

can someone help me how to figure out this error in this function?

Thanks

EDIT:

If i execute it with:

SELECT * FROM [EasyTablet].[dbo].[fn_xxx] (
    'processador','teste'
  )
GO

It shows me this error:

  Only functions and some extended stored procedures can be executed
  from within a function.

You can't use dynamic SQL inside a function (well, aside from this awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing it).

It's unclear to me why you're doing this as a multi-statement TVF in the first place. You're returning one column and one row. Why not use a stored procedure with an output parameter?


The right way to model multiple FACTs schema Background
I'm in a process of designing a database (using a STAR schema).
There are three tables to model: products, tests, states.
The database will be used to store results of tests conducted on products (in a great simplification). There can be many tests pointing out to a single product but each test is unique (they are not shared among products). Besides, I need to record the current state of the product, at the time when the test was conducted. Let's assume that the state of a product describes its current location and owner, which are changing very often. That will most likely involve SCD lvl 2 - to track the history of state changes and to be able to locate a product with all its tests as well as the states it had during these tests.
Problem
I'm not entirely sure how to model this problem. It seems obvious to store every test in a FACT table. This table would then consist of thousands of transactions. On the other hand, there will also be hundreds (and later thousands) of products, so I should probably keep them in a second FACT table. Then, there will also be thousands of state changes, so in order to record their entire history, I would need to keep them in a ... FACT table as well? I've been told that FACT tables are typically used to store multiple-rows data but on the other hand where are the DIMs in this model?
I also don't know how to model the relationships between these tables. Products - states is a 1:* relationship. Products - tests is a 1:* as well. Finally, states - tests is a 1:* too. I would then go with linking products to states and then states to tests (products 1<-* states 1<-* tests), what would allow me to find all states for a particular product and all tests (in all states or in a selected state). What do you think about that? The problem here is that, as I keep adding states, I have two options: either keep duplicating products in the products table (with added "recorded_timestamp" column) or use a SCD lvl 2 in states table, pointing out to the products table with a FK, but this would effectively make the product table a DIM!
Any help here would be very appreciated.

So the first question is: Are you absolutely sure you need to use a star schema?  Having worked in a number of warehouses built to varying levels of quality, I have found star schema to be: 1. Inflexible 2. Difficult to use (tons of joins) 3. Really bad for time series.
The way you have described things (Product, Product State, Test) along with the need to track points in time would generate something like this (slight embellishment for party/test):

If you need to get the state as of a certain test this is attainable by a simple join, which can be encapsulated in a view so users don't have to worry about getting the logic correct:
SELECT
  ProductTest.ProductId
 ,ProductTest.TestDtm
 ,ProductTest.TestCd
 ,<Other relevant columns>
 ,ProductState.OwnerId
 ,ProductState.<Location info> --Don't know if this is address or lat/long, use whatever works best for you
FROM
  ProductTest ProductTest
LEFT JOIN
  ProductState ProductState
    ON ProductState.ProductId = ProductTest.ProductId
         AND ProductState.UpdateDtm =
               (
                 SELECT
                   MAX(UpdateDtm)
                 FROM
                   ProductState
                 WHERE
                   ProductId = ProductTest.ProductId
                     AND UpdateDtm <= ProductTest.TestDtm
               )


Determine the origin IP address of queries against a table? I have a table that is being updated by a service being run at an unknown location.

I've checked all of the servers that normally communicate with my database server and none of them appear to have the service in question running.

I am trying to track down where this service is running, and I'm wondering if it is possible for me to see where queries that affect a particular table originate from?

Is this possible?

Simplest method is to use a trigger and log the IP address from sys.dm_exec_connections. You'll need a logging table; I've guessed at a couple of columns you'll want, but you may want others.

CREATE TABLE dbo.LoggingTable
(
    LoggingID INT NOT NULL
        CONSTRAINT PK_LoggingTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AppName SYSNAME
    , HostName SYSNAME
    , ClientNetAddress VARCHAR(255)
    , LogDateTime DATETIME
);
GO

CREATE TRIGGER dbo.triggername
ON dbo.tablename /* name of the target table  */
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  INSERT dbo.LoggingTable(AppName, HostName, ClientNetAddress, LogDateTime)
  SELECT app_name(), host_name(), client_net_address, SYSDATETIME()
  FROM sys.dm_exec_connections dec
  WHERE dec.session_id = @@SPID;
END
GO

Note that if the update is coming from a connection on the same machine ("The call is coming from inside the house!"), and using shared memory, you'll get <local machine> as the IP address.

Once you've figured it out, don't forget to drop the trigger.

More information about the columns in sys.dm_exec_connections


Clarification on UNION ALL of JSONB_EACH result This function used to merge 2 JSONB and delete certain key when the value is null, refer to this question.

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE key NOT IN (SELECT key FROM json_union WHERE value ='null');
$$ LANGUAGE SQL;

First question, why value = 'null' works, but value IS NULL is not?

Second question, is there any bad reason to use WHERE value <> 'null' instead of WHERE key NOT IN (SELECT key FROM ...) ?

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> 'null';
$$ LANGUAGE SQL;

Third question, why this function also incorrect (always returns NULL)? Is it because UNION ALL change all resulting record into TEXT?

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> (NULL::JSONB)
$$ LANGUAGE SQL;

-- check function:
SELECT COALESCE(jsonb_merge('{}','{"b":null}'),'{"x":"its empty"}'::JSONB) FROM xxx;
      coalesce
--------------------
 {"x": "its empty"}

Question 1
From PostgreSQL Documentation Table 8.23 - JSON primitive types and corresponding PostgreSQL types:

JSON primitive type | PostgreSQL type | Notes
null                |(none)           | SQL NULL is a different concept

So you must not confuse SQL NULL value with with JSON null type.

Question 2
In your previous question you wanted the JSON value null to act as a kind of DELETE type. If you have both {"b": 3} and {"b": null} in the UNION and you do value <> 'null' the {"b": 3} will not be deleted. When you do NOT IN the pair b:3 will be left out of the new set too (the null type is found for the key "b" in the subquery).

Question 3
Related to question 1: NULL::JSONB means a SQL NULL value of the data type JSONB, not a JSON null type.


Does SQL server log when and who enabled / disabled jobs by default? Is there any default logging or history of when a SQL job was enabled or disabled?

Simple answer would be no. I don't think there is any system table that captures or logs this change. However you can find the details from last modified date of sql job for any change.

Please refer to some great insights as answered  here by Aaron and Crafty DBA. This should help for future reference.


renaming a sql server with installed named instances We have a hosted server that is named, by the hosting company, as something like WinEnt99. We would like to rename it to something more meaning like OurMonitor1.

We have already installed a few instances of SQL Server 2008 R2.

Is there anything that we need to (re)configure in SQL Server / SSMS ?

I have an application that was reading the database settings from the registry, and connecting the database fine. Now, it won't connect to the database. The only thing that I can think of is that we renamed the server last week sometime.

EDIT 02 Oct 2012:-
The Server is being renamed. The named instances are not.

I've run

sp_helpserver
select @@servername

Which showed the old server name,
and

sp_dropserver 'Old Server Name\Instance_name'
go
sp_addserver 'New Server Name\Instance Name','local'
go

Then confirmed the details in

sp_helpserver
select @@servername

You are only allowed to change the server name or hostname of a server running SQL server. Microsoft does not support renaming the instance without a reinstall. So if you have a default instance on the server that is referenced by the hostname you can rename the "instance" in that manner. However if you are wanting to go from "hostname\instance1" to "hostname\instance2" you will have to reinstall SQL Server.


Query with custom sorting I have a table structure like below:

CREATE TABLE `sample` (
  `id` int(11) NOT NULL,
  `is_duplicate` tinyint(1) NOT NULL DEFAULT '0',
  `duplicate_with` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `sample` (`id`, `is_duplicate`, `duplicate_with`,`name`) VALUES
(1, 0, 0,'ABC'),
(2, 0, 0,'PQR'),
(3, 1, 1,'XYZ'),
(4, 1, 2,'PPP');

The is_duplicate column is an indicator of whether the row is a duplicate of another row (1) or is a master row (0). If it is a duplicate, the duplicate_with column will hold the ID of the master row of which this one is a duplicate.

Now I want output like below:

3  1  1  XYZ
1  0  0  ABC
4  1  2  PPP
2  0  0  PQR

I want to list rows in such a way that duplicates of the same master row are arranged together and followed by their master row, while the groups themselves should be sorted by master row ID.

In the above example, there are two row groups. The first group includes row 3 and its master row, row 1, which goes after its duplicate. In the same way, the second group consists of rows 4 and 2, row 2 being the master row and following its duplicate.

How can I achieve such a result?

The easiest way is to hold reference to itself in the duplicate_with for the master records while their is_duplicate fields contains zero. Then all the group (master and all its slaves) can be extracted by duplicate_with field value:

SELECT *
  FROM sample
 ORDER BY duplicate_with ASC
        , is_duplicate DESC
        , id ASC
;

If you can't for some reason update master records then you can do the same by joining table with itself:

SELECT z.id AS id
     , z.is_duplicate AS is_duplicate
     , z.duplicate_with AS duplicate_with
     , z.name AS name
  FROM sample AS w
  JOIN sample AS z ON z.duplicate_with = w.id
                   OR z.id = w.id
 ORDER BY w.id ASC
        , z.is_duplicate DESC
        , z.id ASC
;

Sure that approach can be significantly slower on the big tables.


MAXDOP setting algorithm for SQL Server When setting up a new SQL Server, I use the following code to determine a good starting point for the MAXDOP setting:

/*
   This will recommend a MAXDOP setting appropriate for your machine's NUMA memory
   configuration.  You will need to evaluate this setting in a non-production
   environment before moving it to production.

   MAXDOP can be configured using:
   EXEC sp_configure 'max degree of parallelism',X;
   RECONFIGURE

   If this instance is hosting a Sharepoint database, you MUST specify MAXDOP=1
   (URL wrapped for readability)
   http://blogs.msdn.com/b/rcormier/archive/2012/10/25/
   you-shall-configure-your-maxdop-when-using-sharepoint-2013.aspx

   Biztalk (all versions, including 2010):
   MAXDOP = 1 is only required on the BizTalk Message Box
   database server(s), and must not be changed; all other servers hosting other
   BizTalk Server databases may return this value to 0 if set.
   http://support.microsoft.com/kb/899000
*/

DECLARE @CoreCount int;
DECLARE @NumaNodes int;

SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i);
SET @NumaNodes = (
    SELECT MAX(c.memory_node_id) + 1
    FROM sys.dm_os_memory_clerks c
    WHERE memory_node_id < 64
    );

IF @CoreCount > 4 /* If less than 5 cores, don't bother. */
BEGIN
    DECLARE @MaxDOP int;

    /* 3/4 of Total Cores in Machine */
    SET @MaxDOP = @CoreCount * 0.75;

    /* if @MaxDOP is greater than the per NUMA node
       Core Count, set @MaxDOP = per NUMA node core count
    */
    IF @MaxDOP > (@CoreCount / @NumaNodes)
        SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75;

    /*
        Reduce @MaxDOP to an even number
    */
    SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);

    /* Cap MAXDOP at 8, according to Microsoft */
    IF @MaxDOP > 8 SET @MaxDOP = 8;

    PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max));
END
ELSE
BEGIN
    PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.';
    PRINT 'This is the default setting, you likely do not need to do';
    PRINT 'anything.';
END

I realize this is a bit subjective, and can vary based on many things; however I'm attempting to create a tight catch-all piece of code to use as a starting point for a new server.

Does anyone have any input on this code?

The best way to do is -- use coreinfo (utility by sysinternals) as this will give you
a. Logical to Physical Processor Map
b. Logical Processor to Socket Map
c. Logical Processor to NUMA Node Map as below :

Logical to Physical Processor Map:
**----------------------  Physical Processor 0 (Hyperthreaded)
--**--------------------  Physical Processor 1 (Hyperthreaded)
----**------------------  Physical Processor 2 (Hyperthreaded)
------**----------------  Physical Processor 3 (Hyperthreaded)
--------**--------------  Physical Processor 4 (Hyperthreaded)
----------**------------  Physical Processor 5 (Hyperthreaded)
------------**----------  Physical Processor 6 (Hyperthreaded)
--------------**--------  Physical Processor 7 (Hyperthreaded)
----------------**------  Physical Processor 8 (Hyperthreaded)
------------------**----  Physical Processor 9 (Hyperthreaded)
--------------------**--  Physical Processor 10 (Hyperthreaded)
----------------------**  Physical Processor 11 (Hyperthreaded)

Logical Processor to Socket Map:
************------------  Socket 0
------------************  Socket 1

Logical Processor to NUMA Node Map:
************------------  NUMA Node 0
------------************  NUMA Node 1

Now, based on the above info, the Ideal MaxDop setting should be calculated as
a.  It has 12 CPU’s which are hyper threaded giving us 24 CPUs.
b.  It has 2 NUMA node [Node 0 and 1] each having 12 CPU’s with Hyperthreading ON.
c.  Number of sockets are 2 [socket 0 and 1] which are housing 12 CPU’s each.

Considering all above factors, the max degree of Parallelism should be set to 6 which is ideal value for server with above configuration.

So the answer is -- "it depends" on your processor footprint and the NUMA configuration and below table will summarize what I explained above:
8 or less processors    ===> 0 to N (where N= no. of processors)
More than 8 processors  ===> 8
NUMA configured         ===> MAXDOP should not exceed no of CPU’s assigned to each
                                 NUMA node with max value capped to 8
Hyper threading Enabled ===> Should not exceed the number of physical processors.

Edited: Below is a quick and dirty TSQL script to generate Recommendation for MAXDOP setting
/*************************************************************************
Author          :   Kin Shah
Purpose         :   Recommend MaxDop settings for the server instance
Tested RDBMS    :   SQL Server 2008R2

**************************************************************************/
declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int

select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
    ,@HTEnabled = case
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);

select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64
group by parent_node_id
option (recompile);

select @NoOfNUMA = count(distinct parent_node_id)
from sys.dm_os_schedulers -- find NO OF NUMA Nodes
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64

-- Report the recommendations ....
select
    --- 8 or less processors and NO HT enabled
    case
        when @logicalCPUs < 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
                --- 8 or more processors and NO HT enabled
        when @logicalCPUs >= 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : 8'
                --- 8 or more processors and HT enabled and NO NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA = 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
                --- 8 or more processors and HT enabled and NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA > 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
        else ''
        end as Recommendations

EDIT: For future visitors, you can look at test-dbamaxdop powershell function (along with other extremely helpful DBA functions (ALL FREE !!).


How to rotate the CMK of Always Encrypted using SSMS? I have implemented the encryption in SQL Server 2016 using Always Encrypted.

I want to know how to rotate the column master key after the initial one year validity using SQL Server Management Studio 17.0.

I'm looking for the steps that can executed using only SSMS and not PowerShell or any other program.

Is it technically possible to rotate the key only using SSMS?

Experts advice please.

If you look at ALTER COLUMN ENCRYPTION KEY, e.g. at MS:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-column-encryption-key-transact-sql

There are options to both ADD a Column Master Key (start rotation) and DROP (finish rotation).


Eliminate database fragmentation (export, drop and re-import tables) I have one datafile that is highly fragmented. Its size is 10GB now and real size that is used is 2.5GB,so 7.5 is free.

I decided to export data, drop that datafile, recreate it and import data.

What should I mention? Is there any chance to loose some objects?? After dropping datafile?

I have one tablespace, with one datafile. Please provide me with any suggestions about exporting scripts.

There is an option:

TABLESPACES          list of tablespaces to export

if I write script like that

exp username/aaa file=a.dmp tablespaces=my_tb_name log=a.log grants=y indexes=y constraints=y triggers=y

is it enough to import everything from that tablespace and anything that is related to the containing objects(I mean grants, constraints, triggers...)

And is it necessary to be on a database server not on a client to run the export ?

Thank you a lot, waiting you clever suggestions

Instead of export/import, you should use

alter table t shrink space;

which is much less dangerous & keeps the table even accessable during the reorganization. After the shrink, you may use below script (originally from Tom Kyte) to find out to what size you can resize (making them smaller) the datafile(s):

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/


Removing Components from a backup Ok, I will start this off by: Yes, I know I should have paid more attention.  That being said, I was trying to create a full backup of my DB prior to doing truncation on a number of the tables.  When I did this, I failed to carefully inspect the destination section of the wizard.  I ended up placing my full backup inside of a *.trn Transaction Log file.  I can see it if I inspect the contents, but is there any way to remove just the Full Backup component, leaving the Transaction Log component intact?  I only ask this because the full backup is 63 GB.  I don't want to delete the existing Transaction Log in that file if I can avoid it.

No, there isn't a way to separate the two.

You'll have to either hang on to the large file, or take a new full backup and start moving forwards from there.


Update all rows from a table with random foreign key from another table I have four tables: (show on image below)

Topic whit columns:

ID

primary key
int

CategoryID

references to table Category
int

UserID

references to table User
nvarchar(128)

User with column:

ID

primary key
nvarchar(128)

Category whit column

ID

primary key
int

UserCategoryLink with columns

UserID

primary key
references to table User
nvarchar(128)

CategoryID

primary key
references to table Category
int

I will to update table Topic so that the CategoryID is one of the CategoryID's of the table UserCategoryLink.

P.S.: I use MS SQL Server.

3th update: Like you can see on this SQL Fiddle, the category from the topics form every user is not included into the table UserCategoryLink but it must! So can Marc only have topic whit category cycling and snowboarding and not from moto and swimming. Now I will to update all the wrong (correct can also be updated) rows to an ID from the column CategoryID from the table UserCategoryLink.

1st update: I have try the following code

;WITH rs AS
(
    SELECT UserID as Sid, CategoryID as Cid
    FROM UserCategorieLink
)
update Topic
    set CategoryID = (select top 1 Cid from rs where UserID = Sid)
    where UserID = (select top 1 Sid from rs where UserID = Sid);

But the result is not random chosen by the favorite categories for every user. I know it comes from top 1 but it is because I don't know how to generate random numbers.

I will take a random row from a select statement (see code below / like I can do in a programming language like C#) but it gives me an error because RAND generates a random number between 0 and 1.

RAND(select top 1 Cid from rs where UserID = Sid)

The errors are:

  Incorrect syntax near the keyword 'select'

and

  Incorrect syntax near ')'.

on the 7th line.

2nd update: The code give me also the same random number for each topic that comes from the same user. Can that also be a random number? I know that every user has always two favorite categories. The query must pick one of them.

I know that it comes from the where clause of the update statement. But how can I fix it?

Not going to go through what you're trying to accomplish, but for your update, you're using a TOP without an ORDER BY.  While SQL Server doesn't guarantee ordering for such queries, it doesn't just randomly choose, either.  If you want to force a random row to be returned, you'll need to order by a row-level random number.  For that, you can use ORDER BY ABS( CHECKSUM( NEWID() ) ).

Try it out against sys.objects to see the behavior of explicitly ordering randomly.

SELECT  TOP 1 name
FROM    sys.objects
ORDER BY ABS( CHECKSUM( NEWID() ) );

And good luck with whatever it is you're doing.

Edit:

@jean points out in the comments just NEWID() is all that is necessary, which is 100% correct, I just muscle-memory the ABS( CHECKSUM() ) since I tend to need row-level randomization with a % operator.

Edit 2:

So something like this, then?

UPDATE  t
    SET CategoryID = nt.CategoryID
FROM    dbo.Topic t
INNER JOIN (    SELECT  t.ID, ucl.CategoryID,
                        Ordinal = ROW_NUMBER() OVER (
                            PARTITION BY t.ID
                            ORDER BY NEWID() )
                FROM    dbo.Topic t
                INNER JOIN dbo.UserCategoryLink ucl
                    ON  t.UserID = ucl.UserID ) nt
    ON  t.ID = nt.ID
WHERE   nt.Ordinal = 1;


Third Normal Form. Multiple attributes determine another? Concerning 3NF. If you have a composite non-key determinant to a single non-key dependent, does this violate 3NF? Everything I read on transitive dependencies says one non-key determining another non-key, but does not address the composite aspect.

Example: Ingredient(IngredientID (PK) —> Name, Supplier, Classification, UnitOfSale, UnitPrice)

A single supplier supplies an ingredient with a specific name and classification. So, (Name, Classification) --> Supplier. Does this violate 3NF?

Thank you for your time and thoughts.

A relation is in 3NF if for every non-trivial functional dependency, every determinant is a superkey, or, if it is not a superkey, every determinate is a prime attribute (i.e. it is part of a candidate key).

In your example, (Name, Classification) --> Supplier violates the 3NF if and only if the following two conditions are both satisfied:

(Name, Classification) is not a candidate key.
Supplier is not a prime attribute.

For the truth of the two conditions, you have to know either a set of Functional Dependencies which is a cover of all the FDs of the relation (and from those one can find all the candidate keys, and consequently, all the prime attributes of the relation), or, by knowing the meaning of the data, you know that they are true (for instance, you know that a couple of values Name, Classification can appear multiple times inside the relation, and that no candidate key exists that include Supplier).


Log all than queries taking more than X seconds I have a database Microsoft SQL Server 2008r2. I have some applications in different programming languages.

I would like to log all the queries that take more than X seconds.

I guess that the I should create an event session, but I don't understand them enough to do this specific task. The ideal answer would include a link to an online tutorial with examples on event sessions; even a book recommendation would go.

I did the training 6231A "Maintaining a Microsoft SQL Server Database" at Microsoft few years ago, but I have not practiced since; the courseware covers the argument but I cannot understand it.

The syntax you're looking for will be very similar to this:

create event session longrunning_statements on server
add event sqlserver.sql_statement_completed
(
where (duration > 5000000)
)
add target package0.asynchronous_file_target
(Set filename='c:\capture\xe_longrunning_statement.xel',
metadatafile='c:\capture\xe_longrunning_statement.xem');

alter event sesession longrunning_statements on server state=start;

This will set up an event session which will track an sql statement that takes longer than 5 seconds to complete. The duration filter is based on microseconds btw.

If you wanted to restrict this to a specific database (in this case the db with database id=5, then you could change the filter to:

where (duration > 5000000 and database_id=5)

Lots of other filters as well to tie it down to specific sessions or users if required.

This event session will log it's results out to a file (c:\capture\xe_longrunning_statement.xel) which can be read in with the fn_xe_file_target_read_file function like so:

SELECT *
    FROM
        sys.fn_xe_file_target_read_file
        (
            'c:\capture\xe_longrunning_statement*.xel',
            'c:\capture\xe_longrunning_statement.xem',
            null,
            null
        );

(Note that I've used 2008R2 standards for tge xel/xem files and setting up the target. It's slightly different in 2012, but this syntax would still work). The * in the filename is to let the function know you want to load up all of the files if they've rolled over due to size.

You'll then need to parse the data out through your XML tools of choice.

Once you're done with the capture, you can tidy up as follows:

alter event session longrunning_statements on server state=stop;
drop event session longrunning_statements;

One of the best online resources is Jonathan Kehayais' 31 days of Exteneded Events - https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/ - which walks you through the topic with plenty of examples.


What type of datastore should I use for high volume time series data? Imagine a project which requires to be able to process 50,000+ (tiny ints) writes per second. The data, once inserted, won't change. The data is a measurement on a particular time. Sub second resolution is not required (two measurements of the same device can be a second apart).

We are willing to buy/rent more hardware but not more expensive hardware (no fancy RAID etc).

The data is ordered in sets (per device), a couple of hundreds. And should thus be shown/graphed. There is no relation between devices but we do want to group them (showing, for example, 10 graphs).

The only other requirement is that the datastore is open source and *nix based and has reasonable (community) support.

What kind of datastore would you use?

A 'data logging' approach to the problem
For anything involving 50,000 inserts a second you will need to batch your inserts.  If you have to run it on cheap hardware then you need to look at the efficiency of your I/O.
If you assume:

Up to the second latency is not necessary (i.e. batched writes are acceptable)

You have no requirement for analytics within the application except your graphs by device.

Any further analytic requirements can be met by batching data into an external system

Some administrative overhead is acceptable in adding devices to the system (i.e. they have to be registered).

A polled once per second read is acceptable - incoming items can be batched and recorded on a per-second basis with a null recorded if a datum is not produced by a given device within that period.

Then you can store one row per second with all the devices de-normalised into an array of readings.  The application maintains a registry of offsets into this array by device.  As you add more devices the BLOB simply expands.
This changes your problem to one of storing one 50K-ish BLOB per second indexed on the time, which can be done by pretty much any DBMS platform that supports BLOBs.  You might even be able to use a key-value pair system such as Berkely DB.
50K per second is 180MB per hour, 4.3GB per day and approximately 1.5TB per year.  This should be possible to manage even with fairly modest hardware.  Depending on how much you need to archive you can periodically clear down historical data.  You will need something that supports partitioned tables to do this efficiently, though.
Getting the data back out
One disadvantage of this approach is that you would have to read your entire data set to query the statistics for any given device, which would imply scanning 4GB of data for a single day.  If you need to support a lot of users querying the devices on an ad-hoc basis you will need to find a way to supplement this store with a fast querying capability.  Some possibilities for this are:

One hour's data is approximately 180MB in memory.  Cache a rolling period of a few hours - either within your application, or in leading partitions on a fast store (maybe a SSD) that can be archived off on an hourly basis.If your applications that displayed the graphs queried this on a rolling basis (i.e. only got the most recent data) then this structure could be keyed on time and still support a significant query workload.You may need to benchmark the BLOB reader performance of your platform to judge whether you need to build additional caching within your middle-tier, although you will almost certainly need a middle tier server application of some description to avoid shipping the entire BLOB record out to the client.

If you only expect to monitor a small subset of sensors at any given time, then you may be able to run caching on a per-sensor basis, with the cache living for some period (e.g. 10 minutes) after the last read for that sensor.  The main cache would push values out into the per-sensor cache until the per-sensor cache expires.  Per-sensor caching could be implemented fairly efficiently as a ring buffer of 1-2 hours (or whatever period seems appropriate) worth of sensor readings with supplementary data for the beginning and end periods.  A time period should then resolve into an offset within the ring buffer.  In this case the memory overhead per sensor amounts to a few bytes of header information.

Calculate aggregates of average readings for each minute, 10 minutes, hour or suitable period, and store those in a supplementary table.  Graphing data over longer time periods can use the aggregates.

Take the aggregate values and un-pivot them into a dimensional structure in a supplementary data mart (i.e. with time and sensor ID dimensions).  This can be done on (for example) a nightly basis.  Ad-hoc queries on historical data can come from the data mart.

Some combination of these approaches will allow quick access to near-realtime data with a fair amount of read traffic.  Even if you have to cache this with your application and write something hand built to do it.
Administrative overhead
A sensor will have to be registered with the system and given an ID (essentially the ID is - or maps to - an array index within the BLOB).  As the BLOB is opaque the database doesn't need to care about columns.  A KVP store that can deal with BLOBs efficiently may be sufficient.
A sensor can be registered with the system, which will then start recording data.  You may need to explicitly record start/end dates of sensor connections, and maintain metadata that links sensor IDs to offsets within the array.  You will need to recycle array slots as it's a static structure.  Any middle-tier caching will have to be aware of this as well.
Message queues
You may wish to consider an asynchronous architecture using a transactional queue manager such as RabbitMQ to manage spikes in the read workload with the regular writes.  The logging process and a query fronting process would collect requests to be processed in a batch queue.  This would smooth out spikes in the system load.  If you anticipate a significant ad-hoc query workload you may wish to look into this.
Pros and Cons - summary
This structure is optimised to minimise the storage space requirements, which should in turn minimise the I/O overhead on the system and allow it to run on relatively cheap hardware - in particular hardware with relatively cheap storage subsystems.  In principle an ordinary PC should be able to handle the write workload from the sensors, although you may need faster disk hardware or SSDs to cope with any substantial query workload.
However, this architecture requires a fair bit of plumbing to get the data back out.  The Sensor ID cannot be indexed in the database, as it is just an offset into an array of sensor readings.1  This means an entire periods worth of data must be read to get a single sensor reading out.  The querying functionality may therefore require a fairly sophisticated caching function in the middle tier to get decent performance, so this approach comes at the price of some development overhead.
1 This is an egregious violation of first normal form, although a relational store may not be necessary or even desirable for this application.


Welcome message for oracle schema I want to display welcome message when ever a oracle schema try to login database using TOAD or PLSQL developer only

HOWTO configure the banner

In the sqlnet.ora file on the server, set the values for:

SEC_USER_AUDIT_ACTION_BANNER
SEC_USER_UNAUTHORIZED_ACCESS_BANNER

You may have to restart the database for the changes to take affect.

Application Requirements

The application must:

use the OCI driver
fetch the banner (using OCI calls)
display the banner

  Oracle Call Interface (OCI) applications can make use of OCI features to retrieve this banner and display it to the user.

The quote is from Oracle Documentation.

Servers cannot force clients to do something.

Will it work with ...?

SQL Plus - Yes.  See Sam's post
SQL Developer - Yes.  See Sam's post
PLSQL Developer - unknown.  You'll have to use the OCI driver and try it.
TOAD - unknown.  You'll have to use the OCI driver and try it.


Setting constraint so that new value in “Table A” must already exist in “Table B”, else return error? I'm trying to set up some kind of constraint so that I can only enter values in "Table A", if that value exists in "Table B".

I want this to act sort of like a selection list, where there are only a few values that exist in "Table B", which then restricts the values that can be entered into "Table A". (Kind of like a drop down list/ selection list)

I'm using pgAdmin 4 to access a PostGIS DB

Sounds like you want a FOREIGN KEY constraint!

You should do something like this:

CREATE TABLE B (primary_key_value INTEGER PRIMARY KEY... other fields);

CREATE TABLE A (primary_key_field PRIMARY KEY, foreign_key_value INTEGER, other fields...
CONSTRAINT 'name_of_fk_constraint'
FOREIGN KEY (foreign_key_value) REFERENCES B (primary_key_value);

If you try to INSERT a record into table A and the foreign_key_value field doesn't contain the value you're trying to insert in table B's PRIMARY KEY field (it can also be UNIQUE and NOT NULL), you will get an error and your INSERT will fail.

The classic example of this is the orders table and its child the order_line table.

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, client_id, other_fields...);

CREATE TABLE order_line (order_id, part_name, part_id, other_fields...
CONSTRAINT 'order_id_fk' FOREIGN KEY (order_id) REFERENCES orders (order_id);

The client_id field in the orders table could (and indeed should) be a FOREIGN KEY into the client table - the critical thing about FORIGN KEYs is that they establish relationships between the different tables so that you have consistently correct data with the correct client per order and every order_line belongs to one and only one order. Do NOT be tempted to eliminate FOREIGN KEYs for "so-called" performance reasons - they are VITAL for a functioning database system.


How do I write a query that inserts values while AND gets values from another table? I have a table (Tbl2) that needs to be populated with values from another table (Tbl1).

Tbl1 has columns: id (uuid), name, address, gender.

Tbl2 has columns: id (uuid), org, name, address, gender, createdAt, updatedAt.

I want to copy id, name, address, gender from tbl1 into tbl2. How Do I write a query that inserts those values into Tbl2 while also providing new ids, org, createdAt, and updatedAt values?

You need to look at the DEFAULT clause. I think you want something like the following as an example:

CREATE TABLE tbl2
(
  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
  org TEXT NOT NULL DEFAULT SUBSTRING(MD5(RANDOM()::TEXT) FROM 1 FOR 4),
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  gender CHAR(1) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  update_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT tbl2_pk PRIMARY KEY (id)
);

Note that I am using

  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT))

as the PRIMARY KEY. This is close enough to a UUID for the purposes of this answer - I'm using dbfiddle (see this link) and can't install the UUID extension. I'm also using a SUBSTRING of the same function to generate the org field values, since I don't know exactly what that is - I would have thought that a reference table to things like the UN, EU, NATO, ASEAN... &c. would be more appropriate for this, but then, only you know that - see @Philᵀᴹ 's com

Then populate it with some values:

INSERT INTO tbl2 (name, address, gender)
VALUES
('Name_23', 'Address_23', 'M'),
('Name_24', 'Address_24', 'F'),
('Name_25', 'Address_25', 'M');

Notice how I don't have to specify the id or the org fields since there's a DEFAULT in place. Now, we do the same for tbl1.

CREATE TABLE tbl1
(
  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  gender CHAR(1) NOT NULL,
  CONSTRAINT tbl1_pk PRIMARY KEY (id)
);

and:

INSERT INTO tbl1 (name, address, gender)
VALUES
('Name1', 'Address_1', 'M'),
('Name2', 'Address_2', 'F'),
('Name3', 'Address_3', 'M');

And then the INSERT is performed:

INSERT INTO tbl2 (name, address, gender)
SELECT name, address, gender
FROM tbl1
-- WHERE tbl.id NOT IN (SELECT id FROM tbl1) -- MD5 should ensure uniqueness
RETURNING name, address, gender;  -- this is optional - check it out.

And then we check our tbl2:

SELECT * FROM tbl2;

Result:

id  org name    address gender  created_at  update_at
1feaceed5c2d8e37f5565d222b5bd31c    28a1    Name_23 Address_23  M   2020-02-26 05:43:28.366588+00   2020-02-26 05:43:28.366588+00
508c0eecc16e305687b355c684cd5d56    d057    Name_24 Address_24  F   2020-02-26 05:43:28.366588+00   2020-02-26 05:43:28.366588+00
247d6d7bdcef078c4b4f7bef33e91d01    78a5    Name_25 Address_25  M   2020-02-26 05:43:28.366588+00   2020-02-26 05:43:28.366588+00
07bcc6cf6a3a3b79fcc05bafe9ebc8c2    05f8    Name1   Address_1   M   2020-02-26 05:43:28.395899+00   2020-02-26 05:43:28.395899+00
029ef1b1c21df2a0fe25da352114aa1f    48c2    Name2   Address_2   F   2020-02-26 05:43:28.395899+00   2020-02-26 05:43:28.395899+00
f4f51eccf9d4bfa7e011bdc4b2c135b8    8723    Name3   Address_3   M   2020-02-26 05:43:28.395899+00   2020-02-26 05:43:28.395899+00

A simple inspection shows that our INSERT has worked. Finally, notice that our created_at and updated_at fields have also been included - as the default is NOW(). In order for the updated_at field to be modified automatically, you'll need to use a TRIGGER - if you do a search, there are millions on the all-singing, all-dancing web. If this doesn't answer your question, let me know and I'll see what I can do. p.s. welcome to the forum!


Postgres - Revoke database acess from user, but allow replication acess The DB have a replication server, i need to revoke the permission on the user "user_bi" but allow the acess of this same user on the replication server!

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user_bi; -- on master.

This replicate the revoke to the slave.

GRANT SELECT ON ALL TABLES IN SCHEMA public FROM user_bi; -- on the slave.

This returns a error "SQL Error [25006]: ERROR: cannot execute GRANT in a read-only transaction".

Can i revoke the permissions in only one databank ?

I denied the acess by IP on the pg_hba.conf file.


Execute SQL conditionally based on database Compatibility Level The Problem
I have a script that I need to execute on both SQL Server 2016 and SQL Server 2019. If executing on Server 2019+, I want to use syntax that is only supported on that version (specifically, Data Classification).
What I want is something like this, where we simply skip Data Classification if running on a version before 2019:
DECLARE @compatibility_level TINYINT = 0;
SELECT @compatibility_level = compatibility_level FROM sys.databases WHERE name = 'MyDatabase';
PRINT CONCAT('Database compatibility level is: ', @compatibility_level);

IF @compatibility_level >= 150
BEGIN
    ADD SENSITIVITY CLASSIFICATION TO
        MyTable.Forename,
        MyTable.Surname
        WITH (LABEL='Confidential - GDPR', INFORMATION_TYPE='Name', RANK=MEDIUM);

    -- More classifications would be here
END
ELSE
BEGIN
    PRINT 'Data Classification will not be performed, as the compatibility level is not high enough';
END

If I run this on SQL Server 2019+, it works as expected. But if I run it on older version, it fails with Incorrect syntax near 'SENSITIVITY', because it's parsing the whole script before executing it.
Restrictions
I should add that this is part of a set of database migration scripts that are being executed automatically, so I can't simply execute different scripts for different versions (not without a lot of work).
Potential Solutions
One solution I thought of is to build the SQL dynamically, inside the IF block. Something like:
DECLARE @sql NVARCHAR(MAX) = '';
@sql += 'ADD SENSITIVITY CLASSIFICATION TO MyTable1.Column1 ... ;';
@sql += 'ADD SENSITIVITY CLASSIFICATION TO MyTable2.Column1 ... ;';
EXEC sp_executesql @sql;

I haven't tried this, but presume it would work. It feels pretty ugly though - are there any other ways to achieve this in a single script?

Final Solution
I went for the dynamic SQL solution, as it "just works", and it's the most obvious:
DECLARE @compatibility_level TINYINT = 0;
SELECT @compatibility_level = compatibility_level FROM sys.databases WHERE name = 'MyDatabase';
PRINT CONCAT('Database compatibility level is: ', @compatibility_level);

-- Data Classification is only supported from SQL Server 2019 onwards, so we skip it if not supported
IF @compatibility_level >= 150
BEGIN
    DECLARE @sql NVARCHAR(MAX) = '';

    SELECT @sql += 'ADD SENSITIVITY CLASSIFICATION TO
        MySchema.MyTable1.Column1,
        MySchema.MyTable1.Column2
        WITH (LABEL=''Confidential - GDPR'', INFORMATION_TYPE=''Name'', RANK=MEDIUM);' + CHAR(10);

    SELECT @sql += 'ADD SENSITIVITY CLASSIFICATION TO
        MySchema.MyTable2.Column1
        WITH (LABEL=''Confidential'', INFORMATION_TYPE=''Contact Info'', RANK=MEDIUM);' + CHAR(10);
        PRINT @sql;

    -- ...more classifications here, elided

    PRINT @sql;
    EXEC sp_executesql @sql;
END
ELSE
BEGIN
    RAISERROR('Data Classification will not be performed, as database compatibility level %d is not high enough', 10, 1, @compatibility_level);
END;

Other Potential Solutions

I thought to use NOEXEC, to prevent executing SQL if the database compatibility level was too low:

DECLARE @compatibility_level TINYINT = 0;
SELECT @compatibility_level = compatibility_level FROM sys.databases WHERE name = 'MyDatabase';
PRINT CONCAT('Database compatibility level is: ', @compatibility_level);

IF @compatibility_level < 150
BEGIN
    RAISERROR('Data Classification will not be performed, as database compatibility level %d is not high enough', 10, 1, @compatibility_level);
      SET NOEXEC ON;
END;
GO

-- This will only be executed if database compatibility is >= 150
ADD SENSITIVITY CLASSIFICATION TO
    MyTable.Forename,
    MyTable.Surname
    WITH (LABEL='Confidential - GDPR', INFORMATION_TYPE='Name', RANK=MEDIUM);

SET NOEXEC OFF;
GO

However, even with NOEXEC enabled, it still actually interprets the SQL - so this doesn't work if the server compatiblity level is too low (beforew SQL2019), regardless of the database compatiblity level.

@martin-cairney came up with another solution, using the sp_addextendedproperty function, and you can find an example here. I personally find having to mess around with "magic string" GUIDs for sys_sensitivity_label_id to be a PITA.

A further option would be to write directly to the sys.extended_properties table (this is exactly what sp_addextendedproperty does) - obviously this method isn't recommended.


mariadb slow to response due to high query i have a g7 server that use as database server
and i installed window server 2016 and maria db version 10.1.39-MariaDB on it

my hardware have 2 CPU  Intel(R) Xeon(R) CPU E5620 @ 2.40GHz and 64 gig of ram and 4*128 gig hp ssd 6gbps raid 10

but when my load is going up to 1000 query per second it slow down and crashed..
all my tables are innodb and i have 500-600 mil record and db size is about 40-50 gig

my config is

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 512M
innodb_file_per_table=1
max_connection=4000

table_open_cache = 1536
sort_buffer_size = 2M
read_buffer_size = 256K
read_rnd_buffer_size = 256k
myisam_sort_buffer_size = 64M
thread_cache_size = 256
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 12

max_allowed_packet=16M

innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

#innodb_dedicated_server = true

innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 24

which of my config is wrong and how can i get the best performance!?

update:when database stoped ram usage is 4-5 gig and when database in load ram go up to 40-45 gig,also my used my connection is about 3500 connection..

3500 -- is that Max_used_connections? If so, that is terrible. Imagine 3500 people, each with a grocery cart, in a grocery store. Imagine them trying to move through the aisles to pick up food items. It is a mess; essentially it is too crowded for anyone to get anything done. The same thing happens with MySQL/MariaDB.  In this case it is mutexes, and other things.

Put another way, each process gets 2/3500 of the CPU time.  That is, practically none.  So, each connection takes really long to get anything finished.

So, the cure is to find the slow queries and speed them up.  See:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

(Use long_query_time=1 so you can catch some of the shorter, but frequent, queries.)


Query is slow on AlwaysOn Secondary node I have two identical servers (same hardware, cpu, memory etc) in an AlwaysOn cluster (sync commit and read-intent enabled), both servers running with SQL Server 2016 SP1 (13.0.4001.0).
For some of the queries with identical query plans and statistics, the queries are taking over 10secs to run on the secondary replica where as they finish under 2 secs on the primary replica. I have verified the statistics and query plans are exactly the same and tested executing the queries with no other load on these servers. Not able to find the reason why queries are slow on secondary replica.

Query and Plan from primary replica
https://www.brentozar.com/pastetheplan/?id=S1O5Yxscb

Query and Plan from secondary replica
https://www.brentozar.com/pastetheplan/?id=SJdz5ljq-

However, when I give a maxdop = 8 hint in the query on the secondary server, the n it finishes in 2secs.

Also, when I switch roles upon failover, the query executes in 2 secs on the new primary server and on the secondary server it again takes around 10secs. So doesn't seem to be a hardware problem as the query executes fast on the server which ever is designated as primary on the AG cluster and it runs slow on the secondary node.

1) Please check the performance metrics (CPU%, Memory, buffers) on the secondary node. Is it a reporting server. AlwaysOn has an option to send all read-queries to the secondary node.

2) AlwaysOn utilizes snapshot isolation. Can you validate if the tempdb is being heavily used? Please validate the write/read latency on tempdb and other databases. Here is one way to check: Query from Database Journal

DECLARE @Sample TABLE (
  DBName varchar(128)
 ,NumberOfReads bigint
 ,NumberOfWrites bigint)

INSERT INTO @Sample
SELECT name AS 'DBName'
      ,SUM(num_of_reads) AS 'NumberOfRead'
      ,SUM(num_of_writes) AS 'NumberOfWrites'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
  INNER JOIN sys.databases D
      ON I.database_id = d.database_id
GROUP BY name

WAITFOR DELAY '00:05:00.000';

SELECT FirstSample.DBName
      ,(SecondSample.NumberOfReads - FirstSample.NumberOfReads) AS 'Number of Reads'
      ,(SecondSample.NumberOfWrites - FirstSample.NumberOfWrites) AS 'Number of Writes'
FROM
(SELECT * FROM @Sample) FirstSample
INNER JOIN
(SELECT name AS 'DBName'
      ,SUM(num_of_reads) AS 'NumberOfReads'
      ,SUM(num_of_writes) AS 'NumberOfWrites'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
  INNER JOIN sys.databases D
      ON I.database_id = d.database_id
GROUP BY name) AS SecondSample
ON FirstSample.DBName = SecondSample.DBName
ORDER BY 'Number of Reads' DESC;

3) Did you try just doing a simple backup/restore onto the secondary node, removing AlwaysOn, and validating if the query is still slow?

4) I know you don't have NOLOCK hints in query. Can you try rebuilding the indexes on primary replica, See what happens? Query execution takes significantly longer on SQL Server Always On secondary replicas.

"When Snapshot Isolation is enforced, NOLOCK hints are ignored. The discrepancy in execution duration between the primary and secondary replicas occurs because the NOLOCK hint is ignored on the read-only secondary replica where Snapshot Isolation is enforced but not on the primary replica where Snapshot Isolation is not enforced by default. This causes the scan of the clustered index to have key order enforced on the secondary replica. On the primary replica, the NOLOCK hint takes precedence and does affect behavior. When the clustered index is highly fragmented, the enforcement of key order for the scan on the read-only secondary replica causes SQL Server to issue single-page reads. But on the primary replica, SQL Server does an allocation unit scan to read multiple pages per IO request."

5)  Can you validate what the settings are? Queries Maxdop and Memory Allocation as suggested from Kin above, another good idea

-- Find MaxDop
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'max degree of parallelism'
GO

-- Change MaxDop settings replace to 8
EXEC dbo.sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE;
GO
 -- Find Memory allocation on instance
 SELECT [name] AS [Name]
 ,[configuration_id] AS [Number]
 ,[minimum] AS [Minimum]
 ,[maximum] AS [Maximum]
 ,[is_dynamic] AS [Dynamic]
 ,[is_advanced] AS [Advanced]
 ,[value] AS [ConfigValue]
 ,[value_in_use] AS [RunValue]
 ,[description] AS [Description]
 FROM [master].[sys].[configurations]
 WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')


Audit all insert, delete, update and select statements on a schema in Oracle 12.2 I cannot understand how to create a Unified Audit policy in order to audit all INSERT, UPDATE, DELETE and SELECT statements performed on all the objects within a specific schema.

The goal is to track the tables and views involved in a specific PL/SQL procedure for a reverse engineering task.

For what I understood from the documentation, the only option is to specify each table/view to track within the policy. Is there an "audit all objects within a schema" construct for the create audit policy statement?

You described traditional auditing. For unified auditing the parameter audit_trail has no meaning and is actually not considered by Oracle. As mentioned earlier, with unified auditing you have to create an audit policy.
Oracle does not write to SYS.AUD$ with unified auditing. It writes to audsys.aud$unified or spillover files on the OS (if the DB is not accessible). Your data is visible through the view unified_audit_trail, which selects from audsys.aud$unified and v$unified_audit_trail (spillover files).
To audit all insert,update,delete,select on a table scott.t1 you would use something like
create audit policy T1_AUDIT
       ACTIONS select on scott.t1,
               insert on scott.t1,
               update on scott.t1,
               delete on scott.t1;

audit policy T1_AUDIT;


SQL Server 2014 compression and maximum row size I need to create wide denormalized table with lot of decimal(26,8) columns (fewer than 1024 column limit, most of the columns would be null or zero).
I know about 8060 bytes per row limitation, so i tried to create table with page compression.
Code below creates table, inserts one row and queries row size. Row size is far below the limit, but if i try to add one more decimal(26,8) columns to table, operation fails with error "Creating or altering table 't1' failed because the minimum row size would be 8074, including 1256 bytes of internal overhead.". Is there any way to create single table with that many columns?

drop table t1
GO
create table t1(c1 decimal(26, 8) null)
with (data_compression = page)
GO

declare @i int = 2;
declare @sql varchar(100);
while @i <= 486
begin
    set @sql = 'alter table t1 add c' + convert(varchar, @i) + ' decimal(26, 8) null';
    execute (@sql);
    set @i += 1;
end;
GO

insert into t1(c1) select 0
GO
declare @i int = 2;
declare @sql varchar(100);
while @i <= 486
begin
    set @sql = 'update t1 set c' + convert(varchar, @i) + ' = 0';
    execute (@sql);
    set @i += 1;
end;
GO

select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
GO

The limit that you're running into has nothing to do with the data stored on the page. The calculation is done based on the data types of the columns. That's why you run into the error without any data in the table. Compression makes this limit worse. You can read about the technical details behind the overhead here.

You can workaround this issue by using SPARSE columns. That means that it'll be possible for inserts to fail depending on what you insert, but you can bypass the 8060 byte limit. The following code shows that you can create 1023 columns just fine:

drop table t1
GO
create table t1(c1 decimal(26, 8) null)
GO

declare @i int = 2;
declare @sql varchar(100);
while @i <= 1023
begin
    set @sql = 'alter table t1 add c' + convert(varchar, @i) + ' decimal(26, 8) SPARSE null';
    execute (@sql);
    set @i += 1;
end;
GO

However, all of the restrictions around it (read the linked article) may make this not suitable for your use case. Specifically, only NULL values (not 0) are optimized to take up very little space. If you try to insert too many 0s in a single row you'll get an error. Here's what I see when I try to insert 1023 0 values:

  Msg 511, Level 16, State 1, Line 1
  Cannot create a row of size 17402 which is greater than the allowable maximum row size of 8060.

I suppose that if you got really desperate you could create the columns as VARCHAR(27) instead. Variable length columns can be moved off page so that you can exceed the 8060 byte limit in the table definition but inserting certain combinations of values will fail. SQL Server warns you of this when creating the table:

  Warning: The table "t1" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Page or row compression may be helpful if you go with the VARCHAR(27) approach. That will minimize the space used by both 0 and NULL. With VARCHAR(27) I'm able to insert 1023 0 values just fine.


How do I resolve issue with deleted table in SQL Server Database Diagrams? I am working on a database design using the SQL Server Database Diagram tool.  I created a table with the name dbo.Example and before defining any columns, attempted to save.

Got an error that SQL can't save a table without any columns and as I wanted to save the other work I had done before completing this table, I deleted it and saved successfully.

I then make many more changes and save the diagram.  All is good.

UNTIL I am ready to correctly define my dbo.Example table and get the

'Table 'dbo.Example' already exists' error.

The table is not among the tables returned from

select name from sys.tables;

and does not appear in the Database->Tables GUI view.

I can't find where I need to clean up and drop this table.

Where does SQL Server keep the metadata about the database diagrams?

This is a bug in Management Studio.

I was able to reproduce it three ways, on both 2008 R2 and 2012 tools:

Create a new diagram; create a new empty table (any name); delete the table; in the same diagram, try to create a new table with the same name.
Create a new diagram; create a new empty table; delete the table; create a new diagram in the same database, and while the first one is still open, create a new table with the same name.
Create a new diagram; create a new empty table; delete the table; create a new diagram in the same database; close the first diagram; in the second diagram, create a new table with the same name.

From this behaviour, I'm guessing it keeps a list of tables per database outside the context of a single database diagram, and when an empty table is deleted, the reference isn't being cleaned up properly. I believe the fix is to close all diagrams for that database, and re-open. I think it's lucky you were able to save the changes you made with no errors!

I would suggest reporting this on Connect if you feel it's really important. IMO, it seems unlikely it will be fixed because it's so minor, but you never know.


Setting up simple question & answer database I am having trouble thinking this (simple) table relation through. I am setting up a quick SQL DB to populate labels and textboxes dynamically on screens. This is just to prove we can do it, so it doesn't have to be the final set up. We will not be storing any response data at this time, so a storage table is not necessary. The requirements are:

1) One question can have many possible answer options.
ex: (True, False) (Yes, No)

2) One question can only have one group of answers.
ex: Group One: (True, False) Group 2: (Yes, No)

3) Many questions can use one group of answers.
ex: Drivers License? Group Two (Yes, No); Married? Group Two (Yes, No)

I'm drawing a blank on how to build a group of answers off of a set of answers, and allowing a question to only pull from that group of answers. Any help would be greatly appreciated :)

Thank you in advance.

Here's one idea:

CREATE TABLE dbo.AnswerGroups
(
  AnswerGroupID INT IDENTITY(1,1) PRIMARY KEY,
  Description NVARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.Answers
(
  AnswerID INT IDENTITY(1,1) PRIMARY KEY,
  AnswerGroupID INT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerGroupID),
  DisplayText NVARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Questions
(
  QuestionID INT IDENTITY(1,1) PRIMARY KEY,
  DisplayText NVARCHAR(255) NOT NULL
);

CREATE TABLE dbo.AnswerGroupQuestions
(
  AnswerGroupID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerGroupID),
  QuestionID INT NOT NULL UNIQUE FOREIGN KEY
    REFERENCES dbo.Questions(QuestionID),
  PRIMARY KEY(AnswerGroupID, QuestionID)
);

That makes all of this legal:

INSERT dbo.AnswerGroups(Description) SELECT 'YesNo';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(1,'Yes'),(1,'No');

INSERT dbo.AnswerGroups(Description) SELECT 'TrueFalse';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(2,'True'),(2,'False');

INSERT dbo.Questions(DisplayText) SELECT 'Driver''s License?';

INSERT dbo.AnswerGroupQuestions(AnswerGroupID, QuestionID)
  VALUES(1, 1);

INSERT dbo.Questions(DisplayText) SELECT 'Married?';

INSERT dbo.AnswerGroupQuestions(AnswerGroupID, QuestionID)
  VALUES(1, 2);

But prevents this assigning, say, one question to more than one answer group:

INSERT dbo.AnswerGroupQuestions VALUES(2, 1);

Result:

  Msg 2627, Level 14, State 1, Line 1
  Violation of UNIQUE KEY constraint 'UQ_AnswerGr_0DC06F8DA7923A33'. Cannot insert duplicate key in object 'dbo.AnswerGroupQuestions'. The duplicate key value is (1).
  The statement has been terminated.

A better design, as @DForck42 correctly observed:

CREATE TABLE dbo.AnswerGroups
(
  AnswerGroupID INT IDENTITY(1,1) PRIMARY KEY,
  Description NVARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.Answers
(
  AnswerID INT IDENTITY(1,1) PRIMARY KEY,
  AnswerGroupID INT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerGroupID),
  DisplayText NVARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Questions
(
  QuestionID INT IDENTITY(1,1) PRIMARY KEY,
  DisplayText NVARCHAR(255) NOT NULL,
  AnswerGroupID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerID)
);

Only slightly different sample inserts:

INSERT dbo.AnswerGroups(Description) SELECT 'YesNo';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(1,'Yes'),(1,'No');

INSERT dbo.AnswerGroups(Description) SELECT 'TrueFalse';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(2,'True'),(2,'False');

INSERT dbo.Questions(DisplayText, AnswerGroupID)
  SELECT 'Driver''s License?', 1
  UNION ALL SELECT 'Married?', 1;

And of course since there is only one possible value to link a Question to an AnswerGroup, there is no need to demonstrate the prevention of multiple Question -> AnswerGroup associations.


Merge data from a SQL Server backup I have a backup of a SQL Server database that I would like to restore. The issue is that the server that I would restore it to already have a database with the same name, layout etc, which contains data.

Is there some easy way to merge data form the backup into the existing database so that only missing rows are inserted?

For your case i would recommend dbForge Server Data Compare for SQL Server from Devart. It looks very similar to the Redgate SQL Data Compare mentioned by Moisés González.

It allows you to compare your database with the backup file directly or with a second database.

It shows you the differences between the databases:

only in backup / source database
only in target database
diffent rows / fields

You can select the data to restore or generate scripts for inserting in your current database.

I use this software for our customers to restore deleted rows and even a restore of 5 million rows and update of another 5 million rows have been done very quickly. It works very well and can execute also very large repair scripts (the largest one i have generated was about 10 gig).

There is a 30 day trial available which may fit your needs. So you can give it a try without any risk.


How to use function parameters in dynamic SQL with EXECUTE? I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the parameters passed in function is not working.

Below is my function:

CREATE OR REPLACE FUNCTION insertRecordsForNotification(username text, state     text, district text, organizationId text, bloodGroup text, status text,   approveRejectStatus text, emailSubject text, emailBody text, notificationStatus text) RETURNS boolean AS $$
DECLARE
id int;
r moyadev.user%rowtype;
_where text :=
  concat_ws(' AND '
    , CASE WHEN state IS NOT NULL THEN 'state = $2' END
    , CASE WHEN district IS NOT NULL THEN 'district = $3' END
    , CASE WHEN bloodGroup IS NOT NULL THEN 'bloodGroup = $5' END
    , CASE WHEN status IS NOT NULL THEN 'status = $6' END
    , CASE WHEN approveRejectStatus IS NOT NULL THEN 'approve_reject_status  = $7' END);
 _sql text := 'INSERT INTO moyadev.notification_email_details (id,     youth_enrollment_id, youth_email, email_subject, email_body, status, attempt,  sent_date, last_updated_by, last_updated) SELECT uuid_generate_v4(), id, email,  $8, $9, $10, null, null,$1, now() FROM moyadev.youth_enrollment';

 BEGIN

SELECT * into r FROM moyadev.user u where u.user_key=$1;

if (r.level='DISTRICT') then
_where := _where || ' AND ' || 'district=r.district' || ' AND ' ||      'state=r.state' || ' AND ' || 'fk_id=r.fk_id';
elseif (r.level='STATE') then
_where := _where || ' AND ' || 'state=r.state' || ' AND ' ||   'fk_id=r.fk_id';
 elseif (r.level='NATIONAL') then
 _where := _where || ' AND ' || 'fk_id=r.fk_id';
 elseif (r.level='UNIT') then
_ where := _where || ' AND ' || 'district=r.district' || ' AND ' ||    'state=r.state' || ' AND ' || 'fk_id=r.fk_id';
end if;

IF _where <> '' THEN
_sql := _sql || ' WHERE ' || _where;
EXECUTE format(_sql);
END IF;

raise notice 'sql: %', _sql;

RETURN 'TRUE';

END;
$$ LANGUAGE PLPGSQL;

It compiles well but gives below error when I call it using below command:

select insertRecordsForNotification('nssnationalappr@mailinator.com',null,null,null,null,'ACTIVE','APPROVED','test email','test email','PENDING');

ERROR: there is no parameter $8
SQL state: 42P02
Context: PL/pgSQL function insertrecordsfornotification(text,text,text,text,text,text,text,text,text,text) line 39 at EXECUTE

How to use parameter values properly?

You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here.

CREATE OR REPLACE FUNCTION insert_records_for_notification(
        _username text
      , _state text
      , _district text
      , _bloodgroup text
      , _status text
      , _approverejectstatus text
      , _emailsubject text
      , _emailbody text
      , _notificationstatus text)
  RETURNS boolean AS
$func$
DECLARE
   r      moyadev.user%rowtype;
   _where text;
   _sql   text :=
     'INSERT INTO moyadev.notification_email_details (id, youth_enrollment_id, youth_email, email_subject, email_body, status, attempt,sent_date, last_updated_by, last_updated)
      SELECT uuid_generate_v4(), id, email, $7, $8, $9, null, null,$1, now()
      FROM   moyadev.youth_enrollment';
BEGIN
   SELECT * INTO r FROM moyadev.user u WHERE u.user_key = _username;

   _where := concat_ws(' AND '
       , CASE WHEN state               IS NOT NULL THEN 'state = $2'                  END
       , CASE WHEN district            IS NOT NULL THEN 'district = $3'               END
       , CASE WHEN bloodGroup          IS NOT NULL THEN 'bloodgroup = $4'             END
       , CASE WHEN status              IS NOT NULL THEN 'status = $5'                 END
       , CASE WHEN approveRejectStatus IS NOT NULL THEN 'approve_reject_status  = $6' END
       , CASE r.level
            WHEN 'DISTRICT' THEN 'district = $10 AND state = $11 AND fk_id = $12'
            WHEN 'UNIT'     THEN 'district = $10 AND state = $11 AND fk_id = $12'
            WHEN 'STATE'    THEN 'state = $11 AND fk_id = $12'
            WHEN 'NATIONAL' THEN 'fk_id = $12'
         END);

   IF _where <> '' THEN
      _sql := _sql || ' WHERE ' || _where;

      EXECUTE _sql
      USING   $1, $2, $3, $4, $5, $6, $7, $8, $9, r.district, r.state, r.fk_id;
   END IF;

   RAISE NOTICE 'sql: %', _sql;

   RETURN true;  -- boolean!
END
$func$  LANGUAGE plpgsql;

Major points

Do not concatenate parameter values into SQL strings. Very tedious, slow, error-prone and open to SQL injection. Instead pass values to EXECUTE with the USING clause. Related:

SQL injection in Postgres functions vs prepared queries

I removed unused variable id int; and the unused parameter organizationId text. Adapted ordinal reference ($n) accordingly.
Do not confuse the $n notation inside EXECUTE (refer to items in the USING clause) with $n notation in the function body (refer to function parameters)! Related:

Passing array from one plpgsql function to another

Simplified your logic to concatenate the WHERE clause. There were corner case errors: if the initial assignment resulted in empty string, you would start with AND - a syntax error.
Adopt a naming convention that avoids naming conflicts. Parameter names are visible in all statements in the function (but not inside EXECUTE!). Don't use variable names that conflict with column names. A common convention is to prepend parameter and variable names with _.
My advise is to avoid mixed-case identifiers in Postgres, especially when using dynamic SQL.

Related answer on SO:

Test for null in function with varying parameters


C# ASP.net MVC - Which way is more efficient and quicker to store data into database? I am using Microsoft Visual Studio and C# language to query and store data into my database.

I have two ways in which I can store data into my database in SQL server.

For example, I need to store the amount of fruits in my database:

Apples = 10, Orange = 3 and grape = 5 and watermelon = null.

So is it better to store it as:
(UPDATED!
1)

I use a for loop and store it one by one.

for(int i=0; i<  length ; i++)

{ sql.add(fruit[i],value[i],plotdate[i]);}

INSERT INTO fruit (fruit, value, plotdate)
VALUES ('Apple','10','11/01/2016 1:45:00PM');

Or the second method:

2)

I store it all at once,

sql.add(value,value,value,value,plotdate);

INSERT INTO fruit (Apple, Orange,grape, plotdate)
VALUES ('10','3', '5','11/01/2016 1:45:00PM');

Thank you

EDIT:

What I'm trying to achieve:

A device sends me data every 5 minutes so it will send it using the URL

www.example.com/get?apple=10,orange=2,grape=5,watermelon-1,apple=12,orange,13,grape=12 etc..

I then break down the URL I receive and which is why I use for-loop to store all the data for each fruit given by the url.
After doing so the updated version in the database will look like 1).

Compilation of comments
@Sole DBA Guy pointed out:

I'd say method 1 is what most DBAs will say is better of the two options because if you decide to add Banana, it's just another row for the first option but for the second option, it would be a new column. However, why does it need a loop to add the values? Is the loop to get the values for each fruit? Do the same fruit values not exist in the database already?

@MDCCL mentioned:

I agree with @Sole Dba Guy. From a modeling perspective, as usual, it depends on different considerations. For instance, if you want to store an entity type called, perhaps, Fruit which, say, has the attributes FruitNumber, Name and QuantityAvailable, then your first procedure is certainly more suitable and extensible. You have to determine with precision the kinds of things that exist in your context of interest so that you can define which option is more convenient.

Then, I replied to his comment with the following question:

Wouldn't the first method take up more processing time or CPU usage? Since it has to do {sql.add()} again and again due to being in a for loop rather than using {sql.add()} once like the second method.

To which @MDCCL responded:

Maybe, or maybe not, honestly, it is hard to tell accurately without knowing the whole context. That is, in part, an application program question, but it certainly has database implications. Since you would be executing multiple INSERTs, perhaps some kind of batch operation might be helpful in your case. Application programming and relational database implementation are different disciplines so, as a mere suggestion, you should read about relational theory, data modeling and Structured Query Language (particularly the Transact-SQL dialect that is used in Microsoft SQL Server, the platform that, according to your tag, you are currently employing).

Finally, @srutzky added:

The query string might have multiple instances of the same variable? The example shows "apple", "orange", and "grape" showing up twice, plus the mention of the for loop. How likely is it that other fruits will be added, over the next few years at least? If fruits show up multiple times in the query string, what's your plan for doing #2 for ones with multiple entries? Aggregate them into a single entry? Is the relationship between them in terms of when the data was submitted important? Option 1 doesn't have that, outside of the time. Option 1 also repeats fruit names instead of using FruitID.


Your query can return the expected results by adding the reverse condition:

SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP
FROM persons A
LEFT JOIN persons B
ON a.ID <> b.ID
AND (a.Name LIKE CONCAT ("%", b.Name, "%") OR b.Name LIKE CONCAT ("%", a.Name, "%"))
ORDER BY ID;

I don't know if it will be faster, but another way to do it would be to use INSTR:

SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP
FROM persons A
LEFT JOIN persons B
ON a.ID <> b.ID
AND (Instr(a.Name, b.Name) > 0 OR Instr(b.Name, a.Name) > 0)
ORDER BY ID;

SQL Fiddle


Accessing Memory Optimised Tables Under Snapshot Isolation We have a high traffic database, that I am looking to convert to using IM-OLTP for performance gains.
Due to the large size of the table in question, I'm looking to do a hot/cold solution, with a view unioning both together.
Create Table Demo_Cold
(
    [ID] INT NOT NULL PRIMARY KEY,
    [Description] VARCHAR(MAX) NOT NULL
);
GO

Create Table Demo_Hot
(
    [ID] INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
    [Description] VARCHAR(MAX) NOT NULL
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA );
GO

CREATE VIEW Demo
AS
Select *
From Demo_Hot
UNION ALL
Select *
From Demo_Cold;
GO

When I attempt to query this (either directly or view the view) everything is fine:
Select * From Demo

But when I try to set the isolation level to snapshot, I get an error instead:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Select * From Demo

Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

We use snapshot isolation in this database at the moment to allow unrelated parallel commits, and fail on concurrent commits on the same data. According to the MS Documentation, Snapshot isolation is supported by IM-OLTP.
I've even tried setting the escalation setting on to allow this:
ALTER DATABASE CURRENT
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

But it doesn't change the error.
If I set the isolation level to REPEATABLE READ, I get another error:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Select * From Demo

The following transactions must access memory optimized tables and natively compiled modules under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

The same for accessing just the memory-optimised table:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Select * From Demo_Hot

Am I missing a key step required? Ultimately I'd like to be able to continue using Snapshot isolation from our code (to minimise any other changes).

If you intend to be able to update/insert through the view, it will likely not work. Please see my post here:
http://nedotter.com/archive/2017/07/dml-for-memory-optimized-tables-in-partitioned-views/
Rows cannot transition to/from the memory-optimzed side by changing the key/partitioning value. Depending on the version of SQL you are running, it will either cause a stack dump or give an error saying it's unsupported.
Though Microsoft has marketed memory-optimized tables as a hot/cold solution, it can't be done in most cases, at least not without tons of manual intervention. There is no way to validate a FK across the traditional/memory-optimized divide. See my post here:
http://nedotter.com/archive/2016/05/in-memory-oltp-relationship-status-its-complicated/
I've written a few posts on memory-optimized tables and isolation levels here:
http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-1/
http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-2/
Have you proven that the performance issues you are experiencing would be solved by In-Memory OLTP? The use cases are quite narrow.


EAV - COUNT()/SUM() on column and row values with and without NULLs Assume that there is a table

demo

with these data:

id
userID
title
data

1
1
DATE
01-01-2021

2
1
COLOR
RED

3
2
DATE

4
2
COLOR
YELLOW

5
3
DATE
04-01-2021

6
3
COLOR
RED

5
4
DATE
04-01-2021

6
4
COLOR
PINK

7
5
DATE
02-01-2021

8
5
COLOR
PINK

7
6
DATE
08-01-2021

8
6
COLOR
PINK

If the data of the row date is empty color is not picked-up yet.
The information overview i need to view on website php page is:
Picked-up of RED: 2
Total RED: 2
Picked-up of Yellow: 0
Total Yellow: 1
Picked-up of Pink: 3
Total  Pink: 3

Hi and welcome to the forum!
Your schema requires a totally radical overhaul. This image gives some idea of what's wrong with it:

You have used the EAV (Entity-Attribute-Value) database design anti-pattern - search for the writings of Joe Celko, Bill Karwin and others (and links therein) on this topic.
Also, and very important, you should store VARCHAR()s as VARCHAR()s and DATEs as DATEs - you will see this discussed when you read around EAV - data types go out the window and this means that the query optimiser can't generate an optimal plan. This applies to other types as well.
You can see this effect here:
CREATE TABLE date_test (x VARCHAR(10));

INSERT INTO date_test VALUES ('15-05-2021'), ('25-10-2000');

SELECT * FROM date_test ORDER BY x;

Result:
         x
15-05-2021     -- <<==== WRONG order!
25-10-2000

One of the reasons that correct data types are important! Even if you are obliged to store dates as strings (I realise that programmers are subject to constraints), you should use the ISO date format (i.e. YYYY-MM-DD).
TL;DR - the answer!
If you just want the answer, then it's:
SELECT
  dt.color AS "Color",
  COUNT(dt.c_date) AS "Collected",
  COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
ORDER BY dt.color;

Result:
  Color Collected   Total
   PINK         3       3
    RED         2       2
 YELLOW         1       2

It also has the advantage of being the fastest query of those proposed - see the PROFILING section below!
Tackling the schema:
What you should do is (see fiddle here - adapted from @Akina's - I added a collected garment of yellow colour. I don't know what's going on with the id column and I've ignored it hereafter).
So, you create a VIEW - if you can't change the schema due to other constraints - I appreciate that DBAs/programmers don't always have (complete) control over their environments.

View:
CREATE VIEW demo_view AS
(
  SELECT
    x.user_id AS "uid",
    x.data AS "color",
    CAST(
          CONCAT
          (
            SUBSTRING(y.data, 7, 10),
            SUBSTRING(y.data, 3, 4),
            SUBSTRING(y.data, 1, 2)
          ) AS DATE
        ) AS c_date
  FROM demo x
  JOIN demo y
    ON x.user_id = y.user_id
    AND x.title = 'COLOR'
    AND y.title = 'DATE'
);

Table:
CREATE TABLE demo_tab AS
(
  SELECT * FROM demo_view

  --
  -- Could also construct it as above!
  --
);

Then your query on the table (could be on the view also - see fiddle) will be as simple as:
SELECT
  dt.color AS "Color",
  SUM(CASE
      WHEN dt.c_date IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color;

Result (identical for all queries):
 Color   Collected   Total
   RED           2       2
YELLOW           1       2
  PINK           3       3

Now, why, you may ask am I using the "complex" SUM(CASE... instead of the relatively "easy" SUM(t1.data IS NOT NULL)... of Akina's answer here. If you try and run his fiddle using PostgreSQL (see here), it fails. The SUM(... IS NOT NULL) construct is MySQL specific whereas the other works with PostgreSQL, SQLServer and MySQL! If in doubt - test on PostgreSQL - if it doesn't work there, there's usually something fishy going on!
If you want to (or are obliged to) persist with the schema as it currently is, then run this:
SELECT
  t2.data AS "Color",
SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) Total,
CAST(SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS FLOAT) * 100 / COUNT(*) AS "% collected"
FROM demo t1
JOIN demo t2 ON t1.user_id = t2.user_id
WHERE t1.title = 'DATE'
  AND t2.title = 'COLOR'
GROUP BY t2.data
ORDER BY 1;

Result  - added a percentage collected field to it!
  Color Collected   Total   % collected
   PINK         3       3           100
    RED         2       2           100
 YELLOW         1       2            50

I did a performance analysis - see fiddle - using profiling - check the manual:
SET PROFILING = 1;

Run your queries
SHOW PROFILES;

Result (typical one shown - ran several times - may vary! Queries may be truncated):
Query_ID        Duration    Query
       1        0.00056325  SELECT
  dt.color AS "Color",
  COUNT(dt.c_date) AS "Collected",
  COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
ORDER BY dt.color
Query_ID        Duration    Query
       2             0.00064050 SELECT
  dt.color AS "Color",
  SUM(CASE
      WHEN dt.c_date IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
3             0.00074950    SELECT
  t2.data AS "Color",
SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) Total,
CAST(SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS FLOAT) * 100 / COUNT(*) AS "% collected"
FROM demo t1
JOIN demo t2 ON t1.user_id =
4             0.00075000    SELECT
  v.color AS "Color",
  SUM(CASE
      WHEN v.c_date IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_view v
GROUP BY v.color

SET PROFILING = 0;

The table-based query very frequently has the smallest duration (~ 90% of the time - we don't know what's going on with the rest of the dbfiddle server). The query plan for the table-based query is much shorter (EXPLAIN ANALYZE - see fiddle) than the others - good rule of thumb, the shorter the plan, the faster the query!
If you want to use standard SQL (and you should!), you can use this command (see bottom of fiddle) - you can issue the SET sql_mode='ANSI'; command - from the aforementioned Bill Karwin's answer here.
user_id title        data           w            x
      1  DATE  01-01-2021  2021-01-01   2021-01-01
      2  DATE        NULL         NULL        NULL
      3  DATE   04-01-2021  2021-01-04  2021-01-04
      4  DATE   04-01-2021  2021-01-04  2021-01-04
      5  DATE   02-01-2021  2021-01-02  2021-01-02
      6  DATE   08-01-2021  2021-01-08  2021-01-08
      7  DATE   07-07-2027  2027-07-07  2027-07-07

I leave it as an exercise for the reader to put what are now valid dates into the SQL above - it should work the exact same as the MySQL specific construct - you can see this in the PostgreSQL fiddle here.
I also leave it up to you to put in indexes on the necessary fields for large datasets - if you're having problems, get back to us here with a new question containing a link to the this one! EXPLAIN ANALYZE should give a few hints in this regard!


Why are my queries getting really slow? I own a WordPress installation, and Updated to 3.4.1.

After the upgrade, I experienced a very strange slow-down.

After all, I used the

define('SAVEQUERIES', true);

in my wp-config.php with combined with the following code in my theme footer.php

global $wpdb;
echo "<pre>";
print_r($wpdb->queries);
echo "</pre>";

and I found that while I don't have many queries (49 queries I got), there are

4 Queries took more than 9''
3 Queries took more than 8''
4 Queries took more than 7''
4 Queries took more than 6''
8 Queries took more than 5''
1 Query took more than 4''

Here I will show you some of the queries:

/* Execution time 7.0095062E-5 */
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC

/* Execution time 5.3167343E-5 */
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (77375,77379,77381)

/* Execution time 9.2029572E-5 */
SELECT wp_posts.* FROM wp_posts WHERE ID IN (77381,77379,77375)

In most of the queries, the query is simple, with no complexity. I cannot imagine why the queries are so slow.

The WordPress database contains in about 60,000 records and running on Percona-Server.

Any ideas for for what the issue might be, and how I could fix it ?

Take a look the queries in your question

/* Execution time 7.0095062E-5 */
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC

/* Execution time 5.3167343E-5 */
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (77375,77379,77381)

/* Execution time 9.2029572E-5 */
SELECT wp_posts.* FROM wp_posts WHERE ID IN (77381,77379,77375)

Look at the execution time above each query

7.0095062E-5 is .000070095062 seconds
5.3167343E-5 is .000053167343 seconds
9.2029572E-5 is .000092029572 seconds

The SQL queries themselves are fast.

Please read any release notes on WordPress 3.4.1 to see if WordPress upgrade installed any DB-related modules that need tuning or deactivation. Also, check for any data that was added during the upgrade process that may have bloated your tables.