space usage on sys.allocation_units and sp_spaceused

Even though you fixed the immediate rounding issue, the overall algorithm to get per-object / index stats is incorrect. It does not properly handle LOB and row-overflow data. It also excludes: Indexed Views, FullText indexes, XML indexes, and a few other cases. Hence, you might not be seeing all of your data.

The following is an adaptation of the code I posted to an answer on StackOverflow ( sp_spaceused - How to measure the size in GB in all the tables in SQL ) that handles all of the cases that sp_spaceused handles. That S.O. question was only concerned with per-object stats, not per index, so I have adjusted the code to handle things at the index level.

;WITH agg AS
(   -- Get info for Tables, Indexed Views, etc
    SELECT  ps.[object_id] AS [ObjectID],
            ps.index_id AS [IndexID],
            NULL AS [ParentIndexID],
            NULL AS [PassThroughIndexName],
            NULL AS [PassThroughIndexType],
            SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            SUM(ps.row_count) AS [RowCount],
            SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
                    AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    GROUP BY    ps.[object_id],
                ps.[index_id]
    UNION ALL
    -- Get info for FullText indexes, XML indexes, Spatial indexes, etc
    SELECT  sit.[parent_id] AS [ObjectID],
            sit.[object_id] AS [IndexID],
            sit.[parent_minor_id] AS [ParentIndexID],
            sit.[name] AS [PassThroughIndexName],
            sit.[internal_type_desc] AS [PassThroughIndexType],
            0 AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            0 AS [RowCount],
            0 AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    INNER JOIN  sys.internal_tables sit
            ON  sit.[object_id] = ps.[object_id]
    WHERE   sit.internal_type IN
               (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
    GROUP BY    sit.[parent_id],
                sit.[object_id],
                sit.[parent_minor_id],
                sit.[name],
                sit.[internal_type_desc]
), spaceused AS
(
SELECT  agg.[ObjectID],
        agg.[IndexID],
        agg.[ParentIndexID],
        agg.[PassThroughIndexName],
        agg.[PassThroughIndexType],
        OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
        OBJECT_NAME(agg.[ObjectID]) AS [TableName],
        SUM(CASE
                WHEN (agg.IndexID < 2) THEN agg.[RowCount]
                ELSE 0
            END) AS [Rows],
        SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
        SUM(agg.LobAndRowOverflowUsedPageCount +
            CASE
                WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
                ELSE 0
            END) * 8 AS [DataKB],
        SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
            CASE
                WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
                ELSE 0
            END) * 8 AS [IndexKB],
        SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
        SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
            agg.[IndexID],
            agg.[ParentIndexID],
            agg.[PassThroughIndexName],
            agg.[PassThroughIndexType],
            OBJECT_SCHEMA_NAME(agg.[ObjectID]),
            OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
       sp.TableName,
       sp.IndexID,
       CASE
         WHEN (sp.IndexID > 0) THEN COALESCE(si.[name], sp.[PassThroughIndexName])
         ELSE N'<Heap>'
       END AS [IndexName],
       sp.[PassThroughIndexName] AS [InternalTableName],
       sp.[Rows],
       sp.ReservedKB,
       (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
       sp.DataKB,
       (sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
       sp.IndexKB,
       (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
       sp.UsedKB AS [UsedKB],
       (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
       sp.UnusedKB,
       (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
       so.[type_desc] AS [ObjectType],
       COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType],
       sp.[PassThroughIndexType] AS [IndexSecondaryType],
       SCHEMA_ID(sp.[SchemaName]) AS [SchemaID],
       sp.ObjectID
       --,sp.ParentIndexID
FROM   spaceused sp
INNER JOIN sys.all_objects so -- in case "WHERE so.is_ms_shipped = 0" is removed
        ON so.[object_id] = sp.ObjectID
LEFT JOIN  sys.indexes si
       ON  si.[object_id] = sp.ObjectID
      AND  (si.[index_id] = sp.IndexID
         OR si.[index_id] = sp.[ParentIndexID])
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N''  -- optional name filter
--ORDER BY ????


Serial fields at 1 after logical replication

Solved this by dumping just the sequences with pg_dump -t '*_id_seq' dbname > sequences.sql


PostgreSQL claims to support 170 of 179 SQL:2016 features, but it also lists 309 features of SQL:2016 that it doesn't support. Why the discrepancy?

Features in the SQL standard are split between mandatory features and optional features.
As stated here

Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features

(emphasis mine)
So the discrepancy you are seeing, stems from the difference between mandatory and optional features.


How do I sync instance A to instance B without losing any of the data already on instance B?

Golden Gate is answer to these types of problem. Oracle bought it. Therefore it is a Oracle Golden Gate right now.

Look below image which is taken from Oracle Golden Gate Admin Guide
 http://download.oracle.com/docs/cd/E18101_01/doc.1111/e17341.pdf

Also you can look to following article:

http://www.dbasupport.com/oracle/ora11g/Oracle-Replication-Streams-vs-GoldenGate.shtml

Thanks Tevo D for pointing out licensing issue. Actually golden gate may be a expensive solution depending on your circumstances, i.e your company's standing with Oracle, what is your cost of downtime etc.


Get updated rows without triggers or timestamps

Take a copy of the clients binary logs.  Act as if you're doing a disaster recovery.

https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html

The relay log may be an alternative.


Can I rely on functions being executed first in SQL

No.

If you re-write your view with the context filtering against the where clause (instead of the connect by), you'll get the previously set value for the context:

create table t as
 select rownum r from dual connect by level <= 10;

create or replace view v as
  select r val from t where r <=sys_context('global_context','limit');

select f(2), v.* from v;

F(2) VAL
---- ---
   2   1
   2   2

select f(4), v.* from v;

F(4) VAL
---- ---
   4   1
   4   2

select f(4), v.* from v;

F(4) VAL
---- ---
   4   1
   4   2
   4   3
   4   4

As the where clause is evaluated before the columns are selected, the value passed to the function isn't set until after the context is read. The location of the sys_context call in your query (select, where, group by, etc.) will affect exactly when this value is set.


Yes, you have done correct steps to configure default write concern settings.
What comes to the second question, as it is written here at the documentation:

WiredTiger uses checkpoints to provide a consistent view of data on disk and allow MongoDB to recover from the last checkpoint. However, if MongoDB exits unexpectedly in between checkpoints, journaling is required to recover information that occurred after the last checkpoint.
With journaling, the recovery process:

Looks in the data files to find the identifier of the last checkpoint.
Searches in the journal files for the record that matches the
identifier of the last checkpoint.
Apply the operations in the journal files since the last checkpoint.


How do I find gaps between ranges of numbers?

After a pretty good amount of digging, I found this blog post on doing this for date ranges, and it turns out to work equally well on numeric ranges, with a little modification for handling the 0 and 1 endpoints.

Taking the query from there and renaming the columns to be a bit more clear, we have

SELECT * FROM
  (SELECT MAX(T_END) OVER (ORDER BY T_START) GAP_START,
          LEAD(T_START) OVER (ORDER BY T_START) GAP_END
   FROM T)
WHERE GAP_START < GAP_END

This warrants quite a bit of explanation. Step by step:

For each row, it gets the maximum T_END from all rows where T_START is less than or equal to the current row's T_START. This gives us the biggest range end of all the ranges that overlap this row's range start.
For each row, it gets the T_START of the first row where the T_START is greater than the current row. This gives us the next biggest range start.
It filters out any rows where the maximum T_END is greater than or equal to the next T_START. This means that the biggest overlapping range end extends out to or beyond the next range start. In other words, there is no gap between this row and the next, either because this row ends where the next begins or because other overlapping rows cover all points between the two.

This doesn't quite handle the requirement of all gaps over the range of 0 to 1, though. It only picks the gaps between the minimum range start and the maximum range end of all the rows. To check for gaps at the ends of the 0 and 1 range, we can use the following queries.

For gaps starting at 0:

SELECT 0 AS GAP_START, MIN(T_START) AS GAP_END
FROM T
HAVING MIN(T_START) > 0

For gaps ending at 1:

SELECT MAX(T_END) AS GAP_START, 1 AS GAP_END
FROM T
HAVING MAX(T_END) < 1

Both of these queries will return no rows if there is no gap at the endpoint.

Putting it all together into a single query:

SELECT * FROM
  (SELECT MAX(T_END) OVER (ORDER BY T_START) GAP_START,
          LEAD(T_START) OVER (ORDER BY T_START) GAP_END
   FROM T)
WHERE GAP_START < GAP_END
UNION ALL
SELECT 0 AS GAP_START, MIN(T_START) AS GAP_END
FROM T
HAVING MIN(T_START) > 0
UNION ALL
SELECT MAX(T_END) AS GAP_START, 1 AS GAP_END
FROM T
HAVING MAX(T_END) < 1

The result is exactly as stated in the question, ignoring order. SQLFiddle of the query.


Follow below steps :

    a. start SQL Server in master-only recovery mode

     for Default instance : NET START MSSQLSERVER /f /T3608

     for Nameed Instance  : NET START MSSQL$instancename /f /T3608

    b. Check the file location

          SELECT name, physical_name AS CurrentLocation, state_desc
            FROM sys.master_files
            WHERE database_id = DB_ID(N'model');

    4. sp_detach_db 'model'
        go

    3. Move the Model.mdf and Modellog.ldf files from OLD_LOCATION to NEW_LOCATION
    4. Reattach the model database as follows:
    use master
    go
    sp_attach_db 'model','G:\model\model.mdf','G:\model\modellog.ldf'
    go
   5. Stop SQL Server : NET STOP MSSQLSERVER or NET STOP MSSQL$instancename
   6. Restart SQL Server Normally : NET START MSSQLSERVER or NET START MSSQL$instancename
   7. Verify the new location : sp_helpdb model


Try something like

CREATE TABLE test_stmp(StartDate TIMESTAMP NOT NULL,
constraint chk_test_filed check(trunc(StartDate) = to_date('2000-01-01','yyyy-mm-dd')));


Is SELECT COUNT GROUP BY more efficient than counting a result set?

The recommended approach to improve performance is to:

a) Use the database to do the heavy lifting for counting, computations (where possible)

b) Reduce the number of trips to the database and the amount of data brought back on each trip

In that light, I am not sure what you want to use the rank count for, but the query below will return the data ordered by rank, but a "repeated" column with the number of records for each rank is also included

SELECT t.rank, col1, col2, rankcount
FROM tablename t
  INNER JOIN
     (SELECT rank, COUNT(id) as rankcount FROM tablename t2 GROUP BY rank) AS t3
    ON t.rank = t3.rank
ORDER BY t.rank


How to organize a development environment using Oracle XE 11g?

Any serious development project involves source control and a deploy process - SCM (Software Configuration Management). Make sure you put all your database objects (table, index, packages etc..) under revision control. Put your APEX export-files under revision control. Tag them together with the ddl. This way you have a consistent point in time representation of your "system". Make use of the "continous integration" paradigm - build early - build often. Jenkins CI will be a good choice.

When all of this is in place, you branch off your "serious customization"!


I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data.
  I would like to establish a reporting server (may be 2008 or 2012), that consolidates the rows from selected tables across the 8 source servers into a single instance of those tables on the reporting server. This is one-way replication (no changes will be made to the reporting server). I will need to replicate changes from the source databases with relatively low latency (say 20-30 seconds).

You can achieve this with Transactional replication. Below is how you can do it.

Note : You have to change slightly your table schema to achieve this as you have to uniquely identify that rows when you are replicating to the subscriber. As a prerequisite of T-Rep you need to have tables with PK defined.

Below is your sample table on Publisher servers that is on all your 8 servers that you want to consolidate rows on the reporting server :

CREATE TABLE Products
(
ProductID INT not null,
ProductName VARCHAR(25),
ServerName sysname default @@servername not null -- this is to identify which row is from which server ; probably add this using Alter column
)
GO
ALTER TABLE Products
ADD CONSTRAINT pk_Product_ID_ServerName PRIMARY KEY (ProductID)

On the subscriber server, you need to create the same table but with different PK to uniquely identify the rows at subscriber (not doing so, T-Rep is going to fail with PK violation - I am assuming that you cannot modify the PK structure on live PRODUCTION rather its better to modify at the subscriber)

CREATE TABLE Products
(
ProductID INT not null,
ProductName VARCHAR(25),
ServerName sysname default @@servername not null
);
GO

ALTER TABLE Products
ADD CONSTRAINT pk_Product_ID_ServerName PRIMARY KEY (ProductID,ServerName)

Below script will help you setting up T-Rep, just change the databasename, destination server name along with object name.

-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'repl1', @optname = N'publish', @value = N'true'
GO

exec [repl1].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec [repl1].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO
-- Adding the transactional publication
use [repl1]
exec sp_addpublication @publication = N'repl1_2005', @description = N'Transactional publication of database ''repl1'' from Publisher ''server_name\SQL2005''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'repl1_2005', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'server_name\SQLServer2005SQLAgentUser$server_name$SQL2005'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'server_name\SQLServer2005MSSQLUser$server_name$SQL2005'
GO
exec sp_grant_publication_access @publication = N'repl1_2005', @login = N'distributor_admin'
GO

-- Adding the transactional articles
use [repl1]
exec sp_addarticle @publication = N'repl1_2005', @article = N'Products', @source_owner = N'dbo', @source_object = N'Products', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'none', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'Products', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboProducts]', @del_cmd = N'CALL [sp_MSdel_dboProducts]', @upd_cmd = N'SCALL [sp_MSupd_dboProducts]'
GO

-- Adding the transactional subscriptions
use [repl1]
exec sp_addsubscription @publication = N'repl1_2005', @subscriber = N'server_name\SQL2008R2', @destination_db = N'repl123', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'repl1_2005', @subscriber = N'server_name\SQL2008R2', @subscriber_db = N'repl123', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO

Couple of points to note :

In sp_addsubscription make sure that  @sync_type = N'automatic'

And the article properties should be set to :

So finally, you can have rows consolidated from all (in my case 3 servers) as below :

So in summary,

Use T-Rep.
Add an additional column to the existing Publisher databases e.g. serverName to uniquely identify the rows at the subscriber.
Create  table on Subscriber having PK included as ServerName.
Create replication of the tables with @sync_type = N'automatic' and Article property set to "Keep existing object unchanged".
Run snapshot agent.
Check the consolidated data on the subscriber.


what are the costs of enabling extended events?

Of course there will be an increase on CPU, as any other process on the server. But Extended Events are recommended precisely due to the low resource needs of running them. Using Extended Events to capture information takes much fewer resources than using the long old known profiler tool for example. Use it wisely of course, don't setup and run thousands of sessions to capture tons of data, then you can have a problem. We have used Extended Events on our own servers for auditing different processes and from our experience, we almost didn't saw a measurable increase on CPU activity. Yes, it takes some disc space to record all data it gathers, but again, think ahead and plan were to save it in order to affect as less as possible production environment.

As for 2nd question: it depends, as usual. Each system, platform and configuration are different, none is exactly to the other. So, if not possible to tell you how much RAM, CPU, disk space or IO will gonna take. But certainly will not be so much. Again, use common sense, don't start hundreds of sessions capturing gazillion of data, because then obviously you will have a penalty on performance.

For more detailed info check here, here here and here.


Pros and cons of master status table vs private status table?

The main problem with your approach occurs when you want to maintain referrential integrity to the status table.

Suppose you have a member table:

CREATE TABLE member (
  member_id SERIAL PRIMARY KEY,
  status_id int NOT NULL,
  ...
);

Now if you want to enfore referrential integrity to ensure each member has a valid status, you can't. This is because only a subset of the status rows applies to members. As a result, a member can be assigned a purchase_order_status and RI will still pass.

EDIT2

In a comment you clarified that in your solution member would have RI to the single-column member_status table. This suggests your design could land up having a large number of single column tables, just to control refferential integrity. Now any time you add a new status, you need to add it to 2 tables.

There's another problem with your master_status table.

status_code is unique. Meaning that purchase_order and sales_order cannot use the same codes. This seems to be an unnecessarily harsh restriction. However, droping the unique constraint risks duplicating codes within a category.

So the next thing you might consider is adding a status_type and enforcing the unique constraint accross both type and code. So your status_type attempts to fix the underlying problem by artifically splitting the master table into multiple tables.

Basically the problem boils down to: You are trying to consolidate multiple distinct sets of data into a single table because there is a similarity in that some of their columns overlap.

Joe Celko has written a fairly in-depth article on Lookup Tables in SQL.

EDIT On second thoughts, perhaps your approach is not that much like "One True Lookup Table" because you do acknowledge the need for additional tables where more detail is required. The RI issue still remains however.


remove Analysis service from a SQL 2014 cluster

I would suggest not removing it. Why do you need to remove it? If you aren't going to use it anymore, I would just remove it as a cluster resource (and any dependencies), and then disable the service on each node. No need to uninstall anything except to save a tiny amount of disk space. IMHO, anyway.


Enabling admin connection on SQL Server Express to fix logon trigger

The documentation for "Diagnostic Connection for Database Administrators" states:

  To conserve resources, SQL Server Express does not listen on the DAC port unless started with a trace flag 7806.

That TF needs to be specified either on the command line, if you are starting SQL Server via the command line, or in the Registry if you are starting via the Services interface (the typical method).

To get this into the Registry:

open a command prompt
run regedit
navigate to the following key:

  Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL{version_number}.{instance_name}\MSSQLServer\Parameters

where {version_number} is the 2-digit number of the release, such as "11" for SQL Server 2012, and "14" for SQL Server 2017.
Add the following String Value:

SQLArg3
Edit the new String Value to provide the following value data:

-T7806

Please note: upper-case "T", and no space between the "-T" and the number!


HOW to impliment SQL 2012 send_dbmail with OAuth2

Is the answer a different email agent?

Yep. If you wanted to do it inside of SQL Server you could write some CLR code, but I wouldn't advise it.


If you're going to use Percona tools, you could just use pt-archiver, which will do this part for you as well.

But mysqldump will work just as well here, since you've customized this archiving for your schema.


Comparing Query Performance Across DBMSs

I think that you shouldn't rely on any of that. The only real measure is how long the query actually took, when measured from the client side. That also avoids overhead during query execution that you get if you for example use EXPLAIN (ANALYZE) in PostgreSQL.
So I would do it like this:

measure the current time on the client

send a query to the server

wait until execution is complete and the client has got the result

measure the time again and calculate the difference


Transformation of row data based on preceding row for special cumulative sum

Based on this table definition:

CREATE TABLE tbl (  -- no PK?
  id text NOT NULL
, creation timestamp UNIQUE NOT NULL
, operation text NOT NULL
, value numeric NOT NULL
, running_sum numeric  -- optional (not needed for task)
);

Data types and constraints are almost always essential.
(creationdoes not strictly have to be unique. But if there can be duplicate values per group (id), you need to do more.)

Basic query to compute your special running sum

SELECT id, creation, operation, value
     , sum(value) OVER (PARTITION BY id, run ORDER BY creation) AS running_sum
FROM   (
   SELECT *, count(*) FILTER (WHERE operation  LIKE 'override_op_%')
                      OVER (PARTITION BY id ORDER BY creation) AS run
   FROM   tbl
   ) t
ORDER  BY id, creation DESC;

Any operation name starting with 'override_op_' indicates the start of a new run (group, patch, partition).

In addition to the related answer you already linked to:

Calculating Cumulative Sum in PostgreSQL

Consider this related question for details how to partition rows into groups (run in this query, since you are using the term "group" for the id column)

Select longest continuous sequence

I use the new aggregate FILTER clause for the partial count:

Return counts for multiple ranges in a single SELECT statement

You could use the simpler (less clear) expression in older versions:

count(operation LIKE 'override_op_%' OR NULL)

Compute delta

Based on this, you can easily compute the desired delta:

SELECT *
     , running_sum - lag(running_sum, 1, numeric '0')  -- data type must match!
                     OVER (PARTITION BY id ORDER BY creation) AS transformed_value
FROM (
   SELECT id, creation, operation, value
        , sum(value) OVER (PARTITION BY id, run ORDER BY creation) AS running_sum
   FROM   (
      SELECT *, count(*) FILTER (WHERE operation  LIKE 'override_op_%')
                OVER (PARTITION BY id ORDER BY creation) AS run
      FROM   tbl
      ) t
   ) t
ORDER  BY id, creation DESC;

I use the 3-parameter form of the window function lag() to provide 0 (data type must match!) as default value for the first row in the table.


ORA-27100: shared memory realm already exists oracle

Phil is right - you need to see if you have any orphan Oracle processes and ipcs shared memory.

If you have more than one Oracle instance on your server, include the instance in your grep commands; more than likely, you may have some orphan Oracle processes still running.  Assuming your SID is custdev, you could do a

ps -fu oracle | grep custdev

and then do a kill -9 PID (where PID is the process ID) on any of the PIDs that show up.


help in connecting the sql server 2017 developer edition

It sounds like one of the following things is happening:

The sa account is disabled

either explicitly, or because you're in Windows Authentication mode

You don't know the password to the sa account

Since this is installed locally on your PC, you should actually be able to connect with sqlcmd without any parameters to use "Trusted Connection" to authenticate you:

sqlcmd

Once you're in, you should be able to check the authentication mode using an approach like this one:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode]

If that returns "Windows Authentication," then you'll need to go through the process of switching to mixed mode outlined here: Change Server Authentication Mode

If the query above returned "Windows and SQL Server Authentication" then it's possible that you have just mistyped or forgotten the sa account password, or it's disabled for some other reason.  In that case, you can run this code (also included in the docs link above):

ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO


SQL Server: Clarity on which service user does what (backups, etc) RE accessing network shares

The Service Account for SQL Server needs to be granted access to the network share in question.

This indicates a need for you to configure SQL Server to start with a Windows Active Directory account.  If you don't have access to a domain account, you will need to create an account on the SQL Server machine with a matching account name and password on the target network machine.

Use SQL Server Configuration Manager to modify the startup config of the SQL Server Service.

The only way to allow a service on one machine to access a network share is to provide network access with a username and password.  Make sure the SQL Server service can access the network share before restarting SQL Server by logging onto the SQL Server machine using the same username and password, and confirm you can see the network folder.

You also need to ensure you aren't trying to restore from a "mapped" network drive, since the "mapping" won't necessarily be there for the SQL Server service to "see".  Instead of using a mapped drive letter for the restore, use a UNC name, such as \\server\share\folder\backup.bak


MYSQL Huge Data migration (5TB) from old server to new server

$ mysqldump -h source ... | mysql -h dest ...

I'm assuming:

You have the tow machines (source and dest) set up.
The two machines are "close" (not on opposite sides of the globe) so that network delays, etc would need to be considered
That script would be run on one machine -- either one.
If the versions are different, I cannot predict whether there will be some hiccups based on inconsistencies between versions.  If it fails running on one server, try the other.  If that fails come back with the error message(s).
It will take time because 5TB takes time to read and write.

If the versions are radically different, there could be major changes that we need to discuss.


Reclaiming mysql empty space... nothing works

Down to 4% disk space?  This will be tricky.
Find the largest files on the system; sort by size; see if any other files (logs, other apps, etc) can be freed up.

Find out how big ibdata1 is.
Run SHOW TABLE STATUS.  (Show me this, so I can explain what the rest of the steps mean.)
Figure out whether some tables are "file-per-table" and some are in ibdata1.
Figure out how much free space is in ibdata1.  Let'c call this x.
For each table that is not in ibdata1, but is small enough (data_free+index_free) to fit into x, move it into ibdata1 -- but start with the one with the largest Data_free.  Do not bother will tables with free space below 8MB.  (This involves a SET and an ALTER.)
For each of the rest of the tables with more than 8MB free and not currently in ibdata1, rebuild (ALTER).  Start with the smallest and work your way up, until their is not enough free disk to do the next table.

If that won't suffice then:  Get a bigger machine, dump and reload.
For the future:  You should worry about disk space when 50% full (not 96%).


Find direct connections to database server instead of SQL Server Always On Listener

I covered this through two main posts. The first gives you an idea how to see what is being used, currently. The second tells you how to find which connections are read only routed by exploiting the fact that SQL Server can listen on multiple ports and that read only routing accepts whatever endpoint url you give it which is directly given back to the client - this means you can setup specific items just for read only routing and report back on it.

It is much more accurate and no need to go through extended events. You're specifically looking for the second post, but they are related and will give you a better picture when combined together.

Finding What Availability Group Listeners Applications Are Using To Connect
Finding Which Connections Have Been Read Only Routed


Compare a date and a timestamp with time zone with now() in the same query?

This should never fail (I simplified a bit):

WHERE status_code IN ('30000','30005')
AND   expiration > now()

PostgreSQL can compare date and timestamp (with or without time zone) automatically. If one is a date it is cast to timestamp automatically (0:0 hours).

The error message tells a different story. You are actually trying to input a date with invalid syntax.

I wrote a detailed answer about handling of timestamps with or without time zone in PostgreSQL recently - if that should the issue:

Ignoring timezones altogether in Rails and PostgreSQL

As it turns out, expiration is a text column. You need to cast it to date or timestamp (whichever fits your need). If it is in a valid format:

...
AND   expiration::timestamptz > now()

If you have invalid strings like 'No End Date' in that text column, you need to clean the source or treat those specially in a CASE construct:

...
AND   CASE WHEN expiration::text = 'No End Date' THEN 'infinity'::timestamp
           WHEN expiration::text = 'foo' THEN '-infinity'::timestamp
           ELSE expiration::timestamp
      END > now()

The manual about the special value infinity.

The cast to text (::text) is redundant with text, but makes the expression work with date / timestamp values as well.

If the format of the time stamp literal can be ambiguous, use to_date() or to_timestamp() and define the format explicitly:

to_date('07/08/2013', 'DD/MM/YYYY')

How to convert “string” to “timestamp without time zone”

Use the AT TIME ZONE construct if expiration is supposed to be a local timestamp of another time zone:

expiration::timestamp AT TIME ZONE 'UTC' -- desired time zone here

If expiration is in ambiguous / non-standard format, use to_timestamp():

to_timestamp(expiration::text, 'yyyy-mm-dd')


There are different levels of normalization.  If your business logic is set up where each contact has an associated account (but more than one contact can be in an account), then there is no reason to put the account ID in the order table because you can get it by going back to the contact table.  In other words, account ID in the contact table is a foreign key.


Display unique column when not all values are distinct

If I understand your question correctly, I think NOT EXISTS should work.

DECLARE @T TABLE (
    NAME VARCHAR(30)
    ,STATUS VARCHAR(5)
    ,Contact VARCHAR(5)
    ,EpisodeNumber INT
    );
INSERT INTO @T
VALUES
    ('AA', 'C', 'E', 1),
    ('AA', 'C', 'F', 1),
    ('AA', 'C', 'G', 1),
    ('AA', 'D', 'H', 1),
    ('BB', 'D', 'I', 2),
    ('CC', 'C', 'J', 3),
    ('DD', 'C', 'K', 4)
;
SELECT *
FROM @T t
WHERE NOT EXISTS (
        SELECT *
        FROM @T
        WHERE NAME = t.NAME
            AND EpisodeNumber = t.EpisodeNumber
            AND STATUS = 'D'
        );

| NAME | STATUS | Contact | EpisodeNumber |
|------|--------|---------|---------------|
| CC   | C      | J       | 3             |
| DD   | C      | K       | 4             |


With MySQL Replication, what level of resilience is possible?

A lot of the questions you are asking are predicated by use of master-slave replication. Life's a lot simpler if you use master-master replication; master-slave requires detection of a failure on the master and promotion of the slave. While this can be automated to some extent, you also need to think about how you implement reinstating the master.

Regarding data-loss: get over it. Although there are various approaches implemented in different products, even moving transaction control outside of the database cluster, there is still a possibility of losing data. The only practical solution is to design your system so that its possible to identify failed operations (which is good practice as such losses are more often caused by software bugs than system crashes).

Regarding the details of how you implement the system - a lot depends on the OS you are running on, the impact of downtime, the architecture of the application, the nauture of the network connecting the nodes in the application....lots of information which you've not provided.

There are some useful links here


Design consideration regarding state handling: how to store multiple, variable states for one entity

Using the cross reference table between Transaction and ProcessingRule is going to be a very efficient way of returning Transaction records that need to be processed.

You can get a list of Transactions that need processing by:

USE Test;

CREATE TABLE Transactions
(
    TransactionID INT NOT NULL PRIMARY KEY CLUSTERED IDENTity(1,1)
    --extra Transaction fields here as necessary
);

CREATE TABLE Rules
(
    RuleID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
    --extra Rules fields here as necessary
);

CREATE TABLE TransactionRuleProcessing
(
    TransactionID INT NOT NULL
    , RuleID INT NOT NULL
);

DECLARE @RuleID INT = 123; --123 is the rule being processed

SELECT T.*
FROM Transactions AS T
    LEFT JOIN (
        SELECT *
        FROM TransactionRuleProcessing AS TRP1
        WHERE TRP1.RuleID = @RuleID
    ) AS TRP ON T.TransactionID = TRP.TransactionID
WHERE TRP.TransactionID IS NULL;


You can't do it, without using third-party visualisation software


You are running 17568 updates (and 17568 selects) for something that can be done with a single statement:

update d2
   set stav_1 = stav_2,
       stav_2 = stav_3,
       stav_3 = stav_4,
       ....
where usek between 1 and 61;
commit;

If you don't want to type 288 column names, then use your program to generate the above statement and execute that once.

Having numbered columns is very often a sign of a bad data model. The need to "move" values from one column to another is another sign that you should really re-design your data model and normalize it.


Converting (a) an entity-relationship diagram about an airline booking scenario to (b) a relational schema

First, a 1-N relationship, the table on the N side would have the key of the table on the 1 side configured as Foreign Key.

So depending how leg_instance is keyed, either:

  Seat: seat_no, Date(from leg_instance), leg_no (from flight_leg), Number (from flight),
  with key (Number,leg_no,seat_no)
  and foreign key (leg_no,Date) => leg_instance(leg_no,Date)

or

Seat: seat_no, leg_instance_id (from leg_instance)
with key (leg_instance_id, seat_no)
and foreign key (leg_instance_id) => leg_instance(leg_instance_id)

Then, the table on the N side caries all attributes of the relationship, so here Seat would also have Customer_name and Cphone.  It would be permissible to introduce a separate table for Reservation that is 1-1 with Seat, but 1-1 relationships are normally just merged into a single table.  That table might end up being named SEAT, RESERVATION or something like SEAT_RESERVATION, as it implements the ERD shapes with both those names.

  Another question I have is would flight_leg contain scheduled_dep_time & scheduled arr_time ?

You should be able to answer that yourself, now.


Can I Run Two Full Backups on SQL Server 2005 at the Same Time?

Shouldn't be an issue, assuming your I/O subsystem can keep up. ("Different locations" may mean different things to different people - it's not just about drive letters.)


Running pt-table-checksum on a table in different databases

I don't see anything along those lines in the Percona Toolkit Documentation.

There is something you can do to cheat

Scenario

datadir is /var/lib/mysql
Master has test database
Slave has test_slave database

You could create a symbolic link in one of two ways

TECHNIQUE #1

On the Master, run

ln -s /var/lib/mysql/test /var/lib/mysql/test_slave
chown mysql:mysql /var/lib/mysql/test_slave

TECHNIQUE #2

On the Slave (if there is no test database), run

ln -s /var/lib/mysql/test_slave /var/lib/mysql/test
chown mysql:mysql /var/lib/mysql/test

NEXT STEP

Once you created the symbolic link, you can run the checksum against test

pt-table-checksum --host=localhost --databases=test --max-load=Threads_connected:25 --no-check-replication-filters

or against test_slave

pt-table-checksum --host=localhost --databases=test_slave --max-load=Threads_connected:25 --no-check-replication-filters

EPILOGUE

When done, you can remove the symbolic link.

Give it a Try !!!


Assuming the primary key of children is (horror) id:

select p.*

from parents p
  inner join children c on c.parentId = p.id

where c.firstname like 'tom'
  and not exists
      ( select 1 from children c2
        where c2.parentId = p.id
          and c2.id <> c.id
      ) ;

or using GROUP BY:

select p.*

from parents p
  inner join
    ( select parentid
      from children c
      group by parentid
      having min(id) = max(id)          -- you could use instead:  count(*)=1  here
         and min(firstname) like 'tom'
    ) c
    on c.parentid = p.id ;


Cannot log in with default user on Mac 10.10

Just try your normal user account.
bash# psql postgres.
Depending on how you installed PostgreSQL (homebrew, ports or Postgres.app) the default account is set to your account instead of a seperate one. Beside this, consider to update to 9.3.11


How to locate all the tables that have a field named username

I found it...

SELECT table_name, column_name, data_type, ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'dbname' AND column_name LIKE '%username%';


Fragmentation with PXC setup using an ordered UUID as PK

Good.  You understand the problem inherit in UUIDs, and understand how to "fix" it.

Let's start from the other end...  Since you are keeping the data for 2 years, let's start with partitioning by date.  I don't care whether the data is INT, TIMESTAMP, DATETIME, BIGINT, or a rearranged V1 UUID.  (However, you should probably use an date that matches your application.)

PARTITION by the date into 24 partitions.  Each month, DROP the oldest partition and REORGANIZE the future partition into future and nextmonth.  See my blog for more details.

OK, that solves the problem you have not yet encountered -- the ugly overhead of deleting a million rows a month.

But, your concern was about ordering of the clock.  It is not really a problem.  And it is not worth chasing.  Even if an INSERT is a second late -- or even an hour late -- it will make very little difference in the overall performance of the inserts.

Sure, the 'next' row onto the 'end' of a table is well optimized.  But inserting it somewhere in the 'last' block, or even last 100 blocks, is efficient enough.  At best, InnoDB keeps blocks not quite full.  At steadystate with a lot of churn, a BTree is 69% full.  For "Point queries" this number is mostly irrelevant.  For range scans, it has some impact.  I suggest that preventing fragmentation is more costly than ignoring it.

Even with just AUTO_INCREMENT, you cannot be assured that rows will be inserted visible in order!  Think about what happens with multiple threads doing:

BEGIN;
INSERT ... -- the next AUTO_INCREMENT value is grabbed now
...        -- take a variable amount of time
COMMIT;    -- only now is the AI visible to the rest of the world.

So, give up on strict ordering.


I need to figure out the last time that a function was used by SQL Server. How can I do this?

You can use Change Data Capture or Change tracking to analyse the required changes here

You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions

Also,

You can get a possible idea as mentioned here:using below query

SELECT usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction')

So basically you have to find a proper way to analyse the usage.


Returning information in a nested select in the outermost select

This is what JOINs are for:

SELECT  A.info1,
        A.info2,
        B.team
FROM TableA A
INNER JOIN TableB B
    ON A.id = B.xid
WHERE B.userid = 3
AND B.active=1
;


Intermittent "access denied" (1045) message from MySQL

I've experienced problems like this before. It seems to happen when you don't exactly follow the instructions for creating users. There are normally two solutions - perform the operation as root. The other solution is to (re)create your user carefully - but if this is one-off data loading step, then root should be fine.


How can I 'further' improve my table and queries

You don't need the nationality in the outer query.  Remove it, then:

INDEX(tnx_id, sku)  -- instead of just INDEX(txn_id)

That will let both queries be 'covered', as to be indicated by Using index in the EXPLAIN.

Won't it be 'correct' to include the date range in the inner query, too?  That would beg for adding INDEX(sku, tdate)

I don't see why the outer join to products failed to use the index.  Please change suitable fields to NOT NULL and be consistent about datatypes.  Shrink the INTs where practical, and add UNSIGNED where appropriate.  But make sure they are consistent across JOINs.

The 60M was to get you away from "swapping".  Can you see how much RAM is still free, and that you are doing no swapping?  If the is still some RAM, give part of it to the buffer_pool.

Is there a 1:1 relationship between id and trx_id?  If so, there are several more changes to suggest.


I developed a query,but it fetching those record where asd.sal_ID is
null,but i want to fetch all employee from tbl_Employee_Master
table,when ever this query is run'

The asd.sal_ID IS NULL criteria in the WHERE clause will exclude all employees with non-null deductions. Move that criteria the the ON clause of the allowance deduction LEFT JOIN to include allowance deduction rows with NULL Sal_ID values without excluding employees with non-null values too:
SELECT
      M.Emp_ID
    , M.EMp_Name
    , M.EMP_Salary
    , D.Designation_Name AS Designation
    , SUM(CASE T.DA_Name  WHEN 'Loan' THEN (ASD.assign_amt) ELSE 0 END) Loan
    , SUM(CASE T.DA_Name  WHEN 'Advance' THEN (ASD.assign_amt) ELSE 0 END) Advance
    , SUM(CASE T.DA_Name  WHEN 'Mobile' THEN (ASD.assign_amt) ELSE 0 END) Mobile
    , SUM(CASE T.DA_Name  WHEN 'Conveyance' THEN (ASD.assign_amt) ELSE 0 END) Conveyance
    , SUM(CASE T.DA_Name  WHEN 'Medical' THEN (ASD.assign_amt) ELSE 0 END) Medical
    , SUM(CASE T.DA_Name  WHEN 'Rent' THEN (ASD.assign_amt) ELSE 0 END) House_Rent
    , ISNULL(M.emp_OT,0) AS emp_OT
    , 0 AS 'txtpresent'
    , 0 AS 'txtabsent'
    , 0 AS 'txtsalary'
    , 0 AS 'lbOT_Amount'
    , 0 AS 'txthour'
    , ASD.Sal_ID
FROM #tbl_Employee_Master M
INNER JOIN #tbl_designation D ON D.Designation_ID=M.Designation_ID
LEFT JOIN #tbl_assign_Allowance_Deduction ASD ON asd.Emp_ID=M.Emp_ID AND ASD.sal_ID IS NULL
LEFT JOIN #tbl_Type_allowance_Deduction T ON T.DA_ID=ASD.DA_ID
GROUP BY
      M.Emp_ID
    , M.EMp_Name
    , M.EMP_Salary
    , D.Designation_Name
    , M.emp_OT
    , ASD.Sal_ID;


Replicating a Table Structure With no Data Using Merge Replication

You can use the command sp_addscriptexec to run an ad-hoc script on all the subscribers.


Oracle DBLink with username and password

No, there isn't.

Database links created without username and password use authentication data of your current session.

Just create another database link with username + password included.


How to allow connection for edb database in Postgres

With PostgreSQL in general (not EDB specifically) you would do:

stop the PostgreSQL server
start postgres  in a shell in single user mode.
UPDATE pg_database SET datallowconn=true WHERE ... [to undo the error]. Be aware that template0 should normally be left with datallowconn=false.
Type Ctrl+D to quit the single user mode.
start PostgreSQL normally.


How do I add an existing shared data source and dataset to my SSDT/Visual Studio SSRS Project?

Here is an explanation retrieved from various MSDN and TechNet articles.

Prerequisites are

  To create a shared data source or modify its properties, you must have Manage data sources permissions on the report server. If the report server runs in native mode, you can use Report Manager to create and configure the shared data source. If the report server runs in SharePoint integrated mode, you can use the application pages on a SharePoint site. For any report server regardless of its mode, you can create a shared data source in Report Designer and then publish it to a target server.

To import an existing data source in Report Designer

In Solution Explorer, right-click the Shared Data Sources folder in the report server project, and then click Add Existing Item. The Add Existing Item dialog box opens.
Navigate to an existing Report Definition Shared data source (rds) file and then click Open.
Click OK.

Adding Report Data

In Report Builder or Report Designer, you can add data in the following ways.

Add embedded datasets based on shared data sources.
Add embedded datasets based on embedded data sources.

A detailed explanation can be found here: Report Embedded Datasets and Shared Datasets (Report Builder and SSRS)

There are some limitation in Report Designer:

  In Report Designer, you can create shared datasets as part of a report project, and control whether to deploy them to a report server. You cannot browse to a report server and select a shared dataset to add to your report.

List of references:

Create, Modify, and Delete Shared Data Sources (SSRS)
Add and Verify a Data Connection (Report Builder and SSRS)
Report Datasets (SSRS)
Report Embedded Datasets and Shared Datasets (Report Builder and SSRS)


Reason for failed query plan executions in SQL Server

The best way I know to solve this is to set up Extended Events. Specifically you can capture the error_reported event. Use Causality Tracking along with batch/rpc starting and batch/rpc completed to capture every bit of data about what's happening with the queries. Be sure to use some good filtering because this could generate quite a lot of data.


Error 833 is not an 'error' per-se. It is reported because your IO subsystem is slow. the size of the LDF has probably nothing to do with it. It can be caused by many factors:

an inherently slow drive
a flaky hardware that masks errors with many retries (typical of consumer grade drives)
the drive is experience more load that it can bear

You need to investigate by looking at the drive load (Perfmon Physical Disk Object), at any OS reported errors for it (Event Viewer), check the drive SMART status. Also investigate the SQL IO load and how much goes to the problem drive, see sys.dm_io_virtual_file_stats


Is it possible to restore a database from 10.50.4033 in 10.00.1600

Those look like MS SQL Server version numbers, not Oracle ones, 10.50.<patch_level> and 10.00.<patch_level> being 2008r2 and 2008 respectively.

2008 and 2008r2 are considered separate versions (much like 2008r2 and 2012) in most considerations including this one and backups are not backwardly compatible between versions, so no you can not directly restore a backup from 10.50.* on 10.00.* (though you can the other way around).

You will need to recreate the structure and then transfer the data.

If you have your structure in proper source control then remaking it should be easy, if not you can use the generate scripts function within SSMS and run the result on the older instance.

For the data there are numerous options and which will be suitable depends on may things, most importantly being can you connect to both database at once (in which case the data export/import in SSMS may be sufficient) and better still can they see each other over the network (in which case transferring using a linked server setup may be much more efficient). Listing every option available and their pros & cons would be time consuming (there are many sources out there that already do that), but if you update your question with further detail we can give more specific help.


I am trying to achieve a Disaster recovery for Mongo DB

I've discovered MongoShake (https://github.com/alibaba/MongoShake) which is an open source Golang application which does cluster to cluster replication and has been made by Alibaba.
The documentation is focused on its internal work/behaviour, and can be discouraging but you have to look at the example configuration file instead which describe each configuration options (https://github.com/alibaba/MongoShake/blob/develop/conf/collector.conf).
In the majority of the cases, as far as I understood it (not tried it yet), you have 2 options to configure:

mongo_urls which tells where is/are the MongoDB source(s)
tunnel.address which tells to where the data should be synced

This allows you to replicate, asynchronously, a MongoDB cluster from one datacenter to another one.
There is also a 48 minutes tutorial video on YouTube which explains all the replication you can achieve with MongoShake, and also shows a demo of a MongoDB cluster in Singapour replicating to Jakarta, Indonesia.
This solution is quite young, and there is no English blog article showing it, nor official Docker image on Docker Hub (only 3 images without any documentation ).
To me it look promising and needs more write/test/love to rise.


Try putting the USE db_name manually

echo "USE db_name" > output.sql
mysqldump -u root db_name tbl_1 tbl_2 tbl_3 >> output.sql

I can see the wisdom of not mixing the USE command with select mysqldumping of tables. You do not want to risk creating database with the name of a production database. You also do not want the developer to become aware of not the database that will be the target. In this way, the mysqldump, in itself, can never be blamed for overwriting target tables.


How to (optimally) get random sample of (a.id, b.id) pairs from two tables (a, b)?

The best solution depends on the exact definition of your setup. For the example setup it's trivial:

Serial integer columns without gaps.

SELECT DISTINCT
           1 + trunc(random() * 100)::int AS a_id
     , 10001 + trunc(random() * 100)::int AS b_id
FROM   generate_series(1, 1100) g  -- enough excess to make up for possible dupes
LIMIT  1000;  -- only take 1000

The only interesting question: how to fold dupes efficiently. The solution: let Postgres decide. Simply use DISTINCT.
We don't even need to involve the tables at all. Super fast.
Note that random() generates (per documentation):

random value in the range 0.0 <= x < 1.0

Hence 1 + trunc(random() * 100)::int to cover ID numbers between 1 and 100 exactly.
Actual setup?
You need be be more specific about your actual setup. Let's assume there is at least a payload column in each of your tables, not just ID columns.
CREATE TABLE a(a_id integer PRIMARY KEY, a text);
CREATE TABLE b(b_id integer PRIMARY KEY, b text);

INSERT INTO a(a_id, a)
SELECT g, 't' || g FROM generate_series(    1,   100) g;
INSERT INTO b(b_id, b)
SELECT g, 't' || g FROM generate_series(10001, 10100) g;

Query:
SELECT a.a_id, a.a, b.b_id, b.b
FROM  (
    SELECT DISTINCT
               1 + trunc(random() * 100)::int AS a_id  -- cover *whole* key space
         , 10001 + trunc(random() * 100)::int AS b_id  -- maybe add reserve for new rows
    FROM   generate_series(1, 1100) g
    LIMIT  1000
    ) ra
JOIN   a USING (a_id)
JOIN   b USING (b_id);

Truly random, very fast and almost independent of the actual table size.
All you need is indexes on a(a_id) and b(b_id). Or possibly multicolumn indexes to allow index-only scans.

The solution also works for a few gaps from skipped nextval() calls, as long as there are not much more gaps than islands, it's still very cheap to generate enough combinations to cover losses by gaps. (Much cheaper than working with a Cartesian product of big tables or sorting whole big tables with ORDER BY random() anyway.) Just be sure to generate enough combinations.
SELECT a.a_id, a.a, b.b_id, b.b
FROM  (
    SELECT DISTINCT
               1 + trunc(random() * 100)::int AS a_id
         , 10001 + trunc(random() * 100)::int AS b_id
    FROM   generate_series(1, 1100) g  -- enough to cover dupes *and* gaps
    ) ra
JOIN   a USING (a_id)
JOIN   b USING (b_id)
LIMIT  1000;  -- LIMIT moves to outer query to cover gaps

With more than a few gaps, start with a number of combinations that's enough 95 % of the time and add a recursive step to add more rows if you should fall short. There is a recipe for this solution (for a single table) in the related answer. Also more explanation and variations:

Best way to select random rows PostgreSQL


SQL Server execution plan is using PK instead of index

None of your indexes are good for this query

IX_Company_Id works only for the WHERE
IX_Project_02 has not overlap with any part of the query because the leading columns do not match a JOIN or WHERE

This should be better because it matches the JOIN and the WHERE although iIt does rely on a matching (Company_Id, Unit_Id) index on Unit

CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project]
(
    [Company_Id] , --can swap these 2 to see what happens if no index on Unit
    [Unit_Id] ,

    [UTE_Id] ,

    ProcessID

)
INCLUDE
    CalculateSequencialProject,
    CreateDate,
    OpeningDate,
    ForecastClosingDate,
    RealClosingDate,
    ProjectStatus


First, some definitions:

A clustered index is the data. When you see the size of a clustered index, that's the size of the table itself.
A heap is a table that does not contain a clustered index.
A non-clustered index contains the clustered index (or a row identifier if there is no clustered index), so that it can map back to the right row in the table, plus whatever column(s) you're indexing.

It's quite possible for the total size of non-clustered indexes to be larger than the table itself.

Let's answer your questions.

  Is index size in orders table is cumulative of 9 indexes? or Its a single clustered index(as shown in below Fig.2)

This is the cumulative total of all non-clustered indexes (keeping in mind that each of them contains a copy of the clustered index).

  Each index partition shown below in Fig.2 has 830 rows is that mean to have duplicate page data on disk or memory?

Maybe. SQL Server does not care if you create duplicate indexes, but just having the same number of rows does not make it a duplicate. Read more here about determining if an index is a duplicate.

The reason there are 830 rows in each index, is because the table has 830 rows, so each index contains the same number of rows, each of which maps back to the row in the clustered index (depending on which column(s) are indexed).

  Does Non-Clustered index occupy space on disk or only Clustered Index?

All indexes consume space. A clustered index is the data in the table, and each non-clustered index is a copy of whatever data that it is indexing.

  How does fill factor effect on storage of these clustered and non-clustered indexes.

This means that a percentage of space is left on each 8KB data page, to try reducing fragmentation. See link below about data pages.

  How does index occupy size on disk(any references links/books/blog)?

The index is a data page like any other page in the database. It has a header, a slot array, and data in the middle that points to other data pages, and eventually gets to the leaf node where the data sits. You can read more about data pages here. Once you understand that, you can read about index pages here.


Table not analyzed after partition has been split

Use the proper GRANULARITY option.

CREATE TABLE quarterly_regional_sales
      (deptno number, item_no varchar2(20),
       txn_date date, txn_amount number, state varchar2(2))
  PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
         )
      );

