How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown? Given two tables with an undefined row count with a name and value, how would I display a pivoted CROSS JOIN of a function over their values.

CREATE TEMP TABLE foo AS
SELECT x::text AS name, x::int
FROM generate_series(1,10) AS t(x);

CREATE TEMP TABLE bar AS
SELECT x::text AS name, x::int
FROM generate_series(1,5) AS t(x);

For example, if that function were multiplication, how would I generate a (multiplication) table like the one below,

All of those (arg1,arg2,result) rows can be generated with

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x AS result
FROM foo
CROSS JOIN bar;

So this is only a question of presentation, I would like this to also work with a custom name -- a name that is not simply the argument CASTed to text but set in the table,

CREATE TEMP TABLE foo AS
SELECT chr(x+64) AS name, x::int
FROM generate_series(1,10) AS t(x);

CREATE TEMP TABLE bar AS
SELECT chr(x+72) AS name, x::int
FROM generate_series(1,5) AS t(x);

I think this would be easily do-able with a CROSSTAB capable of a dynamic return-type.

SELECT * FROM crosstab(
  '
    SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x
    FROM foo
    CROSS JOIN bar
  ', 'SELECT DISTINCT name FROM bar'
) AS **MAGIC**

But, without the **MAGIC**, I get

ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM crosstab(

For reference, using the above examples with names this is something more like what tablefunc's crosstab() wants.

SELECT * FROM crosstab(
  '
    SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x
    FROM foo
    CROSS JOIN bar
  '
) AS t(row int, i int, j int, k int, l int, m int);

But, now we're back to making assumptions about the content and size of the bar table in our example. So if,

The tables are of undefined length,
Then the cross-join represents a cube of undefined dimension (because of above),
The catagory-names (cross-tab parlance) are in the table

What's the best we can do in PostgreSQL without a "column definition list" to generate that kind of presentation?

Simple case, static SQL
The non-dynamic solution with crosstab() for the simple case:
SELECT * FROM crosstab(
  'SELECT b.x, f.name, f.x * b.x AS prod
   FROM   foo f, bar b
   ORDER  BY 1, 2'
   ) AS ct (x int, "A" int, "B" int, "C" int, "D" int, "E" int
                 , "F" int, "G" int, "H" int, "I" int, "J" int);

I order resulting columns by foo.name, not foo.x. Both happen to be sorted in parallel, but that's just the simple setup. Pick the right sort order for your case. The actual value of the second column is irrelevant in this query (1-parameter form of crosstab()).
We don't even need crosstab() with 2 parameters because there are no missing values by definition. See:

PostgreSQL Crosstab Query

(You fixed the crosstab query in the question by replacing foo with bar in a later edit. This also fixes the query, but keeps working with names from foo.)
Unknown return type, dynamic SQL
Column names and types cannot be dynamic. SQL demands to know number, names and types of resulting columns at call time. Either by explicit declaration or from information in the system catalogs (That's what happens with SELECT * FROM tbl: Postgres looks up the registered table definition.)
You want Postgres to derive resulting columns from data in a user table.  Not going to happen.
One way or the other, you need two round trips to the server. Either you create a cursor and then walk through it. Or you create a temp table and then select from it. Or you register a type and use it in the call.
Or you simply generate the query in one step and execute it in the next:
SELECT $$SELECT * FROM crosstab(
  'SELECT b.x, f.name, f.x * b.x AS prod
   FROM   foo f, bar b
   ORDER  BY 1, 2'
   ) AS ct (x int, $$
 || string_agg(quote_ident(name), ' int, ' ORDER BY name) || ' int)'
FROM   foo;

This generates the query above, dynamically. Execute it in the next step.
I am using dollar-quotes ($$) to keep handling of nested quotes simple. See:

Insert text with single quotes in PostgreSQL

quote_ident() is essential to escape otherwise illegal column names (and possibly defend against SQL injection).
Related:

PostgreSQL convert columns to rows? Transpose?
Dynamic alternative to pivot with CASE and GROUP BY


Why is a NOT NULL computed column considered nullable in a view? I have a table:

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RankingBonus] [int] NOT NULL,
    [Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
    ....
)

And a view:

CREATE View  [dbo].[FilteredRealty] AS
 SELECT
realty.Id as realtyId,
...
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.Ranking,
...
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1
                     AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
WHERE Person.ConfirmStatus = 1

I have a dbml model in C# (LinqToSQL) with the FilteredRealty view in it. The [Ranking] field is recognized as a nullable int and so I have to fix the type in the generated code every time when I change anything in the database. This is very frustrating for me and a lot of manual work.

There are no aggregates used in FilteredRealty (regarding this related question).

Why is the Ranking column of the view considered as nullable if Realty.Ranking is non-nullable?

The [Ranking] field is showing as "Nullable" due to being a computed column. Yes, it is declared as NOT NULL, but as the MSDN page for Computed Columns states, the database engine can change that determination at query-time:

  The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.

So, let's see if this is true:

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RankingBonus] [int] NOT NULL,
    [Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
);
GO

EXEC sp_help 'dbo.Realty';
-- Ranking: Nullable = "no"

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Realty'), N'Ranking', 'AllowsNull') AS [AllowsNull?];
-- 0

SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Realty', '', NULL);
-- Ranking: is_nullable = 1  ==  :-(

Now let's see if their advice regarding ISNULL works:

SELECT * FROM sys.dm_exec_describe_first_result_set(
   N'SELECT Id, RankingBonus, ISNULL(Ranking, -99) AS [RealRanking] FROM dbo.Realty;',
   '',
   NULL);
-- RealRanking: is_nullable = 0

Their advice does seem accurate, so let's try applying that to the definition of the computed column:

ALTER TABLE dbo.Realty
  ADD [RankingFixed] AS (ISNULL(([Id]+[RankingBonus]), -99))
  PERSISTED NOT NULL;
GO

And now we check the properties again, but for the new field:

EXEC sp_help 'dbo.Realty';
-- RankingFixed: Nullable = "no"

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Realty'),
                      N'RankingFixed',
                      'AllowsNull') AS [AllowsNullsNow?];
-- 0

This is looking positive so far, but even the original definition reported "NOT NULL" from these two checks. So let's try the real test -- how the database engine determines the nullability at run-time:

SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Realty', '', NULL);
-- RankingFixed: is_nullable = 0  ==  :-) WOO HOO!


User created index question - sp_Blitz 5/17/18 - Incorrect output? I have SQL Server 2014 CU11 with sp_Blitz 5/17/18 8:44AM release. sp_Blitz is reporting that I have user created statistics on some of my databases. I've executed a script that deletes all user created statistics. However, when I re-run sp_Blitz it still reports them. If I use the T-SQL on Brent's page: SELECT * FROM sys.stats WHERE user_created = 1 then I see all the user created indexes. However if I use alternate code like:

SELECT st.name AS TableName, ss.name AS StatisticName
FROM sys.stats ss
JOIN sys.tables st ON ss.object_id=st.object_id
WHERE ss.user_created = 1
ORDER BY st.name, ss.name;

I see no user created indexes. I've tried several variations of the "find user stats" script & the only one that reports any is Brent's. Why would this be? I've removed almost all the user created statistics, and would like to take care of these leftovers if possible.

You can have rows in sys.stats without a corresponding row in sys.tables.

Classic example: an indexed view. Watch this:

CREATE TABLE dbo.T1 (ID INT PRIMARY KEY CLUSTERED);
INSERT INTO dbo.T1 VALUES (1);
GO
CREATE OR ALTER VIEW dbo.vwJetta WITH SCHEMABINDING AS
  SELECT T1.ID
  FROM dbo.T1;
CREATE UNIQUE CLUSTERED INDEX CLIX ON dbo.vwJetta(ID);
GO
CREATE STATISTICS STAT ON dbo.vwJetta(ID);
GO
SELECT * FROM sys.stats WHERE user_created = 1;

Our query correctly shows a row:

Whereas your query does not, because there's no table involved:

Moral of the story: never, ever doubt anything you see on BrentOzar.com. Just kidding. But believe what you're seeing right here, and get to work digging into those stats.


What specific tests need to be performed under Database migration from DB2 to Oracle? Currently I am going to be testing (I am a Data Analyst) of a larger migration project where they are planning to migrate from DB2 to Oracle.
My question may be little broader; however any information which relates to the following questions would certainly be helpful.

1) How to validate both under Source and Target tables for:

Matching of Total Number of tables
Matching of Data fields under each
table

2)  How to validate from Front end (UI), so that right data is getting populated under right table:
Challenge

There are 500+ UIs, What is the
best way to test in an Optimized way
(to avoid any duplicate testing)
Should I also focus on table mapping
to UI screen

3)  Any specific challenges I need to see under migration

Datalock, Spacing issue, Noisy words

Normally for a straight forward data migration it is enough to know that all data is complete, valid and accessible for the application. Typically subtle differences may pop-up with some data-types like those with date, time, binary fields and long text fields.

In a straight forward data migration, data from table a field a goes to the new system in the same table, the same field. A good check to validate this could be done by computing a MD5 check-sum on all fields of all tables, both in source and target tables. Any difference here shows there is a problem in the migration procedure or software. Make sure to have the same formats in place for the MD5 computation; for example, make sure that the date formats are the same on both sides.

The application has to be tested, that's for sure. What the strategy should be is hard to tell, every application has it's own challenges. If all logic is known and standardized, it could work by making a simple test application that accesses all different field types and see how the interaction goes. In the end a regression test will be needed.


unable to run step 3 of syspolicy_purge_history job on SQL Server 2012 After installing an instance of SQL Server 2012 (SP3) on a server that had previously had SQL Server 2008R2 on it, I began encountering the following error while attempting to run step 3 of syspolicy_purge_history:

  A job step received an error at line 1 in a PowerShell script. The
  corresponding line is 'import-module SQLPS  -DisableNameChecking'.
  Correct the script and reschedule the job. The error information
  returned by PowerShell is: 'File C:\Program Files (x86)\Microsoft SQL
  Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded
  because the execution of scripts is disabled on this system. Please
  see "get-help about_signing" for more details. File C:\Program Files
  (x86)\Microsoft SQL
  Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded
  because the execution of scripts is disabled on this system. Please
  see "get-help about_signing" for more details. '

I logged into the server as the SQL Server Agent account and ran the SQLPS executable directly as administrator. I saw that the Process scope was set to Restricted with all others Undefined. After changing group policy for the computer to RemoteSigned, the error changed to the following:

  A job step received an error at line 1 in a PowerShell script. The
  corresponding line is 'set-executionpolicy restricted -scope process
  -Force'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Security error. '

