This doesn't call for any SCD design -- the state of an entity is not changing, slowly or otherwise. What you have is a series of events that you want to refer to the user associated with the event.

create table Executions(
    ID      int not null auto_increment,
    UserID  int not null,
    ExcDate date not null,
    <any other data concerning the Execution event>,
    constraint PK_Executions primary key( ID ),
    constraint FK_Execution_User foreign key( UserID )
        references Users( ID ),
);

Then remove any Execution related data (ExecutionID) from the Users table. Each execution event relates back to the associated user. So the Users table contains only user data and the Executions table contains only execution event data, including the user that performed the execution. And it maintains all such events not just the first and/or last.


How to decompose the relation R(a,b,c,d,e) whith following FDs into BCNF?

In your relation schema, there are three candidate keys: ABC, ABE and DE.

Since, for instance, AB → D violates the BCNF, we can decompose the original relation in:

R1(ABD) (with dependency AB → D and candidate key AB), and
R2(ABCE) (with dependencies E → C and ABC → E, and candidate keys ABC and ABE)

this is because we decompose in two relations, AB+ and R - (AB+) + AB.

The second relation is still not in BCNF, since in E → C the attribute E is not a superkey.

So we can apply again this method to decompose R2 in:

R3(CE) (with dependency E → C and candidate key E)
R4(ABE) (with no dependency and candidate key ABE)

Both are in BCNF and the final decomposition is constituted by R1, R3, R4.

Finally, it is worth to note that the following dependencies:

DE → AB
ABC → E

are lost in the decomposition.


There is no "BEST" way to add a column. There is only one way - using the T-SQL command. What do you think happens when you use SSMS table design? it's just a way to simplify tasks for those who don't know the syntax. When you click OK, SSMS generates the ALTER TABLE statement and executes it for you.


Check constraint references another column in same table

The syntax you are using is for a column-level constraint, so it can only apply to the column it is attached to... try naming your constraints at the table level instead (this also allows encourages you to give your constraints meaningful names instead of the terrible system defaults):

CREATE TABLE dbo.Medicaments -- always use schema*
(
  MedicamentID INT IDENTITY(1,1) NOT NULL,
  Name VARCHAR(50),
  DateValidity DATE,
  MedicamentRegulated BIT,
  CONSTRAINT pk_Med PRIMARY KEY (MedicamentID),
  CONSTRAINT ck_Med_reg CHECK
  (
    (MedicamentRegulated = 1 AND DateValidity IS NOT NULL)
    OR MedicamentRegulated = 0
  )
);

*see this post.


Best way to get the latest Identity when inserting through View

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope


How to Use OPENROWSET function within a Transactional SSIS Package

You can turn off DTC Promotion for a linked server

EXEC master.dbo.sp_serveroption @server=N'LincServer', @optname=N'remote proc transaction promotion', @optvalue=N'false'

And allow non-transacted updates for the linked server provider:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1

Which might resolve this issue.  Note for the first one you'd need to create a linked sever definition, instead of using the ad-hoc version of OPENROWSET.

But using ACE from a linked server in an SSIS package is a strange thing to do.  SSIS should connect directly.

From the ACE download page:

  The Access Database Engine 2016 Redistributable is not intended:  . .
  .  To be used by a system service or server-side program where the
  code will run under a system account, or will deal with multiple users
  identities concurrently, or is highly reentrant and expects stateless
  behavior. Examples would include a program that is run from task
  scheduler when no user is logged in, or a program called from
  server-side web application such as ASP.NET, or a distributed
  component running under COM+ services.

Microsoft Access Database Engine 2016 Redistributable

So it's better to use ACE from a short-lived process, possibly running under a regular user.  For SSIS package execution uses short-lived processes, and you can control the identity of those processes.


Trigger BEFORE INSERT from date to datetime

It seems each time you are inserting data into both tables i.e. price_changes and prices. Reverse process may be helpful.
If possible  try this :

DELIMITER ;;
CREATE TRIGGER `prices_changes_before_insert`
   BEFORE INSERT ON `prices_changes`
   FOR EACH ROW
   INSERT INTO `prices` (`id_product`, `date`, `value`) VALUES (NEW.`id_product`, NEW.`date`, NEW.`value`)
    ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);;
DELIMITER ;

Then

INSERT INTO `prices_changes` SET
   `id_product` = 3,
    `date` = "2016-08-12 14:50:00", # Is a DATETIME value
    `value` = "20.50";


NLS_NUMERIC_CHARACTERS in SQL loader control file

I had the same question and I find a solution that works for me!

You have to put the sentence set NLS_NUMERIC_CHARACTERS=,. in the beginning of the .bat file. This must be before you call the load in the .ctl file.


It seems you haven't added to service account as a database user to customdb:

USE customdb;
CREATE USER [NT AUTHORITY\SYSTEM];

You will also need to grant the needed permissions on database objects to allow a minimally privileged user to access data.  I suggest you do this via role membership to facilitate manageability:

USE customdb;
CREATE ROLE YourRole;
ALTER ROLE YourRole ADD MEMBER [NT AUTHORITY\SYSTEM];
GRANT SELECT ON YourTable TO YourRole;

These symptoms may also be due to the account name and SID beging different, perhaps because the database was restored/attached. Try dropping and recreating the login and user.


Analysis Services Component set to 'false'?

Ref:

Grant Server Administrator Permissions (Analysis Services)

You need see what accounts are member of local administrator group and determine if those accounts need the privilege mentioned in the document. If these privileges are required and you set to false you will have issues.

  By default, members of the local Administrators group are also granted
  administrative rights in Analysis Server. Although the local group is
  not explicitly granted membership in the Analysis Services server
  administrator role, local administrators can create databases, add
  users and permissions, and perform any other task allowed to system
  administrators. This behavior is configurable. It is determined by the
  BuiltinAdminsAreServerAdmins server property, which is set to true by
  default. You can change this property in SQL Server Management Studio.

To look at the members of local admin group see this picture:

If not you can set to false. If some of those accounts need certain privilege you can create custom roles and add those accounts as member of those roles. See this article for details:

Roles and Permissions (Analysis Services)


How to I get a list of non subscribed users from this database?

It seems to me that you want the cartesian product between users and subscription_types, minus the subscriptions:

select distinct s.user, st.subscription_types
from subscriptions s
cross join subscription_types st
except
select user, sub_type
from subscriptions

This can, of course, be expressed in other ways, but this is probably the most obvious one.


Huge disk space usage increase when altering a column

SQL Server will create a new column, copy the data over, and drop the old column. The table will increase in space by some factor larger than just the size of the new or old column and/or the average or max length of the data. The reason is that temporarily the copy of the data for all rows on a page can't possibly fit on the page, so many new pages will have to be generated.

It doesn't reclaim this space after dropping the column. In order to reclaim the space, you'll need to rebuild the table / clustered index.


How to find all views with invalid column names

You could try importing the database into an SSDT database project and building the project. It should report this type of thing.

Alternatively you could try

SELECT *
FROM   sys.views
       CROSS APPLY sys.dm_sql_referenced_entities (
                                        CONCAT(QUOTENAME(SCHEMA_NAME(SCHEMA_ID)), '.', QUOTENAME(NAME)),
                                        'OBJECT')

And then peruse the error output


Recover a broken or damaged database from the transaction file or "journal"

The MySQL binary log is what you are looking for. Aside from being used for replication, because it stores a range of writes executed on the server, it can be used for point in time recovery.

You will need some kind of regular starting point in case of recovery (such as a full backup), and then you can use the binary logs and mysqlbinlog to roll forward to an arbitrary point in time (e.g. just before an accidental table drop). So they won't substitute backups but can complement them nicely.

Some people was able even to use them to roll back actions under specific circumstances and configuration.


Why add indexes to these columns in the Database?

Why would you add an index to the back of a book? So you can quickly look up a word and see which page numbers it's on.

Why is it fast to look up a word in a book index? Because the words in the index are sorted.

Similarly, in a database index, you can quickly look up a value and get the rows it is on.

The usage of indexes, and the number of columns, is not particularly related. You can have zero, one, or many indexes on (almost) any column in a table.
The index itself is a sorted structure, to allow for fast look-ups, but does not sort the physical rows of its table†.
An index doesn't do anything to the rows. It is a separate structure that points to rows. For example, if you're looking for follower_id 3, you could look at the index and it says, follower_id 3 is on rows 4, 9 and 99. This is faster than looking at every row for follower_id 3.

The last index in your example is a unique index, which prevents one from following the same person twice. An exception will be raised if you try to enter {follower_id=1, followed_id=2} and then {follower_id=1, followed_id=2}.

A unique index is similar to a primary key (though a primary key may not contain nulls), and is identical to a unique constraint in many systems††.

A final question to ask is, is it easy to be the guy that maintains the index of a book if the author is making a lot of revisions to the book?

† unless it's a clustered index

†† In Postgres a unique index may use expressions whereas a constraint may not


How the xlogs files travels form Master to Slave

You're expected to ensure that /path/to/archive is a shared volume, like an NFS fileshare, that both servers can read. Or you're expected to use an appropriate command to copy the files to/from a shared storage location.

I think this might need to be made more obvious in the documentation.

Re your comment:

  Why it is necessary to create a archive directory and the copy doesn't execute directly into pg_xlog?

the reason is that the master's pg_xlog is generally on a different server to the replica(s). The replicas cannot access it. You really don't want to put pg_xlog on shared storage because it's performance critical.

It also does you no good to have a backup that requires access to the master server's pg_xlog when the master server just melted in a fire.

By archiving WALs, PostgreSQL can also reduce filesystem fragmentation. It recycles WAL archives once they're no longer required, renaming them and using them as if they were new files. This is a significant performance improvement that would not be possible if it had to keep all the old WAL files around.

Finally, it creates a logical separation between "transaction logs the master server still needs to operate" and "transaction logs that are now only required for backup/archival".


Shrink database or DB files not reducing size even after dropping biggest table

Exactly how did you run the SHRINK commands?  My understanding is that by default it just releases unused space at the end of the file, but you can also have the database compact itself first to make more space available.  This is a slow and expensive operation, though; around an hour per 10 GB in my recent experience.

That said, I suspect your database is using more space than it ought to due to fragmentation.  If you run a query like this:

SELECT TOP 10
    '[' + S.name + '].[' + O.name + ']' AS TableName,
    CASE WHEN I.type_desc = 'HEAP' THEN '(heap)' ELSE '[' + I.name + ']' END AS IndexName,
    Stats.avg_fragmentation_in_percent, Stats.fragment_count
FROM
    sys.indexes AS I
    INNER JOIN sys.objects AS O ON I.object_id = O.object_id
    INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
    CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), O.object_id, I.index_id, NULL, NULL) AS Stats
WHERE
    S.name NOT IN ('sys')
ORDER BY
    Stats.avg_fragmentation_in_percent * Stats.fragment_count DESC

...you'll see the tables and indices which are both large and badly fragmented.  Running ALTER INDEX MyIndx ON MyTBl REBUILD on a few of those should recover some space.  As Aaron Bertrand said, you probably don't want to actually free this space with SHRINK; your database will need it sooner or later.

Heap tables are prone to wasted space; using ALTER TABLE MyTbl REBUILD on your bigger tables may help too.


I don't think the scan is caused by a search for an empty string (and while you could add a filtered index for that case, it will only help very specific variations of the query). You are more likely a victim of parameter sniffing and a single plan not optimized for all of the various combinations of parameters (and parameter values) that you will be providing to this query.

I call this the "kitchen sink" procedure, because you are expecting one query to provide all the things, including the kitchen sink.

I have videos about my solution to this here and here as well as a blog post about it, but essentially, the best experience I have for such queries is to:

Build the statement dynamically - this will allow you to leave out clauses mentioning columns for which no parameters were supplied, and ensures that you will have a plan that is optimized precisely for the actual parameters that were passed with values.
Use OPTION (RECOMPILE) - this prevents specific parameter values from forcing the wrong type of plan, especially helpful when you have data skew, bad statistics, or when the first execution of a statement uses an atypical value that will lead to a different plan than later and more frequent executions.
Use the server option optimize for ad hoc workloads - this prevents query variations that are only used once from polluting your plan cache.

Enable optimize for ad hoc workloads:

EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Change your procedure:

ALTER PROCEDURE dbo.Whatever
  @Status INT = NULL,
  @IsUserGotAnActiveDirectoryUser BIT = NULL
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX) = N'SELECT [IdNumber], [Code], [Status],
     [Sex], [FirstName], [LastName], [Profession],
     [BirthDate], [HireDate], [ActiveDirectoryUser]
   FROM dbo.Employee -- please, ALWAYS schema prefix
   WHERE 1 = 1';

   IF @Status IS NOT NULL
     SET @sql += N' AND ([Status]=@Status)'

   IF @IsUserGotAnActiveDirectoryUser = 1
     SET @sql += N' AND ActiveDirectoryUser <> ''''';
   IF @IsUserGotAnActiveDirectoryUser = 0
     SET @sql += N' AND ActiveDirectoryUser = ''''';

   SET @sql += N' OPTION (RECOMPILE);';

   EXEC sys.sp_executesql @sql, N'@Status INT, @Status;
END
GO