SQL> exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SQL>

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'QUARTERLY_REGIONAL_SALES';

TABLE_NAME                     SUBPARTITION_NAME                NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHWEST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHCENTRAL                    0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHCENTRAL                    0 2019-07-04 11:46:40

Test1:

SQL> exec DBMS_STATS.SET_PARAM ('GRANULARITY', 'GLOBAL');

PL/SQL procedure successfully completed.

SQL> alter table QUARTERLY_REGIONAL_SALES split subpartition q1_1999_northwest into (subpartition q1_1999_northwest_1 values('OR'), subpartition q1_1999_northwest_2);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
  3  WHERE TABLE_NAME = 'QUARTERLY_REGIONAL_SALES';

TABLE_NAME                     SUBPARTITION_NAME                NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_1
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_2
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHWEST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHCENTRAL                    0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHCENTRAL                    0 2019-07-04 11:46:40

7 rows selected.

Test2:

SQL> exec DBMS_STATS.SET_PARAM ('GRANULARITY', 'SUBPARTITION');

PL/SQL procedure successfully completed.

SQL> alter table QUARTERLY_REGIONAL_SALES split subpartition Q1_1999_SOUTHEAST into (subpartition Q1_1999_SOUTHEAST_1 values ('FL'), subpartition Q1_1999_SOUTHEAST_2);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
  3  WHERE TABLE_NAME = 'QUARTERLY_REGIONAL_SALES';