This occurs even after modifying the job so that it is simply running Get-ExecutionPolicy -List. I've checked the registry tag and tried both deleting the registry key as well as changing it to Unrestricted and Bypass.

The only thing I haven't tried yet is attempting to upgrade the instances to 2014. It is a production box. Any suggestions other than disabling that step or running it using xp_cmdshell?

Please look at this connect item

The SQL PowerShell wrapper SQLPS.EXE seems to execute a PS command Set-ExecutionPolicy restricted -scope Process -Force during its initialization. Then PS engine should then be initialized with command import-module SQLPS  -DisableNameChecking which is blocked because of the "Restricted" execution policy. This raises a critical error and the PowerShell engine terminates.

The workaround is adding/changing a registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps110]
"ExecutionPolicy"="RemoteSigned"

See this and this for additional reference.


Order by on an alphanumerical column I have a column that has strings of the following type:
Miller 10
Allen 20
King 10
....
Jones 100

I try to sort the column based on the numerical part of the data.
I tried the following:

SELECT * FROM the_table ORDER BY CAST(RIGHT(data,2) AS UNSIGNED);

But this does not work. It places Jones 100 as the first entry etc.

What am I doing wrong?

MySQL has a built-in function for parsing texts in the form of SUBSTRING_INDEX().

This simplifies your query to:

SELECT * FROM the_table
ORDER BY CAST(SUBSTRING_INDEX(data,' ',-1) AS UNSIGNED);


Migrating Azure SQL database to Azure sql Managed instance What are different options to move database from "Azure sql server" to "Azure sql managed instance", Looks like below options are not possible
1) Azure migration service - Not supporting azure sql server as source
2) bacpac and use sqlpackage to import, this is not working and getting struck with no result

only options i see is through Azure data factory with self hosted integration runtime

is there any better options to move from "Azure sql server" to "Azure managed instance"

The only way to go with this to get the bacpac file for your Azure SQL Database. Then import this bacpac file into your Managed Instance. This can be achieved through :

SSMS
Export\import using sqlpackage.exe
Via portal which appears to be a bit slow as the Azure SQL Database Import/Export service provides a limited number of compute virtual machines (VMs) per region to process import and export operations
SSDT
bcp

This link explains each method : Export bacpac

Once done. Import the bacpac file into your managed instance using SSMS,sqlpackage or portal.

This will walk you through the process : Import bacpac


How to best store limited specifications? I want to create a database for a specific (ecommerce) product and I'm not quite sure what the best way is to store all my specifications. I know exactly which specifications I want and how to store those (one big table since it won't likely change).

For example lets say I sell movies my table would look something like this:

Database ID
Product Title
Type
Region
Packaging

etc.

Where Type is DVD, Bluray, 3D Bluray etc.

My question is how do I store this Type? I could save this as plain-text / enum (method 1) or alternatively in a new table with all the different types and store the ID of DVD (method 2).

The new table is I think the better way to do it but it creates a lot of tables and messy code because I would also need to do this for Region and Packaging. Also it's not very likely that my data will change and that I need to add more Types and Regions.

When do I create a new table (method2)? Only when its 10+ data types or when it frequently changes or something?

You're correct that a new table is the best way to store it. Referencing Type from the ID of the value in a secondary table will allow you to normalize your data. This helps ensure integrity, reduce duplicate data, and allow for effective querying of any type.

You're also correct that this results in the creation of several tables - welcome to the world of relational schemas :) It may seem like a pain now, but long term this method will be the best for storing, updating, deleting, and querying your data.

In any of these scenarios, you should always create a separate table for each entity, whether they have 3 data types or 10 data types that may or may not change. Requirements may change down the road and you should plan for the unexpected! You can tell what fields should be branched out into separate tables by seeing if it follows the rule for third normal form,

  Each column must depend on directly on the primary key.

Since Type does not follow this, it is best as a separate table.

Further Reading: Litt's Tips on Normalization


DMV CONNECTION_HOST_APPLICATION field is NULL I am trying to monitor cubes and expressions of Analysis Services using DMV queries that are stored on Server's RAM.
I found many useful information about connections on connection's table of SSMS's DMVS rowser using the following mdx statement.

SELECT *
FROM $SYSTEM.DISCOVER_CONNECTIONS

The problem is that the [CONNECTION_HOST_APPLICATION] field of connection's table is not recorded and contains null values for my client application.

  CONNECTION_HOST_APPLICATION specifies "The name of the machine that initiated the connection".

As a result i can not filter the rows that are generated from my client tool to analyse only them. So how can i modify my client tool in order to register this field?

Finally after a lot of research, I, found the solution by adding the property of "application name" in connection string that connects to analysis services instance.

ApplicationName='testapp';

So now both SQL Server Profiler and DMVS are able to tracks application name.


How to find locking schema for set of tables in database? There are some 20 tables in a database of Sybase instance, where i need to find the locking schema of tables.
Once locking schema is found, i need to compare it in other database which also have same 20 tables.
build-ASE16 SP2

Use the following to determine the locking schema for each user table in an ASE database:

SELECT TableName = CONVERT(VARCHAR(30), so.name)
    , LockScheme = CONVERT(VARCHAR(30), lockscheme(so.id))
FROM sysobjects so
WHERE so.type IN ('S', 'U');

The CONVERT(VARCHAR(30), xxx) is simply to make the output easier to see in a console session.  If you are using a GUI tool to connect to the ASE instance, they are probably not necessary.  Be aware that if you have table names longer than 30 characters, they will be truncated in the output.

Run that in both instances and simply compare the output in BeyondCompare, or some other differencing tool.


Shortcuts for code snippets In SSMS I would like to use shortcuts for code snippets, just like in Visual Studio:

  type 'shortcut'; hit TAB -> snippet expanded

In SSMS I need to CTRL+K, CTRL+X, mouse, mouse, find, go back, find, mouse. This is not a shortcut, nothing "short" about it.

I've added this sample snippet to my list of snippets:

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>Transaction</Title>
      <Author />
      <Description>Transaction wrapper</Description>
      <HelpUrl />
      <SnippetTypes />
      <Keywords />
      <Shortcut>tra</Shortcut>
    </Header>
    <Snippet>
      <References />
      <Imports />
      <Declarations>
        <Literal Editable="true">
          <ID>TSQL</ID>
          <Type />
          <ToolTip />
          <Default>TSQL</Default>
          <Function />
        </Literal>
      </Declarations>
      <Code Language="SQL"><![CDATA[
BEGIN TRANSACTION TRAN1
$TSQL$
ROLLBACK TRANSACTION TRAN1]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

I can find it via CTRL+K, CTRL+X, but I'm unable to invoke it by tra + TAB shortcut. Am I missing something? Can SSMS even do this?

I'm using SSMS v13.0.16100.1, but database is SQL Server 2014.

No, while the XML schema contains a shortcut element, the fact is that proper shortcut support was never implemented (several of the other elements are not visible outside the XML, either). The following Connect item was closed as "Won't Fix" back in 2013:

Connect #731728 : SQL Server Management Studio 2012 code snippet shortcut

You may be able to raise awareness of this issue on another place where the SSMS team is paying attention: trello. But I'll warn you: even with an accelerated dev and release cycle, they have a lot on their plates, and I'm not sure this could make any significant dent into the top, say, 50 enhancements.

(Personally, I haven't found that I've need to use my mouse to insert snippets - all that is needed is keystrokes. Obviously YMMV.)


Get the last row that was left outside of a timeframed SQL query Is there a way to write a SQL query with a timeframe condition to include the latest row that is outside of the timeframe (besides solutions like counting the size of the result set and querying for the size+1, etc.) ?

Lets say I have a table A, which holds timestamped value changes.
I want to query for all the changes in the last 24 hours (assume that the time when the query was ran on 8/8/19 00:00) - how do I include the last row that isn't included in 24-hours interval, i.e., row #2 (assuming I don't know when it occurred):

CREATE TABLE A(`timeframe` datetime, `value` int);

INSERT INTO A
    (`timeframe`, `value`)
VALUES
    ('2019-08-06 15:00:00', 10),
    ('2019-08-06 23:00:00', 20),
    ('2019-08-07 14:00:00', 50),
    ('2019-08-07 16:00:00', 10),
    ('2019-08-07 22:35:00', 30),
    ('2019-08-07 23:00:00', 40);

| timestamp        | value |
|------------------|-------|
| 2019-08-06 15:00 | 10    |
| 2019-08-06 23:00 | 20    |
| 2019-08-07 14:00 | 50    |
| 2019-08-07 16:00 | 10    |
| 2019-08-07 22:35 | 30    |
| 2019-08-07 23:00 | 40    |

SELECT value,time FROM A WHERE time >= DATE_SUB(NOW(), INTERVAL 1 DAY)

