SQL : Add non cluster index in SQL table by query

create table tblSample2008
(
id int primary key not null identity(1,1),
name varchar(100),
city varchar(50)
)

CREATE NONCLUSTERED INDEX city_MyCustom_NonclusterIndex
ON tblSample2008 (city asc)



CREATE TABLE [dbo].[tblSample2008](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](100) NULL,
[city] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

generate next number

DECLARE @MAXID INT

SELECT  @MAXID=ID_MAX FROM SDMIS_STUDENT_MASTER_18_19_Mapping_with_dist WHERE Dist_code='2105'

UPDATE SDMIS_STUDENT_MASTER_18_19_SUNDERGARH_2105

 SET @MAXID = ID = @MAXID+1 

where ID is null and assesment_status='N' AND TC_Status IS NULL;

 

DECLARE @MAXXXX INT

SELECT @MAXXXX =MAX(ID) FROM SDMIS_STUDENT_MASTER_18_19_SUNDERGARH_2105

WHERE  assesment_status='N' AND TC_Status IS NULL;

--PRINT @MAXXXX

UPDATE SDMIS_STUDENT_MASTER_18_19_Mapping_with_dist SET ID_MAX=@MAXXXX

WHERE Dist_code='2105'

c# date format dd/MM/yyyy hh:mm AM/PM

today in format : 28/09/2018 7:18 PM

c# code: DateTime.Now.ToString("dd/MM/yyyy h:mm tt")

The Other format for today is as follows


DateTime.Now.ToString("MM/dd/yyyy") Result : 09/28/2018


DateTime.Now.ToString("dddd, dd MMMM yyyy"): Result : Friday, 28 September 2018


DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm:ss"): Result : Friday, 28 September 2018 19:36:45


DateTime.Now.ToString("MM/dd/yyyy HH:mm"): Result : 09/28/2018 19:36


DateTime.Now.ToString("MM/dd/yyyy hh:mm tt"): Result : 09/28/2018 07:36 PM


DateTime.Now.ToString("MM/dd/yyyy H:mm"): Result : 09/28/2018 19:36


DateTime.Now.ToString("MM/dd/yyyy h:mm tt"): Result : 09/28/2018 7:36 PM


DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"): Result : 09/28/2018 19:36:45


DateTime.Now.ToString("MMMM dd"): Result : September 28


DateTime.Now.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss.fffffffK"): Result : 2018’-‘09’-‘28’T’19’:’36’:’45.4574160-07:00


DateTime.Now.ToString("ddd, dd MMM yyy HH’:’mm’:’ss ‘GMT’"): Result : Fri, 28 Sep 2018 19’:’36’:’45 ‘G9T’


DateTime.Now.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss"): Result : 2018’-‘09’-‘28’T’19’:’36’:’45


DateTime.Now.ToString("HH:mm"): Result : 19:36


DateTime.Now.ToString("hh:mm tt"): Result : 07:36 PM


DateTime.Now.ToString("H:mm"): Result : 19:36


DateTime.Now.ToString("h:mm tt"): Result : 7:36 PM


DateTime.Now.ToString("HH:mm:ss"): Result : 19:36:45


DateTime.Now.ToString("yyyy MMMM"): Result : 2018 September

Delete duplicate row in sql , T-SQL: Deleting all duplicate rows but keeping one [duplicate]

  WITH cte AS (
  SELECT child_code,
     row_number() OVER(PARTITION BY child_code ORDER BY child_code) AS 'Duplicatecount'
  FROM SDMIS_STUDENT_MASTER_18_19  where SchoolRegNo='1234567' and Class=6
   and isnull(child_code,'')<>'')
DELETE cte WHERE Duplicatecount > 1

sql date format in dd/MM/yyyy hh:mm:ss

Suppose today date is 26/09/2018  6:42:18:997PM then the related query is as below

SELECT CONVERT(CHAR(10),GETDATE(),103) + ' ' + RIGHT(CONVERT(CHAR(26),GETDATE(),109),14)