TABLE_NAME                     SUBPARTITION_NAME                NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_1                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_2                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST_1                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST_2                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHWEST                       0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHEAST                       0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHCENTRAL                    0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHCENTRAL                    0 2019-07-04 11:52:31

8 rows selected.

SQL>


Design best practices for last tracking with indexed view

As you already know, you can't use a MAX() aggregate in an indexed view. In any case don't fool yourself into believing that creating an indexed view is a magical performance power button. Depending on your workload, maintaining the indexed view may cost you more performance than you're gaining from the queries against it.

A couple of alternatives might be:

(1) create a non-clustered index on dbo.TbDocActions(DocID, ID DESC) or drop the existing clustered index and change it to DocID, ID DESC. (If ID is an identity column, you've probably also set it up to be the primary key and it will be clustered by default, but that doesn't have to be the case.)

(2) maintain a separate table with the DocID as the primary key, and keep the highest ID up to date using triggers (or a stored procedure if you are constraining writes to stored procedures only). You will have to have logic for all three DML types (insert, update and delete) because, in theory, any of these could affect the MAX calculation. (Less risk, obviously, if both ID columns are IDENTITY.) You could also add a column to the main table and keep that up to date the same way.

Note that the same caveats apply - adding indexes or maintaining values in other tables might benefit this specific query, but they are not magic - they might affect other parts of your workload. You should always take your entire business cycle into consideration when thinking about "fixing" any single query...