The result set should include the value changes in last day (rows #3-#6) and the latest row outside the timeframe - row #2.

The simplest (I think so) solution is

  ( SELECT *
    FROM A
    WHERE `timestamp` BETWEEN @from AND @till )
UNION ALL
  ( SELECT *
    FROM A
    WHERE `timestamp` < @from
    ORDER BY `timestamp` DESC LIMIT 1 )
ORDER BY `timestamp` ASC

Alternative:

WITH cte AS ( SELECT `timestamp`, `value`,
                     LEAD (`timestamp`) OVER (ORDER BY `timestamp`) `next`
              FROM A )
SELECT `timestamp`, `value`
FROM cte
WHERE (`timestamp` BETWEEN @from AND @till)
   OR (`next` BETWEEN @from AND @till)
ORDER BY `timestamp` ASC

One more:

WITH cte AS ( SELECT `timestamp`, `value`,
                     CASE WHEN `timestamp` >= @from THEN 1
                          ELSE ROW_NUMBER() OVER (ORDER BY `timestamp DESC`)
                     END grp
              FROM A
              WHERE `timestamp` <= @till )
SELECT `timestamp`, `value`
FROM cte
WHERE grp = 1
ORDER BY `timestamp` ASC

One more else:

WITH cte AS ( SELECT `timestamp`, `value`,
                     COALESCE(LEAD(`timestamp`) OVER (ORDER BY `timestamp DESC`),
                              `timestamp`) grp
              FROM A
              WHERE `timestamp` <= @till )
SELECT `timestamp`, `value`
FROM cte
WHERE grp >= @from
ORDER BY `timestamp` ASC


Any SQL where clause performance similar to where 1=0 (i'm using SQL Server 2008R2 or later)

For centralize manage and consistent, there is a view definition as follow:

CREATE VIEW view_all_situation
(
  select null as src, tf.col1, sum(col2) as fee, sum(isnull(v1.col1, 0)) as fee1 ...
  from table_fee tf
  inner join parameter_table pt on tf.id = pt.id
  left join view1 v1 on ...
  left join view2 v2 on ...
  where pt.col1 = 0
  group by tf.col1

  UNION ALL

  select tfd.src, tfd.col1, sum(col2) as fee, sum(isnull(v1.col1, 0)) as fee1 ...
  from table_fee_detail tfd
  inner join parameter_table pt on tfd.id = pt.id
  left join view1 v1 on ...
  left join view2 v2 on ...
  where pt.col1 = 1
  group by tfd.col1
)
(Note: parameter_table only have 1 row)

so if the value in parameter_table.col1 = 1 then return table_fee_detail else return table_fee. (Note: parameter_table only have 1 row)

As the first part require 10 secs and second part require another 10 secs, finally, it costs 20 secs to return. If there any faster way to let SQL Server execute either one part of the UNION ALL base on the parameter_table value so that it returns in 10 secs?

As dynamic SQL is not applicable in my situation, i can't build it to get the result.

I've tried to use

where EXISTS (select 1 from parameter_table where col1 = 0) and
where EXISTS (select 1 from parameter_table where col1 = 1)

as the where clause, it cannot make the SQL Server to return in 10 secs. It still use 20 secs.

Is it possible to have SQL with result like

where 1=0

so that SQL Server return the result in 10 secs?

For example,
if parameter_table.col1 = 0, i get result from table_fee
Src     col1   fee   fee1
--------------------------------------------
null    USD    100   150
null    EUR    200   150
null    AUD    300   200

if parameter_table.col1 = 1, I get result from table_fee_detail
Src     col1   fee   fee1
--------------------------------------------
USA     USD    100   150
GER     EUR    200   150
AU      AUD    300   200

You are looking to choose rows from one of two tables dynamically. This is generally possible to achieve without dynamic SQL.

To demonstrate, here is a simplified version based on the AdventureWorks sample database:

The idea will be to choose rows from either the TransactionHistory or TransactionHistoryArchive tables for each Product, based on the value of the UseArchive column in a Parameter table:

CREATE TABLE dbo.Parameter
(
    ProductID integer NOT NULL PRIMARY KEY,
    UseArchive bit NOT NULL
);

INSERT dbo.Parameter
    (ProductID, UseArchive)
VALUES
    (1, 0),
    (2, 0),
    (3, 1),
    (4, 0);

This example specifies that rows should come from TransactionHistory for products 1, 2, and 4. For product 3, rows should come from TransactionHistoryArchive.

Solution:

SELECT
    P.Name,
    CA.TransactionID
FROM Production.Product AS P
JOIN dbo.Parameter AS PTR
    ON PTR.ProductID = P.ProductID
CROSS APPLY
(
    SELECT TH.ProductID, TH.TransactionID
    FROM Production.TransactionHistory AS TH
    WHERE
        TH.ProductID = P.ProductID
        AND PTR.UseArchive = 0

    UNION ALL

    SELECT THA.ProductID, THA.TransactionID
    FROM Production.TransactionHistoryArchive AS THA
    WHERE
        THA.ProductID = P.ProductID
        AND PTR.UseArchive = 1
) AS CA;

The execution plan for this query is:

The key here is the outer reference PTR.UseArchive = value inside the apply. This allows the optimizer to construct a plan featuring Filters with Start-Up Expressions. For example, the Filter above the TransactionHistory table Index Seek has:

The Filter Predicate above the other seek is very similar, of course, but tests UseArchive = 1.

Each Filter Predicate is evaluated once per product. It determines whether the seek below it in the plan will be executed on that iteration of the nested loop join.

In this example, the effect is that the TransactionHistory index seek is executed three times; the seek on the TransactionHistoryArchive table is executed just once.

You may be able to implement this pattern for your use case.


How to Block normal user access to mysql user can access mysql by command mysql.
I want to change it so that only root users can run mysql.

ex) mysql // denied
ex) mysql -u root // access

Question need more info.

Unless we set .my.cnf (mysql clinet) on the server, No one can access by mysql simply by hitting mysql, Else you need do mysql_secure_installation to set mysql root password if you haven't done it before.


SYSAUX tablespace is 98% My SYSAUX tablespace is 98% full. In my tablespace statement AUTOEXTENSIBLE=yes.

So I want to know that will it add a new data file if it reaches 100% or if not then should I make makesize to unlimited?

No, Oracle won't add an extra datafile.