Other Date Format: I take today as parameter i.e. 28 Septmber 2018 07:36 PM(Indian Time)
The Format and C# results are as follows.




DateTime.Now.ToString("MM/dd/yyyy") Result : 09/28/2018


DateTime.Now.ToString("dddd, dd MMMM yyyy"): Result : Friday, 28 September 2018


DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm:ss"): Result : Friday, 28 September 2018 19:36:45


DateTime.Now.ToString("MM/dd/yyyy HH:mm"): Result : 09/28/2018 19:36


DateTime.Now.ToString("MM/dd/yyyy hh:mm tt"): Result : 09/28/2018 07:36 PM


DateTime.Now.ToString("MM/dd/yyyy H:mm"): Result : 09/28/2018 19:36


DateTime.Now.ToString("MM/dd/yyyy h:mm tt"): Result : 09/28/2018 7:36 PM


DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"): Result : 09/28/2018 19:36:45


DateTime.Now.ToString("MMMM dd"): Result : September 28


DateTime.Now.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss.fffffffK"): Result : 2018’-‘09’-‘28’T’19’:’36’:’45.4574160-07:00


DateTime.Now.ToString("ddd, dd MMM yyy HH’:’mm’:’ss ‘GMT’"): Result : Fri, 28 Sep 2018 19’:’36’:’45 ‘G9T’


DateTime.Now.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss"): Result : 2018’-‘09’-‘28’T’19’:’36’:’45


DateTime.Now.ToString("HH:mm"): Result : 19:36


DateTime.Now.ToString("hh:mm tt"): Result : 07:36 PM


DateTime.Now.ToString("H:mm"): Result : 19:36


DateTime.Now.ToString("h:mm tt"): Result : 7:36 PM


DateTime.Now.ToString("HH:mm:ss"): Result : 19:36:45


DateTime.Now.ToString("yyyy MMMM"): Result : 2018 September 

all in one windows with proper priority (drop all procedure in a database in sql server,drop all user view in sql server database ,, drop all function in sql server database

GO
---drop all procedure in a database in sql server
declare @userProcedureName varchar(2000)
declare drop_proc_cur cursor
for select [name] from sys.objects where type = 'p'
open drop_proc_cur
fetch next from drop_proc_cur into @userProcedureName
while @@fetch_status = 0
begin
--print @userProcedureName;
   exec('drop procedure [' + @userProcedureName + ']')
   fetch next from drop_proc_cur into @userProcedureName
end
close drop_proc_cur
deallocate drop_proc_cur
GO
GO
-----drop all user view in sql server database 
DECLARE @sqlExecute VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sqlExecute = @sqlExecute + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM   sys.views v

--PRINT @sqlExecute;
EXEC(@sqlExecute);

GO
--drop all function in sql server database 
Declare @sqlExecute NVARCHAR(MAX) = N'';
SELECT @sqlExecute = @sqlExecute + N' DROP FUNCTION '
                   + QUOTENAME(SCHEMA_NAME(schema_id))
                   + N'.' + QUOTENAME(name)
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
print @sqlExecute
Exec sp_executesql @sqlExecute
GO
----drop all user-defined types from sql server database
declare @sqlExecute12 NVARCHAR(MAX) = N'';

SELECT @sqlExecute12 = @sqlExecute12 + N' DROP type '
                   + QUOTENAME(SCHEMA_NAME(schema_id))
                   + N'.' + QUOTENAME(name)
FROM sys.types
WHERE is_user_defined = 1
--print @sqlExecute;
Exec sp_executesql @sqlExecute12

drop all user-defined types from sql server database

drop all user-defined types from sql server database

declare @sqlExecute NVARCHAR(MAX) = N'';

SELECT @sqlExecute = @sqlExecute + N' DROP type '
                   + QUOTENAME(SCHEMA_NAME(schema_id))
                   + N'.' + QUOTENAME(name)
FROM sys.types
WHERE is_user_defined = 1
--print @sqlExecute;
Exec sp_executesql @sqlExecute