Subquery returned more than 1 value. please help. i beg for mercy

You didn't specify which database you were using, but in most databases, you can use a CASE expression inside the COUNT aggregate function to accomplish your goal:

select FORMAT(fecha, 'yyyy-MM') as FECHAS,
       COUNT(*) as TOTAL,
       COUNT(CASE WHEN tipo = 'C' THEN 'X' END) as CREDITO,
       COUNT(CASE WHEN tipo = 'D' THEN 'X' END) as DEBITO
from PRUEBA
where fecha >= '01-08-2014'
group by FORMAT(fecha, 'yyyy-MM')


How to restore a database from a newer version of SQL Server

Sorry, but backup and restore won't allow you to restore that way - you can only restore to a higher version (backwards compatibility) rather than a lower version (forwards compatibility?). You'll need to export and import the data, or use scripts, or some other way of transferring everything.


Think you can, of course stop all jobs making sure that all logs have been applied (restored) on the secondary to make sure it stays in sync. It sounds like the jobs you will be changing are the backup job on the primary and the copy job on the secondary.


A common query rewrite that helps with OR predicates looks like this:

SELECT tTitulo.CdContaCartao,
       tTitulo.CdStatus,
       MAX(DiariaMaxima)
INTO   #DiariaMaxima
FROM   Sistema.Titulo AS tTitulo
CROSS APPLY
       (
           SELECT MAX(DiariaMaxima)
               FROM (
               SELECT     tTIPM.DtDiaria
               FROM       Sistema.TaxaIndice_PagamentoMensal_ContaCartao AS tTIPM_Cartao
               INNER JOIN Sistema.TaxaIndice_PagamentoMensal AS tTIPM
                   ON tTIPM.CdTaxaIndice_PagamentoMensal = tTIPM_Cartao.CdTaxaIndice_PagamentoMensal
               WHERE      tTIPM_Cartao.CdContaCartao = tTitulo.CdContaCartao

               UNION ALL

               SELECT     tTIPM.DtDiaria
                          FROM       Sistema.TaxaIndice_PagamentoMensal_ContaCartao AS tTIPM_Cartao
                          INNER JOIN Sistema.TaxaIndice_PagamentoMensal AS tTIPM
                              ON tTIPM.CdTaxaIndice_PagamentoMensal = tTIPM_Cartao.CdTaxaIndice_PagamentoMensal
                          WHERE      tTIPM_Cartao.CdContaCartao = tTitulo.CdContaCartao_Visa
               ) AS x (DiariaMaxima)
           ) AS DiariaMaxima (DiariaMaxima)