Once you have a workload based on that set of queries that you can monitor, you can analyze the executions and see which ones would most benefit from additional or different indexes - you can do this from a variety of angles, from simple "which combination of parameters are provided most often?" to "which individual queries have the longest runtimes?" We can't answer those questions based just on your code, we can only suggest that any index will only be helpful for a subset of all of the possible parameter combinations you're attempting to support. For example, if @Status is NULL, then no seek against that non-clustered index is possible. So for those cases where users don't care about status, you're going to get a scan, unless you have an index that caters to the other clauses (but such an index won't be useful either, given your current query logic - either empty string or not empty string is not exactly selective).

In this case, depending on the set of possible Status values and how distributed those values are, the OPTION (RECOMPILE) might not be necessary. But if you do have some values that will yield 100 rows and some values that will yield hundreds of thousands, you might want it there (even at the CPU cost, which should be marginal given the complexity of this query), so that you can get seeks in as many cases as possible. If the range of values is finite enough, you could even do something tricky with the dynamic SQL, where you say "I have this very selective value for @Status, so when that specific value is passed, make this slight alteration to the query text so that this is considered a different query and optimized for that param value."


Convert spatial points data to line data for use in Line Layer in SQL Server Reporting Services (SSRS)

Below is a simplistic, single-query way to do it as a list of small 2 point line segments in one query, without using cursors.  There's a lot more that can be done if you build and store your lines in another table with a cursor and the .STUnion method and your point spatialdata field.

Assumptions and changes:  1.  includes city and state.  2. to be more realistic I stored lat and long as decimal(10,7), so, unfortunately this example converts lat and long back to varchar to build the line of Well Known Text (WKT).  3. I renamed order to ptorder since order is a reserved word.  4. Only one path or line exists in the source data table and cities not on the path have a NULL ptorder.  5. ptorder is a sequence without gaps, or else the query gets far more complicated.  6. I'm avoiding cursors.  7. I'm assuming your data looks like this:

5,    42.37, -71.03,  'Boston', 'MA'
3,    39.75, -104.87, 'Denver', 'CO'
2,    33.93, -118.40, 'Los Angeles', 'CA'
4,    25.82, -80.28,  'Miami Intl', 'FL'
NULL, 40.77, -73.98,  'New York', 'NY'
1,    33.43, -112.02, 'Phoenix', 'AZ'

Load some test data like this:

CREATE TABLE pointdatatable (
    ptorder             INT,
    lat                 DECIMAL(10,7),
    long                DECIMAL(10,7),
    pointspatialdata    GEOGRAPHY,
    city                VARCHAR(20),
    state               CHAR(2) )

INSERT INTO pointdatatable (ptorder, lat, long, pointspatialdata, city, state) VALUES
    (5, 42.37, -71.03,  CAST('POINT(-71.03 42.37)' AS GEOGRAPHY),  'Boston', 'MA'),
    (3, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'),
    (2, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'),
    (4, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'),
    (NULL, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY'),
    (1, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ')

Then this query produces the segments using WKT:

SELECT
    a.ptorder,
    a.lat,
    a.long,
    a.city,
    b.ptorder,
    b.lat,
    b.long,
    b.city AS nextcity,
    CAST('LINESTRING(' + CAST(a.long AS VARCHAR) +' '+ CAST(a.lat AS VARCHAR) +', '+
        CAST(b.long AS VARCHAR) +' '+ CAST(b.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
FROM
    pointdatatable a
LEFT OUTER JOIN
    pointdatatable b
ON
    b.ptorder = a.ptorder + 1
WHERE
    b.ptorder IS NOT NULL
ORDER BY a.ptorder


how to find the error description for the error codes found in the extended events file XEL generated by SSAS Tabular query end event

I think you should look at the [TextData] field.
Based on Microsoft doc here:
https://docs.microsoft.com/en-us/analysis-services/trace-events/errors-and-warnings-events-data-columns?view=asallproducts-allversions
it should contain the the text data associated with the error event, like the error message.
please, try this against your trace file.
    SELECT   xe_file.[FILE_NAME] AS [filepath]
        ,[ApplicationName] = xe_file.xml_data.value('(/event/data[@name=''ApplicationName'']/value)[1]','[nvarchar](128)')
        ,[BinaryData] = xe_file.xml_data.value('(/event/data[@name=''BinaryData'']/value)[1]','[varbinary](max)')
        ,[ConnectionID] = CAST(xe_file.xml_data.value('(/event/data[@name=''ConnectionID'']/value)[1]','[nvarchar](128)') AS INT)
        ,[CPUTime] = CAST(xe_file.xml_data.value('(/event/data[@name=''CPUTime'']/value)[1]','[nvarchar](128)') AS bigint)
        ,[CurrentTime] = CAST(xe_file.xml_data.value('(/event/data[@name=''CurrentTime'']/value)[1]','[nvarchar](128)') AS DATETIME2)
        ,[DatabaseName] = xe_file.xml_data.value('(/event/data[@name=''DatabaseName'']/value)[1]','[nvarchar](128)')
        ,[Duration] = xe_file.xml_data.value('(/event/data[@name=''Duration'']/value)[1]','[nvarchar](128)')
        ,[EndTime] = xe_file.xml_data.value('(/event/data[@name=''EndTime'']/value)[1]','[nvarchar](128)')
        ,[Error] = CAST(xe_file.xml_data.value('(/event/data[@name=''Error'']/value)[1]','[nvarchar](128)') AS INT)
        ,[EventClassId] = CAST(xe_file.xml_data.value('(/event/data[@name=''EventClass'']/value)[1]','[nvarchar](128)') AS INT)
        ,[EventSubclassId] = CAST(xe_file.xml_data.value('(/event/data[@name=''EventSubclass'']/value)[1]','[nvarchar](128)') AS INT)
        ,[IntegerData] = CAST(xe_file.xml_data.value('(/event/data[@name=''IntegerData'']/value)[1]','[nvarchar](128)') AS INT)
        ,[JobID] = CAST(xe_file.xml_data.value('(/event/data[@name=''JobID'']/value)[1]','[nvarchar](128)') AS INT)
        ,[NTCanonicalUserName] = xe_file.xml_data.value('(/event/data[@name=''NTCanonicalUserName'']/value)[1]','[nvarchar](128)')
        ,[ObjectID] = xe_file.xml_data.value('(/event/data[@name=''ObjectID'']/value)[1]','[nvarchar](128)')
        ,[ObjectName] = xe_file.xml_data.value('(/event/data[@name=''ObjectName'']/value)[1]','[nvarchar](128)')
        ,[ObjectPath] = xe_file.xml_data.value('(/event/data[@name=''ObjectPath'']/value)[1]','[nvarchar](128)')
        ,[ObjectReference] = xe_file.xml_data.value('(/event/data[@name=''ObjectReference'']/value)[1]','[nvarchar](128)')
        ,[ObjectType] = CAST(xe_file.xml_data.value('(/event/data[@name=''ObjectType'']/value)[1]','[nvarchar](128)') AS INT)
        ,[ProgressTotal] =  CAST(xe_file.xml_data.value('(/event/data[@name=''ProgressTotal'']/value)[1]','[nvarchar](128)') AS INT)
        ,[ServerName] = xe_file.xml_data.value('(/event/data[@name=''ServerName'']/value)[1]','[nvarchar](128)')
        ,[SessionID] = xe_file.xml_data.value('(/event/data[@name=''SessionID'']/value)[1]','[nvarchar](50)')
        ,[SessionType] = xe_file.xml_data.value('(/event/data[@name=''SessionType'']/value)[1]','[nvarchar](128)')
        ,[Severity] =  CAST(xe_file.xml_data.value('(/event/data[@name=''Severity'']/value)[1]','[nvarchar](128)') AS INT)
        ,[SPID] = CAST(xe_file.xml_data.value('(/event/data[@name=''SPID'']/value)[1]','[nvarchar](128)') AS INT)
        ,[StartTime] = CAST(xe_file.xml_data.value('(/event/data[@name=''StartTime'']/value)[1]','[nvarchar](128)') AS DATE)
        ,[Success] = CAST(xe_file.xml_data.value('(/event/data[@name=''Success'']/value)[1]','[nvarchar](128)') AS INT)
        ,[TextData] = xe_file.xml_data.value('(/event/data[@name=''TextData'']/value)[1]','[nvarchar](max)')
FROM    (
            SELECT   [FILE_NAME]
                    ,[XML_DATA] = CONVERT(XML, EVENT_DATA)
            FROM    sys.fn_xe_file_target_read_file(@trace_filepath, NULL, NULL, NULL)
        ) AS xe_file

;


Your schema diagram is good. You should not put any total cost column either to Recipe or RecipeIngedient as it is not compliant to normalization. And so there is no need for triggers.
The total cost sum should be calculated on the fly in a query that you use to list recipes.


Variables in DB data to avoid redundancy?

There is no built-in way of doing this in SQL. If the default is always the project_name, I would probably allow nulls in the other columns, and create a view that uses coalesce() to return a default value:

create view real_data
as
select project_name,
       coalesce(linux_user, project_name) as linux_user,
       coalesce(db_user, project_name) as db_user,
       coalesce(db_name, project_name) as db_db_name
from base_data;

Another option would be a complicated case statement for each column:

create view real_data
as
select project_name,
       case linux_user
           when '{project_name}' then project_name
           when '{db_name}' then db_name
           when '{db_user}' then db_user
           else linux_user
       end as linux_user,
       ... repeat the above for the other columns ..
from base_data;

You could hide the case statement in a function or use a set returning function and dynamic SQL, so you don't need to hardcode each column name and possible variable.

If you only need to make sure you have a default value when creating the rows, then just replacing that in a trigger during insert might also be an option. That way there is no need for the view.


The subquery you have in your code is called a derived table. It's not a base table but a table that "lives" during the time that the query runs. Like views (which are also called viewed tables) - and in recent versions CTEs which is another, 4th way to "define" a table inside a query -  they are similar to a table in many ways. You can select from them, you can use them in from or to join them to other tables (base or not!).

In some DBMS, (not all DBMS have implemented this the same way) these tables/views are updatable. And "updatable" means that we can also update, insert into or delete from them.

There are restrictions though and this is expected. Imagine if the subquery was a join of 2 (or 17 tables). What would delete mean then? (from which tables should rows be deleted?) Updatable views is a very complicated matter. There's a recent (2012) book, entirely on this subject, written by Chris Date, well known expert in relational theory: View Updating and Relational Theory.

When the derived table (or view) is a very simple query, like it has only one base table (possibly restricted by a WHERE) and no GROUP BY, then every row of the derived table corresponds to one row in the underlying base table, so it is easy* to update, insert or delete from this.

When the code inside the subquery is more complex, it depends on whether the rows of the derived table/view can be traced/resolved to rows from one of the underlying base tables.

For SQL Server, you can read more in the Updatable Views paragraph in MSDN: CREATE VIEW.

  Updatable Views

  You can modify the data of an underlying base table through a view, as
  long as the following conditions are true:

  Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in
  any other way, such as through the following:
  An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
  A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using
  the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT
  amount to a computation and are also not updatable.
  The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
  TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

  The previous restrictions apply to any subqueries in the FROM clause
  of the view, just as they apply to the view itself. Generally, the
  Database Engine must be able to unambiguously trace modifications from
  the view definition to one base table.

 Actually delete is easier, less complex than update. SQL Server only needs the primary keys or some other way to identify which rows of the base table are to be deleted. For update, there is an additional (rather obvious) restriction that we can't update a computed column. You can try to modify your query to do an update. Updating the CreatedDateTime will probably work just fine but trying to update the computed RowNumber column will raise an error. And insert is even more complex, as we'd have to provide values for all the columns of the base table that don't have a DEFAULT constraint.


Because Microsoft have implemented UNIQUE constraints to behave like this, apparently.  This is documented here (quote)

  ...UNIQUE constraints allow for the value NULL. However, as with any
  value participating in a UNIQUE constraint, only one null value is
  allowed per column ...

Apparently, this is not the behaviour as defined in the ANSI SQL standard documents (as Martin Smith's link suggests).

Other database servers behave differently in this respect.  E.g. PostgreSQL allows you to do this:

create table t ( id int unique ) ;

insert into t (id) values (1),(2),(3),(null),(null),(null) ;

-- 6 rows affected

See dbfiddle.

When working with Oracle, you can do this (or something similar), too -

create table t ( id int unique ) ;

begin
  insert into t (id) values (1);
  insert into t (id) values (2);
  insert into t (id) values (3);
  insert into t (id) values (null);
  insert into t (id) values (null);
  insert into t (id) values (null) ;
end;
/

select * from t;

ID
----
1
2
3
null
null
null

Dbfiddle here.


You would need to use Dynamic SQL to pass a dynamic file path to the Bulk Insert.

DECLARE @FileName NVARCHAR(4000);

SET @FileName = '/path/to.csv';

DECLARE @sql NVARCHAR(4000) = 'BULK INSERT #CSV
        FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )';

EXEC(@sql);


Does unique email in user table violate normal forms?

To extend the hole idea why index doesn't matter
A indexed filed onlys describes that every row is unique, but this doewsn'z means that it is dependend from other columns than the promary key

A relation is in third normal form if there is no transitive dependency for non-prime attributes

following this definition, it still stands an indexed email field is  dependent on the primary key and does not depend on other columns it is non transitively dependent
Which means that it correspnds teh 3NF


Join 3 tables, aggregate over date field and subtract sum of two columns

This is the final query that worked, with help from Akina's comments.

SELECT
    DATE_FORMAT(s.`shipment_date`, "%m/%Y") AS `month`,
    FORMAT(SUM(u.`amount`),2) AS `net_revenue`
FROM
    (SELECT
        `amount`,
        `shipment_id`
    FROM
        `invoices`
    UNION ALL
    SELECT
        (-1 * `amount`) AS `amount`,
        `shipment_id`
    FROM
        `bills`) u
    JOIN `shipments` s
        ON u.`shipment_id` = s.`shipment_id`
GROUP BY `month`
ORDER BY `month` ASC;


Select value in group based on every/any conditions

If I understand the task correctly, then
SELECT subquery.id,
       subquery.entity_type,
       subquery.entity_id,
       CASE WHEN SUM(subquery.status != 'READY') = 0 THEN 'READY'
            WHEN SUM(subquery.status = 'FAILED') > 0 THEN 'FAILED'
            WHEN SUM(subquery.status = 'IN PROGRESS') > 0 THEN 'IN PROGRESS'
            ELSE 'ADDED'
            END status
FROM (SELECT et.id, et.entity_type, et.entity_id, stl.status
      FROM entity_translations et
      JOIN entity_translation_fields etf ON etf.entity_translation_id = et.id
      JOIN string_translations st ON etf.string_translation_id = st.id
      JOIN string_translation_log stl ON stl.string_translation_id = st.id
      JOIN (SELECT string_translation_id, MAX(changed) changed
            FROM string_translation_log
            GROUP BY string_translation_id) stl1 ON stl1.string_translation_id = stl.string_translation_id
                                                AND stl1.changed = stl.changed
      ORDER BY et.id, etf.string_translation_id, stl.changed ) subquery
GROUP BY subquery.id,
         subquery.entity_type,
         subquery.entity_id;

fiddle


SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

This is probably due to the index limitations. From the MySQL docs: CREATE INDEX Syntax

  Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.

You probably have a VARCHAR column with length more than 767 or a TEXT column.


You can use Event Notifications to get notified whenever a deadlock happens on the server:

See:

Immediate deadlock notifications without changing existing code by Mladen Prajdić
Using SQL Profiler to Resolve Deadlocks in SQL Server by Jonathan Kehayias

Additional links:

The Anatomy of a Deadlock - Jonathan Kehayias
Anatomy of a Deadlock - Part Deux - Jonathan Kehayias
Read/Write deadlock - by Remus Rusanu


How do I check for a null or empty table-valued parameter?

A table can't be NULL, nor can a TVP. How do you check if a table is empty? You certainly don't say IF Sales.SalesOrderHeader IS NULL. :-)

IF EXISTS (SELECT 1 FROM @tvp)
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...


Always On AG is Down when service is stopped

It could be the issue described in INF: AlwaysOn – The secondary database doesn’t come automatically when the primary instance of SQL Server goes down by Arvindh Kalidasan - Support Engineer, Microsoft GTSC.

In this blog we would discuss about behavior of AlwaysOn availability group where the secondary database doesn't come automatically when the primary instance goes down. The secondary database goes into Resolving state. On the failover cluster manager the resource appears in fail state.
[...] we found that if we stop SQL Service manually on primary replica, it fails over to second node only once. Any further attempts of stopping SQL Service (to test auto failover) would not cause failover.

The workaround posted there is:

[...] to have the value set to a higher number for "Maximum Failures in the specified period".

Maximum Failures in the specified period: set to 60
Period (Hours): set to 1


How do I extract text from string between underscores with mysql?

If you have the following:

table is called mytable
column channel has RbkSEM_google_ecom--Branded--Core-_-Realdock
you want to pull out Branded from between the -- tokens
place result in derived_channel

Here is your query

UPDATE mytable SET derived_channel =
LEFT(SUBSTR(channel,LOCATE('--',channel)+LENGTH('--')),LOCATE('--',SUBSTR(channel,LOCATE('--',channel)+LENGTH('--'))) - 1);

Here is an example using user-defined variables

SET @st = 'RbkSEM_google_ecom--Branded--Core-_-Realdock';
SET @dm = '--';
SET @rv = LEFT(SUBSTR(@st,LOCATE(@dm,@st)+LENGTH(@dm)),LOCATE(@dm,SUBSTR(@st,LOCATE(@dm,@st)+LENGTH(@dm))) - 1);
SELECT @rv;

Here is the example executed

mysql> SET @st = 'RbkSEM_google_ecom--Branded--Core-_-Realdock';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dm = '--';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @rv = LEFT(SUBSTR(@st,LOCATE(@dm,@st)+LENGTH(@dm)),LOCATE(@dm,SUBSTR(@st,LOCATE(@dm,@st)+LENGTH(@dm))) - 1);
Query OK, 0 rows affected (0.00 sec)

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


First of all, your PRIMARY KEY depends on the logical nature of your data and will be indexed automatically by the DBMS. If the PK happens to be composite, it will automatically generate a composite index underneath it. The same goes for UNIQUE constraints and FOREIGN KEYs.

On top of that, under InnoDB, PK also acts as a clustered index.

As for the non-key fields, index based on the expected queries. Assuming your table T has several fields: A, B, C, D (etc.), for a query such as...

SELECT * FROM T WHERE A = '...' AND B = '...'

...create a composite index on {A, B} (or {B, A}), but not on C, D etc.

For the...

SELECT * FROM YOUR_TABLE WHERE A = '...' OR B = '...'

...you'll need a separate indexes on {A} and on {B}.

For both...

SELECT * FROM T WHERE A = '...' AND B = '...'
SELECT * FROM T WHERE A = '...' OR B = '...'

...you'll need indexes on {A, B} and {B} (or {B, A} and {A}).

For both...

SELECT * FROM T WHERE A = '...' AND B = '...'
SELECT * FROM T WHERE A = '...'

...you'll only need an index on {A, B}.

For...

SELECT * FROM T WHERE A = '...' ORDER BY B

...you'll need an index on {A, B}.

Etc, etc...

Do not create indexes that you don't need, since they incur a penalty in storage space and in INSERT/UPDATE/DELETE performance. For more on indexing (and other database performance considerations), I warmly recommend reading: Use The Index, Luke!


Best option for a database used by a translation app

You don't typically handle translation inside of the database. This is for no specific reason. It certainly could be done, and has been done but that's not the way things are done now.

There are two huge sources of internationalization data,

Unicode Common Locale Data Repository (CLDR)
International Components for Unicode (ICU)

Unicode CLDR is absolutely gigantic. Normally you use something like Globalize.js which wraps these and pulls down data for the features that it supports.

In order to generate most simple strings, a globalization library needs this much crap... (English only shown for simplicity)

> var msg =
  '{GENDER, select, male{He} female{She} other{They} }' +
  ' found ' +
  '{RES, plural, =0{no results} one{1 result} other{# results} }' +
  ' in the ' +
  '{CAT, selectordinal, one{#st} two{#nd} few{#rd} other{#th} }' +
  ' category.';

You'll get these results:

> var mfunc = new MessageFormat('en').compile(msg);

> mfunc({ GENDER: 'male', RES: 1, CAT: 2 })
'He found 1 result in the 2nd category.'

> mfunc({ GENDER: 'female', RES: 1, CAT: 2 })
'She found 1 result in the 2nd category.'

> mfunc({ GENDER: 'male', RES: 2, CAT: 1 })
'He found 2 results in the 1st category.'

> mfunc({ RES: 2, CAT: 2 })
'They found 2 results in the 2nd category.'


Remote MySQL host - is connecting to remote port 3306 vulnerable?

Establish SSL connections.  Don't worry about the speed -- your 500K-row refresh will take minute(s), with or without SSL.


Manipulating duplicate records in access

If you're just trying to query the table to get the average;

 Select AVG(field2) as Field3 from Table where EXISTS (Select Count(field1) from
 table Having Count(field1) >1)

If you want to view all of the other columns in your query results you'll need to use a self join to calculate the average, or a group by clause. You didn't give an example of what you wanted in your results so I can't help much further.


Is there a reason you don't want to failover?

That would be the most common way. To do a rolling update and away you go.

If you have a witness, disable it first.

When you patch a mirror in this way the mirror will continue to function, to a fashion, and you'll have no application downtime.

When a mirror partner is patched it will leave the mirroring session, but then rejoin it when it is complete - then re-sync.

You can do it the way you described but you're going to have downtime when you patch the prod server and I'm guessing the patch when applied to Prod will trigger failover if that's how your mirror is configured.


How can I write results to SAME results set?

If you are going to keep all of these queries and run them, there are some options.

1

adding union all between all executions, you could use find and replace to add this to each select statement.

Example:

select name from sys.databases where name = 'master'
UNION ALL
select name from sys.databases where name = 'tempdb'

But when using order by you would have to change more parts of your query, an example:

 SET NOCOUNT ON;
 SELECT NAME FROM
 (SELECT TOP(1) name from sys.databases where name = 'master' order by name ) as [sysdatabases]
  UNION ALL
SELECT NAME FROM
 (SELECT TOP(1) name from sys.columns  order by name )  as [syscolumns];

You cannot do this

SET NOCOUNT ON;
SELECT TOP(1) name from sys.databases where name = 'master'
UNION ALL
SELECT TOP(1) name from sys.columns
ORDER BY NAME

Since it would sort on the entire result set.

Find and replace all in ssms

2

You could run result to file or text

Be sure to go to Tools - Options - Query Results - SQL Server - Results to Text
and disable include column headers int he result set.

This is done so you don't get the same column header on each of these queries. You will have to add the headers afterwards.

Open a new query window to implement the changes, and select result to file.

Run the query with NOCOUNT

 SET NOCOUNT ON;

 SELECT name from sys.databases where name = 'master'

 SELECT name from sys.databases where name = 'tempdb'

Change the file type to csv if you'd like:

The result:

3

Insert all the values in a temp table & read from the temp table

 SET NOCOUNT ON;
 CREATE TABLE #temp(val varchar(255));

 INSERT INTO #temp(val)
 SELECT name from sys.databases where name = 'master';

 INSERT INTO #temp(val)
 SELECT name from sys.databases where name = 'tempdb';

 SELECT * FROM #temp;
 DROP TABLE #temp;

These are not the only options you have, and does not account for all the time it will take to execute all of these one by one.

4

Using Powershell

1) Save your query as a .sql file to your desired location

2) Run the next two statements with powershell while changing the parameters

import-module "sqlps" -DisableNameChecking

invoke-sqlcmd -Serverinstance instancename -inputFile "C:\temp\bla.sql" | out-File -filepath "C:\temp\TestOutput.txt"

3) validate the output

Output For this query

 SET NOCOUNT ON;
 SELECT name from sys.databases where name = 'master'
 SELECT TOP(1) name from sys.columns


What are these SFP files that SQL Server distributes?

So it turns out these are packed archive. It's part of the "Platform Abstraction Layer" that hosts the operating system in a library, or "Drawbridge" Library OS. Not much is known about Drawbridge yet, but here is one resource on Github that pointed me over to a tool sfpack.

sfpack was able to extract these archives.


Few uses for the tablespaces:

When you no longer need the data you can drop the tablespace and delete datafiles. This is basically the only way to release space back from Oracle to the operating system.
When doing backups you can exclude tablespace from backup. E.g. if it contains non essential or easily recreatable data.
When doing restore you can skip tablespace. E.g. if you need only specific table or you need to start as soon as possible you can skip index tablespaces and rebuild indexes online.
Tablespace per user can be useful. Then when user is no longer needed you drop the user then drop tablespace and have disk space back. Also if user suddenly starts to generate lots of data only his tasks will break when tablespace becomes full. Other users will be unaffected.
Data blocks corruption usually is contained within tablespace. In such cases only one tablespace is affected.
You can store tablespaces (datafiles) on different types of disks. So you can manually manage which data goes on SSD and which on SATA.
In really rare cases you may need different block size for the tablespace.
You can choose to compress the whole tablespace content.
You can move selected data to a tablespace, unplug it from the database and attach to another database. This is transportable tablespace.


Split rows and assign a specific value to each group

WITH cte AS ( SELECT Customer_id, NTILE(4) OVER ()::TEXT "Value" FROM customers )
UPDATE customers
SET "Value" = 'dos-' ||  cte."Value" || '-' ||  cte."Value"
FROM cte
WHERE customers.Customer_id = cte.Customer_id;

fiddle

PS. It's recommended to use some ORDER BY in OVER clause (for example by Customer_id) - without it the result is not deterministic...


Current version of TimescaleDB, which is 2.0.0, doesn't support push down of aggregates to data nodes (see limitations) and all joins are performed on the access node. So for the queries, which join a distributed hypertable with non-timeseries data (which, I guess, are stored in normal tables), the data will be brought to the access node for the join. Thus you might want to store non-timeseries data on the access node. You can also manually distribute non-timeseries data, however there will be no performance benefits.
Note that if a hypertable refers to non-timeseries data through a foreign key, it is necessary to have the non-timeseries table on the access node and all data nodes and the timeseries data be partitioned across data nodes in the same way as the hypertable.


Do I need to delete archivelog mode or will it automatically get cleared?

Configure archivelog deletetion policy as-

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

Then you can-

RMAN> DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;

  Archived redo logs can be deleted automatically by the database or as a result of user-initiated RMAN commands. Only logs in the flash recovery area can be deleted automatically by the database. For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs when additional disk space is required.

Configuring an Archived Redo Log Deletion Policy

  If I move these archivelog files to other location or disk as backup, should it help me to recover data till that date?

Yes, As long as RMAN can access these files.

  If I need to restore a test database using the production database, is it possible with these archive files? Does it need any other processing?

Archived Redo Logs are not sufficient to restore and recover a test database, you need to have full RMAN backup as well. Howerver, if you have physically identical database like Physical Standby database then these archived logs can be used to refresh such databases.


Storage space requirements for a varchar(40) column to store 'abcd' with charset 'ucs2' and 'utf8mb4'?

abcd is all English, so:  9 bytes for ucs2; 5 bytes for utf8.  But...
UCS2 uses 2 byte per character (at least for 'western' letters).  I think it takes 4 bytes for most Asian characters.
utf8 (or utf8mb4) is a variable-length encoding.  English letters take 1 byte each; most European text takes 1 or 2 bytes per character.  Asian takes 3, sometimes 4 bytes per character.
latin1 has only 1-byte characters, so it is limited to English, plus some accented European letters.
But, but...
VARCHAR(40) stores up to 40 characters (not bytes) in whatever encoding is being used.  The disk space be 1 byte for length plus up to 4*40 bytes for text.  A long example would be 40 Emoji, taking 161 bytes.
<opinion>
There is virtually no reason to use the CHARACTER SET ucs2 (or ucs4).  In particular, anything involving unicode or utf8 should use utf8mb4.
</opinion>
But But But...
Re your comment about 10 bytes -- InnoDB will sometimes use 1 byte for the length, sometimes 2.  But the decision is based on all the columns in the table.  Your "40" does not force a 2-byte length, but some other column might.
The documentation says "For example, a VARCHAR(255) column ... (up to 510 bytes)".  So it needs 2 bytes since the max byte length is >255.  Your example is with "VARCHAR(40)", which is "up to 80 bytes" (<=255).


MariaDB keeps crashing after a few hours with ridiculous memory expectations

You must always use

mysql_free_result( res );
after a query in C after your query has been processed.

https://dev.mysql.com/doc/refman/8.0/en/mysql-free-result.html

This solved my OOM and memory leak from my C daemon, which was the cause of the problem.


There are two advantages to partitioning a table in the same filegroup:

Allowing portions of a large index to be rebuilt incrementally, allowing for more efficient maintenance.  Review the ALTER INDEX [foo] REBUILD PARTITION=n for more details.
Leveraging partition elimination and (possibly) partition level locking to improve query maintenance.  I discuss this on my blog.

There are several things to keep in mind if you're partitioning.

If your table has a clustered index (and it really should), your partitioning key must be part of the clustered index.
To avoid performance issues, you should align your partitions.  This means all your indexes should include your partition key, whether as an include or as part of the index itself.
Index rebuilds for partitions are offline in current versions of SQL Server (2005-2012).  If your partitions are to large and your rebuilding by partition, this could lead to blocking issues.

I recommend doing some thorough research on partitioning before implementing it.  Kendra Little has an excellent list of resources where you can get started.


Considering that MySQL is still a relation database management system an (RDBMS if you will), you should considering the following things:

Your tables are defined by the data they contain and the relationships to other tables
Your data should be as non-redundant as possible

This last point leads to the process of normalization, which is a very important thing to do in a RDBMS.

Have a look at the Wikipedia article on Normalization to have a basic understanding of what that process entails: http://en.wikipedia.org/wiki/Database_normalization

I find that, oftentimes, it can be a good idea to take a step back from the "storage" aspect of your database. Try to look at your application, the data models used within and their relationships. Creating such an abstraction is called an ERM: Entity-Relationship Model, or an ERD: an Entity-Relationship Diagram.
More info on that: http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model

For your case specificially:

Apart from the fact that your not using a design that fits a relational database, please consider the fact that you are creating a very inflexible design!
What if -- for example -- you were to create a new product in the future? Would you add a new column to the table? Surely you'll agree that that's pretty stupid.

Instead, it makes MUCH more sense to create something along the lines of

+-----------+-------------+----------------+
| client_id | client_name | client_address |
+-----------+-------------+----------------+
| 1         | Snake Logan | ...            |
| 2         | Mad Max     | ...            |
| ...       | ...         | ...            |
+-----------+---------- --+----------------+

+------------+---------------+---------------+
| product_id | product_desc  | product_price |
+------------+---------------+---------------+
| 1          | Silver Bullet | 14.00         |
| 2          | Hand grenade  | 7.00          |
| ...        | ...           | ...           |
+------------+---------------+---------------+

+-------------+-----------+------------+-----------------+
| purchase_id | client_id | product_id | purchase_amount |
+-------------+-----------+------------+-----------------+
| 1           | 1         | 1          | 3               |
| 2           | 1         | 2          | 1               |
| 3           | 2         | 2          | 5               |
+-------------+-----------+------------+-----------------+

I'm not saying this is the greatest design ever, but this is more elegant.

If you want to have more info concerning the purchase, I'd recommand building a view that contains the necessary info by joing the tables together. Please don't create a field like purchase_total, I think that would once again be redundant data. The data is already there (purchase_amount and product_id), you should need to do the math (purchase_amount * product_price). You can put that in the view if you want. Tables are for storing data, views are there to present said data.


Pgpool install - libpq is not installed or libpq is old

Instead of compiling PgPool, why not just install it from package management?

yum install postgresql-pgpool-II

I don't use CentOS, but that's it's package name in Fedora 17, and the two are usually consistent.

Since you're using a PostgreSQL 9.2 installed via Yum on CentOS 5, you're obviously using a 3rd party packaging of PostgreSQL, so you're likely to be using the yum.postgresql.org repository. This repository claims to contain PgPool-II, so try:

yum search pgpool

and see if you find any packages. Get details on them with:

yum info packagename

Otherwise, for compiling PgPool you need the development package for PostgreSQL installed:

yum install postgresql-devel

If that alone doesn't do the trick, try making sure pg_config is on the PATH:

export PATH=$PATH:/usr/pgsql-9.2/bin
./configure

You shouldn't need to specify a libdir or includedir explicitly, as the're in /usr/include and /usr/lib (or /usr/lib64) on CentOS and those are default search locations for configure and gcc. It's most likely that you just don't have the development headers installed.


How can I track if a trace is executing?

I think the log file you are referencing in your question is created by the SQL Server default trace.  You should be able to verify that by running:

SELECT path AS [Default Trace File]
    ,max_size AS [Max File Size of Trace File]
    ,max_files AS [Max No of Trace Files]
    ,start_time AS [Start Time]
    ,last_event_time AS [Last Event Time]
FROM sys.traces
WHERE is_default = 1
GO

There is additional useful information about Different Ways to Find Default Trace Location in SQL Server.


What does the GO statement do in SQL Server?

GO is not a part of the TSQL language.  It's a batch separator used by SQLCMD and SSMS.

GO is not a Transact-SQL statement; it is a command recognized by the
sqlcmd and osql utilities and SQL Server Management Studio Code
editor.
SQL Server utilities interpret GO as a signal that they should send
the current batch of Transact-SQL statements to an instance of SQL
Server. The current batch of statements is composed of all statements
entered since the last GO, or since the start of the ad hoc session or
script if this is the first GO.
A Transact-SQL statement cannot occupy the same line as a GO command.
However, the line can contain comments.

SQL Server Utilities Statements - GO
It originated with the command line interfaces, and persists in sqlcmd to this day.  You can write a batch over multiple lines in the command line interface, and the batch isn't sent to the server until you type go.
1> select name, create_date
2> from sys.objects
3> where name = 'foo'
4> go
name                                                                                create_date
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
foo                                                                                2020-07-20 09:56:42.393

(1 rows affected)
1>

You can configure what to use for a batch separator in SSMS. GO is the default, but it can be just about anything else.


Create junction table where one referenced table has a composite key as PK

You have also to define two columns for a double Primary KEY

CREATE TABLE `category_product` (
  `category_title` varchar(20),
    `product_title` varchar(50),
  `product_vendor_name` varchar(50),
  CONSTRAINT `fk_category_product_category_title` FOREIGN KEY (`category_title`) REFERENCES `category` (`title`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_category_product_product_title_vendor_name` FOREIGN KEY (`product_title`,`product_vendor_name`) REFERENCES `product` (`title`,`vendor_name`) ON DELETE CASCADE ON UPDATE CASCADE
);


how to delete all rows with empty field via function/trigger combo in Postgres v 9.3?

There are several things wrong with this trigger.

First: your delete statement. You can't compare NULL using =. You need to use IS NULL:

DELETE FROM test2 WHERE email IS NULL;

Second: a trigger function (quote from the manual) "must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for."

So return test2; should be return new;.

Third: you created a row level trigger, which is going to be very bad for performance. As you are not dependent on the actual values that are changed by the update, a statement level  trigger will be much more efficient, because it only fires once for each UPDATE statement rather than once for each row that has been changed.

In a statement level trigger the return value is ignored, so the suggested return new; from above becomes a return null; as there is no new or old record available in a statement level trigger.

Putting it all together:

CREATE OR REPLACE FUNCTION clean_emp()
  RETURNS trigger AS                                                                           $$
BEGIN
  DELETE FROM test2 WHERE email IS NULL;
  return NULL; -- as we are now using a statement level trigger, null is fine
END;
$$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_empty2
AFTER UPDATE OF name ON test2
  FOR EACH STATEMENT --<< fire only once for each statement, not row
  EXECUTE PROCEDURE clean_emp();

If you also want to disallow empty values ( '' is something different than 'NULL') you would need to change the condition to where coalesce(email, '') = '')

But the trigger approach is wrong to begin with: you should declare the email column as NOT NULL and then nobody will ever be able to put NULL values into that column and therefore you don't need the trigger at all.


How do I create a PRIMARY KEY/UNIQUE constraint that considers trailing space significant?

Since I mentioned that I’m using DATALENGTH in my WHERE clauses, I’m going to try extending that to PRIMARY KEY creation as well. To do this, I’m just going to add a computed column with the DATALENGTH of the first one and then include that column in the PRIMARY KEY:

CREATE TABLE #x (
   Id NVARCHAR(16) COLLATE Latin1_General_BIN2 NOT NULL
  ,IdLength AS DATALENGTH(Id) PERSISTED NOT NULL
  ,PRIMARY KEY(Id, IdLength)
)
INSERT INTO #x VALUES ('asdf');
INSERT INTO #x VALUES ('Asdf');
INSERT INTO #x VALUES ('asdf ');
INSERT INTO #x VALUES ('asdf '); -- Error, proving that UNIQUE works
SELECT '"'+Id+'"' QuotedId, * FROM #x;
DROP TABLE #x;

I get a table like this:

QuotedId           Id               IdLength
------------------ ---------------- -----------
"Asdf"             Asdf             8
"asdf"             asdf             8
"asdf "            asdf             10

The attempt to insert 'asdf ' twice resulted in and error, proving that I am still benefiting from UNIQUE behavior:

Msg 2627, Level 14, State 1, Line 9
Violation of PRIMARY KEY constraint 'PK__#x________A3E5142C3CFA2452'. Cannot insert duplicate key in object 'dbo.#x'. The duplicate key value is (asdf , 10).


No.  If the secondary drops, the primary node will continue operation.  However, you need to be aware of how much disk space the queued transactions will consume.  If you lose the second node for an extended period of time, it may make sense to evict it from the cluster so that the primary node won't queue the transactions.


MySQL parent / child table not showing parent entries

The join should be on the opposite direction and without the WHERE. Think of this as starting with any category (r). All of them either have a parent (so the parent_name and parent_id will appear) or not (so those columns will have NULL):

SELECT e.name AS parent_name,
       e.id AS parent_id,
       r.id AS child_id,
       r.name AS child_name
FROM categories r
     LEFT JOIN categories e
     ON e.id = r.parent
ORDER BY COALESCE(parent_name, child_name),   -- first the parent or itself
         child_name ;                         -- then itself


You could set up transactional replication from your main server to the server on which the views are materialized, and then, use a non materialized or materialized view based on their independent merits

More info:

You have server A and Server B, with A hosting DB A and B Hosting DB B

Now, you can configure transactional replication between A and B in such a way that Server A will have 2 Databases: A and B_Repl , similarly B will have B and A_Repl

The REPL databases are essentially an updated copy of the source databases. You can find many guides on how to configure replication on SQL Server

Once you have done that, you can create views on server A, and when you need to refer to database B, you refer to B_Repl instead. Similarly for server B


Order by bug on SQL Server 2005 vs SQL Server 2012

If you are running on compatibility level of 80 (SQL-Server 2000), then this is the expected behaviour. It was corrected in version 2005.

You can check the Compatibility levels page at MSDN where the differences are listed. In the section "Differences Between Lower Compatibility Levels and Level 90", one of the many items is:

  When binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and
  column prefixes are sometimes ignored. This can cause the result set
  to return in an unexpected order.

  For example, an ORDER BY clause with a single two-part column
  (.) that is used as a reference to a column in a
  SELECT list is accepted, but the table alias is ignored. Consider the
  following query.

SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1

  When executed, the column prefix is ignored in the ORDER BY. The sort
  operation does not occur on the specified source column (x.c1) as
  expected; instead it occurs on the derived c1 column that is defined
  in the query. The execution plan for this query shows that the values
  for the derived column are computed first and then the computed values
  are sorted.

which is exactly what you describe.

The easiest workaround is to not use the same alias for one column in the subquery and for another in the main query.


Open SQL Server Management Studio
expand instance of SQL server
expand Replication Folder
expand Local Publications
Right click publication that you want to check for any expired subscriptions and choose 'Launch Replication Monitor'
expand My publishers, right click on publisher in question and click 'Configure replication alerts'
Choose 'Replication Warning: Subscription expiration (threshold: expiration) and click Configure
select Response and check Notify Operator
Add new operator

This will alert you when you have expired subscriptions. It will show all expired subscriptions in Replication Monitor on the Subscription Watch List Tab. If you set it up correctly, will also give you email alerts.

EDIT:

In order for you to receive emails, database mail must be configured.

Connect to instance
expand the management folder in object explorer
right click on Database Mail and choose configure database mail
add profile name and click Add to configure email address and email server

if you don't have an email server you can use google / yahoo /etc.

Hope this helps!!


XE_FILE_TARGET_TVF WaitType in Azure SQL

You can find the answer here: https://www.sqlskills.com/help/waits/xe_file_target_tvf/

  This wait type is when a thread is waiting for data to be returned when using the sys.fn_xe_file_target_read_file table-value function to read event data from a file target.

this has no affect on SQL server performance and also applied to SQL on premise and Azure SQL.


Data sync from different SQL Server versions databases

You can find a lot of articles about MS-SQL Server 7.0 replication in Technet and MSDN.

Two-Way Replication with SQL Server 7.0

Microsoft SQL Server 7.0 Replication Made Easy

Replication for Microsoft SQL Server Version 7.0


how to delete the file which is open in bcp.exe

The easiest way is to restart the machine however that can't be recommended since it is a server and not PC. I would suggest to find the process in the task manager in the process tab and look for anything starting with or similar to BCP and you can do "End Process" or if that doesn't work then "End Process Tree". Once you killed this process, file would be released and will be ready for any I/O operation.

Please let us know in case this helps.


The simple occurrence of SOS_Scheduler_Yield don't mean very much. This can have some other causes.

Generally you can take a look at this DMV to clarify some things.

SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type = 'SOS_SCHEDULER_YIELD'

Interpretation of dm_os_wait_stats:

High counter values, low time means everything ok. This mainly just means that there are many queries which run in parallel and need to be scheduled to different threads.
High counter values, high time can cause some problems. Beware you many intensive queries which may take long time to run (e.g. Aggregation on big tables).
Low counter values, high time can indicate some intensive queries which take very long to complete. Beware this may be a CPU bottle neck which may be through bigger data loads or poor design (e.g. user defined functions).

Another good resource to look at may be the schedulers itself:

SELECT *
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
ORDER BY scheduler_id

It will provide you the status of each CPU. Basically description of this statement:

parent_node_id represents the NUMA node of the core.
scheduler_id and cpu_id are basically in many cases the same. They represent the CPU number.
status will represent if the CPU is used for processing steps in the SQL Server instance (affinity_mask)
is_idle represents the state of the cpu. 1 = it does nothing/not much and 0 = on load!
*_switches_count represent the value of the switches between some states. High context_switches_count tends to show that the CPU cycles between different operations while idle_switches_count indicates if the CPU goes often idle and back to work or not. Generally low idle_switches_count indicates that your system is under permanent pressure.
current_tasks_count shows how many tasks were assigned to this cpu.
runnable_tasks_count shows how many tasks really run on the corresponding cpu.
load_factor shows the pressure on this single core.

Hopefully this will clarify things for you.


When performing a cross-database transaction, in which transaction log(s) is the information stored?

The transaction log isn't recording the SQL statements being executed, as you might be expecting. Instead, it's recording the changes to the raw data in each database, independently.

It's possible for a stored proc from one database to be working entirely in the transaction log of another database.

... database1..my_stored_procedure AS
BEGIN
INSERT INTO database2..table1 (col1) values (1);
  ^^ changes written to database2's tlog
INSERT INTO database2..table2 (col1) values (2);
  ^^ changes written to database2's tlog
END
^^ when this transaction is committed, COMMIT is recorded in database2's tlog

Or for it to make changes to both.

... database2..my_other_stored_procedure AS
BEGIN
INSERT INTO database1..table1 (col1) values (1);
  ^^ changes written to database1's tlog
INSERT INTO database2..table1 (col1) values (2);
  ^^ changes written to database2's tlog
END
^^ when this transaction is committed, COMMIT is recorded in BOTH database1's and database2's tlog

What's recorded in the transaction log are the actual data changes, not the SQL statements which caused them to happen. The entries to each transaction log file are entirely independent, except insofar as the COMMIT is written to the two log files at the same time, once the transaction is committed.

The same logic applies if you have a larger transaction running a number of stored procedure across multiple databases. Once you COMMIT your transaction, the COMMIT will be recorded in the log of every database that participated in the transaction.

It's perfectly possible to restore a backup of database2, and replay its transaction logs on a server that doesn't have database1.

This behaviour allows some flexibility in how procedures and views are laid out in SQL Server. Many database administrators keep their stored procedures - especially maintenance ones - in a database (e.g. Admin) that's completely separate to application/user databases, and write the results of the maintenance operation to that database. Thankfully, it's possible to restore one of the user databases to a development server without needing to copy across Admin as well.


Proper way to index table columns which can be filtered in multiple ways

Every index adds some value, and comes at a cost.  It's up to you to figure out whether the value outweighs the cost.

In order to get a handle on the value, you have to find out which queries use the index, and how much slower those queries run without the index in place.

In order to get a handle on the cost, you have to find out how much slower updates run on the underlying table, and how much space the index itself takes up.

There are tools to help you evaluate these facts.  Facts are objective.  The value you place on them is not.  How much value you place on the query speed up and how much cost you assign to the update slowdown depends on what you are trying to achieve, and on your relative priorities.  That's up to you.


As at MongoDB 4.0, chunk migration for a sharded collection does not automatically run compact or free disk space when complete. However, storage used by documents that have been migrated is marked as available for reuse for new data insertion/updates. An underlying assumption is that you will continue to add data to shards over time, so disk space does not need to be aggressively freed.

If you do want to free up disk space after significant changes in storage usage (for example, removing or migrating a large number of documents) you can manually compact collections.

It is important to note that the  compact command currently blocks operations for the database being compacted, so is not advisible to run on a production system without careful consideration of impact. One approach would be to compact each of your original shards using rolling maintenance (compacting one secondary at a time). If you have a significant number of collections to compact, you could also re-sync secondaries (one at at time) to rebuild all data files without downtime.


Indexing only for most recent entries in PostgreSQL

The index type you are looking for is Range Type


SSRS minus first two dates as the header

Can you do the calculations in the dataset the report uses? May have an easier time doing that then building the expression needed to calculate the column for SSRS


SQL Server 2005 - Map login to pre-existing user

For a failover-type situation, I use the handy 'sp_help_revlogin' stored procedure as published in http://support.microsoft.com/kb/918992

This procedure, when run on your primary server, will script out each login WITH the SID (this is the important part for making sure your logins are mapped to their respective database users) and hashed password (so the password remains the same on each server.) The resulting script can then be run against your failover server to create the logins. Be sure to review the output before running it.

As-is, it scripts all of the logins in the server, but could be modified to script only the logins you're interested in. It could also be automated to generate a script on a regular basis.


PostgreSQL dblink password encrypted or not?

Basically, dblink uses a libpq-style connection info string.
Add sslmode=require to your connection string or a more restrictive setting to disallow unencrypted transfers. More details in the manual here.

I would also advice not to have the password in your SQL statement at all. SQL statements may get logged. Or backups are stored in an insecure location ...
I use the password file .pgpass for dblink connections.


Is support for Parallel Scalar UDF a reasonable feature request?

It is fairly well documented that UDFs force an overall serial plan.

I'm not certain it is all that well documented.

A scalar T-SQL function prevents parallelism anywhere in the plan.
A scalar CLR function can be executed in parallel, so long as it does not access the database.
A multi-statement table-valued T-SQL function forces a serial zone in a plan that may use parallelism elsewhere.
An inline table-valued T-SQL function is expanded like a view, so has no direct effect.

See Forcing a Parallel Execution Plan and/or Craig Freedman's Parallel Execution presentation.

  There are claims about UDFs being a black box must use cursor.

These claims are not correct.

  Extra points for explaining why the engine forces the whole plan to be serial instead of just the UDF calculation stage.

My understanding is that the current restrictions are a purely the result of certain implementation details. There is no fundamental reason why functions could not be executed using parallelism.

Specifically, T-SQL scalar functions execute inside a separate T-SQL context, which complicates correct operation, coordination and shutdown (especially in the case of an error) significantly.

Equally, table variables do support parallel reads (but not writes) in general, but the table variable exposed by a table-valued function is not able to support parallel reads for implementation-specific reasons. You would need someone with source code access (and the freedom to share details) to provide an authoritative answer, I'm afraid.

  Is support for parallel UDF a reasonable feature to request?

Of course, if you can make a strong-enough case. My own feeling is that the work involved would be extensive, so your proposal would have to meet an extremely high bar. For example, a related (and much simpler) request to provide inline scalar functions has great support, but has languished unimplemented for years now.

You might like to read the Microsoft paper:

Froid: Optimization of Imperative Programs in a Relational Database (pdf)

...which outlines the approach Microsoft look to be taking to address T-SQL scalar function performance issues in the release after SQL Server 2017.

  The goal of Froid is to enable developers to use the abstractions of UDFs
  and procedures without compromising on performance. Froid achieves this goal using a novel technique to automatically convert imperative programs into equivalent relational algebraic forms whenever possible. Froid models blocks of imperative code as relational expressions, and systematically combines them into a single expression using the Apply operator, thereby enabling the query optimizer to choose efficient set-oriented, parallel query plans.

(emphasis mine)

Inline scalar T-SQL functions are now implemented in SQL Server 2019.


Table income and expense is better to have it together or apart

If you consider income as a negative expense, and have categorised it sufficiently, a single table is just fine. The columns describing an income transaction are essentially identical to the columns describing an expense.

You can easily have indexes that allow you to quickly find your income or expenses, based on your categories (because negative income is not necessarily an expense, you should have things categorised appropriately). And with an index on time you can easily pull up your change over time.

If you're doing double-entry accounting, you should be able to easily add up your transactions and see they always add to zero. It's a useful test for consistency. Then some reports will leave out "equity" lines to compare Profit and Loss, etc.

If you use separate tables, you can use UNION ALL queries to query across the lot, but I would consider this extra effort you don't really need.


My PLE Counter value is 483.what is the safe side for PLE buffer manager value?

The Page Life Expectancy counter tells you for how many seconds an 8KB data page will remain in the buffer pool, before being flushed out.

A low number can be a sign of memory pressure, but it may also be a sign of nothing.

If your data access patterns involve a lot of report-style queries, reading a lot of data from large tables, this could be normal.

Monitor the value over several days, at different times in the day, and you may find that this is an outlier. I have a customer whose PLE goes to zero every time a certain large index is rebuilt, but it steadily increases after that.

My first sense is to review indexes, and see if a well-placed index can help with large reads. Another best practice is to avoid using SELECT * FROM table if you can help it.

You may need to increase the physical RAM in the server, but that shouldn't be your first reaction.

(Read more here about PLE and NUMA nodes: http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/)


Just do it in the client if you must.

SELECT *
FROM product
WHERE add_time >= NOW() - interval '30 day';

But if you must you can offload some of this to the server, thought I wouldn't because it's largely view-logic.

SELECT
  add_time >= NOW() - interval '12 hours' AS 12_hours,
  add_time >= NOW() - interval '24 hours' AS 24_hours,
  add_time >= NOW() - interval '7 day' AS 7_day,
  *
FROM product
WHERE add_time >= NOW() - interval '30 day';

You could also put it all into a case statement if you really wanted...

SELECT
  CASE
    WHEN add_time >= NOW() - interval '12 hours' THEN '12h'
    WHEN add_time >= NOW() - interval '24 hours' THEN '24h'
    WHEN add_time >= NOW() - interval '7 day' THEN '7d'
  END,
  *
FROM product
WHERE add_time >= NOW() - interval '30 day';


The InnoDB log sequence number is in the future

What was suggested to you thus far is what can be done to bring the database to a consistent state.

Here is what you need to know about InnoDB.

First of all here is the InnoDB Architecture in Pictorial Form

Look at the Picture. What components are essentially for InnoDB's self healing (sounds better that crash recovery)?

The Double Write Buffer has the cache of changed blocks to be used for recovery.
The Insert Buffer handles updates to nonunique indexes
The InnoDB transaction logs (ib_logfile0,ib_logfile1) contain Redo Playback Info also used in recovery.
There are undo logs (1023 of them, the maximum number of concurrent transactions)

You need three files for recovery

ibdata1
ib_logfile0
ib_logfile1

The whole datadir folder (/var/lib/mysql) needs to be restored from the same moment in time it was being backed up. If there are no physical copies of datadir from the same moment in time, then log sequence numbers for future transactions can never be referenced correctly.

If you do not trust your host in this matter, perhaps you can get MySQL started with innodb_force_recovery set to an appropriate value.

Here are the values from the MySQL Documentation

  1 (SRV_FORCE_IGNORE_CORRUPT)

  Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

  2 (SRV_FORCE_NO_BACKGROUND)

  Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.

  3 (SRV_FORCE_NO_TRX_UNDO)

  Does not run transaction rollbacks after crash recovery.

  4 (SRV_FORCE_NO_IBUF_MERGE)

  Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics.

  5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

  Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

  6 (SRV_FORCE_NO_LOG_REDO)

  Does not do the redo log roll-forward in connection with recovery.

Once you select the value you want, start up MySQL with it. Then, perform a mysqldump of all the data. Keep that mysqldump somewhere.

I would set innodb_force_recovery to 6. Restart mysql. Do the mysqldump (MyData6.sql)
Change it to 5. Restart mysql. Do the mysqldump (MyData5.sql)
Change it to 4. Restart mysql. Do the mysqldump (MyData4.sql)
Change it to 3. Restart mysql. Do the mysqldump (MyData3.sql)
Change it to 2. Restart mysql. Do the mysqldump (MyData2.sql)
Change it to 1. Restart mysql. Do the mysqldump (MyData1.sql)

You now have 6 snapshots of the data based on how much could be recovered. You would then have to load each MySQLDump into a separate instance of MySQL. You would have to then peruse the data and determine if enough of the data has been recovered. Percona has a Data Recovery Toolkit that would do all this way more efficiently than I am saying it.

My answer is simply a poor man's approach to this.

I hope this helps !!!


DBCC CHECKDB WITH PHYSICAL_ONLY option runs slower compared to a Full DBCC CHECKDB?

According to Microsoft, no.

DBCC CHECKDB Documentation

  PHYSICAL_ONLY
  Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.
  A full run of DBCC CHECKDB may take considerably longer to complete than earlier versions.

I confirmed this by running DBCC CHECKDB against the same 50GB database twice, once with PHYSICAL_ONLY specified, and the other without any options set. The results:

 With physical_only:
 SQL Server Execution Times:
 CPU time = 46894 ms,  elapsed time = 61546 ms.

 All checks:
 SQL Server Execution Times:
 CPU time = 247964 ms,  elapsed time = 263678 ms.

Having said all that, do you see that PHYSICAL_ONLY takes longer than a full check?

EDIT: Here's the results for a 320GB database:

With physical_only:
SQL Server Execution Times:
CPU time = 552805 ms,  elapsed time = 1218867 ms.

All checks:
SQL Server Execution Times:
CPU time = 2074875 ms,  elapsed time = 3007516 ms.

So, no - PHYSICAL_ONLY on databases larger than 200GB is not slower than a full check.


Migrate/Export DB from Oracle 11g Enterprise to Oracle 11g Express

This is example with HR user(example user in Oracle database) and Countries table. We'll extract a couple of rows with query - where region_id=2(table has 25 rows).
I created another user, HR2 and table Countries with same structure(but no FK constraints to ease import).
From HR we will export subset of Countries table and in HR2 we'll import the data.

> [oracle@orcla ~]$ expdp system/password tables=hr.countries dumpfile=testdump.dmp content=data_only query=hr.countries:\"where region_id=2\"

Export: Release 11.2.0.1.0 - Production on Wed Dec 9 23:51:33 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** tables=hr.countries dumpfile=testdump.dmp content=data_only query=hr.countries:"where region_id=2"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "HR"."COUNTRIES"                            5.921 KB       5 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/orcl1/dpdump/testdump.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:51:35

[oracle@orcla ~]$ impdp system/password directory=data_pump_dir dumpfile=testdump.dmp remap_schema=hr:hr2
Import: Release 11.2.0.1.0 - Production on Wed Dec 9 23:52:04 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=data_pump_dir dumpfile=testdump.dmp remap_schema=hr:hr2
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR2"."COUNTRIES"                           5.921 KB       5 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 23:52:06

Ok, now to check the result.

sqlplus hr2/hr2

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 00:02:39 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from countries;

  COUNT(*)
----------
         5

This is a general idea so adjust it to your environment and needs.


Dedicated SQL Server instance for SQL Agent

A: every job query will require four-part names.

B: that Agent's  credentials will require privileges on all instances which is a security risk.


Recovering credential data from a master DB backup

I don't have a restored master DB to try this, but running the statement of the view definition (with the references to master. changed to your user db name) should be a step in the right direction.
You can get the statement by running
sp_helptext [sys.credentials]


Creating a stored procedure and adding date manually to filename

Declare a variable first - but use format 112 for this. Like this:

DECLARE @query_attachment_filename NVARCHAR(255) =
    N'pca-test-' +
    CONVERT(NCHAR(8), GETDATE(), 112) +
    N'.csv';

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
...
@attach_query_result_as_file = 1,
@query_attachment_filename = @query_attachment_filename,
@query_result_header =0,
@query_result_separator = ' '


Over 99 million records with geography column & spatial index - VERY VERY VERY slow queries!

Look at the estimated query plan, and make sure the index is being used.

Also, the complexity of your MultiPolygon could be a significant factor. If you imagine your index as a series of grids over your MultiPolygon, there will be grids that either completely covered by your MultiPolygon or completely not covered by your MultiPolygon. Your ProblemChild points that fall into these grids are easy. When the grids are only partly covered by your MultiPolygon, it'll drill into the next level in and try the same.

When there are no more grid levels to drill into, the complexity really kicks in. If you have a particularly crinkly line, and you need to figure out which side of that line a particular point is, you need to check a lot of line segments and do maths around those. If you can simplify your MultiPolygon a lot, it'll speed up your query significantly. You can do this using the .Reduce() method, but then you need to be careful about correctness, in case you have points near the borders that would be on the other side of the line if were simplified.

By simplifying, I mean reducing it. Imagine an octagon, with eight points. If that were expressed using six points, or a four-point square, then the shape becomes different and something near a corner may have moved inside or outside as it the number of points reduced.

Edit: I've blogged about this topic at http://sqlblog.com/blogs/rob_farley/archive/2015/04/29/tuning-slow-spatial-queries-in-sql-server.aspx


Find the highest graded student in each state, using Relational Algebra

I don't actually feel very comfortable with relational algebra, so, I'll do it first using standard SQL and then use a tool called RelaX - relational algebra calculator 0.18.2 to do the translation.

First, the table you wrote, I'll call it students, and define it and fill it with:

CREATE TABLE students
(
    id INTEGER PRIMARY KEY,
    grade INTEGER,
    state TEXT
) ;

INSERT INTO students
    (id, grade, state)
VALUES
    (1, 83, 'CA'),
    (2, 94, 'TX'),
    (3, 92, 'WA'),
    (4, 78, 'CA') ;

RelaX will translate this into a dataset, represented by the following tuples:

group: Joan (imported from SQL)

students = {
    id:number, grade:number, state:string
    1        , 83          , 'CA'
    2        , 94          , 'TX'
    3        , 92          , 'WA'
    4        , 78          , 'CA'
}

In order to find what you're looking for, we first need a table with tuples in the form (state, grade), having the maximum grade of each state. This query is done, in SQL with a MAX(grade) per state using a GROUPs BY state. You would write it like:

SELECT
    state, max(grade) AS grade
FROM
    students AS s2
GROUP BY
    state ;

Next, you need to JOIN this table (that is named max_grades) to the students one, and you do it ON equal states, and equal grade (i.e.: the max grade per state)...

SELECT
    s1.id
FROM
    students AS s1
    JOIN
    (
        SELECT
            state, max(grade) AS grade
        FROM
            students
        GROUP BY
            state
    ) AS max_grades
    ON s1.state = max_grades.state AND s1.grade = max_grades.grade

... and this gets translated by RelaX to the following relational algebra expression and reponse:

   π  s1.id  ρ  s1 students ⨝  s1.state = max_grades.state and s1.grade = max_grades.grade  ρ  max_grades ( π  state, grade  γ  state; MAX(grade)→grade  ρ  s2 students)

  s1.id
  1
  2
  3

NOTE1:

If several students of one state would have the max grade, this expression would return ALL of them, not just an arbitrary one of that state.

Alternative:

If you cannot GROUP BY, you can use another construct:

SELECT DISTINCT
    id
FROM
    students
EXCEPT
SELECT
    s1.id
FROM
    students AS s1
    JOIN students AS s2 ON s1.state = s2.state AND s1.grade < s2.grade

This goes a bit more in-line with your original thinking, although I personally find it less clear...

The translation to relational algebra is:

   π  id students -    π  s1.id  ρ  s1 students ⨝  s1.state = s2.state and s1.grade < s2.grade  ρ  s2 students


How can one calculate percentage from previous week in aggregate query?

When you use MySQL >= 8.0, you can use CTEs (common table expressions)

 ;
WITH
    (
        SELECT  DATE(s.created) as "Date",
                COUNT(s.id) AS "Accounts credited",
                SUM(s.withdrawal) "Total Credited"
            FROM  statements s
            WHERE  s.status_id IN ('OPEN', 'PENDING')
            GROUP BY  DATE(s.created)
            ORDER BY  s.created DESC
    ) AS cte
SELECT  cte.*,
        100 * (cte.`Total Credited` - prev.`Total Credited`) /
              prev.`Total Credited`  AS "Difference in %"
    FROM  cte
    LEFT JOIN  cte prev  ON cte.`Date` = prev.`Date` - INTERVAL 7 DAY;

If you use an earlier version, just replace the CTE with appropriate subqueries, like

SELECT <your_fancy_percentage_calculation>
FROM (<cte from above>) a
LEFT JOIN (<cte from above>) b ON a.Date = b.Date - INTERVAL 7 DAY;

MySQL is smart enough to not execute the same query twice, I think.

Or you create a view and use this.


Postgresql small index for row contains query

Since seems like each value of id has many occurences in the table.  Given that, you can use a GIN index with its compressed posting lists to save space.  Usually GIN indexes are used with array types or their equivalents, but you can use the btree_gin extension to get access to GIN indexes with a scalar type.

However, GIN indexes have several limitations.  They don't support ORDER BY, which doesn't seem to matter to you.  And they don't support index-only-scans, which may be important to you.  Even if the index shrinks to fit in RAM, each tuple pointer found in the index still has to go to the table for resolution.  If your Btree index doesn't fit in RAM, surely your table doesn't either.  So you will still have lots of random seeking into the table.

Indeed, that was almost certainly the problem in the first place--not random seeks against the btree index, but rather against the table.  When you are using the BRIN index to drive table access, you naturally are going to visit the table with high locality of reference.  It is not that the BRIN index is effective, it is that the same condition that allows the BRIN to be used also makes the table access for the particular set of rows being returned also be efficient.  If you were to replace the BRIN indexes with equivalent Btree indexes, you would probably find that while the storage requirement went up, the performance stays mostly the same.


Use twice DISTINCT ON ina multiple join situation

If I understand correctly a product can have more than one image/video an of course more than one category.

In this case you can use a LATERAL JOIN and limit the result to only one row.

I've set up one small fiddle just to show you:

select p.id,
       p.name,
       i.image,
       v.channel,
       v.url,
       c.name
from   products p
left join lateral (select id, image
                   from   image
                   where  product_id = p.id
                   limit  1) i on true
left join lateral (select id, channel, url
                   from video
                   where product_id = p.id
                   limit 1) v on true
left join lateral (select c.id, c.name
                   from   categoryproduct cp
                   join   category c
                   on     cp.category_id = c.id
                   where  cp.product_id = p.id
                   limit 1) c on true;

id | name  | image | channel | url  | name
-: | :---- | :---- | :------ | :--- | :---
 1 | prod1 | img1  | c1      | url1 | cat1
 2 | prod2 | img3  | c3      | url3 | cat3

db<>fiddle here


What are some known hazards with cyclical replication between two MySQL master servers?

Cringe, gag, etc.

5.1 and 5.6 differ by 2 major versions.  Replication is 'guaranteed' to work one direction, not the other.

Don't turn on GTIDs in 5.6; 5.1 will not like it.

Don't use any features new to 5.5 or 5.6.

Simply, don't do it.  Get the 5.1 upgraded ASAP.


Foreign key references on column definitions are ignored. Feature, not a bug — why?

By design MySQL has been less SQL standard compliant than other open source database (i.e PostgreSQL): I think this was an intented design choice in both cases and I think that for MySQL the road is still long even with all Oracle Corp. and MariaDB achievements in the last decade.


Get interactive psql shell after executing queries

psql has a built-in feature to set defaults: the ~/.psqlrc file.

You can put any command in there that should be executed when you start psql

So just create that file and put:

\setenv PAGER 'pspg -s 0'
\pset pager always

into it.


Mysql - How can I replication structure, not data

The blackhole engine type will work for what I need.  We are using row based replication and can't change to statement based but the warnings are not being logged so that won't be an issue.

As a side note, one thing I found somewhere suggested show variables like 'have_blackhole_engine' and nothing came up (nor for show variables like '%blackhole%' or show global variables like '%blackhole%') but I decided to create a table with the engine and it showed up just fine.


Where to put my.ini file for 5.6.21 MySQL Community Server on Windows

The folder C:\Program Files\MySQL\MySQL Server 5.6 is the correct location for my.ini.
There are two possible reasons 1400 shows up as table_definition_cache.
REASON #1
You did not restart mysql.
Login to Windows DOS command line as Administrator and run
C:\> net stop mysql
C:\> net start mysql

Value should be in place afterwards.
REASON #2
The default value for table_definition_cache is -1 (autosized). That makes mysqld figure out a fair value for table_definition_cache, given your OS configuration.
CAVEAT
Make sure you place
table_definition_cache = 400

under the [mysqld] group header before you restart mysql
GIVE IT A TRY !!!
I dug a little further and learned why 1400 comes up for table_definition_cache
According to the Documentation for table_definition_cache

The default value is based on the following formula, capped to a limit of 2000:
400 + (table_open_cache / 2)

The default value for table_open_cache is 2000
Applying the formula
400 + (table_open_cache / 2) =
400 + (2000 / 2) =
400 + 1000 =
1400

One of two things is happening

table_definition_cache is still not being picked up from my.ini
table_definition_cache is being picked up from my.ini, but is being rejected due to needed resources


Is BulkCopy available for SQL-Server 2012

Yes, BCP.EXE for SQL Server 2012 works on Windows 10. I have tried it myself several times, though do not use it extensively (so there could be a minor issue with one of the features that I haven't used, but I highly doubt it).

Also, BCP (like the other command-line utilities) is freely available, so you should test it for yourself on your system(s). You can download it from the following page:

Microsoft® SQL Server® 2012 Feature Pack

Expand the "Install Instructions" section and scroll down to (or search for) "Microsoft® SQL Server® 2012 Command Line Utilities".


ORA-01017 error when conecting to Oracle Database Standard Edition 2.12.2.0.1.0 for Microsoft Windows x64 (64-bit)

Check the netca config. There needs to be an entry in tnsnames.ora.


You will have to check the following items

If the decommissioned Slave server still has /etc/my.cnf, do one of the following

rm -f /etc/my.cnf
mv /etc/my.cnf /etc/my.cnf.old

If the Master Server still has any --report-host in its /etc/my.cnf

remove report-host from /etc/my.cnf
service mysql restart

You may need service mysql restart on the Master anyway
Make sure you delete master.info in the datadir of the old Slave

Give it a Try !!!


Create an unique integer id column for an existing string column

To add the new column use:

ALTER TABLE the_table ADD COLUMN id_int integer;

To populate the new column you need an UPDATE statement.

I am assuming you have a primary key column named pk_column in your table. Obviously you need to replace that with the actual primary key column in your table.

update the_table
   set id_int = t.rn
from (
  select pk_column,
         dense_rank() over (order by id_string) as rn
  from the_table
) t
where the_table.pk_column = t.pk_column;

If you really have a table without a primary key (why?), you can use the built-in ctid instead:

update the_table
   set id_int = t.rn
from (
  select ctid as id_,
         dense_rank() over (order by id_string) as rn
  from the_table
) t
where the_table.ctid = t.id_;