Improve Performance -Update query with OR in WHERE How do we improve this Update query Performance as currently it is taking 1 Minute to update 17643 Rows.

Query ->

  Declare @date datetime = '09-Dec-2013'
    update  #tmp_sp_abc
    set     test = rtrim(xyz_test)
    from    #tmp_sp_abc t1,
            t_h_bg_pc_it t2
    where   (t2.id_i = t1.i or t2.id_s1 = t1.s)
    and     t1.r_type = 1
    and     t2.[date] = @date

Tables Row Count: -
    #tmp_sp_abc -> 125352
        t_h_bg_pc_it -> 14798 Rows

t_h_bg_pc_it table has 300 columns with primary key on id_i column
and #tmp_sp_abc has 11 columns with no primary key and no indexes.

found that "OR" condition is the root cause of this much time consumption but, can't change it.

tried to add indexes on: -

Table: - t_h_bg_pc_it
Columns: - [xyz_test], [id_i], [id_s1], [date]

Table: - #tmp_sp_abc
Columns: - [i], [s], [r_type] include [test]

but, by doing this saved only 5 seconds.

Attaching the Execution Plan Snaps (Without above indexes and with indexes).

Please advice.

The best option if you really want to squeeze out the most is to parallelise the query.

Instead of a single UPDATE, run multiple copies. Each of them operate on a subset of the data in the temporary table. Keep adding more copies and more subsets until you run out of cores of CPU (whatever happens first).

If you don't have the ability to parallelise. Run two UPDATES, one that works on the first part of the OR, another on the second. If possible, add indexes on t_h_bg_pc_it to support both queries. Like this:

Declare @date datetime = '09-Dec-2013'
update  #tmp_sp_abc
set     test = rtrim(xyz_test)
from    #tmp_sp_abc t1
join    t_h_bg_pc_it t2
  on    t2.id_i = t1.i
where   t1.r_type = 1
and     t2.[date] = @date

update  #tmp_sp_abc
set     test = rtrim(xyz_test)
from    #tmp_sp_abc t1
join    t_h_bg_pc_it t2
  on    tt2.id_s1 = t1.s
where   t1.r_type = 1
and     t2.[date] = @date

You can now index t_h_bg_pc_it on both s, date, xyz_test and id_id, date, xyz_test (two indexes). This should speed things up. It may also be useful to index #tmp_sp_abc on r_type. If possible, do both the parallelism and the indexing/rewrite.

Finally: Fire whoever it is that comes up with naming conventions for your tables. Life is too short for this type of cryptic naming, SQL Server does auto-completion.


Multidimensional databases on SQL Server 2008 R2? I am developing a business intelligence-product using ASP and SQL Server 2008 R2.

Each time I get a new client I'm copying a current client's database without data. This is OK for now, but can be annoying on the long term.

My new problem is: When doing further development on the product with changes on the database setup (column names and such), then these changes needs to be done on every client-database.

Is it possible to do any kind of multidimensional data warehousing - Having separate data-entries on the "same" table?

I hope you know what I mean :)

FYI: Each client have its own user account mapped to their own database to increase the security

On the trials and tribulations of making generic B.I. products

For the moment I'm assuming that the physical structure of the data model remains the same and you just want to change fields and their definitions to site-specific customisations.  If you want to frig the database structure you're really into building bespoke systems.  The other assumption that I'm going to make is that you have a star schema.

This can be done with metadata for each of the dimensions and fact tables that has the attributes for each table.  This metadata allows you to construct a customised view over a generic base structure.  You will have to structure the ETL to map to the base data but you can configure a customised view over it.

There are a couple of ways you can manifest the base tables: generic columns and an entity-attribute-value structure.  In each case, the customer's view is customised by metadata stored in the system.

Generic columns

A table with generic columns will have columns with names like 'Code1', 'Code2' etc. and groups of columns of particular types (code, money, float, int etc.).  You implement a table like this for every dimension in your system, and a table with some integer and money values for each of the fact tables.

This is customised by creating a view that links the columns to a business facing name.  Te metadata is used to generate the view.  If you have cubes and/or a suite of reports sitting on top of this, you can also programatically annotate the dimensions and report fields based on the metadata, allowing them to be productised but customisable.

The view is simply a view over the base table that renames the columns.

Advantage: The structure is relatively efficient to query, even through the views.
Disadvantage: The tables have a finite number of columns, so you may need to alter them if you run out.
Disadvantage: The meaning of the underlying data is quite opaque without the view, which doesn't help the readibility of the ETL code.

Entity-Attribute-Value

An entity-attribute-value structure has a 1:M relationship against the base table.  The relationship has the attribute type (from a reference table) and the value against the parent entity's primary key.

You have a base table, a reference table for the attribute names and types and a link table that records the value for each attribute against the entity.  It is commonly used on software products.  The view has to calculate a pivot or similar function to flatten the data out and can be generated from the attribute list for the entity type.

Advantage: Infinitely flexible - you can add attribubtes by updating a reference table, although you will need to modify the ETL code to populate it.
Disadvantage: Relatively inefficient and fiddly to query directly.  You may want to build a supplementary process to produce a flattened view.  The code that does this can be generated from the metadata, though.

Similar things can be done with base metrics on the fact tables.

A bit of experimentation will show you how to programatically annotate columns onto a cube dimension or report model, so it is possible to make a template and use a metadata based system to synchronise the cubes or front-end artifacts by generating columns.

If you build reports in a templatable way (base columns with particular signatures) you may also be able to do something similar with canned reports if you have a significant body of these as a part of your product.  This might be getting into diminishing returns, though.

One golden rule:

Never, ever edit generated code, or make an architecture that relies on subsequent human intervention in generated code.  That way lies madness.


The distribution agent failed to create temporary files I'm testing transactional replication between 4 databases, 2 in one machine(Virtual) and 2 in other(also virtual). None of my virtual machines is running an anti-virus.

It's all working as expected, unless I try to insert a blob of considerable size (65mb in this case) if I insert a blob of 3kb works just fine.

I'm having the following error "The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\100\COM' directory. System returned errorcode 5".

This problem is documented here.And I already changed the settings of that folder so that is not read-only anymore (right click on the folder->properties and changed the read only-attribute) I'm not sure if this is the right way to grant permissions to do it.

The reason I say I have no anti-virus running is because I also read that was one source for this problem, as the antivirus might block the folder while inspecting it.

Also tried to change Distribution Profile for OLEDB streaming, that also didn't work..

What else can I do to solve this problem?

Error 5 is an NTFS permissions problem.  What account is the SQL Agent service on the distributor running under?


Is the Execution Path for a Function invoked inside a Stored Procedure cached? According to some of my research a benefit of using Stored Procedures over Functions is that they cache an execution path which provides performance benefit due to not having to recalculate it.

So I was wondering if I invoke a Function from within a Stored Procedure is the entire Stored Procedure still cached?

Here's a mocked up example function - it would be used to add further details onto a table selected in the Stored Procedure.

CREATE FUNCTION [dbo].[fnExampleFunction]
(@EmployeeDetails   EMPLOYEEDETAILS READONLY
)
RETURNS @retEmployees TABLE
(AppraisalLevelId INT,
 JobTitleId  INT,
 LocationId INT,
 Name1 nvarchar(200),
 Name2 nvarchar(200),
 Name3 nvarchar(200)
)
AS
     BEGIN
         INSERT INTO @retEmployees
                SELECT
             ed.*,
              jt.JobTitleName,
              l.LocationName,
              al.AppraisalLevelName
                FROM @EmployeeDetails ed
                     LEFT OUTER JOIN AppraisalLevels al ON al.AppraisalLevelId = ed.AppraisalLevelId
                     LEFT OUTER JOIN EmployeeDetails m ON m.EmployeeId = ed.ManagerId
                     LEFT OUTER JOIN JobTitles jt ON jt.JobTitleId = ed.JobTitleId
                     LEFT OUTER JOIN Locations l ON l.LocationId = ed.LocationId;
         RETURN;
     END;
GO

So the Stored Procedure in psuedo would be used IF @Condition Return SelectedTable ELSE Return fnExampleFunction(SelectedTable)

First of all, why do you use multi-statement table-valued functions instead of inline function?  Multi-statement function calls are expensive and introduce significant CPU overhead.

Second, the way you want to use it is very strange:

  The Stored Procedure in psuedo would be used IF @Condition Return
  SelectedTable ELSE Return fnExampleFunction(SelectedTable)

You just cannot return tables from sp using Return.

And now to your main question:

  So I was wondering if I invoke a Function from within a Stored
  Procedure is the entire Stored Procedure still cached?

Of course stored procedure's plan will be cached.

There is a difference of what is cached when you use multi-statement function  (which starts with a  BEGIN  and ends with an  END  keyword) or inline function:

Multi-statement functions (table-valued or scalar) have query plans on their own. Inline functions don't. SQL Server expands and embeds inline table-valued functions into the actual queries, similar to regular views, and it optimizes their statements as part of the queries. As a result, there are no separate calls of the function and you don’t have to deal with its associated overhead.

Here is how your multi-statement function can be re-written as inline function:

CREATE FUNCTION [dbo].[fnExampleFunction]
(
    @EmployeeDetails   EMPLOYEEDETAILS READONLY
)
RETURNS TABLE AS RETURN
(
    SELECT
    ed.*,
    jt.JobTitleName,
    l.LocationName,
    al.AppraisalLevelName
    FROM @EmployeeDetails ed
            LEFT OUTER JOIN AppraisalLevels al ON al.AppraisalLevelId = ed.AppraisalLevelId
            LEFT OUTER JOIN EmployeeDetails m ON m.EmployeeId = ed.ManagerId
            LEFT OUTER JOIN JobTitles jt ON jt.JobTitleId = ed.JobTitleId
            LEFT OUTER JOIN Locations l ON l.LocationId = ed.LocationId
)


Subquery in INSERT works in SQL but not PDO I want to insert a new dataset into a MySQL table tab with external data, but also with data from another table otherTab using the others' table primary key and another condition. However, it could be that the requested row simply does not exist (anymore) or the result set is empty due to a mismatch in the other supplied data. All columns are forbidden to be NULL.
My first attempt was:
INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234