Apply is not always the best method for this, though I've often had success with it over using a regular derived JOIN.

Some background on similar problems here:

Analysing A Query Plan
How to Optimise Query


Preventing users from accessing DB while indexes are being rebuilt

USE master;
GO
ALTER DATABASE YourDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
USE YourDatabase;
--Run index maint scripts...
GO
ALTER DATABASE YourDatabase
SET MULTI_USER;
GO


The DBA StackExchange was made to address technical and strategic aspects of DBMS products and deployment of such into infrastructures. When it comes to dealing with difficult DBAs, there is no black-and-white answer to offer. In your particular case, I will make an exception.
I answered two interesting posts four(4) years ago

May 02, 2011 : How could DBAs be more 'programmer friendly'?
May 24, 2011 : Why is Database Administration so hard?

I discussed 4 ways people have become DBAs. Somewhere, over the course of time, your head DBA has lost touch with what Developers and Developer/DBAs are doing and their ever-changing needs. In fact, times have changed and the developer technologies of today may no longer be in the head DBA's wheelhouse.
You need to comply with the standards put in place until productivity slows down considerably. OH, I AM SORRY, THAT'S WHATS HAPPENING NOW !!!
Guess what? You must get a third-party involved (his boss or his boss's boss) and thoroughly prove that the standards are interfering with product development. In other words, your Agile Development has become Fragile Development.
I recently saw a picture in LinkedIn that might reflect what you are going through

Have all your ducks in a row. Do all you homework. Have all your facts and figures down in writing. Think of it in terms of Six Sigma: You want to streamline process development while eliminating wasted efforts.
FINAL WORDS
Mind you, you should never approach this with a complaining spirit. You need to present this situation in such a way that the you want the head DBA on your side instead of off your back. If the head DBA is in your company, he/she brings something to the table in the eyes of your company. You don't want to diminish the head DBA's dignity. You must find a way to appeal to it. Notwithstanding, you must also let the current productivity speak for you and the head DBA (without casting blame). In the end, you will find the solution that creates a team atmosphere which includes the head DBA or the company may step in with standards even harder to deal with. Now, GO DO YOUR HOMEWORK ...
Please let us know how it turns out !!!


Is it OK to Change System Locale after MSSQL2012 installation

In general you should be ok. The only area that I am aware of that could be affected is SQLCLR, in which case the following conditions must be true:

(
The LCID of the OS does not match the LCID of the default Collation of the Database where the Assembly is loaded,

OR
The default Collation of the Database where the Assembly is loaded is either a binary Collation, or is any combination of the following: case-sensitive, accent-insensitive, width-sensitive, or Kana-type-sensitive
),

AND
custom SQLCLR code is being used
SqlString is being used as an input parameter type
The SqlString parameter is being concatenated inline with a string while not using either the Value property or the ToString() method (i.e. "some string" + SqlStringInputParam;)
Or, possibly using Regular Expressions / Regex.

To be clear, this issue relates to the default Collation of the Database in which the Assembly is loaded, not the Instance- / Server- level Collation, nor even the Collation of any string column or any string literal or variable with a Collation set via the COLLATE keyword.

If your Database has a default Collation of SQL_Latin1_General_CP1_CI_AS because that is what the Instance-level Collation is, then neither # 1 nor # 2 above are true. And in that case, IF there is any SQLCLR code in that Database that matches items 4 - 6 above, then you wouldn't see any problem. BUT, changing the Locale of the OS would make item # 1 above true, in which case if there is any SQLCLR code that matches item 4 - 6 above, then that code will start to throw "Collation mismatch" exceptions.

Admittedly there is a fairly low probability of running into this. But, it has happened, so it's best to be aware of it.


The way you handle null or missing data depends on where you have your missing data.

If you have missing data in fact tables in your measure columns you should probably leave them as null so they don't get considered in the NONEMPTYCROSSJOIN queries sent by most reporting applications.

If you have missing data in your foreign keys in your fact table (i.e. when your ETL process looks up the surrogate keys in your dimension table but the related row doesn't exist in your dimension) you need to decide based on your user requirements, but the general approach is to convert them to blank, 'unknown, invalid or whatever your requirements are.

If you have blank attribute values you should probably leave it as blank as to not confuse the users.

One thing to keep in mind specific to SSAS is that using error configurations in SSAS to let SSAS handle missing dimension keys has a huge impact on processing performance so you should probably solve that in your ETL process. The (unknown) dimension member is also very confusing for your end users as they don't know what it means when seen on a report and often forget to include it in filters (they have to select the unknown member on every dimension or they will be missing data).

As usual Kimball has a best practice on handling missing data in your dimensional model: Design Tip #128 Selecting Default Values for Nulls

So in your case I wouldn't convert them to NULL but to something meaningful when it comes to dimension keys in your fact table (just insert a row in your dimension with a surrogate key and a meaningful value such as -1, 'missing') and leave them as null or convert them to null in fact table measures so they play well with aggregates and NONEMPTY queries.


I finally solved this using this scheme (not as simple as firstly expected):

SELECT ST_X(geom) as value, ST_Y(geom) as datetime from (
    select (ST_DumpPoints(simplifiedLine)).* as g1 from (
        SELECT ST_Simplify(ST_MakeLine(the_geom.resulted), 15) as simplifiedLine FROM (
            SELECT ST_MakePoint(value, extract(EPOCH FROM datetime)) as resulted FROM table
            ORDER BY datetime ASC
        ) as the_geom
    ) as g
) as pt

Hope that will help others.


When using Oracle Data Guard with TDPO, what is the best strategy to
  ensure backups continue after failover/switchover?

Use RMAN catalog, because without a catalog, backups taken on one site do not appear in the controlfile of the database on the other site. You need a centralized catalog for backups in a Data Guard configuration.

Use role-based services, and TNS entries with connect-time failover, using these services. Use these TNS entries for logging in to the database when performing backups. This way you always log in to the primary/standby database, and you do not need to check for the role of the database.

  TSM/TDPO uses a feature called datamove which makes sure that backups
  from primary and standby are moved to one TSM node. (TSM node A
  (primary) and B (standby) move there Backups to C. Restore will use
  C).

The database/RMAN layer has nothing to do with. Just configure TSM and TDPO properly, and this will be transparent for the database, always using the same tdpo.opt file.

  The RMAN scripts called by TDPO need a logic to prevent backups are
  taken from both (primary and standby). This could be done by querying
  the role of the database.

No, see above, role-based services, connect-time failover TNS entries.


Why adding SQL Server user to "Perform volume maintenance tasks" improves the speed of database resizing so much?

That's because of Instant File Initialization.  In short, SQL Server can take advantage of this privilege for database data files (not transaction log files).  What this means is that SQL Server does not have to zero out the data file(s) when initializing.

Without the "Perform volume maintenance tasks" privilege granted to the SQL Service account, upon the database creation SQL Server needs to zero out "test.mdf" and "test_log.ldf".

With the "Perform volume maintenance tasks" privilege permission set, SQL Server only needs to zero out "test_log.ldf".  A significantly less overhead, therefore minimized duration for the CREATE DATABASE in your testing.

References
How and Why to Enable Instant File Initialization
Instant Initialization – What, Why and How?


Survey database design: associate an answer to a user

Survey Database Schema.
This is a real classic, done by thousands.  They always seems 'fairly simple' to start with but to be good it's actually pretty complex. To do this in Rails I would use the model shown in the attached diagram.  I'm sure it seems way over complicated for some, but once you've built a few of these, over the years, you realize that most of the design decisions are very classic patterns, best addressed by a dynamic flexible data structure at the outset.
More details below:

Table details for key tables

answers
The answers table is critical as it captures the actual responses by users.
You'll notice that answers links to question_options, not questions.  This is intentional.
input_types
input_types are the types of questions.  Each question can only be of 1 type, e.g. all radio dials, all text field(s), etc.  Use additional questions for when there are (say) 5 radio-dials and 1 check box for an "include?" option or some such combination.  Label the two questions in the users view as one but internally have two questions, one for the radio-dials, one for the check box.  The checkbox will have a group of 1 in this case.
option_groups
option_groups and option_choices let you build 'common' groups.
One example, in a real estate application there might be the question 'How old is the property?'.
The answers might be desired in the ranges:
1-5
6-10
10-25
25-100
100+
Then, for example, if there is a question about the adjoining property age, then the survey will want to 'reuse' the above ranges, so that same option_group and options get used.
units_of_measure
units_of_measure is as it sounds.  Whether it's inches, cups, pixels, bricks or whatever, you can define it once here.
FYI: Although generic in nature, one can create an application on top of this, and this schema is well-suited to the Ruby On Rails framework with conventions such as "id" for the primary key for each table.  Also the relationships are all simple one_to_many's with no many_to_many or has_many throughs needed.  I would probably add has_many :throughs and/or :delegates though to get things like survey_name from an individual answer easily without.multiple.chaining.


MySQL: "CREATE DATABASE mydb" command hangs

A short time afterwards I noticed the database was created, and further commands CREATE DATABASE succeeded.  Most likely, the issue was caused by a database lock that was eventually released later.


how do I get extra fields with in a column with calculations

Generated columns(MySQL/MariaDB) make these fields possible.

Depending on what you are doing the 1-31 columns for days may not make sense. It will be hard to do partial month retrievals. Your queries appear to be month totals so it might be ok.


Is there a way to force Deferred Name Resolution even if the table exists when creating a stored procedure?

No.
I feel really guilty just typing that, but no, sadly.  That's the first time I've heard of this use case, and it makes perfect sense.  Best to submit a request for it on https://feedback.azure.com/forums/908035-sql-server and your grandchildren will be able to do it. ;-)


How to upgrade SQL Server 2008 to SQL Server 2014 servers with mirroring configured

Your research is correct you have to first apply service pack on mirror instance and then failover the mirroring so as to make this mirror instance as primary. Now you have to apply patch on instance which is nor mirror(which was previously primary). I have done it many times and I do not see much Gotchas.

  and also how should I proceed with this upgrade without causing an outage?

There will always be an outage you just cannot avoid it, when you failover SQL Server have to be stopped on current instance and failed over and started on other so you can see outage is just unavoidable.

For your reference

Minimizing downtime when patching instance which is mirrored
Upgrade Mirrored instance
Steps to Apply a Service Pack or Patch to Mirrored SQL Server Databases

Start reading the first 2 articles and then the third one has step by step method with screenshots.

The high level steps would be

Remove the witness server from mirroring(if you hjave configured witness)
Apply the service pack to the mirror server
Failover the mirrored databases over to make it principal
Apply the service pack to the former principle server (which is now a mirror)
Apply the service pack to the witness server
Add the witness server back to the mirroring configuration


How can i tune below proc either by changing query or adding index

The first thing I would do is avoid using table variables in heavy SQL operations. You should use a temp table instead. Either initially or select your table variable @table1 into a temp table then use that temp table in your update statement.
Table variables are historically known to be bottlenecks because statistics aren't maintained on them, and even with Microsoft's improvements to them in SQL 2014 they can still perform poorly. Here's a good Brent Ozar article about them: https://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/
You might also benefit from using the force seek hint on "anotherbg table". But start with the above change first.
For reference: http://nisalbi.blogspot.com/2015/11/what-is-forceseek-and-forcescan-table.html?m=1
And MS Docs on Table Hints: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
Typically query hints should only be used in very situational cases by people with experience, but I have found that with larger tables, sometimes the SQL Engine attempts to do an index scan when an index seek truly is a better choice. (Updating the table statistics on "anotherbg table" might more naturally fix your issue too.)
So it's situational and you'll need to test but it's possible a force seek index hint will help.
This is what your update query would look like with the index hint:
UPDATE bigtable
    SET value1 = 33, statusdate = @today
from
    bigtable t
    INNER JOIN anotherbigtable t1 WITH (FORCESEEK) on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate <= @parameter2
    AND t.value1=32
    AND t1.payid =2


what does a "/" mean after a sql statement

/ is the Oracle equivalent of SQL Server's GO...

It would seem that your ERP supports multiple RDBMS platforms, and you're looking at a script originally written for an Oracle database.

As it is currently written, you'll probably get a compilation error on SQL Server. Trade that / for a GO and you'll be in business (assuming the rest of the script is correct, of course).


You can keep the following in mind when caring about updating statistics (copied from Rebuilding Indexes vs. Updating Statistics (Benjamin Nevarez)

By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

Rebuilding an index, for example by using ALTER INDEX … REBUILD will also update index statistics with the equivalent of using WITH FULLSCAN unless the table is partitioned, in which case the statistics are only sampled (applies to SQL Server 2012 and later).

Statistics that were manually created using CREATE STATISTICS are not updated by any ALTER INDEX ... REBUILD operation, including ALTER TABLE ... REBUILD.  ALTER TABLE ... REBUILD does update statistics for the clustered index, if one is defined on the table being rebuilt.

Reorganizing an index, for example using ALTER INDEX … REORGANIZE does not update any statistics.

The short answer is that you need to use UPDATE STATISTICS to update column statistics and that an index rebuild will update only index statistics.  You can force an update to all statistics on a table, including index-stats and manually created stats, with the UPDATE STATISTICS (tablename) WITH FULLSCAN; syntax.
The following code illustrates the rules encapsulated above:
First, we'll create a table with a couple of columns, and a clustered index:
USE tempdb;

IF OBJECT_ID(N'dbo.SomeTable', N'U') IS NOT NULL
DROP TABLE dbo.SomeTable;

CREATE TABLE dbo.SomeTable
(
    rn int NOT NULL IDENTITY(1,1)
        CONSTRAINT pk
        PRIMARY KEY NONCLUSTERED
    , i int NOT NULL INDEX i
    , d sysname NOT NULL
) ON [PRIMARY] WITH (DATA_COMPRESSION = NONE);

CREATE UNIQUE CLUSTERED INDEX cx ON dbo.SomeTable (i, d);

CREATE STATISTICS d ON dbo.SomeTable (d) WITH FULLSCAN;

INSERT INTO dbo.SomeTable (d, i)
SELECT c1.name, c1.id
FROM sys.syscolumns c1;

This query shows the date when each stats object was last updated:
SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

The results show no updates have yet taken place, which is correct since we just created the table:
╔═══════════════╦═══════════╦═══════════╗
║  ObjectName   ║ StatsName ║ StatsDate ║
╠═══════════════╬═══════════╬═══════════╣
║ dbo.SomeTable ║ cx        ║ NULL      ║
║ dbo.SomeTable ║ i         ║ NULL      ║
║ dbo.SomeTable ║ pk        ║ NULL      ║
║ dbo.SomeTable ║ d         ║ NULL      ║
╚═══════════════╩═══════════╩═══════════╝
Let's rebuild the entire table, and see if that updates stats:
ALTER TABLE dbo.SomeTable REBUILD;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

╔═══════════════╦═══════════╦═════════════════════════╗
║  ObjectName   ║ StatsName ║        StatsDate        ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.590 ║
║ dbo.SomeTable ║ i         ║ NULL                    ║
║ dbo.SomeTable ║ pk        ║ NULL                    ║
║ dbo.SomeTable ║ d         ║ NULL                    ║
╚═══════════════╩═══════════╩═════════════════════════╝

The results show only the clustered index stats were updated.
Next, we perform a discrete UPDATE STATS operation:
UPDATE STATISTICS dbo.SomeTable(d) WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

As you can see, we've just updated the stats on the d column:
╔═══════════════╦═══════════╦═════════════════════════╗
║  ObjectName   ║ StatsName ║        StatsDate        ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.590 ║
║ dbo.SomeTable ║ i         ║ NULL                    ║
║ dbo.SomeTable ║ pk        ║ NULL                    ║
║ dbo.SomeTable ║ d         ║ 2018-09-17 14:09:13.597 ║
╚═══════════════╩═══════════╩═════════════════════════╝
Now, we'll update stats on the entire table:
UPDATE STATISTICS dbo.SomeTable WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

╔═══════════════╦═══════════╦═════════════════════════╗
║  ObjectName   ║ StatsName ║        StatsDate        ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.600 ║
║ dbo.SomeTable ║ i         ║ 2018-09-17 14:09:13.600 ║
║ dbo.SomeTable ║ pk        ║ 2018-09-17 14:09:13.603 ║
║ dbo.SomeTable ║ d         ║ 2018-09-17 14:09:13.607 ║
╚═══════════════╩═══════════╩═════════════════════════╝
As you can see, the only way to be certain all the stats are updated is to either update each one manually, or to update the entire table with UPDATE STATISTICS (table);.


How to send Email from a SQL Job with results of multiple select statements

You are basically sending 1GB file from sql server which is max. You can reconfigure the default using

  exec msdb.dbo.sysmail_configure_sp 'MaxFileSize','2000000'-- 2GB

I would say, use bcp out the file, compress it using zip or 7zip and then send using powershell or ssis.


Installing SQL Server version 10.50.4000 is failing

I got it working. This is how I got it working... I used a CD.

I don't know why it worked. I didn't even have to use a key, and it was the same version I downloaded multiple times from the Microsoft website...


Can't create an Availability Group with Powershell when instance uses non-standard port

Change $SqlServerPrimName = "SERVER\INSTANCE,Port" to $SqlServerPrimName = "SERVER\INSTANCE" and change the port for the instance afterwards.

$ComputerName = gc env:computername
$SqlServerPrimName = "SERVER\INSTANCE"
$SQLServerPort = 1433
$wmi = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $computerName
$wmi.ServerInstances["$SqlServerPrimName"].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].value = "$portNumber"
$wmi.ServerInstances["$SqlServerPrimName"].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpDynamicPorts"].value = [System.String]::Empty
#We need to commit the changes by calling the Alter method
$wmi.ServerInstances["$SqlServerPrimName"].ServerProtocols["Tcp"].Alter()


The ORA-01403 seems to indicate that you have a System Event Trigger for DROP USER.

You'll need to hunt down the appropriate trigger.

SELECT *
from dba_triggers
where base_object_type like 'DATABASE%'
and triggering_event like 'DROP%'
and status='ENABLED'
;

If that doesn't work, then I recommend that you open a ticket with Oracle Support.


SELECT to_number(replace(DESCRIPTION, rtrim(DESCRIPTION, '0123456789')))
FROM   PART
WHERE  PAR_ID=123
AND    DESCRIPTION LIKE 'CHARGE BATTERIES%';

When there is no number at the end it will result NULL. If you want to have zero then use:

SELECT nvl(to_number(replace(DESCRIPTION, rtrim(DESCRIPTION, '0123456789'))), 0)
FROM   PART
WHERE  PAR_ID=123
AND    DESCRIPTION LIKE 'CHARGE BATTERIES%';


created a user with clusterMonitor and dbOwner role, but db.getRoles() shows enableSharding role

You get that "role" : "enableSharding" for customerdb because of "role" : "dbAdmin". So, that use can shard customerdb, but nothing else.


Is it possible to re-use a query plan from the plan cache from one database in another database?

No

Ok, so that's the answer to your question, but you probably want to know why.

Ok, so why not?

Different databases might have different data, and thus need different plans.

Say you restore the WideWorldImporters database to the same server TWICE (Let's call it WWI_1 and WWI_2). You've got two identical databases. SQL Server could possibly create one plan and use it in queries for both databases.

But the problem is, the instant these two databases come online, thier "sameness" forks. They can be altered independently. Even if they maintain the same schema, their data can change independently. Thus, SQL Server must consider the databases independently when compiling plans. WWI_1 could have different statistics from WWI_2, which could result in different plans. In order for SQL Server to use the same plan for both databases, SQL Server would need to keep track of the differences after forking--which would be more complicated & more expensive than just compiling/maintaining separate plans.

Let's take a real-world example

Let's pretend you are a software company, and host software for your customers. Every customer gets their own database. In your hosting environment, you might have hundreds of databases on every server, where every database has identical schema, but data unique to each client.

One client has a customer base with 95% California clients. Querying the address table for all customers in California would result in a table scan.

-- For CustomerA this query returns 95% of the table, so it scans
SELECT CustomerID
FROM dbo.Addresses
WHERE StateCode = 'CA';

A different client has a customer base that is evenly distributed across every US state, and has a significant international business as well. A comparatively small percentage of clients are from California. In this case, querying the address table for all customers in California would result in a table seek.

-- For CustomerB this query returns <1% of the table, so it seeks
SELECT CustomerID
FROM dbo.Addresses
WHERE StateCode = 'CA';

Identical queries, on databases with identical schema, with radically different plans due to different statistics. Even if these two databases were both originally restored from the same source backup, SQL Server will need to compile separate plans.

It's not enough that the two queries are 100% identical, and the schema is 100% identical. A reused plan based only on those criteria could be very wrong--and thus SQL Server won't do it.

Could you do it if you really wanted to?

Uh....kind of.

Let's use the same "you are a software company, and host software for your customers" example. You want to force the same plan across every hosted database, regardless of what SQL Server wants to do. You could use a plan guide and apply the same guidance to every single database on the server. This isn't quite the same as "insert [one] query plan into another database's plan cache"... but it would be virtually the same thing.

What about a totally trivial plan?

Something like SELECT COUNT(*) FROM dbo.SomeTable would be simple enough to use the same plan on both two databases, right? No, not even then!

Let's create an example:

Create a sample database
Create a table & populate it with some data

Note the table has a Clustered PK & a non-clustered index

Backup & restore a second copy to the same server

Here's some code to do that:

CREATE DATABASE Sample1;
GO

USE Sample1
GO

CREATE TABLE dbo.SomeTable (
    SomeID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    StuffType int,
    OtherStuff varchar(100),
    INDEX OtherStuff(OtherStuff)
);
GO

SET NOCOUNT ON;
INSERT INTO dbo.SomeTable (StuffType,OtherStuff)
SELECT  object_id%50,
        name
FROM sys.objects;
GO 1000

BACKUP DATABASE Sample1 TO DISK = '/var/opt/mssql/data/Sample1.bak' WITH INIT;

RESTORE DATABASE Sample2 FROM DISK = '/var/opt/mssql/data/Sample1.bak'
    WITH MOVE 'Sample1' TO '/var/opt/mssql/data/Sample2.mdf',
         MOVE 'Sample1_log' TO '/var/opt/mssql/data/Sample2_log.ldf';

Now, let's do that COUNT(*) query. Same plan on both? Yes! On my laptop, it's performing the count by scanning the clustered PK

SQL Server is choosing to scan the PK because it's smaller. The non-clustered index is larger because it's highly fragmented.

Now index maintenance happens on the Sample1 database (but not Sample2). Someone rebuilds the indexes on dbo.SomeTable:

ALTER INDEX ALL ON Sample1.dbo.SomeTable REBUILD;

How does that affect the query plan?

On Sample1, the non-clustered index is now nice & compact. It is smaller than the PK. Scanning the non-clustered index will be the right choice on `Sample1 because it's smaller, fewer IOs, and will be faster.

On Sample2, scanning the PK is the right choice, because it's smaller, fewer IOs, and thus faster.

Identical schemas with identical data, with the second forked from a recent backup of the first. Both of these queries have different execution plans, but both also have the best query plan for their scenario.


What is Fastest way to shrink a datafile?

DBCC SHRINKDATABASE
The fastest way I find to shrink a data file is to first use the SHRINK DATABASE command to see what it takes off the top. It will usually do this quickly--but not always. Keep an eye on blocking locks while you're doing this.
caveat
SHRINKDATABASE is impartial and will attempt indiscriminately attempt to shrink any file in the database. If you don't appreciate this behavior then don't run this one.
DBCC SHRINKFILE
After you've used SHRINKDATABASE you'll need to use SHRINKFILE. Here I find it's important to "chunk" the shrinks in small sizes. Also, I use a T-SQL DELAY to allow the database to gain back some resources to do other activities as needed. Using the script below you'll need to enter 1.> your database name, 2.> your logical file name of the file, 3.> and the upper and lower bounds, in MB, for your upper database size in MB and 4.> your lower database size in MB. You'll notice that it shrinks in 100 MB increments but, again, you'll need to find the size that's right for you. Warning: larger increments aren't necessarily more speedy. Don't worry "too much" if your lower bounds is too low--it won't/can't shrink past it. (You'll have to forgive my code, it's over 10 years old but still get's the job done).
--Cut and paste the code that this T-SQL generates and run THAT code against your DB
 declare @next int, @last int, @DELAY VARCHAR(255), @SHRINK VARCHAR(1000)

    SELECT @DELAY='WAITFOR DELAY ''000:00:01'''
    select @next=102000, @last=50000
    while(@next >=@last)
    begin
    --print @next
    SELECT @SHRINK ='use [<your DB name>] DBCC SHRINKFILE (N''<enter logical file name>'','+convert(varchar(255),@next)+ ')'
    print @shrink
    print @DELAY
    print 'go'
    select @next=@next-100
    end