Normally the SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script ${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.

See Oracle® Database Administrator's Guide 11g to get an idea of regular sizing.


php single table language separated autoincrements? I have 1 table for posts.
And my columns are
Id | id_language | lang

I want to achieve, that there is a second autoincrement id for its language. For example the 80.post is the 5.post written in english language. I need separate index number for sef url. Is it possible

It sounds like you're saying you have the same posts available in different languages. Assuming that's the case, I'd split the table in two.

post would have the basic info about the post that is not language dependent:

the unique ID for that post's content
the ID of the user who wrote the post
the date the post was created
...

post_language would be the content for each specific post, translated into a specific language:

post_lang_id - unique ID for this post_language row
post_id - foreign key to post; identifies the underlying post that his has content for
language (or language_id): This can either be the name of the language this content is written in, or a foreign key to a language table, with all the languages you've got content in. I'd be inclined to use language_id, but it's up to you.
post_title
post_content
...

In the post table, post_id would be the primary key, and an auto-increment value. In post_language, post_lang_id` would be the primary key, and an auto-increment value.


Find next entity with ordered junction table I have guides which are linked to series (many to many) with a junction table like so:

guides

id  etc.. (more columns)
--
1
13
21
30

series

id  etc.. (more columns)
--
1
3
4

series_guides (primary key consists of these 3 columns) also guide_id is unique, a guide can only link to a series once.

PK series_id  PK guide_id  PK order
------------  --------     -----
1             13           1
1             21           3
1             1            2
3             30           2

Now my question is: How do I get the next guide when I know the ID of some other guide?

Example: Someone is reading guide 13 (guide_id), what query do I need to run to get the next guide from the series. Which is guide 1.

This is what I tried so far, and I actually think it works. However maybe there is a better way to do this?

SELECT
    sg2.`order`,
    sg2.guide_id
FROM series_guides sg
JOIN series_guides sg2 ON sg2.series_id = sg.series_id
WHERE sg.guide_id = 13 AND sg2.order > sg.`order`
ORDER BY sg2.`order`
LIMIT 1;

Any help is much appreciated

Your query is correct and does work. I think you can make the intent a little bit more clear by writing it in a slightly diffent way (although this is open for debate). The execution plans (on MariaDB) are exactly the same.

The comments in the middle should be explanatory:

SELECT
    sg.series_id, sg.guide_id, sg.ord
FROM
    series_guides sg
    JOIN
    (-- We get the current `series_id`, `ord`, based on `guide_id`
    SELECT
        series_id, ord
    FROM
        series_guides sg
    WHERE
        guide_id = 13
    ) AS sg0
    -- We want to be in the same series_id, but greater ord
    ON sg.series_id = sg0.series_id AND sg.ord > sg0.ord
ORDER BY
    sg.ord
LIMIT
    1 -- We just want one result
;

This will give you:

series_id | guide_id | ord
--------: | -------: | --:
        1 |        1 |   2

If you change then the guide_id to be 1, you'll get the next value as:

series_id | guide_id | ord
--------: | -------: | --:
        1 |       21 |   3

Check everything at dbfiddle here

Remarks and (simplified) table definitions:

I've defined the tables with different namings, to avoid confusions (and not to clash with the ORDER keyword, just in case):

CREATE TABLE guides
(
    guide_id integer PRIMARY KEY,
    guide_stuff varchar(100)
) ;

CREATE TABLE series
(
    series_id integer PRIMARY KEY,
    series_stuff varchar(100)
);

If guide_id must be UNIQUE (and NOT NULL, or otherwise, it doesn't make any sense) it's already a candidate key. Being the simplest candidate key, it's the one I'd use as primary key:

CREATE TABLE series_guides
(
    series_id integer NOT NULL REFERENCES series(series_id),
    -- If this is unique, make it just the primary key. It's simpler
    guide_id integer PRIMARY KEY REFERENCES guides(guide_id),
    ord integer NOT NULL,
    -- This covering index will prove useful
    UNIQUE (guide_id, series_id, ord)
) ;

Probably, you would want to have also another restriction: UNIQUE (guide_id, ord) as well, to avoid situations where your order might not we well defined.


database mirror problem with Network Service account I have two SQL Server 2008 R2 instances installed on same Server and the engine log on as Network Service account which is set by installation.

there is a database TestDB on both instances. I try to setup database mirror for this case and I failed with following error:

  The server network address "TCP://myserver.mycompany.com:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

Then I changed the account to Local System for engine and restarted sql server service and I am able to setup mirror.

How to resolve this problem if I want to keep Network Service to run the engine service?

Use a local system account, or create a local or domain account.

The NetworkService account has its limitations as that documentation explains.


How to whitelist all connections in MySQL It would be convenient for me to whitelist a server so that anyone with the username and password can connect to it remotely? Is this possible? I do understand this is horrible for security purposes if it's even possible (I haven't found anything that mentions a way to do such.)

This should be easily achieved by issuing the command:

GRANT ALL ON *.* to user@'%'; -- you can also use dbname.* to restrict their
                              -- access to a given database.

as explained here and here.

You can do some restricting (normally a good idea) by making use of the wildcard (% is the MySQL wildcard) - you can also do '%.domain.com' or '%.123.123.123' or similar if necessary.

You don't have to GRANT ALL PRIVILEGES - you could just give them SELECT and/or UPDATE for example and not INSERT... This is all explained here.


Insert from Another Table + Removing Duplicate Entries in Postgres I'm trying to insert data from one PostgreSQL table to another while removing duplicate entries but I'm having some trouble with the code.

I have two tables I'm working with: mytesttable, which contains test entries and entries, which is my main entries. The entries table has a primary key of (username, date, time, x0, x1, x2).

Code that I'm trying to implement from this post: Optimal way to ignore duplicate inserts?

BEGIN;
INSERT INTO entries (username, date, time, x0, x1, x2, x3, x4, x5, x6)
SELECT username, date, time, x0, x1, x2, x3, x4, x5, x6
FROM mytesttable;
EXCEPTION
WHEN unique_violation
THEN
/* ignore */;
END;

And my error message:

ERROR:  duplicate key value violates unique constraint "log_pkey"
DETAIL:  Key (username, date, "time", x0, x1, x2)=(duplicate key information) already exists.
ERROR:  syntax error at or near "EXCEPTION"
LINE 1: EXCEPTION

UPDATE

I tried to implement one of the suggestions from below with no luck. The documentation for BEGIN says that you need a semicolon and I'd get a syntax error on the INSERT without it, so I kept it.

BEGIN;
INSERT INTO log (username, date, time, x0, x1...)
SELECT username, date, time, x0, x1, x2...
FROM stevietable;
EXCEPTION
WHEN unique_violation
    THEN RAISE NOTICE 'I am ignoring a unique violation';
END;

And the error:

psql:/tmp/test.sql:4: ERROR:  duplicate key value violates unique constraint "log_pkey"
DETAIL:  Key (duplicate key information) already exists.
psql:/tmp/test.sql:7: ERROR:  syntax error at or near "EXCEPTION"
LINE 1: EXCEPTION
        ^

Why not just use distinct on like this:

INSERT INTO entries(username, date, time, x0, x1, x2, x3, x4, x5, x6)
SELECT DISTINCT ON (username, date, time, x0, x1, x2) username, date, time, x0, x1, x2, x3, x4, x5, x6
FROM mytesttable;

?

But beware that this will retain the first row for every distinct combination -- which might not be the right thing without an order by clause this. But since you don't order in your question I presume you either don't care or haven't thought that far yet.


Why would shrinking TEMPDB log lead to slowness? I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?

I'd ask the person who told you that, to at least hear why they think it will decrease performance.

One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you have Auto Growth set. Depending on how you configured it, it will grow a set amount each time, or a percentage of it's size each time. Thus, each time your transaction log needs to grow, SQL Server has to do work, which you could notice regarding performance.

A primary reason is detailed in the first link below:

  When the Auto Growth mechanism of the Transaction Log kicks in, SQL
  Server always has to zero-initialize the new chunk that is added to
  the end of the file. It doesn’t matter if your SQL Server instance is
  running with the privilege Instant File Initialization, or not – the
  Transaction Log is always zero initialized.

What makes more sense is setting a dedicated drive for this file, and setting the LOG file to the size of the drive. Why shrink to 0 when you know it's only going to grow again, leaving you in the same spot with it’s size, cause fragmentation, etc?

Why Transaction Log Auto Growths are Degrading your Performance
Percent or 1MB Growth in Use, Slow File Growths
Microsoft's Considerations for AutoGrow

tempdb uses the simple recovery model and clears the log when ever a checkpoint is issued. This automatically happens when the log usages reaches 70% and in the case of tempdb, it doesn't force dirty tempdb pages to disk. See Paul Randall's post on this. VLF's are made inactive if possible during the checkpoint as well. When you shrink the log, the VLFs are removed.

Paul Randal 8 Steps to better Log Throughput (see 7-8)
Pinal Dave Reducing High VLF Counts


Import from incremental backups to a new host in Oracle 11g I am using Oracle 11g. I would like to know that whether it is possible to import from incremental level 0 & 1 backups to a new host using RMAN. If yes, how can I do that?

For level 1 I am using differential method.

yes, oracle can do that provided there is a full backup available to start the restore with. When that is restored you can recover the incremental backups against the restored database. When the incrementals were done online (mostly the case, you also need the archived log files)


Table check contraint to verify unique on specific value Oracle 10g: Suppose the following table (T_REGISTER):

ID_PROCESS   PERIOD    CUSTOMER    STATUS
===========================================
0001234      201801    12300344    INVALID
0001236      201801    12300344    INVALID
0001246      201801    12300344    UNPAID
0001249      201801    12300344    UNPAID
0001278      201801    12300344    COMPLETED

ID_PROCESS is the PK.
But I also need that (PERIOD + CUSTOMER + STATUS) to be unique just when the STATUS is COMPLETED.

In other words, for one UNIQUE (PERIOD + CUSTOMER), I don't care having several INVALID or UNPAID rows, but I only can admit one unique (PERIOD;CUSTOMER; STATUS="COMPLETED")

Any elegant solution using CHECK CONSTRAINT before going for the trigger solution?
Thanks in advance...

Check constraints are not for enforcing uniqueness.
Unique function-based index can do the job (because Oracle accepts multiple NULL values on unique columns).

create table test1(ID_PROCESS   int , PERIOD int, CUSTOMER    int, STATUS varchar2(20));

create unique index idxu1_test1 on test1
    (case when STATUS ='COMPLETED' then PERIOD else null end,
     case when STATUS ='COMPLETED' then CUSTOMER else null end
     );

insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (1,1,1, 'invalid'); -- ok
insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (2,1,1, 'invalid'); -- ok
insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (3,1,1, 'invalid'); -- ok

insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (4,1,1, 'COMPLETED'); -- ok
insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (5,1,1, 'COMPLETED'); -- ORA-00001: unique constraint violated


The system cannot find the file specified I'm running into a problem when trying to run any of the jobs created by Ola's script:

  The process could not be created for step 1 of job
  0x19DAD4A7019FB04D9C984027FCA2F4E3 (reason: The system cannot find the
  file specified).  The step failed.

I've made sure that sqlcmd is in the PATH environment variable, and I've also put the sql agent service account in local administrators to make sure it isn't a permission issue.

When I log into the server locally with an account that has local admin rights and sysadmin on the sql server and try to run sqlcmd.exe I get the following:

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes
  Provider: Could not open a connection to SQL Server [2]. .

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout
  expired.

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and i f SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online..

Has anyone else run into this issue?

This ended up being a permission issue to the path where sqlcmd resides.


Use data in a tablespace to populate another postgres database instance without actually inserting I have a postgres database (version 8.4) with many tables. Some tables are huge in size, but the data is static (common for any instance). I have stored such tables in a separate tablespace. Is there a way to use this existing data. Or can I create another database instance (may be on a separate server) and create empty bigTable and point to the existing tablespace to populate the data in the existing bigTable?

Can I use the existing data in a tablespace to be populated in another postgres database instance without actually inserting the data, as reinserting the whole data takes so much time.

I tried updating the pg_class table to point to the existing reltablespace and relfilenode. But it didn't help me fully since I was not able to change the database OID. Is there a way so that we can create a database instance on another database server with he same database OID?

A table belongs to one database. Period. You can share a tablespace among databases of the same DB-cluster, but you cannot share a table.

But you can use dblink for what you have in mind. This way you can query tables from a different database - even from a different DB-cluster on a different server. Simple SELECT queries on the same server are very fast. Try this search for code examples.

Or look at CREATE FOREIGN TABLE for a very similar functionality. For now, I prefer dblink, because it is mature and stable. The new SQL/MED features (Management of External Data) FOREIGN DATA WRAPPER and FOREIGN TABLE are still in the making ..


Syncing data from primary member to secondary after election Maybe this is kind a stupid question but, I'm novice in configuring replication and I need to clear something before makeing anything else.
Moment ago I configured replica set with 3 members in it. It's all working well but I think I don't understand the part about copying data from primary to secondary member of replica set after new election.

Let's say I made one collection in my primary member, after that kill instance of primary member, election has been made and now I have new instance as primary member on example --port 27018.
What happend with all data on first instance which I killed? Do I need to make mongodump? Do I need to use some sync method for instant syncing or read the oplog and how to do that? Beacause, I tought that if I made an replSet, and turn on all three instances which are connected, all of them will listening the same oplog and after election update the database, or I'm completly wrong about that and I'm clearly missing some things?

Thank you!

It depends how far behind it fell and what status you have it in your config file at the time of resync.

Basically if you've been down so long that it can't catch up with the OpsLog or the OpsLog no longer has the transactions it needs due to it being removed over time you'll want to resync yourself.

Check out the docs for your version:

Restart the mongod with an empty data directory and let MongoDB’s normal initial syncing feature restore the data. This is the more simple option but may take longer to replace the data.

See Procedures.

Restart the machine with a copy of a recent data directory from another member in the replica set. This procedure can replace the data more quickly but requires more manual steps.

See Sync by Copying Data Files from Another Member.

Let us know if you have more questions. Good luck.  Test test test!


Circumstances under which a windows server login becomes disabled Can anyone confirm under what circumstances a SQL Server windows login account changes from Enabled to Disabled?

More specifically does this need to be a human interaction or can SQL Server automatically disable a windows account under some circumstances (say if it connects too frequently?)

I do not have access to the SQL Server logs so I can't check these

We have many SSIS jobs running against many SQL Servers under a service account. We discovered yesterday that the windows account was disabled on one source server (but still functions against many other servers)

Update: its possible this account was not disabled but I'm still interested in the answer.

SQL Server never automatically disables any account. Only a human intervention can do that....

Probably you could put a request for your DBA to provide error logs for the timings you wanted....and you could find the info from the logs....


select runs in batch mode, but CTAS runs in row mode I have the same query that shows as running in batch mode when run as a select statement, but row mode when the same query is the select part of a CTAS operation. Both tables (plus the resulting table) are all distribution aligned.

Why is this? Obviously I would like it to run in batch mode if possible.

Causes row mode execution:

create table mytable1
with
(
    distribution = hash(c1)
)
as

query:

with  filterData as
(
    select
        a
        ,b
        ,c
        ,d
        ,DateStamp
    from [DW_reporting].[table1]
    where num > 0
)

    select
        p.[DateStamp]
        ,p.[a1]
        ,p.b1
        ,p.c1
        ,p.d1
        ,e.a
        ,e.b
        ,case when e.d is not null then 1 else 0 end as Flag
    from SOmeOtherTable p
    left join filterData e on e.a = p.a1
                                            and e.c = p.c1
                                            and e.DateStamp < p.DateStamp

Running explain on the query showed that something underneath was setting MAXDOP to 1.

Forcing the query to use 0 overcame this, as columnstores require DOP >= 2

Therefore adding the below hint enables batch mode

    OPTION (MAXDOP 0)

I don't understand why this is happening -  my understanding is that CTAS and building a new columnstore should always be parallel, but this resolves the issue.

Edit: This appears to be the default setting as it's aiming for the best optimum columnstore quality. So you have to make your choice between read performance and index quality or use an intermediate store, with each query having a different maxdop hint.


Copying Data to Other Database Tables Should I use SSIS or stored procs to copy data from a number of database tables on one server to database tables on a different server?

Each table's structure is identical to the destination server except for an identifier added to the destination table. There is no real data transformation.
The process would get called from a user application.

My thoughts on using SSIS is that each copy process can run simultaneously. Can I do this with stored procs?

Any suggestions?

Thanks.

Seeing some comments saying not to use SSIS for this, but my vote would be for using SSIS based on the fact that you said that the data would be transferred between servers. If you used a stored proc, you would have to have a linked server setup.

And yes, using SSIS would allow you to run your extract and load on your tables in parallel. It would also give your more flexibility to do things like dump your extract to raw files to get that done quickly without putting as much pressure on your source system and then import the raw files on your destination server where it might take longer.

I also guess you could use replication, but if you don't have replication already installed and configured, that would be a bigger lift to get up and running then writing a simple SSIS package that could do all this for you, in my opinion.


Index file - size changed I realised today that my index file(which is about 250GB) is smaller than yesterday for about 7GB.

Everything, I done in the meantime, was index rebuild.

Is it possible that index rebuild is the reason that file became smaller?

Tnx.

By index file, you mean you place indexes on a dedicated filegroup that maps to the file in question?

Yes, it could indeed be smaller. Indexes can become fragmented over time (inserts and updates need to split pages when inserting into a range) causing free space on your pages over that defined by your fillfactor. If your fillfactor is say 80%, this is not maintained as pages are split, meaning your free space on a page could be 50%. When the index is rebuilt, your pages will be 80% full again, meaning less pages (space) is required for the index as a whole.

Do you mean your physical file reduced in size when viewed in windows explorer? The above describes the space used by an individual index. If your physical file has gone smaller, you must also have a process that shrinks the file to reclaim the lost space (autoshrink on or a job that shrinks?). In general, this is not good practice as your file will only have to grow again as more data is added and your indexes become fragmented again.


Which option do I have to modify to see more records without press `q` I'm connected remotly to my database server with cygwin in full screen.

When I query my database with

   select id, name from table limit 27;

I get 27 records and I can do a new query seeing the result of the previous query.

But, If I replace 27 by 28, I see 28 records in one screen with an (End) in reverse video.
If I want to do a new query, I have to press q.

I can't see the result of the previous query even if I use the scrollbar of my cygwin window.

Which option do I have to modify to increase this buffer ?

My psql client and database server version is 9.1.4 and they are installed on Ubuntu 12.04

You can turn off (and on) the paging in psql:

\pset pager [on|off]

If you don't specify on or off, the command will toggle the setting.  (More on \pset in the documentation.)
In the shell, you can even set the pager you want to use:

PAGER=/usr/bin/less

If you start psql after this, less will be your pager.


Backup to unc path in workgroup environment I am having a problem backing up the database to the network path using dbatools.io Backup-DBAdatabase command.
This is a workgroup environment. The SQL Server runs under the NT Service\MSSQLSERVER account. The location I am trying to backup is a standalone  NAS drive.
I mapped the network folder to the SQL Server machine but when I backup to that drive it fails with

WARNING: [14:25:08][Backup-DbaDatabase] Backup Failed |
System.Data.SqlClient.SqlError: Cannot open backup device
'K:\BackupTest\15Jan2021_12_34\full\sdsd_180_infras_b18c6_202101151425.bak'.
Operating system error 3(The system cannot find the path specified.).

If I use the UNC path I get

WARNING: [14:24:04][Backup-DbaDatabase] Backup Failed |
System.Data.SqlClient.SqlError: Cannot open backup device
'\\XX.XX.XX.XX\backup\BackupTest\15Jan2021_12_34\full\XX_180_infras_b18c6_202101151424.bak'.
Operating system error 1326(The user name or password is incorrect.).

I am aware the issue is due to the SQL Service account's lack of permission on the NAS folder not sure how can resolve it.
I really appreciate your help

the problem are not coming from the dba tools but from your network.
You can't mount a share on a local path. That path (K) can't be seen by sql server.
The unc path works with the user that is running the sql server service, in your case the computer account; but you can't authorise the compunter account in your nas under workgroup.
The only option i can suggest you to test is to change the sql server user to a local windows user and then create a user in the nas that match it in the username and password.
Then you can try with the UNC path; but i'm not sure if it will work.


Using XQuery to filter out some children, but preserve parents SET NOCOUNT ON;
DECLARE @xml AS Xml = 'bbb<c>ccc</c><d>ddd</d>';
SELECT @xml;
SELECT @xml.query('/a/*[self::b or self::c]');
SET @xml.modify('delete /a/d');
SELECT @xml;

Gives the following resultsets

Original:

bbb<c>ccc</c><d>ddd</d>

Filtered to exclude non-(b|c) - but parents are missing:

bbb<c>ccc</c>

What I want (available with delete steps but not with .query):

bbb<c>ccc</c>

Is is possible to preserve the parents in an XQuery?

You may try following

SELECT @xml.query('
    element a {
        for $node in /a/*[local-name() != "d"]
            return $node
    }');

But it may not be suitable if your real XML is more complex and you have to exclude node at a deeper level of nesting.

Speaking honestly, I don't think that XQuery is the good tool for such sort of tasks. In essence we have XML and we need to remove a node somewhere within, i.e. we need to transform it. The more suitable tool, I think, is XSL transformations. SQL Server has no built-in XSLT capabilities unfortunately (it can be added via SQLCLR function, though).

This is how body of XSL for solving this task could look

<!-- Skip "d" under "a" -->
<xsl:template match="a/d" />

<!-- Apply identity transform to other nodes -->
<xsl:template match="@*|node()">
  <xsl:copy>
    <xsl:apply-templates select="@*|node()" />
  </xsl:copy>
</xsl:template>

It will work for a element residing anywhere within XML, not just at the root.

SQLCLR-function for performing non-parameterized XSL transformation can be as simple as few lines of C# code

[SqlFunction(Name = "XslTransform")]
public static SqlXml XslTransform(SqlXml xml, SqlXml xsl)
{
    if (xml.IsNull || xsl.IsNull)
        return SqlXml.Null;

    var xslt = new XslCompiledTransform();
    using (var xr = xsl.CreateReader())
        xslt.Load(xr);

    var xws = new XmlWriterSettings
    {
        Encoding = Encoding.Unicode,
        OmitXmlDeclaration = true
    };

    var output = new MemoryStream();
    using (var xw = XmlWriter.Create(output, xws))
    using (var xr = xml.CreateReader())
    {
        xslt.Transform(xr, null, xw);
        xw.Flush();
    }

    output.Seek(0, SeekOrigin.Begin);

    return new SqlXml(output);
}

It should be declared in database like

CREATE FUNCTION SQLCLR.XslTransform
(
    @xml xml,
    @xsl xml
)
RETURNS xml
AS EXTERNAL NAME [AssemblyName].[ClassName].[XslTransform];
GO

And it can be used then as

DECLARE
    @xml xml = N'(Your XML goes here)',
    @xsl xml = N'(Your XSL goes here)';

SELECT SQLCLR.XslTransform(@xml, @xsl);


Relationships between subtypes of an entity I have a very big (hundred of milions rows) table which represent an entity (event) having many subtypes (different kind of recorded events).
Each row is identified by an id (obj_id) and has an attribute obj_type that records the type of event.

All the subtypes share the most important attributes (represented on the main table) but a few of them (a minority) have aditional attributes that are represented in additional children tables.

I have to represent new relationships involving rows from the main table. For example record that an event of type 'A' is in relation with an event of type B and an event of type 'C' or 'D', or an event of type 'X' or 'Y' is in relation with an event of type 'Z'.

Which one of the two schemas below is better? (Is any of the two not advisable?)

Schema 1:

create table supertype(
obj_id varchar2(30) primary key,
obj_type varchar2(1) check (obj_type in ('A','B','C','D',..)),
attribute1  varchar2(1),
attribute2  varchar2(1),
attribute3  varchar2(1),
...
attributen  varchar2(1)
)

create table relation_A_with_B_and_cd(
obj_id_subtype_A varchar2(30) primary key  references supertype(obj_id),
obj_id_reference_to_subtype_B varchar2(30) references supertype(obj_id),
obj_id_reference_to_subtype_C_or_D varchar2(30) references supertype(obj_id),
attribute_det_a1  varchar2(1),
attribute_det_a2  varchar2(1),
)

create table relation_XY_with_Z(
obj_id_subtype_X_or_Y varchar2(30) primary key  references supertype(obj_id),
obj_id_reference_to_subtype_Z varchar2(30) references supertype(obj_id),
attribute_1 varchar2(1),
attribute_2  varchar2(1),
)

Schema 2:

create table supertype(
obj_id varchar2(30) primary key,
obj_type varchar2(1) check (obj_type in ('A','B','C','D',...)),
attribute1  varchar2(1),
attribute2  varchar2(1),
attribute3  varchar2(1)
)
alter table supertype add constraint UQ_type unique(obj_id,obj_type)

create table relation_A_with_B_and_cd(
obj_id_subtype_A varchar2(30) primary key ,
obj_type_subtype_A varchar2(1) check (obj_type in ('A')),
obj_id_reference_to_subtype_B varchar2(30) ,
obj_type_reference_to_subtype_B varchar2(1) check (obj_type_reference_to_subtype_B in ('B')),
obj_id_reference_to_subtype_CD varchar2(30) ,
obj_type reference_to_subtype_CD varchar2(1) check (obj_type reference_to_subtype_CD in ('C','D')),
attribute_det_a1  varchar2(1),
attribute_det_a2  varchar2(1),
constraint fk_subtype_a foreign key (obj_id_subtype_A,obj_type_subtype_A) references supertype  (obj_id,obj_type),
constraint fk_ref_subtype_B foreign key (obj_id_reference_to_subtype_B ,obj_type_reference_to_subtype_B) references supertype  (obj_id,obj_type),
constraint fk_ref_subtype_CD foreign key (obj_id_reference_to_subtype_CD ,obj_type_reference_to_subtype_CD) references supertype  (obj_id,obj_type),
)

create table relation_XY_with_Z(
obj_id_subtype_XY varchar2(30) primary key,
obj_type_subtype_XY varchar2(1) check (obj_type in ('X','Y')),
obj_id_reference_to_subtype_Z varchar2(30) ,
obj_type_reference_to_subtype_Z varchar2(1)  check (obj_type reference_to_subtype_Z in ('Z')),
attribute_1 varchar2(1),
attribute_2  varchar2(1),
constraint fk_subtype_XY foreign key (obj_id_subtype_XY,obj_type_subtype_XY) references supertype  (obj_id,obj_type),
constraint fk_ref_subtype_Z foreign key (obj_id_reference_to_subtype_Z ,obj_type reference_to_subtype_X) references supertype  (obj_id,obj_type)
)

Considerations

The second one duplicates some information but the constraint ensures the object ids in the relations are of the correct subtype. It leaves me perplexed however to declare unique (obj_id,obj_type) since this is not a minimal key, because obj_id is already the primary key.
The first one doesn't duplicate data but doesn't perform any check on the correctness of event types participating in the relation.

When implementing subtypes of an entity there are various solutions on Oracle:

Create one wide table with the attributes of all subentities. Entity type stored in a field and used to enforce business rules ("You may only fill column A for entity type B").
Create one master table with the shared attributes and per subentity another table. And relations back to the master table.
Create one table for each subtype and include the shared columns.

Here you are suggesting option 2. Option 2 keeps you master table rather narrow in terms of columns and might save some storage. However, it has serious drawback when you want to enforce business rules declaratively or through PL/SQL triggers or alike.

For a very large table and option 2 I would recommend against using declarative constraints, even when I am a lover of robust databases. It can get really slow, you need to appropiate indexes for locking and parallel loading high volumes can be challenging in terms of performance and parallelization.

For a very large table I would recommend either option 3 or option 1. Option 1 is rather simple to understand and given the large number of columns Oracle allows in one table, it shall not be a problem. Storage should not be an issue; trailing null columns are not stored at all in data blocks and null values within the row itself only require 1 byte per column to signal "hi, I have no value for this column". For option 1 you can write well-performing and parallellizable views that select only one or more of the subtypes. Option 1 also can be loaded fast in parallel. And you can implement many rules requiring a fk as a check constraint on a row, which does not involve locking or other problems hindering high volume use.

Unless... of course when one of the subtypes have comparetively very wide columns compared to the rest. That would make each of the data blocks contain considerably less rows. In that case I would recommend option 3, since they are such wide differences and use 'union all' constructs to run queries on the whole supertype. A mix can be option 3 and option 1: option 3 for the subtypes with optional columns with lots of data (varchar2(4000) filled nicely) and option 1 for the other subtypes.


Linking tables in Access with unique ID I am creating a database in MS Access that has 3 different tables with 3 separate types of information for one set of users, Personal, Business and Board.

I have one table created with all the users and a unique ID for each.

How do I link up the other 3 tables with the unique ID so the correct information is linked up to each user in each of the tables?

I understand setting relationships, but can't figure out to what field I link up the ID field in the users table to the other tables.

Each table should have a UserID column, used to "link", or relate, rows in the User table with rows in the Personal, Business, and Board tables.

In SQL language, this looks like:

CREATE TABLE Users
(
    UserID int PRIMARY KEY
    , UserName varchar(30)
);

CREATE TABLE Personal
(
    PersonalID int PRIMARY KEY
    , UserID int FOREIGN KEY Users(UserID)
    , PersonalDetails varchar(40)
);

CREATE TABLE Business
(
    BusinessID int PRIMARY KEY
    , UserID int FOREIGN KEY Users(UserID)
    , BusinessDetails varchar(40)
);

CREATE TABLE Board
(
    BoardID int PRIMARY KEY
    , UserID int FOREIGN KEY Users(UserID)
    , BoardDetails varchar(40)
);

Then, when you want to query those tables, you'd write something like:

SELECT UserName
    , PersonalDetails
    , BusinessDetails
    , BoardDetails
FROM Users
    INNER JOIN Personal ON Users.UserID = Personal.UserID
    INNER JOIN Business ON Users.UserID = Business.UserID
    INNER JOIN Board ON Users.UserID = Board.UserID


Sqlite query slow whilst it should be fast I have the following query:

SELECT breakType,date,blockname,modname,metadata,nbtdata,x,y,z
  FROM BlocksBrokenByExplosion
  WHERE dimension=0
    AND y>60
    AND y<70
    AND x>150
    AND x<250
    AND z>150
    AND z<250
    AND date > '2015-05-08 00:10:00'

With a query plan:

"0","0","0","SEARCH TABLE BlocksBrokenByExplosion
 USING INDEX BlocksBrokenByExplosion_Indexes (dimension=? AND y>? AND y<?)"

The index I am using is:

  CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON
       'BlocksBrokenByExplosion' ('dimension' ASC,'y' ASC,'x' ASC, 'z' ASC)

Analyze shows the following information:

  "BlocksBrokenByExplosion","BlocksBrokenByExplosion_Indexes","78193 78193 2114 25 2"

The problem i'm facing

This query takes 200ms to execute and return 4409 rows which is very poor performance in my opinion and I can't help but feeling this can be optimised.

The table only contains 78.000 rows and according to the query plan the index is used.

The question

How do I optimise this query so it will execute in more acceptable speed, or is this the best I am going to get with sqlite?

And does anyone know why the x and z indexes are not being used according to the query plan?

EDIT 1

I managed to shave off 30ms by reformatting the query in this form

SELECT *
FROM
  (SELECT breakType,date,blockname,
                         modname,
                         metadata,
                         nbtdata,
                         x,
                         y,
                         z
   FROM BlocksBrokenByExplosion indexed BY BlocksBrokenByExplosion_IndexesX
   WHERE dimension=0
     AND x>150
     AND x<250
     AND dimension = 0
     AND date > '2015-05-08 00:10:00')
WHERE z>150
  AND z<250
  AND y>60
  AND y<70

And index

CREATE INDEX 'BlocksBrokenByExplosion_IndexesX' ON
   'BlocksBrokenByExplosion' ('x' ASC)

So now it runs in 170ms, but that's still slower than i'd want it. My target is 50ms at least... So i'm still open to tips and tricks how to make this faster.

Multiple ranges lookups cannot be optimized with normal (B-tree) indexes.

You have to create an R-tree index for your coordinates.


Delete rows from a table with inner join conditions I have a query that selects data from a table based on some inner joins:

select * from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

The output of this contains the rows from table1 that I want to delete. So I tried this:

delete from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

But that is not a valid query and returns an error. How can I delete all the rows from table1 which are the result of the above SELECT statement?

If your aim is only delete rows from table1, you can re-write your query as follow:

DELETE FROM table1  p
WHERE EXISTS(
    SELECT 'MYROW'
    FROM table2  e
    JOIN table3  d
        ON  d.col4 = 'IO'
        AND d.col5 = -1
        AND e.col3 = d.col6
    WHERE e.col1 = 'YU'
    AND e.username = p.username
    AND p.col2 = d.col3
);

You convert your INNER JOIN between main table (table1) and the others with using of WHERE EXISTS condition.

Note: string literals (like 'IO') need to be quoted with single quotes and not double quotes.


SQLite3 doesn't have concat_ws, looking for workaround I'm trying to concatenate some columns and I would like to use space as my  output string delimiter.

Basically concat_ws would be a perfect fit, but it doesn't exist for SQLite3.

I'm inside a SELECT already and I'd like to end up with a string that looks like this:

col1_val col2_val col3_val

I have col1, col2, and col3 as my columns, and they may have empty strings as values which I'd not want to delimit.

col1, is safely expected to exist, to not be NULL, and not be empty.
However either col2 and col3 might be empty, and I'd thus want to skip.

I've tried a few different ideas, but all of them seem to end up with double delimiters when a string is empty.

So some expected results:

col1_val
col1_val col2_val
col1_val col3_val
col1_val col2_val col3_val

You can use CASE:

SELECT coalesce(col1, '')
       || CASE
            WHEN coalesce(col1, '') <> ''
                 AND (coalesce(col2, '') <> ''
                       OR coalesce(col3, '') <> '')
              THEN ' '
            ELSE
              ''
          END
       || coalesce(col2, '')
       || CASE
            WHEN coalesce(col2, '') <> ''
                 AND coalesce(col3, '') <> ''
              THEN ' '
            ELSE
              ''
          END
       || coalesce(col3, '')
       FROM elbat;


Enrolling for Microsoft SQL Server related Newsletters from Microsoft I would like to know the process about how to automatically receive information about updates related to SQL Server from Microsoft. I am referring to kind of newsletter kind of alerts..

Updates like :

Latest Service pack releases
SQL Server Version releases
SQL Server enhancements etc...

Can anyone let me know if you already having this ways in place. Thanks.

Follow this feed for notifications of SQL Server updates https://blogs.msdn.microsoft.com/sqlreleaseservices/


Select the minimum of a calculated distance value without sorting I have a query dealing with geo distances. The query is super fast, returning in roughly .1175 seconds on my 2.24 million row table. However, I only need the lowest distance, and using the built-in order by is way too slow.

Is there any way to just keep track of a running minimum and just give me that?

For example, if I have these results:

city a - 45km
city b - 48km
city c - 12km

can I just have it give me the 12km, keeping in mind that all the distance values are calculated?

Here is the query that sorts:

SELECT
    City,
    (
        6371 *
        acos(
            cos(radians(-60.61384878636903)) *
            cos(radians(st_x(location))) *
            cos(radians(st_y(location)) -
            radians(112.80061386895574)) +
            sin(radians(-60.61384878636903)) *
            sin(radians(st_x(location))))
        ) as distance
FROM table_name
HAVING distance <  5
ORDER BY distance ASC LIMIT 1

The table structure is as follows:

id - int(12)
location - Point()
City - varchar(255)

The problem lies in that the order by flag takes way too long to sort the data and get the lowest. Can it just keep a running minimum and then just give me that without a major performance hit?


Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted? In SSMS we are attempting to bulk insert from a csv file into a table that has a column encrypted using SQL Server 2016's Always Encrypted feature.

This is the command we're using:

INSERT INTO membersE
SELECT *
FROM OPENROWSET(
    BULK 'c:\members.csv',
    FORMATFILE = 'c:\membersEFormat.xml',
    FIRSTROW = 2
    ) m

This returns the typical error you get when attempting to insert into an encrypted column:

  Msg 206, Level 16, State 2, Line 6
  Operand type clash: varbinary is incompatible with varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'DATABASE') collation_name = 'Latin1_General_BIN2'

We understand that you can't insert into an encrypted column via SSMS and that you need to use a .NET 4.6.1+ client, but we'd like to know if bulk insert operations are not possible as well?

SAMPLE CODE THAT WORKED FOR ME
(to satisfy Windows10's request)

        SqlCommand cmd;
        SqlConnection conn;
        SqlBulkCopy copy;
        SqlDataAdapter da;
        DataTable dt;

        using (conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString))
        {
            conn.Open();

            using (cmd = new SqlCommand("SELECT * FROM members", conn))
            using (copy = new SqlBulkCopy(conn))
            using (da = new SqlDataAdapter(cmd))
            using (dt = new DataTable())
            {
                cmd.CommandTimeout = 600;

                da.Fill(dt);

                cmd.CommandText = "TRUNCATE TABLE membersE";
                cmd.ExecuteNonQuery();

                copy.DestinationTableName = "membersE";
                copy.WriteToServer(dt);
            }
        }

Bulk Insert operations in the manner you are describing are not supported for encrypted column via SSMS.

Please refer to this (https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-existing-data-with-always-encrypted/) article to migrate your existing data to Always Encrypted

Also, please note that doing bulk inserts through a C# (.NET 4.6.1+ client) app is supported.

You can do this in c# using SqlBulkCopy specifically using SqlBulkCopy.WriteToServer(IDataReader) Method. I am assuming you are trying to load data from csv file to a table with encrypted column (say encryptedTable). I would do the following

Create a new table (say unencryptedTable, for security purposes, you might consider creating this table in a local sql server instance) with the same schema without any column encryption.
Load the csv data into unencryptedTable, using the method that you described in the question
Do select * from unencryptedTable to load the data in a SqlDataReader then use SqlBulkCopy to load it to the encryptedTable using SqlBulkCopy.WriteToServer(IDataReader) Method

If you have additional questions regarding this, please post questions in the comment section and I will try my best to address them :)


Connecting a standalone Microsoft R Server to a SQL Server with R Services and a SQL Server without R Services Let's assume I have 3 machines with me.
My first machine has SQL Server 2016 with R Services installed on it.
My second machine has SQL Server 2016 without R services enabled on it.
My third machine has a standalone Microsoft R Server installed on it.

I understand that since my first machine  has SQL Server with R Services, I can store R script as stored procedures on SQL Server and call it later for analysis.
My question lies with how my standalone Microsoft R Server connects to either SQL Servers (with and without R Services enabled).
From what I understand, it uses ODBC to connect to SQL Server and uses the RevoscaleR functions to query or analyze the SQL data - in both cases.

If that's the case,

What benefit or limitation does my machine 3 (standalone MRS) have while connecting to SQL Server with R Services vs. SQL Server without R Services?
In either cases, can I invoke any stored procedures from my R IDE remotely?
Can I push the compute context to SQL server in both cases?
Is there anything special that SQL Server with R Services offers for a standalone Microsoft R Server when compared to a SQL Server without R Services.
Is there any difference in the database engine's ability to perform other tasks either way?

Imagine I'm reluctant to enable R Services on my SQL Server because it may take up the memory and slow down the performance of the database engine itself. All I want to do is get a standalone Microsoft R Server and connect it to SQL Server without R Services and get all the scalability and performance anyway.
What is it that I'm missing by doing this?

I can answer some of these questions hopefully:

The standalone machine has the benefit of most of the machine resources at its disposal.  However as far as benefits "while connecting" go, there are none. The point of SQL Server with R services is to keep the analytics close to the data.  This has little or no consequence for external clients connecting to the data, as I see it at least
you can invoke SQL Server stored procedures from R using the RODBC library, for example this worked for me:

install.packages("RODBC")
library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=.\\sql2014;database=tempdb;trusted_connection=true')
data <-sqlQuery(dbhandle, "EXEC sp_who2")
data
odbcCloseAll()

Results:

push compute is a feature of the ScaleR library, eg function RxInSqlServer.  See here for more details.
regarding "anything special that SQL Server with R Services offers for a standalone Microsoft R Server...", you could conceivably call the R stored procedure sp_execute_external_script from R using RODBC, but this seems a bit pointless.  A dedicated SQL Server box (ie without R Services enabled) does not have to share its resources, and this kind of answers your last question I think; the point of having a dedicated box of either kind (MRS only or SQL Server only) would be so that neither has to share its resources.  The point of having a SQL Server with R Services enabled would be so the box could perform dual roles, resources permitting, plus keeping the analytics close to the data.  I hope that makes sense.

Here's a nice article on persisting machine learning models to a table using SQL Server with R Services.  How would you do that in standalone R?

Maybe someone else can chime in because honestly this seems very similar to your other questions.  You might guess not that many people are doing this so one of the best people to do this is you; try these things out, report back : )


How to install MongoDB database as a service in ESxi? I want to know is it possible to install mongodb directly to the ESXi without guest OS?
Please tell me how to setup Data storage as a service environment inside ESXi.

Answer is simple, you cannot do that! MongoDB cannot operate without OS, period!


Trigger to get next prefixed ID: Is there a better way? I have a projects table:

+------------+-----+
| PROJECT_ID | ... |
+------------+-----+
| LC000001   |     |
| LC000002   |     |
| LC000003   |     |
| LC000004   |     |
| LC000005   |     |
+------------+-----+

As you can see, the PROJECT_ID column has a text datatype (NVARCHAR2) and it has a prefix (LC).

I'm guessing that creating an ID with a prefix might have been a been a poor design choice. But, right or wrong, this is how my organization has set up it's business.

I've written a trigger that automatically inserts the next PROJECT_ID when a new row is created in the application's attribute editing environment (more here).

CREATE OR REPLACE TRIGGER "PROJECTS_PROJECT_ID_TRIG" BEFORE INSERT ON PROJECTS
    FOR EACH ROW
      BEGIN
        SELECT 'LC' || LPAD(TO_CHAR(CAST(SUBSTR(MAX(PROJECT_ID),-6) AS NUMBER(15,0)) + 1),6, '0')
        INTO :NEW.PROJECT_ID
        FROM PROJECTS;
      END;

However, this is literally the very first trigger I have ever written. The fact that it deconstructs the textual ID, get's the next number, and then reconstructs the textual ID, seems overly complicated to me. I can't help but think Oracle might have a more sophisticated way of handling this.

Is there a better way?

I have a subsequent, related question here: Can an Oracle IDENTITY column be nullable?

I expect your trigger to throw runtime "Mutating table" error . Unless autonomous transaction is used because you cannot query the table that fires the row-level trigger inside its body.

I'd rather keep it simple - create a sequence, for instance ,  CREATE SEQUENCE SEQ_PROJECT START WITH 6 INCREMENT BY 1.
Also, I'd fire trigger only if project_id is not set (WHEN clause) .

CREATE OR REPLACE TRIGGER "PROJECTS_PROJECT_ID_TRIG" BEFORE INSERT ON PROJECTS
    FOR EACH ROW
    -- Fire trigger only if statement doesn't set project_id
    WHEN (NEW.PROJECT_ID IS NULL)
      BEGIN
       :NEW.PROJECT_ID := 'LC' ||to_char(SEQ_PROJECT.NEXTVAL,'FM000000');  <- Fill Mode
      END;


MySql Replication in two directions I am successfully able to set up master-slave replication of database with MySQL. So, whenever I am updating my database on master, It will automatically replicated on the slave. Now, I have situation like, it is also possible that database might be updated on the slave, and it should be replicated on master. Basically, I want the Replication in two direction.

Please, suggest me some possible ways

This is called as MySQL Master-Master replication.
You can follow these links which can be helpful to you:

1. How to setup Master-Master replication

2. How to setup Master-Master replication

Another way to implement multi-master replication, including circular replication is using MySQL Cluster.

3. MySQL Cluster replication - Multi Master


Database upgrade script for removing columns So I have a SQL database project which tracks our application's database schemas, stored procedures, etc. It also has deployment script generation sections, including pre- and post-deployment scripts. Part of these scripts includes doing things like caching values of columns that have been removed so they can be moved elsewhere (usually normalized into its own table).

The problem is that the upgrade script process I have implemented, which automatically detects the version and what updates need to be applied at deploy-time, includes references to columns which no longer exist. This is fine when deploying to either an empty database (so the script executor doesn't know there's no column) or to a being-upgraded database, as it can see the column before the deployment. The script validator however doesn't like that I'm referencing columns from a table that it knows about, even if the section which references it will never get called because of the conditions around it.

My current solution is to store in a string all of the parts of the script which handle columns being removed, then running those sections dynamically using EXEC. This is a little icky to me (and EXEC is a code smell issue), though not the worst thing. This is code that's only run once per DB and is never accessible during production operation.

What I want to know is, is there a better way of doing this? Can I somehow comment to the validator that "This won't run, please ignore it"? Is this whole process terrible? (I'm not that experienced of a DB admin at all, so any expertise here would be very helpful.) If I'm doing this all wrong, how do you handle one-off scripts for database updates? I've also considered doing one-off conditional compilations using SQLCMD declarations/labels, then automatically removing sections that aren't needed for a particular version-to-version upgrade using a script.

As a last note, we are using SQLPACKAGE to generate our upgrade scripts. Is there possibly a way to tell the tool to omit some sections in certain cases?

Code sent to SQL Server is validated, prior to execution, by SQL Server itself.  The query processor checks object names for existence prior to running the query, without regard for any code paths that may or may not be taken.

The only way to avoid the issue you're experiencing is to us sp_executesql exactly as it sounds like you are doing now.  Since you are doing this in an upgrade script that you control, there is virtually zero chance for a SQL injection problem, assuming you aren't taking non-validated input from a user (or via a config file the user has access to).

In case you haven't seen this, read Erland Sommarskog's bible on dynamic SQL. - it's a long read, but well worth it.


Fast Conversion to Big INT I have a huge number of large databases (300+!) that vary in size from teeny tiny to absolutely massive (120 GB+) in size.  I've already determined a way to generate scripts to decide what indexes, foreign keys, primary keys, and default constraints need to be removed in order to convert a known set of columns from int to bigint data types.

The problem we're running into is on the larger databases, we're hitting 2-3 days runtime on this query, which isn't acceptable in production. Is there a method to make these conversions that is faster than the current drop-change-rebuild process?

I had a similar conversion of a key column from int to bigint that involved hundreds of tables, some over 100GB.  I used an approach like the one below, which accomplished the migration a couple of hours against the large client databases. If the database is in the FULL recovery model, make sure sufficient transaction log space exists and/or perform log backups during the migration to avoid filling the log.

1) Drop referencing foreign keys on tables to be modified and schemabound objects

2) Optionally, drop all non-clustered indexes (including non-clustered primary key and non-clustered unique constraint indexes) on tables to be modified to reduce disk space requirements during the migration.

3) For each table, create new table using SELECT...INTO with an explicit CAST of the column to bigint, with ISNULL to coerce a NOT NULL column when necessary. Drop original table and rename new table to original name.

4) Recreate clustered indexes/constraints, non-clustered indexes/constraints, followed by other constraints and objects.


MySQL no longer starting "An attempt was made to access a socket in a way forbidden by its access permissions." (Windows 10) I don't think this is another "some other process is using port 3306 rtfm" kinda question.
I have been running MySQL 8.0.x fine for a couple of months on Windows 10 for some local python development. As of this week it refuses to start (don't know the exact date of when it started, but logs say it was still running 30/10/2020).
2020-11-07T10:57:43.452460Z 0 [System] [MY-010910] [Server] C:\tools\mysql\current\bin\mysqld: Shutdown complete (mysqld 8.0.22)  MySQL Community Server - GPL.
2020-11-07T11:01:21.048896Z 0 [System] [MY-010116] [Server] C:\tools\mysql\current\bin\mysqld (mysqld 8.0.22) starting as process 3480
2020-11-07T11:01:21.071269Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-11-07T11:01:21.618118Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-11-07T11:01:22.095397Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2020-11-07T11:01:22.174810Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-11-07T11:01:22.175307Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-11-07T11:01:22.177656Z 0 [ERROR] [MY-010262] [Server] Can't start server: Bind on TCP/IP port: An attempt was made to access a socket in a way forbidden by its access permissions.

At first I thought maybe the 3306 port was in used, but that was not the case. Did a reboot to be sure. Stopped Docker. Checked if the exception was still present in the windows firewall. All good.
Tried port 3307 just to see what happened. Disabled Windows Defender. Same result. So my feeling is this might be caused by some Windows Update or changed security setting.
netstat shows no process listening to port 3306:
netstat -ano | find /i "3306"
<no output>

I did google a lot, but couldn't really find anything beyond SELinux or Antivirus preventing stuff.
Any pointers. Probably Windows Defender / Firewall, but don't know what setting to adjust. Mysql exception rule is in place:

Valentijn

Found the issue. Even though I rebooted (turned off, turned on), my ethernet connection (via dell dock) was not active. So only wifi was active and apparently windows prevents services from listening to ports on that. Even though there's an exception present for public networks in the firewall.
Anyway, after disconnecting and reconnecting the ethernet cable it started working again.


Importing data from MS Access database to an existing Postgres database I'm developing a Django based website with a Postgres DB.  The problem I'm having is importing the data from an Access DB into it.  To be specific, I'm not trying to translate/import the schema from Access into Postgres flavoured SQL -- as far as I've gathered there are lots of tools to do that, and it seems more or less straightforward.  I'm trying to import data from just a number of tables and, hopefully, their relationships.

Basically, my problem is that I'm not sure at all what would be the most painless way of doing this.  The best idea I could come up with so far is dumping the database from Access into a Postgres compatible SQL, massaging the SQL to get it in line with the schema in the new DB and then loading that.  But this sounds like too much yak shaving.  Is there a better way to do this?

On the Postgres side of things everything is still in development stage, so the DB will be empty, I'm not at risk of loosing data from it.  I should note for completeness sake that I'm using South for migrations in Django and there are a few migrations done (not sure if this can complicate things). The Access DB is Access 2003 and Postgres is 9.1.

I'll be happy to give more details if needed. Thanks!

I'd tend to export each table as CSV from Access, something it's quite good at. Then import it into PostgreSQL using COPY, possibly via a preprocessor like a Python script with psycopg2 if there's lots of mangled / invalid data.

If there are lots of tables, VBA (Visual Basic for Applications) or similar can be used on the Access side to automate the export, and your scripting language of choice can automate the import.

Alternately, you might be able to find an ETL tool that can connect to access and load into PostgreSQL directly. See if Pentaho Kettle, Talend Studio, CloverETL, etc can do it - possibly over ODBC if nothing else.


Execution time with warm up cache I have a query that takes around 1 second on first execution and 15-50 ms after running the same query again. The only difference between EXPLAIN outputs is that the second execution contains a lot of Buffers: shared hit which are probably (and correct me if I'm wrong) responsible for the noticeable speed up.

So I was wondering, when people talk about queries taking x milliseconds, are they usually referring to the time it takes on first execution?

Do you care about a slow query that after caching is actually quite fast? This assumes that your application requires reasonable response times (e.g. e-commerce, real-time analytics, etc).

I'm using PostgreSQL 9.5, but I intended to ask something general enough to be widely useful.

If the reporter had any thoughts of rigor I would expect the hot cache / cold cache information to be included in the report. Otherwise it is like saying a car costs $y without saying which option, warranty, service or taxes that includes. Similarly I would expect to see it stated that subsequent tests were performed under the same conditions and how that was achieved. In less formal discussion I would assume hot cache, as that is the usual operating condition, unless told otherwise.

The two conditions emphasise different aspects of the host configuration. For a cold cache one sees more of an impact from the disk subsystem. For hot cache it is more about memory pressure and efficiency of the query plan. It would be unusual to find a workload that was purely one or the other in a real-world scenario, however.

SQL Server, for one, assumes a cold cache when costing a query plan.


Duplicate databases error I have two Oracle 11gR2 databases on separate servers. I want to duplicate these databases.

For this, I do these steps :

In auxiliary db :

create password file with password that is same if target password

In target database:

Create a pfile and set db_name to auxiliary db
in tnsname.ora set the auxiliary db
with sqlplus connect to auxiliary db and startup, nomount with pfile.

With rman connect to these db and when I want to duplicate these databases with command below, I have an error that's in attach of this question.

Duplicate database to orcl1 with active database spfile nofilenamecheck.

Can any one help me?

the error messages you got because you don't have privileges to log in to the server so you need to grant the user the necessary access.

some tips to solve this issue:

1) Mount the remote disk locally (map network drive).

2) Use rman CATALOG command to make the backups available.

3) user has enough privileges on ORACLE level


MySQL-5.1 is not updating some rows with a simple UPDATE query I'm trying to update a column in a table to be the same as the contents in the column of another table as part of a de-normalization process for a data warehouse project. The data is confidential, so just to cover my ass I'm going to provide an example using abstract table and column names.

I have three tables, call them A, B and C. A has about 15M rows, and is the main data cube table, and B and C are fact tables, with C being a lookup table for descriptions. Table A has a column dtype which is either 1 or 0, where 1 indicates the data is actual (derived from fact tables of actual transactions) and 0 indicates the data is theoretical (derived from projections). If I run the following statements

UPDATE A SET desc = NULL; -- Just for clarity with regards to initial state

UPDATE A, B, C SET A.desc = C.desc WHERE B.id = A.b_id AND C.id = B.desc_id AND A.dtype=1
  -- produces many warnings the last 64 of which are data truncation warnings

SELECT count(*)
FROM
  A
  JOIN B ON B.id = A.b_id
  JOIN C ON C.id = B.desc_id
WHERE
  A.dtype = 1
  AND A.desc <> C.desc

The last select statement returns 200,000+ for the count. How is this possible?

I'm using MySQL version 5.1.67-0ubuntu0.11.10.1. All the tables are MyISAM.

It turns out that there are duplicate matches for many cases of the join between A and B, and MySQL is choosing the C.desc in an undefined but consistent manner from the duplicates. When performing an UPDATE, it chooses one of the duplicates, but when SELECTing it chooses another. So selecting the ones that are different will return a result, but updating rows will indicate no changes.

Moral of the story: Don't forget to check for duplicates, because MyISAM doesn't.


SQL-Server: Why do we use FILE = 1 in scripts for restoring a database Recently when I came across the script for restoring a database, I got a doubt on why we have to use "FILE = 1"? Can't we restore a database without that statement!? Basically, what it is used for?

You are allowed to save more than one backup in a backup file (i.e. device). The FILE clause lets you access a particular backup operation when there are multiple to choose from within the .bak file.
For more information on the various options of the RESTORE command, please see the following Microsoft documentation for RESTORE Arguments.
If you look under the Backup Set Options section (within the Syntax description), you will find:

FILE ={ backup_set_file_number | @backup_set_file_number }
Identifies the backup set to be restored. For example, a backup_set_file_number of 1 indicates the first backup set on the backup medium and a backup_set_file_number of 2 indicates the second backup set. You can obtain the backup_set_file_number of a backup set by using the RESTORE HEADERONLY statement.
When not specified, the default is 1, except for RESTORE HEADERONLY in which case all backup sets in the media set are processed. For more information, see "Specifying a Backup Set," later in this topic.
Important
This FILE option is unrelated to the FILE option for specifying a database file, FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }.

Yes, you should be able to restore without the FILE = 1 as 1 is the default anyway. And, if you only ever have a single backup-set in a backup file, then it shouldn't pose a problem.
An example to help illustrate when you would use the FILE option is the following, which is Example B from the RESTORE command documentation page (linked above). It shows doing two restores from a single backup file: the first RESTORE is the FULL backup, and the second RESTORE is the DIFFerential backup.
RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
   WITH FILE = 6
      NORECOVERY;
RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
   WITH FILE = 9
      RECOVERY;


Indexing in Big Data I have my data in gridb Database. To make the execution of my search query faster, I would like to do indexing on big data. Can we do indexing in GridDB? If yes, how?

It looks like you can index your data according to thier SQL documentation for CREATE INDEX. Sample code from their docs:
CREATE INDEX [IF NOT EXISTS] index_name ON table_name ( column_name_to_be_indexed );
GridDB's specifications and rules on index creation:

For a table, an index with the same name as an existing index in the table cannot be created.

If a transaction under execution exists in a table subject to processing, the system will wait for these to be completed before creating the data.

An index cannot be created on a column of BLOB type and ARRAY type.

Up to 16 columns can be indexed.

Time series table does not allow a primary key in a composite index.

My recommendation would be to index on the fields you plan to most commonly use in predicates (JOIN, WHERE, and HAVING clauses) in your queries, as a starting point.