but the problem with it is that a returned empty result set is cast to the type of the column in tab, leading to a 0 as entry data.
The query shall be efficient and avoid unnecessary querying. This is how I achieve it with four subqueries:
INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234
WHERE EXISTS (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT')
  AND EXISTS (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG')

Is this, or a JOIN, or a transaction with a temporary variable and IF construct the only option?
I tried with other constructs, e.g. (SELECT IFNULL(SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT', NULL)) to enforce NULL or even a string into the target column, but it also gets casted to a 0 or some value instead.
EDIT
Here is the code for dbFiddle:
code
CREATE TABLE `tab` (
  `id` int NOT NULL,
  `seUuid4` binary(16) NOT NULL,
  `rxUuid4` binary(16) NOT NULL,
  `text` varchar(16)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `otherTab` (
  `uuid4` binary(16) NOT NULL,
  `lgUuid4` binary(16) NOT NULL,
  `data` varchar(16)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `otherTab`
  ADD PRIMARY KEY(`uuid4`);
ALTER TABLE `tab`
  ADD CONSTRAINT `tab_ibfk_1` FOREIGN KEY (`rxUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `tab_ibfk_2` FOREIGN KEY (`seUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO `otherTab` (uuid4, lgUuid4, data) VALUES
(UNHEX("22224444aaaa49c782408b2fe8c4dee0"), UNHEX("00001234aaaa4444aaaa432187654321"), "JPG"),
(UNHEX("11113333aaaa49c782408b2fe8c4dee0"), UNHEX("12340000bbbb6666bbbb432187654321"), "TXT");

INSERT INTO tab (id, seUuid4, rxUuid4, text)
SELECT
    1,
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('00001234aaaa4444aaaa432187654321') AND data = 'JPK' LIMIT 0,1),
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('12340000bbbb6666bbbb432187654321') AND data = 'TXT' LIMIT 0,1),
    'some text'

This interestingly works exactly as expected. I verified my code (PHP - I know..) and the PDO prepared statement fires out exactly the same command, but it gets inserted as INSERT INTO tab (id, seUuid4, rxUuid4) VALUES (1, 0x00000000000000000000000000000000, 0x00000000000000000000000000000000, 'datatext'); while the SQL client and phpMyadmin deliver the expected cannot insert null error message.
I am off to check the PDO options - sorry for the confusion :-)
EDIT2: changed the title name and will leave it open if someone has an idea by chance. Otherwise, I will ask the PHP/PDO experts.

I could fix your problem, with the insert
INSERT INTO tab (id, seUuid4, rxUuid4, text)
SELECT
    1,
   IFNULL ((SELECT  uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('00001234aaaa4444aaaa432187654321') AND data = 'JPK' LIMIT 0,1) IS NULL,0),
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('12340000bbbb6666bbbb432187654321') AND data = 'TXT' LIMIT 0,1),
    'some text';

but this gives following error

Schema Error: Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (test.tab, CONSTRAINT tab_ibfk_2 FOREIGN KEY (seUuid4) REFERENCES otherTab (uuid4))

Which means that you must enter there a uuid, which is exists


How does SQL Server determine how much memory to allocate to Data cache SQL Server Standard Edition has a limit to how much memory it can use for the data cache. With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache? Lets assume we have 400GB of memory- so much of that will be allocated to the data cache, plan cache and working memory.

How does SQL Server decide how much of this 400GB goes to each respective area?

With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache?

SQL Server memory( Buffer Pool) is designed to adjust its requirement according to available memory on the system (I mean RAM present on the Windows Server). The exact algorithm is something no one knows but what I can tell you is when SQL Server boots up it start allocating memory keeping in mind three flags, these are resource monitor flags.

MEMPHYSICAL_HIGH
MEMPHYSICAL_LOW
RESOURCE_MEM_STEADY

Actually there is lot of things underneath but I am trying to simplify things for you. SQL Server will keep allocating memory based on its need as long as  MEMPHYSICAL_HIGH notification is signaled in window or untill it feels it no longer requires memory and most pages it needs are cached in memory. If there is a memory shortage MEMPHYSICAL_LOW is flagged and SQL Server will try to trim its various caches. When a steady state is reached flag RESOURCE_MEM_STEADY is flagged and this means SQl Server has memory somewhere between high and low flag. I would quote from Karthick PK's blog

  The default level of available memory that signals a
  LowMemoryResourceNotification event is approximately 32 MB per 4 GB,
  to a maximum of 64 MB. (By default, the threshold is 64mb on most
  systems).

  The default level that signals a high-memory-resource notification
  event is three times the default low-memory value (By default, the
  threshold is 64*3=192 MB on most systems).

  Key points:

  Once the available memory on the system goes below 192 MB HighMemoryResourceNotification (MEMPHYSICAL_HIGH) signal is revoked by
  windows and SQL Server will not grow its Bpool.
  Once the available memory on the system goes below 64 MB LowMemoryResourceNotification (MEMPHYSICAL_low) is signaled by windows
  and SQL Server will shrink its Bpool (reduce its memory usage).
  When the available memory in the system is between 192Mb and 64 Mb (I.e between LowMemoryThreshold and HighMemoryThreshold) SQL Server
  will not grow or shrink its usage (With some exceptions which we will
  see in a while)

You of course have option to change the threshold values from the registry.

  How does SQL Server decide how much of this 400GB goes to each respective area?

Again its dynamic and depends on requirement. So you have 400 GB memory and suppose you have set max server memory 350 GB. SQL Server will not, at startup, try to grab all 350 GB. It depends on workload. If, after startup you are running heavy workload it will take at max to  350 GB and if it further needs memory it will take help of Lazywriter and Checkpoint process to create some space in buffer pool by flushing dirty pages to disk and pull more pages. The moral here is there is no fixed rule, lot of things depend on workload and SQL Server configuration.

The above also holds true for data cache which takes chunk of buffer pool.

PLAN CACHE:

Plan cache is bit interesting. Let us talk after SQL Server 2005 Sp2 when there was no upper limit on plan cache in SQL Server. The plan cache would normally try to grow when queries start hitting SQL server but it cannot grow indefinitely. There is a formula which keeps track on what maximum plan cache can grow before old/stale plans be removed from it to create space for new one.  See below pic taken From Kalen Delaneys's blog.

The Cache pressure limit  defines after what limit old entries from plan cache would be removed. To calculate Target memory which would be used in calculating pressure limit see This Q&A Blog from Kalen

I do not have the formula for newer versions. The things changed with arrival of query store in SQL Server 2016 but I am not sure whether this formula changed. Anyways I do not think there is whole point in discussing this formula.


Calculating a cumulative sum with an alias in postgresql I have two tables - user credit and payments.
credit is just a total of the amount of credit a user has at the present.
Payments is a list of payments by date that a user needs to make.
I need to create a report showing the amount of credit available for each payment a user is to make - taking into account the credit used up on previous payments.
CREATE TABLE credit (
  user_id INT,
  amount INT
);

CREATE TABLE payments (
  user_id INT,
  due timestamp,
  amount INT
);

INSERT INTO credit values (1, 100);
INSERT INTO credit values (2, 200);

INSERT INTO payments values (1, '2021-04-01', 20);
INSERT INTO payments values (1, '2021-04-02', 20);
INSERT INTO payments values (1, '2021-04-03', 20);
INSERT INTO payments values (2, '2021-04-01', 100);
INSERT INTO payments values (2, '2021-04-02', 300);
INSERT INTO payments values (3, '2021-04-03', 20);

Result should look like this:

user_id
due
amount
credit_available
credit_used
credit_remaining

1
2021-04-01
20
100
20
80

1
2021-04-02
20
80
20
60

1
2021-04-02
20
60
20
40

2
2021-04-01
100
200
100
100

2
2021-04-01
200
100
100
0

3
2021-04-01
20
0
0
0

I have set up a fiddle here:
http://sqlfiddle.com/#!17/e812b/9/0
I thought I could do it pretty simply with a LAG() but I can't reference the previous row's calculated alias credit_remaining column within the select like so:
SELECT
    ...,
    least(0, payments.amount - LAG(credit_remaining, 1, credit.amount) OVER (PARTITION BY user_id ORDER BY user_id, due)) as credit_remaining
FROM ...

SELECT user_id,
       payments.due,
       payments.amount,
       COALESCE(credit.amount, 0) + payments.amount - SUM(payments.amount) OVER (PARTITION BY user_id ORDER BY payments.due) available,
       LEAST(payments.amount, COALESCE(credit.amount, 0) + payments.amount - SUM(payments.amount) OVER (PARTITION BY user_id ORDER BY payments.due)) used,
       GREATEST(0, COALESCE(credit.amount, 0) - SUM(payments.amount) OVER (PARTITION BY user_id ORDER BY payments.due)) remaining
FROM ( SELECT user_id FROM credit
       UNION
       SELECT user_id FROM payments ) userlist
LEFT JOIN credit USING (user_id)
LEFT JOIN payments USING (user_id)
ORDER BY user_id, due

https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=7e5814dbe02ced69684d331b277dd205
PS. payments (user_id, due) must be defined as unique - if not then the output is indefinite (or you must use another ordering in the window definition, for example, by payments.id additionally).


Table with only a single constant value? I use C# entity framework code first.

My knowledge regarding databases is almost nothing. I don't know if it's common or good practice so I wanted to ask you guys how I should do it.

Say I have a table of letters and a table of stations.

I develop an application that sends letters from one station to other stations. Each letter can be sent to different stations - it's a many to many relationship.

Each letter that is sent is associated with a station - the source station of the letter (a letter cannot be sent to it's own source station, etc..).

Each database instance represents a station.

On application startup I know which station is my source station and want to save that information in the database.

How should I save which station is the source station? Should I have a flag in the stations table in each row indicating if it is the current instance station? This sounds bad to me.

Is there a way to have a table with only 1 value? For example that will be called InstanceStation that will contain only one row with a single column - StationId? Is this a good practice?

I tried to be as clear as possible, I hope my situation is clear.

Having a flag on the Stations table that indicates which station is local would be exactly how I'd handle this, presuming you don't have a billion stations.  I'd likely call the column IsSourceStation or something, and make it a BIT value, that can accept NULL.  I would mark the local station row as 1, and leave all other rows as NULL, since that won't take any space (see my comments below regarding space).

I'd add a filtered index to the IsSourceStation column, filtered as WHERE IsSourceStation = 1.  This index will allow extremely fast lookups to determine the name of the local station, if that is required.

Looking for the Stations row that corresponds to our "home" station could be accomplished by:

SELECT *
FROM Stations
WHERE IsSourceStation = 1;

This will be very fast with the index I suggested, regardless of how many rows are in the Stations table.

Looking to confirm a station is not the home station?  Use this:

IF EXISTS (
    SELECT 1
    FROM Stations
    WHERE IsSourceStation IS NULL
        AND StationID = 1234
    )
BEGIN
    -- StationID 1234 is NOT the home station
END

The null bitmap used in SQL Server is a fantastic optimization designed for just this type of situation where very few rows in a nullable column actually contain a value.

My statement above, while technically correct in that the null bitmap is used to save space, in the case of a table with a single bit column, there is no appreciable difference between defining the column as nullable vs having it be not nullable, with a default value of 0.  I used the following test bed to determine this on SQL Server 2012:

USE tempdb;
IF EXISTS (SELECT 1 FROM sys.tables t WHERE t.name = 'TestBit')
DROP TABLE dbo.TestBit;
IF EXISTS (SELECT 1 FROM sys.tables t WHERE t.name = 'TestBitNotNull')
DROP TABLE dbo.TestBitNotNull;

CREATE TABLE dbo.TestBit
(
    TestBitID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , IsBit BIT NULL
);

INSERT INTO dbo.TestBit (IsBit)
SELECT TOP(1000000) NULL
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

CREATE TABLE dbo.TestBitNotNull
(
    TestBitID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , IsBit BIT NOT NULL
        CONSTRAINT DF_TestBitNotNull
        DEFAULT ((0))
);

INSERT INTO dbo.TestBitNotNull(IsBit)
SELECT TOP(1000000) 0
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

The above code creates two tables, each with a single INT column, and a single BIT column.  The first table allows the BIT column to be NULL; the second table does not.

I used the following to inspect the actual on-disk data pages for the first page of each table:

SELECT TOP(10) *
    , %%PHYSLOC%%
FROM dbo.TestBit
    CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%);

SELECT TOP(10) *
    , %%PHYSLOC%%
FROM dbo.TestBitNotNull
    CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%);

The DBCC PAGE command can be used with the last option set to "3" to see the actual column values stored on the page, along with quite a bit of detail about each row "slot".  In my two tables above, the first page of each table was 334 and 342 respectively.

DBCC PAGE (2, 1, 334, 3) WITH TABLERESULTS;

DBCC PAGE (2, 1, 342, 3) WITH TABLERESULTS;

The output for slot 0 from each of the DBCC PAGE commands above shows the following for the table with the nullable column:

And this, for the column that is not nullable:

The "memory dump" value in the 2nd column on the first row shows the actual data stored on disk in hex format; both variants are precisely the same.

Indeed, when looking at the on-disk size for both tables using this query:

SELECT o.name
    , i.name
    , p.partition_number
    , p.rows
    , UsedMB = au.used_pages / 8192E0
    , TotalMB = au.total_pages / 8192E0
    , AvgRowsPerPage = p.rows / CONVERT(DECIMAL(10,2), au.used_pages)
FROM sys.allocation_units au WITH (NOLOCK)
    INNER JOIN sys.partitions p WITH (NOLOCK) ON ((au.type = 1 OR au.type = 3) AND au.container_id = p.hobt_id) OR (au.type = 2 AND au.container_id = p.partition_id)
    INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
    INNER JOIN sys.objects o WITH (NOLOCK) ON p.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
WHERE o.name = 'TestBit'
    OR o.name = 'TestBitNotNull'
ORDER BY o.name;

We see both tables are identically sized:

My conclusion in light of the above data is that it is probably easier just to use a non-nullable column with a default value of 0 since that eliminates the potentially problematic null handling required for nullable columns.

Where the null bitmap does help is when you have more than 8 nullable bit fields.  If you take my sample tables TestBit and TestBitNotNull and give them 16 bit fields each, you'll see the following table sizes for 1,000,000 rows:


Application Continuity on Single instance I recently was going through Application Continuity in Oracle 12c. As I read through the Oracle link which I understand when used in RAC, will be able to replay the transaction (DDL/DML) in the event the node goes down.

In the link, it also mentions creating a service for non-RAC (standalone) instances as well. How does Application continuity work in case of single instance.

declare
params dbms_service.svc_parameter_array;
begin
params('FAILOVER_TYPE'):='TRANSACTION';
params('REPLAY_INITIATION_TIMEOUT'):=1800;
params('RETENTION_TIMEOUT'):=604800;
params('FAILOVER_DELAY'):=10;
params('FAILOVER_RETRIES'):=30;
params('commit_outcome'):='true';
params('aq_ha_notifications'):='true';
dbms_service.modify_service('[your service]',params);
end;
/

Say, if a recoverable error occurs on the single instance, where does it replay the transaction? Can someone help me understand this?

Application Continuity is not limited to RAC. It works with Data Guard as well. For example an in-flight transaction can be replayed after a switchover on the new primary (former standby). With a single instance primary and standby, this requires the Active Data Guard option to be licensed.

Application Continuity

  Application Continuity is supported for Oracle Data Guard switchovers
  to physical standby databases. It is also supported for fast-start
  failover to physical standbys in maximum availability data protection
  mode. Note that primary and standby databases must be licensed for
  Oracle RAC or Oracle Active Data Guard in order to use Application
  Continuity.


Trying to understand an RCSI example-The Potential Dangers of the Read Committed Snapshot Isolation Level There's this article The Potential Dangers of the Read Committed Snapshot Isolation Level which demonstrates RC vs RCSI isolation levels.
I get the RC example but not the RCSI one.In particular how the query has gotten the result -3.
Can somone explain how this query works under RCSI ?

Under locking read committed it's very likely (but not guaranteed) that when two sessions run this code
BEGIN TRANSACTION
DECLARE @QtyRequired int, @QtyRemain int
SELECT @QtyRequired = 4
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1
IF @QtyRemain - @QtyRequired >= 0
BEGIN
    UPDATE Inventory SET Qty = Qty - @QtyRequired
    WHERE ItemID = 796 AND LocationID = 1
    -- Do other stuff in other tables or databases to check out the item
    WAITFOR DELAY '00:00:10'
    SELECT 'Checkout complete'
END
ELSE
    SELECT 'Not enough qty!'
COMMIT TRANSACTION

There will be enough difference in time that the second session runs
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1

after the first session has run
UPDATE Inventory SET Qty = Qty - @QtyRequired
WHERE ItemID = 796 AND LocationID = 1

And so the second session will be unable to acquire an S lock on the row, and will be blocked until the first session commits.  This isn't a guarantee, however, as it's quite possible that both sessions run
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1

before either of them runs the update.
With RCSI it's simply more likely that they will both read the same value for @QtyRemain as the SELECT is never blocked by the pending UPDATE on the other session, and just returns the "last-known-good" value for the row from the version store.
But since the locking read committed version has the same problem, albeit in a smaller window, they are both broken, and should be fixed by changing the first query to:
SELECT @QtyRemain = SUM(QTY) FROM Inventory with (UPDLOCK) WHERE ItemID = 796 AND LocationID = 1

Which guarantees that even if two sessions attempt the query at the same time, one will be granted the U lock, and the other will be blocked until the first session commits.


MySQL CLustering Options I have two MySQL Database Servers i.e.
One is Active Server --> Master and second is Passive Server --> Slave.

And as we know we have 3-4 MySQL HR,DR Solutions / Clustering options.For example

01 : MySQL NDB Cluster

02 : MySQL InnoDB Cluster

03 : MySQL Cluster Carrier Grade

My First Question is i want to deploy One Active & One Passive MySQL Server so which Cluster Option / HA,DR Option will be fit for above mentioned scenario ?

My Second Question is suppose i have deploy One Active & One Passive MySQL Server in any above mentioned Cluster Option / HA,DR Option. So what about licensing ? I have to buy license of MySQL Servers/ Cluster ?

Regards,

Mubashar Iftikhar

Two servers cannot give you as much HA as three.  And the three need to be geographically spread across 3 data centers.  That way, if a datacenter fails (and they do), the other two have a quorum and can continue running.

If you have already implemented your application using InnoDB, then the simplest is to go with 02: InnoDB Cluster.  Yes, it can have one active and multiple passive nodes.  I don't think there are $$ licenses for 01 and 02.

Another option is (again, no $$ license) is to have 3+ servers using InnoDB, plus Galera's clustering solution.  (Or MariaDB, which has Galera builtin.)


Mysql slave status is completely perfect but data is not reflecting I have setup replication using mysql utilities. Slave can able to read and execute master's position and binlog files perfectly. seconds_behind_master is also 0. But data is not reflecting into slave. what could be the reason ?
Anybody help e please

Do you use a maria-DB 10 Server with version below 10.0.18? Do your relay-log files contain any transactions? There was a bug which could lead to sich problems.


Postgres - indexing multi-tenant database Our multi-tenant application uses Postgres database. The application was created many years ago, and now we are seeing some performance issues when load increases.
Every database table we have has at least two indexes - Id (PK) and TenantId (FK). On every select we added TenantId filter, and when we fetch an item by Id there also TenantId is added in the where clause.
Now, my question is that did we do it wrong. Instead of adding two indexes we should have added index on (TenantId, Id) and that would have improved our query performance? Would this have improved both the scenarios - when selecting using tenantId and tenantId + Id?

Yes, that single index would serve both queries. But you cannot get rid of the index on id, since it is necessary to implement the primary key constraint.
I would research if the indexes are really the cause of your performance problem.


XtraBackup Restore Full Backup OK but I can't initialize mysqld I'm using XtraBackup mode Streaming and Compressing Backups to perform my backup on a MariaDB 10.19:

$ innobackupex --user=$db_user --password=$db_pass --compact --stream=tar ./ --no-timestamp | gzip - > $WORKING_DIR/$BCK_FILE

All seems ok even when I restore the backup, innobackup output shows:

...
innobackupex: Finished copying back files.

160801 13:52:31  innobackupex: completed OK!

but when I start the mysqld I find the next message in its log:

2016-08-01 15:52:33 7f08d8ed87c0 InnoDB: Error: page 371 log sequence number 2340146
InnoDB: is in the future! Current system log sequence number 2337804.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
...
a lot of times!

In some cases I can't initialize the mysql.

On the page 23 of the manual 'PerconaXtraBackup-2.1.9.pdf' you can read:
'Note that the streamed backup will need to be prepared before restoration. Streaming mode does not prepare the backup.'

I have no idea of how prepare the backup before the restoration in this situation.
In the manual explains how prepare it using --apply-log argument but the backups aren't performed using stream and compressing mode.

Anybody knows how prepare it correctly?

How can I retore it?

Should I use the --apply-log to prepare it? How?

Thanks for your time.

Best rgrds,

Boadrius

In Manual itself it is explained that

Decompress backup

$ innobackupex --decompress /data/backup/2013-08-01_11-24-04/

When the files are uncompressed you can prepare the backup with the --apply-log option:

$ innobackupex --apply-log /data/backup/2013-08-01_11-24-04/

You should check for a confirmation message:

130802 02:51:02 innobackupex: completed OK!

Now the files in /data/backups/2013-08-01_11-24-04 is ready to be used by the server.


Postgres server is not starting I have installed postgres 11 earlier on my MacBook Pro. Suddenly it stopped working. Usually I start my postgres server by following command

pg_ctl -D /usr/local/var/postgres start

and it returning the following error

dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.63.dylib
  Referenced from: /usr/local/Cellar/postgresql/11.1_1/bin/postgres
  Reason: image not found
no data was returned by command ""/usr/local/Cellar/postgresql/11.1_1/bin/postgres" -V"
The program "postgres" is needed by pg_ctl but was not found in the
same directory as "/usr/local/Cellar/postgresql/11.1_1/bin/pg_ctl".
Check your installation.

How can I solve this?

try to install\upgrade, Assuming you have Ubuntu on your machines then execute the following commands:

Upgrade:

sudo apt-get update
sudo apt upgrade

Install:

sudo apt install zsh
sudo apt-get install powerline fonts-powerline

If you have Fedora:

sudo dnf install zsh

MacOS

brew upgrade postgresql


SQL query failing because of commented part of T-SQL query I am executing below query but I am facing issue.

Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along with remote connections and I am able to execute the code on local machines (Ex: If I connect to SQL 2008 R2 machine and execute on SQL 2008 R2 Successful, If I connect to SQL 2014 Machine Successful on SSMS on SQL 2014.Issue is only when I connect to SQL 2008 R2 and execute on SQL 2014.When I remove the commented part I am able to execute the code successfully using SQLCMD.) I am not aware of any compatibility issues of this case.

  Msg 121, Level 20, State 0, Line 0
  A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

But when I removed the commented part and execute the query it is running successfully in fraction of seconds. Please let me know what is wrong with the commented part. I have pasted the T-SQL  query below.

/********************************************************************************
* Summary: Creates source database schema for BCP Import. Only created PK on the tables
* No other constraints are replicated
* Parameters:
* tempdbname
* Called by: PROCS.uspEngagement_Import
* Remarks:
* Copyright: Copyright 2012 by XXXXX XXXXX. All rights reserved.
* Author: XXXXX
* Creation Date: July 07 2012
* Revision History:
* <Modified By> <Date Modified> <Changes made>
* XXXXX     July 31 2012    Removed IF EXISTS as this is required for non sysadmin user
* XXXXX     July 31 2012    Removed reference to sys.database_files as it returns NULL for non sysadmin users
* XXXXX     Aug 26 2012     Added Logic for FILESTREAM, ROWGUIDCOL and UNIQUE Columns. Note FILESTREAM
                                Can be created only if there is a UNIQUE ROWGUIDCOL
* XXXXX     Sep 08 2012     Removed logic to create default constraints. The defaults are not required besides
                                are hinderance for schema upgrades
* XXXXX     Feb 02 2013     Added logic to avoid further generation of script if DB already exists
* XXXXX     Oct 24 2013     Added logic to create Database mdf and ldf in the path specified by @pstrFilePath
                                Required for Bug# 29221
                                Further removed comments and modified indentation
* XXXXX     Jan 06 2014     Refer Bug 30633. Conversion fails if schemas with '\' are present
                                in the source XXXXX Database. Added square braces '[' to take care of this issue
                                when generating schemas
                                Further removed the redundant code to PRINT 'Database Exists' if the Temp DB
                                already exists
* XXXXX/XXXXX Dec 09 2014    PaperSizeinBytes column having issues during BCP IN, added logic to manage computed columns and its definition.
                                Computed column definition being hard coded now as xp_cmdshell cannot read from sys.computed_columns
* XXXXX  Dec 18 2015        Bug fix#73849: Conversion creates database in paths not excluded by antivirus
                                (mdf, ldf and filestrTemp DB to be created where current XXXXX DB SQL Server is configured)
*********************************************************************************/

Given that the script works without the header comment block but fails with it (but only for remote / TCP connections), the issue is most likely due to a parsing error. For example, SQLCMD and SSMS do their own pre-parsing of the submitted script (to break it into batches), and sometimes that pre-parsing doesn't work correctly (e.g. "GO" in 2nd half of nested block comments breaks batch parsing in SSMS and SQLCMD). To narrow it down, you can try the following tests:

Highlight the entire /*...*/ comment block at the top and then hit Control-K, Control-C to comment each line out via inline comments (i.e. --):

--/********************************************************************************
--* Summary: Creates source database schema for BCP Import. Only created PK on ...
--...
--*********************************************************************************/

Remove just a portion of the comment block. So maybe start by removing most of it (keep the top 3 lines and the bottom ***/ line). Then, if it works, keep adding back in the lines, starting with line #4, until it stops working. Meaning, the comment block would start out as being just the following:

/********************************************************************************
* Summary: Creates source database schema for BCP Import. Only created PK on ...
* No other constraints are replicated
*********************************************************************************/

If that works, add in the 4th line so that the comment block is now:

/********************************************************************************
* Summary: Creates source database schema for BCP Import. Only created PK on ...
* No other constraints are replicated
* Parameters:
*********************************************************************************/

O.P. tried method #2 above and was able to get the script to work remotely with only the following line (4th from the bottom) removed:

/*******...
  Computed column definition being hard coded now as xp_cmdshell cannot read from sys.computed_columns
...*******/

O.P. then used inline comment (method #1 above) to narrow down a subset of the offending line and was able to get the following to work:

/*******...
  Computed column definition being hard coded now as --xp_cmdshell cannot read from sys.computed_columns
...*******/

At this point it is still unclear as to the exact root cause of the issue. I was unable to reproduce this error using the exact same comment block, testing in both SSMS 2008 R2 and SSMS 2012, even enabling SQLCMD Mode and forcing a TCP/IP connection (verifying via SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;). @MartinSmith asked in the discussion if there was "any sort of network security between the client and the server that might take exception to xp_cmdshell". That still could be a possibility, hence more testing is needed in order to determine what layer, specifically, is unhappy with xp_cmdshell (or some other part of that line that is to the right of the newly added --).

UPDATE

While the root cause has not been identified, here is more info to hopefully help narrow this down:

The O.P. ran the following two statements via SSMS, SQLCMD, and .NET SqlClient:

Running just xp_cmdshell (by itself) gets a "missing parameter" error, not the timeout
Running xp_cmdshell 'cls' gets the timeout error. The error includes "HRESULT: -2146232060".

SELECT CONVERT(VARBINARY(30), -2146232060 );
-- 0x80131904

Searching on "HRESULT: 0x80131904" finds that it is a not-very-specific SQL Server error (but mostly related to SharePoint, for some reason). Here are some of the variations on it:

https://social.technet.microsoft.com/Forums/office/en-US/24e3d1ea-7827-4e19-b099-fd44e7c0f9ec/error-exception-from-hresult-0x80131904?forum=sharepointadminprevious

  My problem was solved by rebooting all servers in the farm, including SQL Server, which would have truncated TEMPDB, as one article suggests.

https://social.technet.microsoft.com/Forums/sharepoint/en-US/823ce071-72a8-4d6d-9bef-068cbe2b602d/error-exception-from-hresult-0x80131904?forum=sharepointadminprevious

  We saw several errors on the SharePoint application showing up for Application EventIDs that led us to a VMWare forum that suggested disconnecting the CD/DVD drive from the SQL and SharePoint servers.  Since doing that the environment has been stable and there have been no  HRESULT: 0x80131904 errors.  From other forums it appears the issue is related to the location from which the media is used for installation.

https://social.technet.microsoft.com/Forums/office/en-US/21627022-f910-4bc8-a6f6-6e70f9b0ebff/exception-from-hresult-0x80131904?forum=sharepointadminprevious

  the root cause of the problem was that the location where our SQL server log files are being written to ran out of drive space (i.e. this being the SQL Server that's hosting our SharePoint content database).  We were able to resolve the problem by removing many of the older log files from that directory in order to free up some drive space.

https://stackoverflow.com/questions/23216704/sharepoint-2010-web-part-error-exception-from-hresult-0x80131904

  The comparers are case sensitive. should be . In my case, it was , changed to and it works now!

Meaning: this might be related to Locale / default language, Code Page, or Collation.

The O.P. ran the following three queries in SSMS and received an "Incorrect syntax" error:

select 1/0;
go
xp_cmdshell --comment
'cls';

and:

select 1/0;
go
axp_cmdshell --comment
'cls';

and:

select 1/0;
go
p_cmdshell 'cls';

All three of those queries received the following error:

  Msg 102, Level 15, State 1, Line 1
  Incorrect syntax near ' '.

The O.P. ran the following query in SSMS and received a different "Incorrect syntax" error, but not the timeout error:

select 1/0;
xp_cmdshell 'cls';

That query received the following error:

  Msg 102, Level 15, State 1, Line 2
  Incorrect syntax near 'xp_cmdshell'.

@Tom V mentioned that this issue has been submitted on Microsoft Connect, but has also been closed as "Not Reproducible":

Text 'xp_cmdshell' causes transport-level error: semaphore timeout period has expired from remote system.
Here are two very similar reports (both on S.O. but neither with any resolution):

SQL bcp The semaphore timeout period has expired
SQL Strange Timeout Issue


SQLPlus "no listener" only with a remote connection from Windows The Oracle database is running on a linux server.
The lsnrctl service is running.
Locally, I can connect to database from sqlplus and sql developer.
Remotely, from windows, I can't connect.
sqlplus on windows says that image above shows.
sql developer says that Network Adapter could not establish the connection.
I have done ping from the windows machine to the linux server and is ok.

lsnrctl status:
[oracle@oracledb ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-SEP-2015 19:30:58

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                21-SEP-2015 16:43:32
Uptime                    0 days 2 hr. 47 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "mondev" has 1 instance(s).
  Instance "mondev", status READY, has 1 handler(s) for this service...
Service "mondevXDB" has 1 instance(s).
  Instance "mondev", status READY, has 1 handler(s) for this service...
The command completed successfully

listener.ora content:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

The listener works as expected. It is configured to listen at localhost (127.0.0.1) and so it cannot accept connection from another host.

I assume that the listener runs on host with IP 192.168.1.130 .
So in the listener configfile change  localhost to 192.168.1.130 and restart the listener.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.130)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle


Releasing Free Space I am running a rather large database on SQL Server 2008 R2 Express. Recently I hit the 10GB limit, so the system would not allow me to create anymore objects, ect..

I have archived off a very large amount of records, around 3 quarters of the data that was there.

The database file is still around 10GB though, I was wondering if there is anything I need to do to release that free space in SQL Server.

If you are talking about the file system space and releasing it back to the Operating System, then you will have to manually do this operation.

First find out how much free space you have in each file:

use YourDatabase;
go

;with file_cte as
(
    select
        name,
        physical_name,
        size_mb =
            convert(decimal(11, 2), size * 8.0 / 1024),
        space_used_mb =
            convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
    from sys.database_files
)
select
    name,
    physical_name,
    size_mb,
    space_used_mb,
    free_space_mb = size_mb - space_used_mb,
    space_used_percent =
        convert(decimal(5, 2), space_used_mb / size_mb * 100)
from file_cte;

Then if you are absolutely sure that you need to shrink a database file, you can use DBCC SHRINKFILE().

To shrink your file, an example would be like this:

dbcc shrinkfile(YourDataFileName, <target_size_mb>);

But... only shrink your database files if you absolutely must.  If your files are going to re-consume that same space in the future, it doesn't make sense to shrink your files.  A necessary read is Why you should not shrink your data files by Paul Randal.


Join returns no results In a trigger, I have:

SELECT TOP 1 t.Id
FROM deleted d
INNER JOIN Table t
ON t.Id = d.Id

SELECT TOP 1 t.Id
FROM Table t
WHERE t.Id = (SELECT TOP 1 d.Id FROM deleted d)

Table is a related table that references the trigger's table. Trigger is after Delete

Why does the first return no result but the second does?

As Aaron Bertrand has pointed out on his comment, the only reason you has no records could be related to the order. But IMHO first query should return one record unless there are no coincidences on the related table.

CREATE TABLE T
(
    ID INT,
    F1 CHAR(1),
    PRIMARY KEY (ID DESC)
);

INSERT INTO T VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D');

CREATE TABLE TREF
(
    ID INT,
    F1 CHAR(1),
    PRIMARY KEY (ID DESC)
);

INSERT INTO TREF VALUES (1,'A'),(2,'B'),(5,'C'),(6,'D');

CREATE TABLE T1
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    RES NVARCHAR(MAX)
);
GO

8 rows affected

CREATE TRIGGER TRG_T_DELETE
ON T
AFTER DELETE
AS
BEGIN

    INSERT INTO T1
    SELECT 'COUNT: ' + CAST(COUNT(*) AS VARCHAR(10)) FROM deleted;

    INSERT INTO T1
    SELECT 'deleted: ' + (SELECT * FROM deleted WHERE ID = d.ID FOR XML RAW)
    FROM   deleted d;

    INSERT INTO T1
    SELECT TOP 1
           'join: ' + (SELECT * FROM TREF WHERE ID = T.ID FOR XML RAW)
    FROM   deleted d
    JOIN   TREF T
    ON     T.ID = D.ID;

    INSERT INTO T1
    SELECT TOP 1 'IN: ' + (SELECT * FROM TREF WHERE ID = T.ID FOR XML RAW)
    FROM   TREF T
    WHERE  T.ID = (SELECT TOP 1 ID FROM deleted ORDER BY ID DESC)
END
GO

✓

DELETE FROM T WHERE ID >= 2;
SELECT * FROM T1;
GO

ID | RES
-: | :----------------------------
 1 | COUNT: 3
 2 | deleted: <row ID="2" F1="B"/>
 3 | deleted: <row ID="3" F1="C"/>
 4 | deleted: <row ID="4" F1="D"/>
 5 | join: <row ID="2" F1="B"/>

dbfiddle here


Slave as a master in PostgreSQL I have set up a hot standby in  PostgreSQL 9.0  on Windows.

I want that, at the time of fail-over, the slave should take over the role of master and start performing operations (both read and write) automatically.

How can I do that?

I would start looking at repmgr from 2ndQuadrant. It is an open source replication management framework for PostgreSQL and takes care of things like failovers.

Otherwise you need to make sure the other node is dead (shoot the other node in the head), then remove the recovery.conf and restart.  Note that at this point you will have irrevocably failed over and you cannot fail back until you set up the former master as a slave to the new master and then fail over again.


SQL Pass 2013 Session recordings I am going through the below link and am really interested in watching Paul White's

Understanding the Optimizer and Interpreting Execution Plans

http://www.pass.org/summit/2013/Sessions/Schedule.aspx

But I couldn't find this session anywhere in the pass learning/recording session.

Can someone let me know how I can get this?

That was a full day pre-conference session, for which a fee (separate from the main conference) was required to attend. It was not recorded, and so will never be available.
From the PASS Summit 2013 FAQs: (emphasis added)

What will be included on the PASS Summit 2013 session recordings and how can I purchase them?
The PASS Summit 2013 session recordings include all the Conference Sessions, including half-day sessions and spotlights. (Note: Pre-Conference Sessions are not recorded.) Attendees can purchase either the session recordings download option for $195 or the recordings on USB flash drive for $240 (one session recordings purchase at the attendee price per attendee). Note: Shipping charges for the USB flash drive are $20 within the continental US and $30 international. Streaming of the sessions will be made available for free to attendees after Summit.


How to insert binary data into a PostgreSQL BYTEA column using libpqxx? I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx.

What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is lacking in details.  The fact that http://pqxx.org/ has been down for the past few days isn't helping me...

How would I go about inserting the contents of somefile.bin into a table with a BYTEA column?

What I have is along these lines:

pqxx::work work( conn );
work.exec( "CREATE TABLE test ( name varchar(20), data BYTEA )" );
work.exec( "INSERT INTO test( name, data ) VALUES ( 'foo', <insert filename.bin here> )" );
work.commit();

If it makes a difference, I'd like to use the new hex format for BYTEA available in PostgreSQL 9.1.

Took a while, but I figured out how to do it.  Here is some example code:

pqxx::connection conn( ... );
conn.prepare( "test", "INSERT INTO mytable( name, binfile ) VALUES ($1, $2)" );
pqxx::work work( conn );
for ( ... )
{
    std::string name = "foo";
    void * bin_data = ...; // -- obviously do what you need to get the binary data...
    size_t bin_size = 123; // -- ...and the size of the binary data

    pqxx::binarystring blob( bin_data, bin_size );
    pqxx::result r = work.prepared( "test" )( name )( blob ).exec();
}
work.commit();


MySQL NDB cluster maximum data size According to the limitations outlined at http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-limitations.html, there can only be 48 data nodes.

Does that mean, with number of replicas 2, I can actually have only 24 nodes to store data?

If the data memory is, say 4GB, then, does it mean I can only store 24*4=~100GB?

If so, how can I set up a cluster to store TBs of data?

Data does not have to be memory-resident in NDB.  (That limitation existed long ago.)  So, there is much more than 4GB limitation per pair of servers.


Returning value with Clustered index I have emp table (empno, ename, job, mgr, hire_date, sal, deptno)
with no index at all.
 If i run select * from emp  i get the rows ordered by empno.
Now i create a clustered index like this:

Create clustered index name on emp(ename)
If i run select * again, the rows are now ordered by ename.
Now if i drop the index. drop index name on emp I would expect to see the rows ordered back by empno, like before creating the index.
Instead I still see the rows ordered by ename, as if the index were still there.
Is there a way to see the rows ordered back by empno as before without using "order by empno?".
I tried to log off and connect again to make a refresh, but did not work.
The only way was to drop and recreate the table.
Maybe is there a parameter in the table definition that is changed when the  index was created, and it is not updated when the index is deleted?

It seems to me that there is a default method for displaying the data before the index, which is not preserved / restored when the index is deleted

When you created the clustered index with ename the rows in the table were shuffled to be in that order. When you dropped the index the rows were not reshuffled.

There is no guarantee that the rows will be returned by a query in the order that they are stored, but often this is what happens.

If the order is important then you would add an ORDER BY (and would be well advised to cluster the table on that field too to avoid sorting every time).

But don't choose a clustering key just because of that. For a more thorough discussion of clustering and heaps see http://kejser.org/clustered-indexes-vs-heaps/


Does binlog_group_commit_sync_delay affect existing binary logging in MySQL We want to tune the setting binlog_group_commit_sync_delay to increase replication performance. Our slave is currently two days behind. Does changing this setting affect the current backlog or will we see the impact after two days?

Existing logs are not affected. MySQL won't read and modify existing logs, it would be incredibly expensive and risky. So, yes, you'll see the benefit of this in 2 days (assuming the lag won't increase or decrease).
To me, it sounds like you are trying random optimisations to reduce the lag. If I'm correct, I recommend to investigate the reasons for slowness before making changes. Once you have an idea to make the slave faster, change a single variable, see if your hypothesis was correct, and if not undo the change.
If solving the problem is urgent, there are some things you can do to make a replica faster, like setting sync_bilog=0 and/or innodb_flush_log_at_trx_commit=0. In most cases you'd see the replica start to catch up. However, note that this is risky: if your replica crashes you will lose data. Depending on how unlucky you are, you may have to import a new backup from the source.


SQL Server Management Studio backup not shown when restoring I am trying to restore a database from a backup taken on another server. I am doing this by using SQL Server 2012 Management Studio.

After I have taken the backup on server 1, I go on to select the database on server 2, choose restore, and I select the database on server 1 as source.

After that, the only backup available is an old one taken by another user. It is from the correct database, but not the backup I just took. The newly created backup is not showing in the list.

The backup I took is copy-only, but the old backup that shows up is copy-only as well. It was another user who took the backup that shows up, but I have administrator rights at both server and I don't think that's the problem (but I can't rule it out). I was able to take the backup on server 1 when using Windows integrated security, so it seems strange if I suddenly don't have rights to view the backup anymore. However, it might be that SSMS is run in a such manner that it doesn't have necessary rights. I am not sure.

I am probably missing something here, but right now I can't see what. What can be possible sources of error to why I can't see my newly taken backup in the list of available backups?

Normally when you want to restore a database from a different server,  you're looking to create a device backup to a network drive, and restore from that.

The way the restore from database interface works is by querying the msdb database for recent backups, to see what you can restore. Accordingly you should be using a device backup:

  If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. In this case, select Device to manually specify the file or device to restore.

As the msdb database on server 2 will have no knowledge of the backups taken on server 1.


How to limit an inner query by outer query attribute I have a requirement to sort a table by multiple criteria, select the first and associate with another table.  The DB is Postgres.

Let's say we have players and teams where player has played from several teams.  This is not the real composition of the model but should suffice for our example.

The requirement is to get the most recent record of the team the player played for, ordered by teams.end_date if it's present, otherwise by teams.start_date.

The algorithm would seem to be group by players.id, and order by the criteria mentioned above.  However all of my attempts to limit the results to 1 gives me the first team record that is ordered by that criteria.

Here's what I think is the closest I got:

SELECT
  latest_teams.*
FROM players
  JOIN (
    SELECT
      player_id,
      team_name,
      start_date,
      end_date
    FROM teams
    ORDER BY .... --this part is fine
    limit 1
  ) AS latest_teams
  ON players.id = teams.player_id

How can I get the scope of the inner query to be that of the player id?

First, if you SELECT only from the teams table and there is a FOREIGN KEY constraint that REFERENCES players, you don't need the join to players at all.

Now, there are various ways to write this type of queries, there is even a tag at SO and DBA.SE, greatest-n-per-group. We need the greatest 1 in this case. The most simple code is to use the DISTINCT ON construct (which is a Postgres, non-standard addition to SQL).
If you need columns from players, you can simply join and add the columns in the select list.:

SELECT DISTINCT ON (t.player_id)
  t.player_id,
  t.team_name,
  t.start_date,
  t.end_date
FROM teams AS t
  -- JOIN players AS p
  --   ON p.id = t.player_id
ORDER BY
    t.player_id,  -- this needs to match the DISTINCT ON ()
    ....      -- pick what order you want, so the 1st is chosen for each player
 ;            -- no LIMIT

Another way that works in most other DBMS is to use window functions. The criteria for which row to be chosen goes inside the OVER clause:

SELECT
    g.player_id,
    g.team_name,
    g.start_date,
    g.end_date
FROM
  ( SELECT
      t.player_id,
      t.team_name,
      t.start_date,
      t.end_date,
      ROW_NUMBER() OVER (PARTITION BY t.player_id
                         ORDER BY ...) AS rn
    FROM teams AS t
      -- JOIN players AS p
      --   ON p.id = t.player_id
  ) AS g
WHERE
    g.rn = 1 ;

If you are not in some ancient version of Postgres, there is also the LATERAL syntax, that is often the most efficient. Especially when there is a "driving" table with the distinct values we want to base the grouping (the players here) and if these values are relatively small in number and have many possible options (i.e. in this case, the biggest the teams table is, in comparison to the players, the better for this query).
Also notice how this query resembles very much your initial idea. It does, literally, exactly what you want: allow the players columns to be referenced inside the lt subquery:

SELECT
    lt.player_id,
    lt.team_name,
    lt.start_date,
    lt.end_date
FROM players AS p
  JOIN LATERAL
    ( SELECT t.*
      FROM teams AS t
      WHERE p.id = t.player_id
      ORDER BY ....     -- pick the order
      LIMIT 1
   ) AS lt ON TRUE ;


Most recent fixed date (i.e. 4th July) before given date Given a fixed date, for instance 27/04, what would be the best way to get the most recent year that the aforementioned date happened before another given date?

Some examples:

Date A: 27/04
Date B: 25/05/2016
Result: 27/04/2016

Date A: 27/04
Date B: 25/04/2016
Result: 27/04/2015

A bit of background as to why we need this:
I'm correcting an issue in a report to do with insurance claims for the broker I work for. The report joins a few tables, but the two relevant ones to this question are policies which stores the start date of a policy, and reportedclaims which stores the claims that have been reported while the client has been insured with us.
What we need is the start date of the most recent policy year.
Each year when a policy is renewed its start date is advanced by one year. This means that when we run this report the start date of the policy may be after the claim date, which makes no sense. We don't store the original start date, but even if we did we'd have a similar problem if a claim came in 3 or 4 years after the policy was first incepted:

Original start date: 27/04/2012
Current start date: 27/04/2016
Claim date: 26/04/2016
Required start date: 27/04/2015

I could do the calculation in PHP, but since the rest of the report is being created in MySQL I'd prefer not to do that if I can avoid it.

We are currently using a column call uyear which stores the underwriting year that the claim falls under. This is ideal for the task, and saves any weighty calculations in the SQL or the script. Unfortunately I've just found out that this data is unreliable as the people entering it are quite often entering the incorrect year.

First, let's use MySQL's format for dates:

Start date:  xxxx-04-27
Claim date:  2016-04-26

Now we need to combine the '2016' (or 2016-1) from the Claim date to the '04-27' of some Start date.  It does not matter whether it is the original or current "start date".  To decide on the "-1", we need to compare both mm-dd values, which can be done as simple string.

In the following code, I am assuming DATE datatype was used; using string functions works fine.  For example:

SELECT LEFT(curdate(), 4), RIGHT(curdate(), 5);
+--------------------+---------------------+
| LEFT(curdate(), 4) | RIGHT(curdate(), 5) |
+--------------------+---------------------+
| 2016               | 11-21               |
+--------------------+---------------------+

Here's the expression to generate '2015-04-27', which can be used as a DATE or in DATE_FORMAT() to format it as desired:

CONCAT(
    LEFT(claim_date, 4) -
    IF(RIGHT(claim_date, 5) <=        -- may want just '<'?
       RIGHT(start_date, 5),  1, 0),  -- whether to back up a year
    '-',
    RIGHT(start_date, 5) )  -- tack on proper 'mm-dd'

I thought about using julian dates, but leapday messes up things.


PostgreSQL on RDS: How to bulk import data with FK constraints? Is there an efficient way to bulk import data into a PostgreSQL 9.4 database on RDS that makes extensive use of foreign key constraints? The standard method of using pg_restore fails because RDS does not appear to allow you to disable system triggers.

For example, using the command:

pg_restore --host foo.rds.amazonaws.com --port 5432 --username "bar" --role=rds_superuser --dbname "baz" --data-only --disable-triggers --no-owner --no-privileges --no-tablespaces --verbose --exit-on-error "baz.dump"

Results in error messages such as:

pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied: "RI_ConstraintTrigger_a_30548" is a system trigger
    Command was: ALTER TABLE xxxx DISABLE TRIGGER ALL;

Here's a workaround that works for me:

Create the schema then dump the tables only, in order such that no FK constraint checks fail:

pg_dump --username=postgres --data-only --table=table_1 --table=table_2 --table=table_3 --table=... --format=custom foo > foo.dump

pg_restore will now work.


Query All Tables In PostgreSQL Database For Text I do not have write privilges on the database so I can not create a proc to run.  I was originally going to use this function

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
  FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
      FROM information_schema.columns c
      JOIN information_schema.tables t ON
        (t.table_name=c.table_name AND t.table_schema=c.table_schema)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}')
        AND t.table_type='BASE TABLE'
  LOOP
    EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
       schemaname,
       tablename,
       columnname,
       needle
    ) INTO rowctid;
    IF rowctid is not null THEN
      RETURN NEXT;
    END IF;
 END LOOP;
END;
$$ language plpgsql;

but I need a way to scan all fields in all tables to search for the keywoard '%mxltw%' how can this be done w/o dumping the data and w/o creating a stored proc?

Try to generate the whole list of SQL statements to be run in a single SQL file and run this SQL file with psql.


What is ERROR_STATE() in SQL Server and how it can be used? I read that ERROR_STATE() can help to distinguish between different states/locations in the source code where same type of error can occur. But it is not really clear to me that how it can be useful.

MSDN states:

  ERROR_STATE() Returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

How it can be really used? Can some one give me an example, the ones provided in this reference article don't really help explain things well for me?

The purpose of SQL Server error states is for the SQL Server development team to be able to identify in code the exact place system errors were raised, given that many errors are raised in multiple places.

You as an end user (ie. developer of applications using SQL Server) can similarly use the state passed in to RAISERROR so that your product support can identify the place a procedure raises an error, for example:

create procedure usp_my_proc
as
if <somecondition>
  raiserror(N'Error foo and bar', 16, 0);
if <someothercondition>
  raiserror(N'Error foo and bar', 16, 1);
go

See how the two state allow you to distinguish later which error case was hit. Before you say 'but I can look at the error message' I'm telling you one word: internationalization.


Postgresql 9.3 GROUP BY on an indexed column in 30m rows table taking a long time I have a simple table which holds GPS data for various vehicles with 30 million rows.

         Column         |            Type             | Modifiers | Storage  | Stats target | Description
------------------------+-----------------------------+-----------+----------+--------------+-------------
 id                     | bigint                      | not null  | plain    |              |
 equipmentid            | bigint                      |           | plain    |              |
Indexes:
    "some_pkey" PRIMARY KEY, btree (id)
    "some_idx" btree (equipmentid)

The table has more columns, but in my opinion they are not revelant for my question (but if you think they are let me know and I'll add them).

I'm trying to obtain the last GPS position for each equipment like so:

user=> explain (analyze, verbose, buffers) select max(gps2.id) from GpsData gps2 group by gps2.equipmentId;
                                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.56..131266837.64 rows=298 width=16) (actual time=64679.945..637667.147 rows=307 loops=1)
   Output: max(id), equipmentid
   Buffers: shared hit=5662655 read=11225117
   ->  Index Scan using some_idx on public.gpsdata gps2  (cost=0.56..131099834.76 rows=33399980 width=16) (actual time=35.452..634355.329 rows=29176300 loops=1)
         Output: equipmentid, id
         Buffers: shared hit=5662655 read=11225117
 Total runtime: 637677.321 ms
(7 rows)

As you can see this query runs in more than 5 minutes, which is an awful lot. It also seems to me that this is one of the most simple use-cases for a GROUP BY (i.e. I expect it to be much faster).

I'm trying to look at the analysis, but... I don't know where to begin. I mean it does say rows=29176300, which looks like a lot of rows, but still 5 mins seems excessive.

I should also note that I've forced an index scan via  SET enable_seqscan = off hoping it would be faster, but I was wrong (I had Seq Scan instead of index scan in my output before; it used to take around 4 minutes which is slightly less)

Any hints for me on where to start? My postgres version is PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit.

Edit: Some more info, as requested.

user=> select pg_total_relation_size('some_idx');
 pg_total_relation_size
------------------------
             1620033536
(1 row)

Looking at the number of buffers it took to read the index, you are probably suffering from table and index bloat.

If you can afford to lock the table, run vacuum full analyze gpsdata to clean up old data.

There are several possible reasons why auto-vacuum did not take care of cleaning up properly and re-using deleted space:

You have many sessions accessing that table that are in the state "idle in transaction". Rows that are used by a transaction can't be cleaned up. Make sure you properly commit all transactions - that includes SELECT statement which also start a transaction.  The easiest way to do that is to enable autocommit (default in psql)
You have a high frequency of updates on that table so that the table is constantly changed and auto-vacuum can't keep up (or doesn't get a chance to clean up - see 1.)
you have disabled auto-vacuum

An index on gpsdata (equipmentid,id) might speed up the calculation.


how to make secondary slave as readable in MongoDB? How do I make a secondary slave as readable in MongoDB?

We can do this via shell command

rs.slaveOk()

But how we can do it permanently for all session via config file?

For mongo shell sessions there is a concept of mongorc files which include JavaScript to execute when the mongo shell starts. You can use this feature to extend or customise the behaviour of the interactive shell.

If you want secondary reads to be allowed by default for all shell sessions you can either:

add the rs.slaveOk() command to the .mongorc.js in the current user's home directory:

$ echo "rs.slaveOk()" >> ~/.mongorc.js

add to the global /etc/mongorc.js which is executed before the user-specific '~/.mongorc.js` (Note: requires MongoDB 2.6+):

$ echo "rs.slaveOk()" | sudo tee -a /etc/mongorc.js


Large queries on MyISAM tables crashing MySQL I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that the site has grown very large in size, I have started to see performance on my MySQL server slow down, and due to the intense queries that ResourceSpace runs, the site will start having issues loading pages due to very long running queries.  When I login to MySQL, I will see multiple of the same queries running for very long amounts of time (300+ seconds), with most having the state of 'Waiting on table level lock'. The queries will never finish, and I either have to kill them, kill MySQL, or MySQL itself will completely crash. Has anyone ever seen behavior like this with this particular software or any PHP website in general?

Also, I am considering converting the engine on the tables over to InnoDB. I was wondering if there is any considerable risk with altering the tables and setting the engine to InnoDB?

I altered each of the tables that was using MyISAM as the engine, and the query time greatly decreased.  My site has also stopped locking up and responds much quicker during peek usage times.


Role to view SQL server job log We have a monitoring tool that can check if Microsoft SQL Server jobs have run successfully. What I want to do is create a user that has as little permission as possible but it must be able to watch the history log to see if the job completed.

What role should I assign the user?

Edit:
The servers are Microsoft SQL Server 2005 to 2012

To be able to check all jobs you would need go add the user to the SQLAgentReaderRole in the msdb database which also gives that user permission to create jobs. To minimize the access granted you can also grant the user select permission on the tables used by the sql server agent


How to restrict multi-tenant MySQL database? I have following table:

Create table `webservice`(
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `hostname` text NOT NULL,
  `portalid` int NOT NULL,
  `serviceid` int NOT NULL,
  `customerid` int,
  primary key (`id`)
)

I have multiple customers accessing multiple portals and services using different FQDN but same DOCROOT and MySQL login. It is multi-tenant application.

Now I want every visitor, to be able view only the data to which he is authorized to. All these users are using single MySQL connection login.

On first connect, browser is requesting the FQDN, so it should grant permission to view the associated "portalid" only rows. So this way without knowing FQDN, it is impossible to get access to rows with different portalid.

When user logs in, he's views should be restricted to only records with his own customerid.

I dont know how to do it, but I think I could use session data in SQL, which would allow to fetch the required data. However I dont know how to setup this at the moment.

I have session like this: https://security.stackexchange.com/questions/17318/how-to-secure-mysql-based-web-session-data-table

The simplest answer is probably: give each portal a different MySQL database, with a different login and password.

The next-simplest answer is: put each portal's stuff in a separate table.  Review your code carefully to ensure that each portal can only access data in its own table.

If you don't want to change your database schema, the pragmatic answer is: When the code for your portal application sets up a MySQL connection, the first thing it should do is create a view.  Define the view so it provides access to only the subset of the data associated with that particular portal.  You can define the view using a SQL statement with a WHERE clause of the form WHERE portalid = xxx (replace xxx with the id of the currently running portal).  e.g.,

CREATE VIEW webserviceview
AS
SELECT *
FROM webservice
WHERE portalid = xxx

(fill in xxx with the currently running portal).  Now make sure that all of your code only accesses webserviceview and never accesses the webservice table directly.

When a user logs in, you can use the same idea to further restrict access.  Create a view that has only the data for the currently logged-in user.  e.g.,

CREATE VIEW currentuser
AS
SELECT *
FROM webserviceview
WHERE customerid = yyy

Then make sure that your code only accesses the currentuser table, never the webservice or webserviceview table.

If you want to read some research papers that take this sort of idea a bit further, check out the following papers:

Diesel: Applying Privilege Separation to Database Access, ASIACCS 2011.
CLAMP: Practical Prevention of Large-Scale Data Leaks, IEEE Security & Privacy 2009.
Nemesis: Preventing Authentication & Access Control Vulnerabilities in Web Applications, Usenix Security 2009.


SQL Server: Get location of transaction log from MDF file Our SQL Server 2012 Express server crashed and lost some data. We managed to rescue the main MDF, but can't find the LDF. Is there a way to get this information from the database MDF file or msdb, or temp mdfs that were found?

This is stored in a couple or locations in the MDF.

the base table sysprufiles - I've checked a few databases installed locally and found it on page 155 and page 153 of the ones I've checked. I imagine this will always be fairly early in the file but it doesn't appear to have a fixed location.
sysfiles1 - which is reliably on page 32 for every database I have checked so far.

The initial version of this answer used a hex editor to read it from the MDF.

Dan Guzman points out there is an undocumented DBCC command that is  massively simpler.

DBCC CHECKPRIMARYFILE ( N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf',1);

Returns a result like

+--------+---------+-------+---------+--------+---------+------+-------------------------+----------------------------------------------------------------------------------------------+
| fileid | groupid | size  | maxsize | growth | status  | perf |          name           |                                           filename                                           |
+--------+---------+-------+---------+--------+---------+------+-------------------------+----------------------------------------------------------------------------------------------+
|      1 |       1 | 26240 |      -1 |   2048 |       2 |    0 | AdventureWorks2012_Data | C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf |
|      2 |       0 |   128 |      -1 |     10 | 1048642 |    0 | AdventureWorks2012_Log  | C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf  |
+--------+---------+-------+---------+--------+---------+------+-------------------------+----------------------------------------------------------------------------------------------+

I was curious as to how that approach worked so looked at the activity in process monitor.

This ends up reading the file header (page 0), database boot page (page 9) and page 32 of the file.

I didn't see page 32 mentioned on either of the preceding pages so I think that page is hardcoded to contain the information rather than being looked up from them.


What is the purpose of attaching a database? I always see this option called attach database in SQL Server. Why would someone attach a database? Why not just restore a database from the .BAK file? I guess I don't see the purpose of it.

What would be the result of attaching it? It's quicker than a restore, but what is the technical result? Can you still alter the database? Add data to it? The preferred method or not recommend it?

I have seen
Attach/detach vs. backup/restore but nothing in there explains the purpose of attaching or detaching. It basically doesn't even touch on it.

Attaching/Detaching could be quicker than backup/restore depending on the situation.

A detach is instantaneous and you can start copying the files right away, and the attach is near instantaneous too. Backup takes a while, and restore takes a while too.

This technique was a lot quicker before we had backup compression because the size of the files that needed to be copied over were more or less the same size depending on the situation.

Backup compression has an influence on that, since the backups are now a lot smaller than the data files.

It all depends on your IO speed and the source and destination of the copy.

The math is fairly easy, If the time of taking a backup, copying over the files, and restoring the backup is shorter than copying over the mdf+ldf files it's faster to backup/restore, otherwise it's faster to detach/attach.

The detach/attach method obviously only works if your database can go offline.

If your IO subsystem is fast, and the network link over which you have to transfer the database is slow it might be faster to transfer a compressed backup (if that's available in your version/edition). If your IO subsystem is slow and so is the process of backing up and restoring but you can copy the files over quickly it might make sense to detach, copy over the relatively larger .mdf files and reattach.

In my opinion, this used to be more of an issue with large databases before we had backup compression available. If I recall correctly this was a possible question in Microsoft Certification exams before we had compression but I haven't seen it in the material for more recent versions.

The end result is basically the same, an exact copy of the database.


mysql trigger update after insert I need help to create a trigger that can calculate prices after promotions.
I have two tables "product" and "promotion".
after inserting in promotion table the promotion price and the product id, I want to update the price in product table to the new price after promotion.
here is my try :

 CREATE TRIGGER update_promotion
       AFTER INSERT ON promo
          SELECT @prix_promo = prix_promotion FROM INSERTED;
          SELECT @id = id_prod FROM INSERTED;
          SELECT @prix_sanspromo = prix FROM produit
           WHERE id_produit = @id;
          FOR EACH ROW
           UPDATE produit SET prix_promo=((@prix_promo*prix)/100) WHEREid_produit = @id;

This is an answer which follows the table definitions you posted:

CREATE TRIGGER update_promotion
  AFTER INSERT ON promotion
  FOR EACH ROW
  UPDATE product
      -- Here you are losing the old price!
     SET price = NEW.percentage * price / 100,
   WHERE id_product = NEW.id_produit;

However, the example you posted does not match the column names you gave later. This means you are not posting your actual code, and this makes everybody’s job harder.

Maybe this is only a toy example to learn how to use MySQL triggers. As such, it’s fine. In production, the very idea to change the price in the main product table upon insertion of a new promotion is questionable. A better design would be not to change the product table, add start_date and end_date columns to the promotion table, and read the current prices either from a view which applies the active promotions for the day, or, if you have millions of products and performance becomes an issue, from a temporary table regenerated each day (and possibly replicated to slave servers... you know your scale).


SQL Server 2016: create credential and add SQL Server Agent proxy for it I'm trying to script a creation of SQL Server identity to be then used to execute SQL Server agent jobs via a proxy.

It looks as though I can only use a Windows account and thence I would have to provide its password in plain text. Seriously? There must be a better way to do this. I need this script to work on my team-mates' machines as well as mine:

USE [msdb]

CREATE LOGIN [proxy_login] WITH PASSWORD=N'passw0rd',
    DEFAULT_DATABASE=[SSISConfig], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE CREDENTIAL [my_cred] WITH IDENTITY='proxy_login', SECRET='passw0rd'

EXEC msdb.dbo.sp_add_proxy
    @proxy_name=N'My_Proxy',
    @credential_name=N'my_cred',
    @enabled=1

Error:

  Msg 14720, Level 16, State 1, Procedure sp_verify_credential_identifiers, Line 69 [Batch Start Line 0]
  The operation failed because credential '@credential_name' identity is not a valid Windows account

In ideal world I would like to use the SYSTEM_USER login for the credential without having to supply their password.

The operation failed because credential '@credential_name' identity is
  not a valid Windows account

That is because you did not use Windows account, but SQL Server account, that is wrong. You just misunderstand SQL Server Agent Proxies

  SQL Server Agent proxies use credentials to store information about
  Windows user accounts.  The user specified in the credential must have
  "Log on as a batch job" permission on the computer on which SQL Server
  is running.

  SQL Server Agent checks subsystem access for a proxy and gives access
  to the proxy each time the job step runs.  If the proxy no longer has
  access to the subsystem, the job step fails.  Otherwise, SQL Server
  Agent impersonates the user that is specified in the proxy and runs
  the job step.

In your cited article there is this phrase that you did not read carefully:

  A credential is a record that contains the authentication information
  that is required to connect to a resource outside SQL Server.

That is, CREDENTIAL for Agent is some account to access OS(Windows) resources, so it's Windows account, OS does not know nothing about your SQL Server logins. The permissions to Windows resources are granted to Windows accounts, and one of this account you can use as a proxy for Agent.


Set in dynamic query value of variable declared outside dynamic query I have a stored procedure which i simplified this way :

DECLARE @variable1 INT

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT

    SET @variable2 = 1
    SET '+CAST(@variable1 AS VARCHAR)+' = @variable2

    SELECT @variable1 as V1, @variable2 as V2
'
EXEC(@SQL)

But this script don't give me anything ! I'm pretty sure it's something related to scope. The thing is that i need to declare the variable outside the dynamic query.

thanks for help !

EDIT :

WHILE LOOP UNTIL SELECT COUNT xxx = 0
BEGIN
    DECLARE @variable1 INT

    DECLARE @SQL VARCHAR(MAX)

    SET @SQL = '
                EXEC STORE PROC WITH PARAMETER @Param1 = @variable1 (first loop @Param1 is null)

                STORE PROC RETURN A VALUE

                SET @variable1 with return value of store proc
                and use it in second loop, third loop...
    '
    EXEC(@SQL)
END

Three things worth mentioning:

Always use PRINT to view the resulting dynamic SQL whenever you work with dynamic SQL. You will see that the SQL variable is actually holding NULL.

DECLARE @variable1 INT

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT

    SET @variable2 = 1
    SET ' + CAST(@variable1 AS VARCHAR) + ' = @variable2

    SELECT @variable1 as V1, @variable2 as V2
'

PRINT(@SQL)

-- EXEC(@SQL)

The reason because the dynamic SQL is NULL is because you are concatenating a NULL value which is the @variable1 contents. I believe you wanted to write down the text '@variable1' as literal:

DECLARE @variable1 INT

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT

    SET @variable2 = 1
    SET @variable1 = @variable2

    SELECT @variable1 as V1, @variable2 as V2
'

PRINT(@SQL)

Whenever you use EXEC, the scope changes and variables declared outside can't be accessed anymore. So inside the dynamic SQL, you won't be able to read @variable1 since it's not declare anywhere. If we execute the dynamic SQL:

The way you can set variables values inside a dynamic execution and be able to read them from the outside is by supplying parameters via the OUTPUT option. This will require to use the SP sp_executesql rathen than a direct EXEC:

DECLARE @externalVariable INT

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT = 1
    SET @resultVariable = @variable2'

EXEC sp_executesql
    @stmt = @SQL,
    @params = N'@resultVariable INT OUTPUT',    -- Declare the "input" parameters for the dynamic SQL
    @resultVariable = @externalVariable OUTPUT  -- Supply the "input" parameters for the dynamic SQL

SELECT
    Result = @externalVariable -- Read the updated value

Note that I changed data types to NVARCHAR since sp_executesql works with unicode inputs.

Another example with more parameters:

DECLARE @firstNumber INT = 15
DECLARE @secondNumber INT = 3
DECLARE @result INT

DECLARE @SQL NVARCHAR(MAX) = '
    SET @multiplicationResult = @inputFactor1 * @inputFactor2'

EXEC sp_executesql
    @stmt = @SQL,
    @params = N'
        @multiplicationResult INT OUTPUT,
        @inputFactor1 INT,
        @inputFactor2 INT',
    @multiplicationResult = @result OUTPUT,
    @inputFactor1 = @firstNumber,
    @inputFactor2 = @secondNumber

SELECT
    Result = @result -- 45!

If you don't have to read back results from variables, you can build your dynamic SQL by "hard-coding" the variables values directly into the script. Make sure to correctly use data type conversions inside the script and also escape NULL and literal values:

DECLARE @DateVariable DATETIME = GETDATE()
DECLARE @StringVariable VARCHAR(100) = NULL
DECLARE @FloatVariable FLOAT = 15.14

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        DateVariableContents = CONVERT(DATETIME, ''' + ISNULL(CONVERT(VARCHAR(100), @DateVariable), '') + '''),
        StringVariableContents = ' + ISNULL('''' + @StringVariable + '''', '''''') + ',
        FloatVariableContents = CONVERT(FLOAT, ''' + ISNULL(CONVERT(VARCHAR(100), @FloatVariable), '') + ''') '

PRINT(@DynamicSQL)

EXEC(@DynamicSQL)

Printed:

SELECT
    DateVariableContents = CONVERT(DATETIME, 'Mar 21 2019  3:27PM'),
    StringVariableContents = '',
    FloatVariableContents = CONVERT(FLOAT, '15.124')

Result:

DateVariableContents        StringVariableContents  FloatVariableContents
2019-03-21 15:28:00.000                             15.124


SSIS Script to split string into columns I have a dataset (log file) with a number of columns; one of them is "Other-Data" below (unordered string) and need to parse the string to create the derived columns according the u value (U1, U2, U3, etc...). The output columns should be something like:

U1   U2  U3                  U4   U5   etc.
null odw odw : CH : de : hom null null
EUR  sss DE:de:hom           null null
EUR  crm crm                 null null

Other-Data:

u3=odw : CH : de : hom;u2=odw : Product : DSC-HX20V;~oref=http://www.bidl.ch/lang/de/product/dsc-h-series/dsc-hx20v
u1=EUR;u2=sss:Checkout-Step4:Orderacknowledgement;u3=DE:de:hom;u11=1;u12=302338533;u13=SVE1511C5E;u14=575.67;~oref=https://shop.bidl.de/shop/bibit/success.do
u15=1187;u13=SVE14A1C5E~VAIOEWY401;u11=1~1;u10=843.9~121.14;u9=1038~149;u3=crm : FI : fi : hom;u1=EUR;u2=crm : Checkout : Order acknowledgement;~oref=https://shop.bidl.fi/shop/bibit/success.do

Can anyone help with this?

Usually the way you'd go about this is to have a Script component and enter your VB / C# code to handle this type of input. If you have a variable number of columns than you should forget about SSIS being able to handle this since it doesn't work well with non-finite number of columns (being that tables have a finite number of columns on them).

Having said this, if you have a finite number of columns in the input file, than what you can do is specify the delimiter (the semicolon in the above case) and have the SSIS split the data into the columns and then do the ETL process on them, if needed.

If you have an upper limit of columns, but you get files with less columns than placing a script task before the DFT module to append the extra delimiters that are needed.

Since the first row looks like the header row (column names) you can set the flat file source to skip the first row.

Another alternative would be, if you have a small number of file formats, would be to create different names for each of the log files and have individual DFT modules handle each type of file.

Hope this helps.


SQL Server AG secondary replica database still in Restoring mode I created an AlwaysOn in SQL Server 2019 and I selected the Full Database and Log Backup option. Now it created a database in secondary replica but it is still in Restoring mode. My DB size is 16MB and it is very small just for test.
What is the problem?

I found the solution. It is because my account doesn't have sufficient permission.


How do I run a long-running stored procedure on SQL Server, even if I get disconnected? I have a stored procedure in an Azure SQL Database that does not return a result and can take several hours to run. It takes one parameter, and I am running it through the query editor window in Management Studio on my local machine:

EXEC @return_value = dbo.ComputeBatchData @BatchId = 2

My problem: If I get disconnected, the procedure is cancelled.

The procedure does not need to run on a schedule or anything. I just want to run the procedure and not have to worry about disconnects.

Azure Automation: Schedule the Execution of SQL Jobs in Azure with Automation Service

and make a small powershell script and schedule it in the azure dashboard.